Storing and deleting data used to authenticate

[alphamoment]

This is my current layout

 

Log Table

 

userid  int(11)

email     int(11)  

zoneid  int(11)  

cash    int(11)  

status  int(11)  

creatime  datetime 

<?php



$Result = mysql_query("SELECT * FROM login WHERE name='$name' and email='$email'");

$count=mysql_num_rows($Result);

if($count==1)

{

$row2 = mysql_fetch_array( $Result ); 

$ID = $row2['ID'];

$TIME = $row2['creatime'];

MySQL_Query("INSERT INTO log (id, zoneid, cash, status, creatime) VALUES ('$ID', '1', '100', '1', '$TIME')");

$_SESSION['name'] = $row2[name];

$_SESSION['email'] = $row2[email];

header("location:success.php?sys=success");



?>

 

I'm running a Voting system for a game, When each user Votes for their account, they will receive some rewards.

 

But before the script completes, I want it to store the Email enterd into a seperate document or table, which automates deletion after X hours.

So that when they try to run it again using the same Email before X hours is over, it will decline. 

 

 

Example;

 

Users must login to Vote.

 

<form  name="form" method="post" target="_blank" action="vote.php?sys=vote">

<input type="text" name="name" value="<?php echo htmlentities($_SESSION['user']['0'], ENT_QUOTES, 'UTF-8'); ?>"><br>

<input type="text" name="email" value="Email">

<input type="submit" name="Submit" value="Vote">

 

This is  how I want the process to go..

 

<?php 

$name='John Doe', $email='JohnDoe@test.com'



Insert $email into $DocumentorTable



if $email already exists in $DocumentorTable > Error Try again later.

if $email doesnt exist in $DocumentorTable > successful



if successful >



MySQL_Query("INSERT INTO log (id, zoneid, cash, status, creatime) VALUES ('$ID', '1', '100', '1', '$TIME')");

header("location:success.php?sys=success");



?>

 

 

I'm not very good with PHP as of yet, so I'm not entirely sure on how to finish the layout to a working standard.. Any help is very appreciated.

 

However, if this is a process that is difficult or complex, maybe there's a way I can do this with the logged in session?

 

Like, update the users session when they have voted with a mark, then after X hours the mark will automatically delete putting the session back to normal allowing them to vote again.

But if the mark still stands, the voting will not commence.

 

Thank you in advance!!

[iarp]

You already have the logic and code written to do what you want in the second code block. The following isn't actual code, but what you have already written can very easily be turned into this

SELECT my,fields,here FROM DocumentorTable WHERE email = ...
count = mysql_num_rows..
if($count >= 1) {
    email already exists, echo error
} else {
    INSERT INTO DocumentorTable (..) values (...)
    echo success
}

Edited April 14, 2014 by iarp

[alphamoment]

Thanks for the reply! I will modify my code and try to get it working! I will report back with an update soon, hopefully with a final working code.

I appreciate your time.

[alphamoment]

Okay, here's my update!

After fiddling with the code for goodness how long... I had to change many things that didn't seem to add up. However, The code is now working, thanks to your support! 

Now; my only issue is. How can I get the row it's created in my table to delete itself automatically after 6 hours?

[iarp]

You need to create another php script that a cronjob of some type that runs the command "delete from DocumentorTable where date_created >= DATE_SUB(NOW(), INTERVAL 6 HOUR)"

[Jacques1]

Hi,

 

counting the rows before the INSERT query (as suggested by iarp) is naive and doesn't survive concurrent requests. Let's say a particular e-mail address is ready to be voted for, and then you get 100 votes for this address at the same time. Since your check sees no rows at that point of time, all votes are accepted. But now you have 100 rows when you only wanted one.

 

This is not just a theoretical issue. If people find your reward worthwhile, they will actively abuse this bug.

 

So it doesn't work like this. If you want to make sure a value is unique, you must put a UNIQUE constraint on the table column. In your application, you first try to insert the row and then check if the constraint has been violated:

try
{
    $database->query('
        INSERT INTO
            unique_test
        SET
            x = 1
    ');
}
catch (PDOException $insert_exception)
{
    // If the query fails, check the error code; "1062" is a violation of a UNIQUE constraint.
    if ($insert_exception->errorInfo[1] === 1062)
    {
        echo 'Duplicate entry!';
    }
    else
    {
        // Otherwise, just pass the exception on.
        throw $insert_exception;
    }
}