View Full Version : Update Private Messages Table

03-09-2007, 08:41 PM

I'm a newbie when it comes to PHP code, so please bear with me. I'm getting nowhere with my codes. I've coded a private message system for my site, but the problem is, it doesn't update the MySQL database with "set read = 1 where id =bla". When someone clicks the "read message" button, it'll go to a different page, with this code on top:

$query = "UPDATE private_messages SET read = 1 WHERE id = '".$_GET['id']."'";

I simply wish to set the '0' to '1', so I can use that as 'This message has been read' and to get the total new messages from the database.

I've also tried another code:


$result = mysql_query("SELECT * FROM private_messages WHERE id = '$id' LIMIT 1") or die(mysql_error());

while($row = mysql_fetch_array($result)){

$update = mysql_query("UPDATE private_messages SET read = 1 WHERE id = '$id' LIMIT 1") or die(mysql_error());


The above code for my downloads, when I wish to add a '1' to the total count. But it doesn't set the 'read' part to 1.

That's that. It simply doesn't work. As for the other thing I cannot get:

$count = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM private_messages WHERE read = 0 AND userid='".$_SESSION['user_id']."'"),0);

It works when I leave the 'WHERE read = 0' part out. It also doesn't work when I try WHERE 'bla' = 'bla' AND WHERE 'bla' = 'bla'. It's kinda confusing, since I cannot find the solution to my problem anywhere.

Many thanks for any help in advance...

03-09-2007, 08:52 PM
For your first one try echo'ing the query before you execute it to make sure it's properly formatted, and use "or die(mysql_error());" like you did in the other query.
For the last one use this code to find out the problem(or echo the query):

$result = mysql_query("SELECT COUNT(*) FROM private_messages WHERE read = 0 AND userid='".$_SESSION['user_id']."'") or die(mysql_error());
$count = mysql_result($result, 0) or die(mysql_error());

03-09-2007, 08:59 PM

I've just tested the code you posted and I'm ending up with this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read = 0 AND userid='1'' at line 1

I've also had that before, weird considering it should indeed work.

03-09-2007, 09:13 PM
Try this:

$count = mysql_result(mysql_query("SELECT COUNT(*) FROM `private_messages` WHERE `read` = '0' AND `userid` = '".$_SESSION['user_id']."'"),0);

03-10-2007, 10:15 AM

That helps. Not sure why though, since the other one should have worked also. Ah, whatever works is good enough for me:thumbsup:

As for the update stuff, I'll try some more things before I post here again. Maybe I could figure it out myself, I'm in a good state right now and everything seems to work out...

Thanks again for the help!