Welcome to WebmasterWorld Guest from 54.205.170.21

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

simple update command gone wrong

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

10+ Year Member



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

?>

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

WebmasterWorld Senior Member 10+ Year Member



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)

10+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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.

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

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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

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

10+ Year Member



But yet I can do this

UPDATE user_mail SET id=99 WHERE id=1

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

10+ Year Member



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)

10+ Year Member



hpche got it, those sneaky reserved words [mysql.com]!
10:17 pm on Aug 12, 2003 (gmt 0)

10+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month