Automatic TimeStamp Actions

[Techykid3]

Do you mean your server? I'm assuming you're running a cron job on Apache. If you want to delete records, this is the only way to do it short of configuring MySQL events (as I mentioned before).

Personally, I would do this a much more elegant way. I'm not clear on how your system works, but I would set it up so that when a person tries to vote, their vote will be accepted again IF the timestamp is after the 24 hour time period, and not allow them if it's during the period.

It's a much faster, more elegant and least resource-hungry option for you.

Thats EXACTLY, what I want! How do I do that?

[keyboard]

In your sql database, can two people have the same name. Are you getting their name from a form input are from a login cookie?

Also do you have a unique id column?

This is a basic kind of script. Is this what your looking for. You would have to fill in the details.

<?php
 mysql_connect("localhost", "username", "password") or die(mysql_error()); 
 mysql_select_db("database") or die(mysql_error()); 

$result = mysql_query("SELECT * FROM `table` WHERE `name`='their name'") or die(mysql_error());  
$row = mysql_fetch_array( $result );

$math = time();
$science = $row['time'];
$english = $math-$science;

if ( $english < "10800" ) {
    
die(' You have already submitted a vote within the last twenty four hours');

} else{

}
?>

I think it should work. If it won't, let me know.

[JShor]

I don't know what your current code/system even looks like. Please post the code you currently have and your table structure, and we'll work from there.

[Brillig]

Generally good practices is never to delete any records, except for certain specific circumstances.

Easiest thing to do is set up a VIEW that excludes anything older than a day based on timestamp and use that for your SELECTS.

[Techykid3]

In your sql database, can two people have the same name. Are you getting their name from a form input are from a login cookie?

Also do you have a unique id column?

This is a basic kind of script. Is this what your looking for. You would have to fill in the details.

<?php
 mysql_connect("localhost", "username", "password") or die(mysql_error()); 
 mysql_select_db("database") or die(mysql_error()); 

$result = mysql_query("SELECT * FROM `table` WHERE `name`='their name'") or die(mysql_error());  
$row = mysql_fetch_array( $result );

$math = time();
$science = $row['time'];
$english = $math-$science;

if ( $english < "10800" ) {
    
die(' You have already submitted a vote within the last twenty four hours');

} else{

}
?>

I think it should work. If it won't, let me know.

How do I get my time column the same as your format?
No I do not have a unique ID column
They put their username in a field, no verification, because it doesn't need it yet.
No, two people can not use the same username twice, until the username is pruned for outdateness (24 hours from the time they are entered into the table.

I don't know what your current code/system even looks like. Please post the code you currently have and your table structure, and we'll work from there.

Table structure: username | diamonds | voted | alias | date
You also need to know, a single username can be listed several times, but as long as its not on the same voting website, which is what alias says.
Code:

<?php
$vote=$_POST['vote'];
$user=$_POST['playername'];
if($vote==ms){
ob_start();
$host2="CUT"; // Host name 
$uname="CUT"; // Mysql username 
$passwd="CUT"; // Mysql password 
$db_name="minecraft"; // Database name 
$tbl_name="votes"; // Table name

// Connect to server and select databse.
mysql_connect("$host2", "$uname", "$passwd")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// To protect MySQL injection
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE username='$user' and alias='Minestatus'";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
echo "<div align='center'>Minecity Voting Error</div>";
include('/vote/error.php');
}
else{
$datenow = date('Y-m-d H:i:s');
$sql2="INSERT INTO votes (username, diamonds, voted, alias, date) VALUES ('$user', '1', 'yes', 'Minestatus', '$datenow')";
$result2=mysql_query($sql2);
header('Location: CUT');
}
ob_end_flush();
}
if($vote==gtop){
ob_start();
$host2="CUT"; // Host name 
$uname="CUT"; // Mysql username 
$passwd="CUT"; // Mysql password 
$db_name="minecraft"; // Database name 
$tbl_name="votes"; // Table name

// Connect to server and select databse.
mysql_connect("$host2", "$uname", "$passwd")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// To protect MySQL injection
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE username='$user' AND alias='GTop100'";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
echo "<div align='center'>Minecity Voting Error</div>";
include('/vote/error.php');
}
else{
$datenow = date('Y-m-d H:i:s');
$sql2="INSERT INTO votes (username, diamonds, voted, alias, date) VALUES ('$user', '1', 'yes', 'GTop100', '$datenow')";
$result2=mysql_query($sql2);
header('Location: http://www.gtop100.com/in.php?site=64552');
}
ob_end_flush();
}
else{
echo "<br>";
}
?>

I wrote it all myself
Also, I have some extra variables in there, so try to ignore them.

Generally good practices is never to delete any records, except for certain specific circumstances.

Easiest thing to do is set up a VIEW that excludes anything older than a day based on timestamp and use that for your SELECTS.

If I were to do that, I can't let them use the same username again, it'll conflict when the person is being checked for if they voted.

[JShor]

Okay, you need to convert your `date` field to a data type of BIGINT, and you cannot store dates like date('Y-m-d H:i:s'), you must store them with time() for this to work. This will input a UNIX timestamp, which can be manipulated by simple arithmetic to accomplish this script (you can always convert time() to date('Y-m-d H:i:s') format later, when you want to display it to the user).

Assuming you change the data type of that field to BIGINT, change your ELSE conditional statement to this:

$datenow = time();
$one_day_ago = strtotime("-1 day");

$voted_already = mysql_query("SELECT * FROM `votes` WHERE username='$user' AND date > $one_day_ago") or die(mysql_error());

// If there is one vote by the user within the last 24 hour period in the db, disallow the user to vote again.

if(mysql_num_rows($voted_already) > 0) {
echo "You cannot vote more than once in the same 24-hour time period";
} else {
// All is well.
$sql2="INSERT INTO votes (username, diamonds, voted, alias, date) VALUES ('$user', '1', 'yes', 'GTop100', '$datenow')";
$result2 = mysql_query($sql2);
} 

That has a conditional which will look in the database to see if there was another vote within the past 24 hours, and if there is one or more rows that match, it means the user has voted before and cannot vote again. Forgive my run-on sentence, I'm sleepy.

Also, fyi, to convert the UNIX timestamp into the format you want, supply the timestamp in the second argument of date().

date('Y-m-d H:i:s', $timestamp); 

$timestamp is UNIX time.

Some helpful resources on php time:
http://php.net/mktime
http://php.net/date
http://php.net/time

[Techykid3]

Okay, you need to convert your `date` field to a data type of BIGINT, and you cannot store dates like date('Y-m-d H:i:s'), you must store them with time() for this to work. This will input a UNIX timestamp, which can be manipulated by simple arithmetic to accomplish this script (you can always convert time() to date('Y-m-d H:i:s') format later, when you want to display it to the user).

Assuming you change the data type of that field to BIGINT, change your ELSE conditional statement to this:

$datenow = time();
$one_day_ago = strtotime("-1 day");

$voted_already = mysql_query("SELECT * FROM `votes` WHERE username='$user' AND date > $one_day_ago") or die(mysql_error());

// If there is one vote by the user within the last 24 hour period in the db, disallow the user to vote again.

if(mysql_num_rows($voted_already) > 0) {
echo "You cannot vote more than once in the same 24-hour time period";
} else {
// All is well.
$sql2="INSERT INTO votes (username, diamonds, voted, alias, date) VALUES ('$user', '1', 'yes', 'GTop100', '$datenow')";
$result2 = mysql_query($sql2);
} 

That has a conditional which will look in the database to see if there was another vote within the past 24 hours, and if there is one or more rows that match, it means the user has voted before and cannot vote again. Forgive my run-on sentence, I'm sleepy.

Also, fyi, to convert the UNIX timestamp into the format you want, supply the timestamp in the second argument of date().

date('Y-m-d H:i:s', $timestamp); 

$timestamp is UNIX time.

Some helpful resources on php time:
http://php.net/mktime
http://php.net/date
http://php.net/time

How do I make it so that does NOT make another row for the person, meaning that if they vote again (after the 24hour period), that it will replace the row with the new one, or at least update the information, each person has 2 rows, one for the first vote (ms), and the second vote (gtop).

Its chosen by a form that posts the selection, opening the statement that is for their selection, for example, they can vote on both websites, once every 24 hours.

[JShor]

You can't "replace" rows. You could delete one row, then insert a new one, or you could update the information of the existing row.

Please be more specific on what you're trying to accomplish.

[Techykid3]

I need to remove the row, then insert another. But what if there is no row to delete, meaning they have never voted before, does that mean the mysql query will halt, and not insert a new row? Or will it insert a new row?

[JShor]

To delete the row:

mysql_query("DELETE FROM `votes` WHERE username='$user' AND website='$website'") or die(mysql_error()); 

No, MySQL will not return an error if there is no row to delete. It just won't do anything. After you perform a query to delete the specified row, you just need to insert a brand new one.