Page 1 of 1
Update database on page load.... how?
Posted: Thu Jul 15, 2010 10:57 am
by tom8521
When a user loads a page, I would like a simple database update to occur, but am unsure how. Can you help me.
I need this to happen:
I want to UPDATE the MAIL table, and change the STATUS to NULL WHERE ID = $id.
Re: Update database on page load.... how?
Posted: Thu Jul 15, 2010 2:10 pm
by califdon
tom8521 wrote:When a user loads a page, I would like a simple database update to occur, but am unsure how. Can you help me.
I need this to happen:
I want to UPDATE the MAIL table, and change the STATUS to NULL WHERE ID = $id.
What is it that you are trying to do? You mean you want to update the database every time a search engine bot hits your site? Why? In any case, the page must be a PHP script and you simply update the database. It would also be possible to call a special script in the onLoad event of the HTML page, but the result would be the same. It comes back to "what is it that you are trying to do?"
Re: Update database on page load.... how?
Posted: Sat Jul 17, 2010 12:01 pm
by tom8521
I have a messaging system on my website, and I wanted the database to update when the page loads saying the message had been read.
In the database new messages have a '1' in the 'Status' field, so I wanted the '1' to be removed after the message had been read/page had been loaded.
Re: Update database on page load.... how?
Posted: Sat Jul 17, 2010 12:52 pm
by califdon
OK, so a user will login and ask to view a waiting message, and you want to status its record as "read"? I would simply update the database at the same time the message is queried (immediately after, to be technically accurate). You could get picky and say, "but it hasn't yet been loaded," but the fact is that you can never insure that the recipient has actually READ the message, only that it has been requested. There's no reason to get into the page loading and the extra code that would require. In the same logic that queries the message, add an Update query that statuses the record.
Re: Update database on page load.... how?
Posted: Sat Jul 17, 2010 4:10 pm
by tom8521
Well, as I am not the most skilled of PHP developers you may need to help me out once again. Here is the stuff that displays the messages, but I am unsure of how to tell it to update the table, removing the 1 from the Viewed table row.
Can you help?
Code: Select all
$result2 = mysql_query("SELECT * FROM mail WHERE To_user = '$user' AND Id = '$msgid' ORDER BY Date desc");
while($rowA = mysql_fetch_array($result2))
{
echo "<div id=\"inbox-detail\">From:" . $rowA['From_user'] . "<br />";
echo "Received on:" . $rowA['Date'] . "<br /><br />";
echo "Subject:<div id=\"inbox-indent\">" . $rowA['Subject'] . "</div><br /><br />";
echo "Message:<div id=\"inbox-indent\">" . $rowA['Message'] . "</div><br /></div>";
echo "-----------------------";
echo "<br /><br />";
}
Re: Update database on page load.... how?
Posted: Sat Jul 17, 2010 4:28 pm
by Da_Elf
Code: Select all
$result2 = mysql_query("SELECT * FROM mail WHERE To_user = '$user' AND Id = '$msgid' ORDER BY Date desc");
while($rowA = mysql_fetch_array($result2))
{
$sql="UPDATE mail
SET Read ='1'
WHERE message_ID = $rowA['message_ID'] ";
mysql_query($sql);
echo "<div id=\"inbox-detail\">From:" . $rowA['From_user'] . "<br />";
echo "Received on:" . $rowA['Date'] . "<br /><br />";
echo "Subject:<div id=\"inbox-indent\">" . $rowA['Subject'] . "</div><br /><br />";
echo "Message:<div id=\"inbox-indent\">" . $rowA['Message'] . "</div><br /></div>";
echo "-----------------------";
echo "<br /><br />";
}
[/quote]
that would be the simplest way to make sure each item displayed in the while loop are updated in the database as read (i dont know your exact database names)
Re: Update database on page load.... how?
Posted: Sat Jul 17, 2010 4:31 pm
by buckit
I think the complexity is not being able to see your design.
what califdon is saying is when you query the message from the database you can also update that same database entry. (califdon, sorry if I am incorrect)
so based on your submitted code, you simply need to add the following into your while loop:
Code: Select all
mysql_query("UPDATE mail SET STATUS = NULL WHERE ID = {$rowA['ID']}");
in your SELECT statement you are specifying you ID. This intent is to return the single message, correct? if so then there is no need for the While. but it does work.
another thought to throw at you... rather than using 1 or NULL in your status field... I would recommend using 1 or 0. True or False. If its been read then 1 if not read then 0.
Re: Update database on page load.... how?
Posted: Sat Jul 17, 2010 4:40 pm
by tom8521
I feel that this is slow close to working, but something still isn't quite right. I don't get any errors, the page loads fine, but it just isn't updating the database still.
Could it possibly be because I am using a $variable in the statement? - Look at the code and I think you will see what I mean.
Code: Select all
$result2 = mysql_query("SELECT * FROM mail WHERE To_user = '$user' AND Id = '$msgid' ORDER BY Date desc");
while($rowA = mysql_fetch_array($result2))
{
$sql="UPDATE mail
SET Viewed ='0'
WHERE Id = [b]$msgid[/b]";
mysql_query($sql);
echo "<div id=\"inbox-detail\">From:" . $rowA['From_user'] . "<br />";
echo "Received on:" . $rowA['Date'] . "<br /><br />";
echo "Subject:<div id=\"inbox-indent\">" . $rowA['Subject'] . "</div><br /><br />";
echo "Message:<div id=\"inbox-indent\">" . $rowA['Message'] . "</div><br /></div>";
echo "-----------------------";
echo "<br /><br />";
}
Re: Update database on page load.... how?
Posted: Sat Jul 17, 2010 5:18 pm
by tom8521
Actually, things are working! Thanks guys. It was the answer from buckit that worked this time.