• 0

Sorry guys, I have another DB question for you


Question

Please pardon me, but I am very new to this stuff.

OK, I have a database table that looks like this:

??+----+------+------+------------+----------------+------+

??|?ID?|?FROM?|?TO???|?MESSAGE????|?DATE???????????|?SEEN?|

??+====+======+======+============+================+======+

??|??1?|?Jim??|?Lisa?|?I?love?you?|?20030128172358?|?1????|

??+----+------+------+------------+----------------+------+

??|??2?|?Lisa?|?Jim??|?F.?Off!????|?20030128225611?|?1????|

??+----+------+------+------------+----------------+------+

??|??3?|?Jim??|?Lisa?|?Whatever!??|?20030129004810?|?0????|

??+----+------+------+------------+----------------+------+

??|??4?|?Will?|?Ash??|?Hey!???????|?20030129143814?|?2????|

??+----+------+------+------------+----------------+------+

??|??5?|?Ash??|?Andy?|?Hi?????????|?20030129181707?|?0????|

??+----+------+------+------------+----------------+------+

??|??6?|?Andy?|?Ash??|?Hello?Ash??|?20030130024258?|?5????|

??+----+------+------+------------+----------------+------+

It holds messages from people to other people, and it also holds the number of times the messages is viewed, as exampled in the SEEN column. When the user loads their message, I increment the SEEN field.

The problem that I'm having is that when I update the SEEN field, it also updates the DATE field. The DATE field should only be updated when the message is firsts added to the table.

Here's the code that I am using to update the SEEN field when a message is viewed:

$MessageID = $post_data["ID"];
$MessageSeen = $post_data["SEEN"];
$MessageSeen++;
echo "This message has been loaded ".$MessageSeen." Times.";
$sSQL = "UPDATE messages set SEEN=$MessageSeen where ID=$MessageID";
$q->query($DB,$sSQL);

Any thoughts? Thanks.

Ash

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

don't use the MySQL TIMESTAMP field. Use time() to insert the unix timestamp (or UNIX_TIMESTAMP() in SQL) to a INT field. As for the SQL statement, try this instead:

UPDATE messages set SEEN=SEEN+1 where ID=$MessageID

Works just as well with a little less PHP (plus you don't have to select out the previous value if you don't need to :) )

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.