Modify row

[Jabbamonkey]

My old message asked how to add a new row. (see http://www.codingforums.com/showthread.php?s=&threadid=4157). Now I'd like to know of a way to modify current rows. For example, I created a form that automatically fills in the fields (with the database information). But, when I try to modify current rows, nothing happens.

I tried using the same code when I added a new row:

INSERT INTO advisoryarticles(article_date,article_title,articl
e_url,bioid,article_source) VALUES('$article_date','$article_title','$article_
url','$bioid','$article_source');

But this didn't work. Is there something else I should be doing?
thanks for the help.


Jabbamonkey

[mordred]

Well, that couldn't work, because INSERT just does what it says: It inserts new rows into a table. What you are searching for is the UPDATE command, like

UPDATE 
    advisoryarticles 
SET 
    article_date = '$article_date',
    article_title = '$article_title',
    article_url = '$article_url',
    bioid = '$bioid',
    article_source = '$article_source'
WHERE
    article_id = '$article_id'

There's plenty of info in the mysql docs:
http://www.mysql.com/documentation/m...ce.html#UPDATE

BTW, wouldn't that question be more suited in the mysql forum?

[Jabbamonkey]

I tried that using the following (different table and variables)

mysql_query("
UPDATE
sciadvisoryarticles
SET
sciarticle_date = '$sciarticle_date',
sciarticle_title = '$sciarticle_title',
sciarticle_url = '$sciarticle_url',
sciarticle_member = '$advisory_id',
sciarticle_source = '$sciarticle_source'
WHERE
sciarticle_id = '$sciarticle_id')",$db);

I checked the variables (i.e. $sciarticle_title) before, and after running this query, but nothing changed in the database. Can you see anything I'm doing wrong?

Jabbamonkey

[mordred]

Yep, there is an orphan parentheses at the end of your SQL query. Remove it and see what happens then.

If it's still not working, put your query into a variable called $sql, and try these lines

PHP Code:

if (!$result) {
  echo "Error: " . mysql_error() . "<br />";
  echo "Query was: $sql";
} else {
  echo "Updated rows: " . mysql_affected_rows();
} 


after you send the query to the mysql database. They should provide you with adequate info as to what's not working properly. It can be that your query executes correctly, but due to the conditions described in the WHERE clause no matching rows were found and thus no update took place.

EDIT: Of course you have to assign the return value of mysql_query() to a variable called $result or the above won't work, like $result = mysql_query(...).

[Spookster]

Moving this to the mysql forum...

[Jabbamonkey]

Worked perfectly.

How do you set NULL values?

Before updating the row, I tried the following

if (!$sciarticle_source)
{
$sciarticle_source = "NULL";
}

But, unfortunately, that didn't make the field null, it gave it the value "NULL".

Any ideas?

Jabbamonkey

[mordred]

Setting a field to NULL? Well, have you tried

UPDATE tablename
SET fieldName = NULL
WHERE ...

I think that should work, you'll have to write that into the SQL query though, not in your PHP code.

[Jabbamonkey]

Is there a way to do it using php? As I said, I attemped to set the variables before entering them into the query. Since I don't know if the value will be Null or not, I need to set it to Null if and only if they meet certain conditions.

I know I could setup a few if statements, and a few different queries, but I'd like to keep it as simple as possible. Let me know if you have a simpler solution...

Thanks.

Jabbamonkey


// ###############
// SET NULL VALUES
// ###############

if (!$sciarticle_date || $sciarticle_date == '0000-00-00')
{
$sciarticle_date = "NULL";
}
if (!$sciarticle_source)
{
$sciarticle_source = "NULL";
}

// ###############
// UPDATE ROWS
// ###############


mysql_query("
UPDATE
sciadvisoryarticles
SET
sciarticle_date = '$sciarticle_date',
sciarticle_title = '$sciarticle_title',
sciarticle_url = '$sciarticle_url',
sciarticle_member = '$advisory_id',
sciarticle_source = '$sciarticle_source'
WHERE
sciarticle_id = '$sciarticle_id'",$db);

[mordred]

Not really another idea, but have you made sure your database application is normalized? For me it seems you're having rows that may or may not contain chunks of data, perhaps your better served with separating that information that's optional to another table.

Another possiblity would be to only set those fields in the query that correspond to the variables you get, but that would also leave you with some if-clauses to set the right SQL into the variables, e.g:

if (cond1 == true) {
$article = "sciarticle = '$sciarticle',"
}

...
UPDATE
sciadvisoryarticles
SET
sciarticle_date = '$sciarticle_date',
$article
$articleB
...



Hmmh, if I think about it, it's just the other way round how you have it at the moment. So I'm not all to sure my musings are of any real help here.