Delete Rows after 10 minutes or so....

Superman702 · August 8, 2011

MySQL: 2.11.4

I'm making something like an Emergency Broadcast Ticker for a project. I have standard users going to index.php and an admin goes to admin.php. With a standard "IF:ELSE" statement, I set up scrolling text to come across for users when there are table entries and standard text to appear if the table is empty. The problem I have is though, I would rather have the row entries for my table expire after a certain amount of minutes (NOT DAYS).

Can anyone help show me how to set up a PHP command that checks to see how old a MySQL row entry is and if it is over a certain amount of time, then to delete it? I'm been surfing for awhile, but I'm not realling finding what I need.

Plus, The IF:ELSE statement isn't working out. I am trying to pull how many rows are currently in my table. Everytime I excute the command, it ALWAYS brings up the first IF statement. I try to have the variable of $num_rows show itself, but it turns out blank. Nothing is in the field Can someone take a look at it?

<HTML>
<HEAD>

<TITLE>Test #2</TITLE>

<?php

// Connects to your Database

$link = mysql_connect("*****", "*****", "*****");

mysql_select_db("*****", $link);

$result = mysql_query("SELECT * FROM Message", $link);

$num_rows = mysql_query("SELECT COUNT * FROM Message, $link");

if($num_rows < 1){

echo $num_rows;

echo "<BR><table width=\"100%\" border=\"0\"> ";

echo "<tr><td width=\"10%\"><CENTER>LA</CENTER></td>";

echo "<td width=\"80%\"><CENTER>Order Menu</CENTER></td>";

echo "<td><CENTER>Blank</CENTER></td>";

echo "</TR></table>";

}

else{

echo $num_rows;

echo "<BR><table width=\"100%\" border=\"0\"> ";

echo "<tr><td width=\"10%\"><CENTER>LA</CENTER></td>";

echo "<td width=\"80%\"><marquee behavior=\"scroll\" direction=\"left\"><B>IMPORTANT MESSAGE: </B>".$info['Main'] . "</marquee></td>";

echo "<td><CENTER>Blank</CENTER></td>";

echo "</TR></table>";

}

?>

</HEAD>

<BODY>

MasterACE14 · August 8, 2011

$num_rows = mysql_query("SELECT COUNT * FROM Message, $link");

should be

$num_rows = mysql_num_rows($result);

Superman702 · August 8, 2011

Awesome. That solved the 2nd half. Thanks MasterACE14!

MasterACE14 · August 8, 2011

That solved the 2nd half.

You're welcome. You haven't posted the code for the first half?

Superman702 · August 8, 2011

I'm still working on that one site. I'm not getting the data to come out right it looks like. It's not pulling the data from the MySQL right I think. Right after IMPORTANT MESSAGE: it comes up blank.

Plus I was looking for help with this too:

Can anyone help show me how to set up a PHP command that checks to see how old a MySQL row entry is and if it is over a certain amount of time, then to delete it? I'm been surfing for awhile, but I'm not realling finding what I need.

MasterACE14 · August 8, 2011

I'm still working on that one site. I'm not getting the data to come out right it looks like. It's not pulling the data from the MySQL right I think. Right after IMPORTANT MESSAGE: it comes up blank.

You have a problem with your logic. The 'if' statement checks if there IS any records. Otherwise the 'else' runs when you know there is NO records. So obviously $info['Main'] won't display anything. Also you haven't fetched the data in the first place...

$info = mysql_fetch_assoc($result);

Superman702 · August 8, 2011

yeah, I kind of caught that after I went back to it. So I ended up changing it to:

if($num_rows = 0)

So if there are no rows, it would display normal text. Else would display the message. But it still comes up blank. I think I have a type-o somewhere.

MasterACE14 · August 8, 2011

try this:

<?php 
// Connects to your Database 
$link = mysql_connect("*****", "*****", "*****"); 
mysql_select_db("*****", $link); 
$result = mysql_query("SELECT * FROM Message", $link); 
$num_rows = mysql_num_rows($result); 
if($num_rows == 0){ 
echo 0;
echo "<BR><table width=\"100%\" border=\"0\"> "; 
echo "<tr><td width=\"10%\"><CENTER>LA</CENTER></td>"; 
echo "<td width=\"80%\"><CENTER>Order Menu</CENTER></td>"; 
echo "<td><CENTER>Blank</CENTER></td>"; 
echo "</TR></table>"; 
} 
else{ 
$info = mysql_fetch_assoc($result);
echo $num_rows;
echo "<BR><table width=\"100%\" border=\"0\"> "; 
echo "<tr><td width=\"10%\"><CENTER>LA</CENTER></td>"; 
echo "<td width=\"80%\"><marquee behavior=\"scroll\" direction=\"left\"><B>IMPORTANT MESSAGE: </B>".$info['Main'] . "</marquee></td>"; 
echo "<td><CENTER>Blank</CENTER></td>"; 
echo "</TR></table>"; 
}

?>

Can anyone help show me how to set up a PHP command that checks to see how old a MySQL row entry is and if it is over a certain amount of time, then to delete it? I'm been surfing for awhile, but I'm not realling finding what I need.

with this you can use a 'timestamp' field in your table, and compare the timestamp to the current time.

Superman702 · August 8, 2011

I copied and pasted your code and it worked perfectly. Thank you so much for your help!

As for the timestamp idea...yeah I could add a timestamp. How would I set up the DELETE ROW command for PHP to read it to MySQL?

MasterACE14 · August 8, 2011

	
        // Delete Time - 5 mins old.
$5mins = time()-60*5; // current time - (60 seconds x 5)
        
        // delete all records older than 5mins
mysql_query("DELETE FROM `table` WHERE `time` < '$5mins'"); // have the time field as an INT, personally I find it easier to work with than a timestamp.

       // insert new records with time...
      $timeToInsert = time();

Superman702 · August 8, 2011

Truly awesome! Thank you so much!!!!!

MasterACE14 · August 8, 2011

no worries, glad I could help.

Psycho · August 8, 2011

You know, there really is no need to DELETE the rows to get what you want to accomplish. In fact, there are very good reasons NOT to delete the data (e.g. historical purposes).

Instead of deleting the records, just set up your SELECT queries to only get the records that are less than 10 minutes old, for example

SELECT *
FROM table
WHERE TIMESTAMPDIFF(MINUTE, `time`, NOW()) < 10

phpSensei · August 8, 2011

Regarding mjdamato's reply,

This may not be related to the deleting a query based on a time interval but

I woud actually have a field called 'active' or 'deleted' or 'w.e', so instead of deleting it I update this column to true or false instead of deleting them.

Psycho · August 8, 2011

Regarding mjdamato's reply,

This may not be related to the deleting a query based on a time interval but

I woud actually have a field called 'active' or 'deleted' or 'w.e', so instead of deleting it I update this column to true or false instead of deleting them.

Actually deleting the record or only marking it as deleted is about the same amount of work and is not necessary. In fact, logically (at least how I read this) the records shouldn't be deleted so much as they should "expire". And, even so, having a process to explicitly "expire" the records is unnecessary. By changing the SELECT queries you don't need any automated processes to delete/expire the records.

Superman702 · August 9, 2011

So I should have my page select only those entries that I want displayed, rather than going thru and delete them then? It isn't going to cause a memory issue or take longer to pull the information from the database...is it?

Psycho · August 9, 2011

Everything has a cost. How many notifications will you have each day and how many concurrent requests will you have? Even if your answer to both of those is A LOT, you are still better off only selecting the records you want to display and then creating an automated process to delete records once a week or some other infrequent time period as is needed. You then get only the records you want displayed and you don't incur the unnecessary overhead of having to constantly delete records throughout the day. Doesn't that make more sense?

Superman702 · August 9, 2011

That does make more sence and I see your point. This is going to be used at a place of business as well and keeping track of the message could be benfical. I'm going to try to change my code to include that...It may take me an hour or so to figure it out. I'll post again soon....

Superman702 · August 16, 2011

So I tried to change:

$result = mysql_query("SELECT * FROM Message", $link); 
$mins = time()-60;
mysql_query("DELETE FROM `Message` WHERE `Timestamper` < '$mins'");
$num_rows = mysql_num_rows($result);

to

$mins = time()-60;
$result = mysql_query("SELECT * FROM main WHERE `Timestamper` < '$mins'", $link);
$num_rows = mysql_num_rows($result);

and the message is not going across being displayed anymore. Is there a different way I should be doing this to be selecting rows that were created within the last 60 seconds? It works great when I delete the rows after 60 seconds, but I don't know how to just use the messages within the last 60 seconds, rather then deleting them.

Superman702 · August 16, 2011

$mins = time()-60;
$result = mysql_query("SELECT * FROM main WHERE Timestamper <= '$mins'", $link);
$num_rows = mysql_num_rows($result); 
if($num_rows == 0){ 
echo "<table width=\"100%\" border=\"0\"> "; 
echo "<tr><td width=\"10%\"><CENTER></CENTER></td>"; 
echo "<td width=\"80%\"><CENTER>Main Menu</CENTER></td>"; 
echo "<td><CENTER></CENTER></td>"; 
echo "</TR></table>"; 
} 
else{ 
$info = mysql_fetch_assoc($result);
echo "<table width=\"100%\" border=\"0\"> "; 
echo "<tr><td width=\"10%\" bgcolor=\"yellow\"><CENTER><font color=\"RED\"><B>ALERT</B></FONT></CENTER></td>"; 
echo "<td width=\"80%\" bgcolor=\"red\"><marquee behavior=\"scroll\" direction=\"left\"><B><font color=\"white\">Special Message: </B>".$info['Main'] . "</marquee></font></td>"; 
echo "<td width=\"10%\" bgcolor=\"yellow\"><CENTER><font color=\"RED\"><B>ALERT</B></FONT></CENTER></td>";; 
echo "</TR></table>";

Tried this one and the message doesn't go away now. I can't win...haha

Psycho · August 16, 2011

$mins = time()-60;
$result = mysql_query("SELECT * FROM main WHERE `Timestamper` < '$mins'", $link);
$num_rows = mysql_num_rows($result); 
and the message is not going across being displayed anymore. Is there a different way I should be doing this to be selecting rows that were created within the last 60 seconds? It works great when I delete the rows after 60 seconds, but I don't know how to just use the messages within the last 60 seconds, rather then deleting them.

What kind of field is 'Timestamper'? A MySQL timestamp field is a different type of value than a PHP timestamp - so you can't compare them directly, you will need to convert one or the other. Also, that logic seems backwards. That query is trying to pull all records LESS than '$mins' (which is now - 60 seconds) - which would be all records OLDER than '$mins'.

Please state what field type 'Timestamper' is and show the code you are using to set it or are you just using the MySQL auto-opulate with the date the record is created?

Superman702 · August 17, 2011

The Timestamper field is a INT field. It is taking time() exactly as it is when it is submitted.

<HTML>
<HEAD>
<meta http-equiv="REFRESH" content="6;url=interface.php">
</HEAD>
<BODY>
<? 
$Main=$_POST['Main']; 
$Timestamper=time();
mysql_connect("localhost", "root", "******") or die(mysql_error()); 
mysql_select_db("message") or die(mysql_error()); 
mysql_query("INSERT INTO `main` VALUES ('$Main', '$Timestamper')"); 
Print "Your message will now be broadcasted."; 
?> 
</BODY>
</HTML>

My database is set up with the same name a lot. Just to clear the air:

Database Name: message

Table Name: main

Field Names (in order): Main varchar(200) and Timestamper int(11).

I will update the field names, but I was just trying to get something up and running. I'm sorry for the same name field if it confuses anyone.

This is the whole interface.php file that holds the form that submits the information:

<? 
session_start();
if(!session_is_registered(myusername)){
header("location:main_login.html");
}
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="******"; // Mysql password 
$db_name="calltracker"; // Database name 
$tbl_name="members"; // Table name 
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$data = mysql_query("SELECT * FROM main");

$adminstatus=$_SESSION['admin'];
?> 
<html>
<head>
<style type="text/css">
body { font: 14px/1.3 verdana, arial, helvetica, sans-serif }
h1 { font-size:1.3em }
h2 { font-size:1.2em }
a:link { color:#33c }    
a:visited { color:#339 }    

#buffer { position:absolute; visibility:hidden; left:0; top:0 }
div#display { 
    margin: 2em 1em 2em 0;
    border: none;
    padding:6px 8px;
    display:none; /* until loaded */
}
</style>
<title>Main Menu</title>
<BODY BACKGROUND="background.jpg" STYLE="background-repeat:no-repeat;">
<script src="dw_loader.js" type="text/javascript"></script>
</head>
<body>
<BR>
<div id="display"></div>
<iframe id="buffer" name="buffer" src="top.php" onload="dw_displayExternal()"></iframe>
<?
if($adminstatus == 1){ 
echo "Welcome " .$_SESSION['nickname']. " | Standard Member ";
echo "<a href=\"logout.php\">Logout</A>";
}
if($adminstatus == 2){ 
echo "Welcome " .$_SESSION['nickname']. " | Supervisor ";
echo "<a href=\"logout.php\">Logout</A>";
echo "<BR><BR><B><CENTER>Main Menu Alert System</CENTER></B><BR>";
echo " <form action=\"process.php\" method=\"post\">";
echo " Message to Display: <input type=\"text\" name=\"Main\"> <input type=\"submit\" value=\"Submit\"> <BR></form>"; 
}
if($adminstatus == 3){ 
echo "Welcome " .$_SESSION['nickname']. " | Manager";
echo "<a href=\"logout.php\">Logout</A>";
echo "<BR><BR><B><CENTER>Main Menu Alert System</CENTER></B><BR>";
echo " <form action=\"process.php\" method=\"post\">";
echo " Message to Display: <input type=\"text\" name=\"Main\"> <input type=\"submit\" value=\"Submit\"> <BR></form>"; 
echo " <a href=\"add.php\">Add User</A>";
}

?>
<BR>

</body>
</html>

This is top.php, which displays the message submitted. This is the main file I believe I am having problems with. I am unsure how to display messages within the database that were submitted only 60 seconds ago (or whatever time I set in the future.)

<HTML>
<HEAD>
<TITLE>v2.0</TITLE>
<meta http-equiv="refresh" content="20"> 
</head>
<BODY>
<?php 
// Connects to your Database 
$link = mysql_connect("localhost", "root", "******"); 
mysql_select_db("message", $link); 
$mins = time()-60;
mysql_query("DELETE FROM `main` WHERE `Timestamper` < '$mins'");
$result = mysql_query("SELECT * FROM main", $link); 
$num_rows = mysql_num_rows($result);
if($num_rows == 0){ 
echo "<table width=\"100%\" border=\"0\"> "; 
echo "<tr><td width=\"10%\"><CENTER></CENTER></td>"; 
echo "<td width=\"80%\"><CENTER>Main Menu</CENTER></td>"; 
echo "<td><CENTER></CENTER></td>"; 
echo "</TR></table>"; 
} 
else{ 
$info = mysql_fetch_assoc($result);
echo "<table width=\"100%\" border=\"0\"> "; 
echo "<tr><td width=\"10%\" bgcolor=\"yellow\"><CENTER><font color=\"RED\"><B>ALERT</B></FONT></CENTER></td>"; 
echo "<td width=\"80%\" bgcolor=\"red\"><marquee behavior=\"scroll\" direction=\"left\"><B><font color=\"white\">Message: </B>".$info['Main'] . "</marquee></font></td>"; 
echo "<td width=\"10%\" bgcolor=\"yellow\"><CENTER><font color=\"RED\"><B>ALERT</B></FONT></CENTER></td>";; 
echo "</TR></table>"; 
}

?> 
</BODY>
</HTML>

I am making somewhat of an Emergency Alert System type ticker. A message is displayed when there has been one submitted within a certain amount of time ago. After the message is X number of minutes old, it will stay in the database (rather than deleted like at the start of the discussion...but I was convinced to try to keep the messages for record keeping purposes, which makes more sence) but not be displayed. I figured the code out on how to delete it if it is after a certain amount of time, but I don't know how to select only the ones in the database that are X number of minutes old. I tried several different type of codes (seen above) and looked at the PHP site, but I am still not doing something right. I think it may be within my logic somewhere.

This works just fine:

mysql_query("DELETE FROM `main` WHERE `Timestamper` < '$mins'");

But when I remove the code above and replace it with this, nothing is ever displayed:

$result = mysql_query("SELECT * FROM main WHERE `Timestamper` < '$mins'", $link);

Psycho · August 17, 2011

Sorry, I'm not going to read through all that code. But, just looking at the first code block and your response, I will make the following suggestion. Don't use PHP timestamps as your value for the records. You then lose the ability to use the built-in functions within MySQL to work with data by date. You are making this harder than it needs to be.

Just create a MySQL datetime or timestamp field that is autopopulated with the current timestamp when the record is created. Then you don't even need to include the field in your INSERT statements. But, if you want to stick with what you have:

This will delete all records older than $mins

mysql_query("DELETE FROM `main` WHERE `Timestamper` < '$mins'");

This will SELECT all records older than $mins

$result = mysql_query("SELECT * FROM main WHERE `Timestamper` < '$mins'", $link);

If you are running the DELETE before the SELECT then you will get nothing because you just deleted all the records you are trying to select. But, going back to my original statements in this thread, why are you trying to delete all the records older than 10 minutes? Let them stay and only select the records that are UP TO 10 minutes old. Then - if you need to - implement a weekly (?) process to purge old records.

Superman702 · August 18, 2011

I don't know if it was just an error with my code or what....I copy and pasted your code and it worked exactly as it should. I also renamed my database and tables. Perhaps the code was confused with multiple same names of databases or tables. I'll work on a process or a purge php page for admins to access to delete prior messages is need be.

Thanks again for your help!

Sign In

Delete Rows after 10 minutes or so....

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information