limit database updates per hour using timestamps.

[AON07]

what i have so far is data coming from a text box, the user also can select which tables they would like to insert that data into, (up to 4 seperate tables). after its submitted the code checks if the record exists, if not it inserts the data into the correct tables along with the current timestamp..

what i need is after it checks if the record exists. if the record exists i need t check the timestamp compared to the current time.. if the current time is greater than the timestamp by 1 hour (or any other number of minutes i specify. then i want to move that record to the top of list..

if it has Not been greater than the specified time i want to display how many minutes are left before they can move there entry to the top of the list.

Ive never worked with timestamps before can someone please give me some insight. Also the timestamp is stored in the table column "time"

Thanks,

<?php
require('FC_DB_connection.php'); 

//check if form has been submited

if(isset($_POST['submit'])){

//new code sent from form
$textbox_value = ($_POST['code']);
$checkbox = ($_POST['checkbox']);
// To protect MySQL injection
$textbox_value = stripslashes($textbox_value);
$textbox_value = mysql_real_escape_string($textbox_value);
//process data and insert into database

foreach($checkbox as $tablename) {

//check if record exists
$checkcode = "(SELECT count(*) FROM $tablename WHERE code ='$textbox_value')";
$check = mysql_query($checkcode);

$check_result = mysql_fetch_row($check);
$check_result = $check_result['0'];

//need to pull time stamp from database.
//if current time is greater than 1 hour of timestamp.
//then move $textbox_value to top of the list.
//if Not then display how much time is left before hour is up.

if($check_result >0)
{
echo '<table bgcolor="#cccccc" border="1">';
	echo '<tr><td width="400">';
echo "Your code has already been added to the $tablename database.";
echo "</td></tr>";
	echo "</table>";
}
else
{

$query = "INSERT INTO $tablename (code, time) VALUES ('$textbox_value', NOW())";
$result = mysql_query($query) or die(mysql_error());
if($result) {
	echo '<table bgcolor="#cccccc" border="1">';
	echo '<tr><td width="250">';
	echo "code added to $tablename Database";
	echo "</td></tr>";
	echo "</table>";
	
}
else
	echo "could not add code to $tablename Database <br />";
}
}

?>

[nav33n]

What do you mean by

then move $textbox_value to top of the list.

? Top of which list ?

[AON07]

Sorry for the misunderstanding. "$textbox_value" is the variable assigned from the textbox in the form that the user uses to submit there data. I will be displaying a list of all the users codes that are submitted. They will be able to move there code to the top of that list every hour or the amount of time I specify.

[nav33n]

And where will you get this list from ? Anyway,

//need to pull time stamp from database.
//if current time is greater than 1 hour of timestamp.
//then move $textbox_value to top of the list.
//if Not then display how much time is left before hour is up.

I hope you know how to pull the timestamp from the table.
Lets say, its $time1.

$time1 = //value from the table
$time2 = time();
$diff = ($time2 - $time1)/(60*60);
//60*60 is to convert it to hrs 1 hr = 60 mins; 1 min = 60 secs
if($diff > 1) {
// I don't know how you have that list.. I guess its an array ? Well, display it :-/
} else {
echo $diff; //display the difference
}

[AON07]

And where will you get this list from ? Anyway,

Here is the list that is generated.. well its actually (4) seperate lists all pulled from a seperate table in the database.. This list display is a whole other issue because i'm having problems with this as well. but its a start of how i need it to look when it outputs.. "if ya have any suggestions on this as well i'm all ears"

//query 4 different tables
$one_query = "SELECT * FROM table1";  
$two_query = "SELECT * FROM table2";
$three_query = "SELECT * FROM table3";
$four_query = "SELECT * FROM table4";

//execute each query
$one_result = mysql_query($one_query);
$two_result = mysql_query($two_query);
$three_result = mysql_query($three_query);
$four_result = mysql_query($four_query);

//define $color=1
$color="1";

//start table for display
echo '<table align="center" border="0">';
echo '<tr height="40">';
echo "
<th bgcolor='#ffffff' width='200'>Table 1</th>
<th bgcolor='#ffffff' width='200'>Table 2</th>
<th bgcolor='#ffffff' width='200'>Table 3</th>
<th bgcolor='#ffffff' width='200'>Table 4</th>";
echo "</tr>";

//loop through results
while($one_rows=mysql_fetch_array($one_result)){
while($two_rows=mysql_fetch_array($two_result)){
while($three_rows=mysql_fetch_array($three_result)){
while($four_rows=mysql_fetch_array($four_result)){


//if $color==1 table row color = #??????
if($color==1){

//display results in first color(s)
echo "<tr height='30'>

<td bgcolor='#ff6666' align='center'>".$one_rows['code']."</td>
<td bgcolor='#ccffff' align='center'>".$two_rows['code']."</td>
<td bgcolor='#ff99ff' align='center'>".$three_rows['code']."</td>
<td bgcolor='#ffff99' align='center'>".$four_rows['code']."</td>

</tr>";

//set $color==2, for switching to other color
$color="2";
}
else{

//display results in second color
echo "<tr height='30'>
<td bgcolor='#ff9999' align='center'>".$one_rows['code']."</td>
<td bgcolor='#99ccff' align='center'>".$two_rows['code']."</td>
<td bgcolor='#ffccff' align='center'>".$three_rows['code']."</td>
<td bgcolor='#ffff66' align='center'>".$four_rows['code']."</td>

</tr>";
//set $color back to 1
$color="1";
}
}
}
}
}



	echo '</table>';
?>

[nav33n]

Instead of all these, why don't you store the result in an array ? Then, If the time difference > 1, use array_unshift to push the data to the front of the array and print it !