simple update command gone wrong

[bobnew32]

Ok this problem is really pissing me off. When I load a page, its supposed to go in the database and update the number from 0 to 1 so that the page in the future now knows it has been read.

So I have the following code (the id is stored in the url)

<?php

$conn = mysql_connect("localhost", "host", "pw");
$db = mysql_select_db("datbase");
$id = $_GET['id'];

$sql= "update user_mail set read= 1 where id= $id";
$result= mysql_query($sql);

?>

[dmorison]

What error message are you getting?

If it's just a case of "nothing happens", have you verified the value of $id with an echo() statement?

[bobnew32]

Yes I have verified it. I did print $id and it printed out the id of one which I wanted to change.

Edit: well it printed out 1 not one

[dmorison]

Ok,

Next thing is to check the value of mysql_affected_rows following your query. Remember that affected rows will be zero if there is no _acutal_ change to the row (i.e. the value was already set to "1".)

Alternatively, when faced with a stubborn query I usually head straight into Webmin (I don't know if phpMyAdmin allows direct SQL query) and check it from there).

You can also enter the query directly into the mysql binary if you're on *nix.

[RobinC]

How I'm doing my site, I've got a counter for every page, and every page is a MySQL table, with "name" and "count" elements (ie, not far off from yours i suspect). I make the update seperate from the read this way -

// Lets start with the page counter, get and update...
$result = mysql_query("SELECT * FROM pages WHERE name='$page'");
$page = mysql_fetch_object($result);
mysql_query("UPDATE pages SET count=count+1 WHERE name='$page->name' LIMIT 1");

No doubt it can be improved on, but it works (and I did verify that in phpMyAdmin ;-P )

[bobnew32]

Ok I tried the method of testing the query in phpmyadmin and it didn't work!

Here is what I input:
UPDATE user_mail SET read= '1' WHERE id= '1'

user_mail is definitely the table, and read is an int(1) default 0

id is an int primary auto_increment

[mavherick]

drop the single quotes, those are for text fields not for numbers like you're set up.

try this:

UPDATE user_mail SET read=1 WHERE id=1

[bobnew32]

I get this error:

Database database_name - table user_mail
Error
SQL-query : [Edit]

UPDATE user_mail SET read=1 WHERE id=1

MySQL said:

You have an error in your SQL syntax near 'read=1 WHERE id=1' at line 1

[bobnew32]

But yet I can do this

UPDATE user_mail SET id=99 WHERE id=1

[hpche]

Maybe it has something to do with 'read' being a SQL reserved word? If you try re-naming that colum to something different it might solve the problem, at a guess?

[mavherick]

hpche got it, those sneaky reserved words [mysql.com]!

[bobnew32]

You guys are the best, I renamed it and all my script began to work! Thx again!