homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

simple update command gone wrong

 7:19 pm on Aug 12, 2003 (gmt 0)

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)


$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);




 7:22 pm on Aug 12, 2003 (gmt 0)

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?


 7:32 pm on Aug 12, 2003 (gmt 0)

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


 7:40 pm on Aug 12, 2003 (gmt 0)


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.


 8:15 pm on Aug 12, 2003 (gmt 0)

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 )


 8:27 pm on Aug 12, 2003 (gmt 0)

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


 8:30 pm on Aug 12, 2003 (gmt 0)

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


 8:32 pm on Aug 12, 2003 (gmt 0)

I get this error:

Database database_name - table user_mail
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


 8:34 pm on Aug 12, 2003 (gmt 0)

But yet I can do this

UPDATE user_mail SET id=99 WHERE id=1


 8:46 pm on Aug 12, 2003 (gmt 0)

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?


 9:19 pm on Aug 12, 2003 (gmt 0)

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


 10:17 pm on Aug 12, 2003 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved