Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Automatic TimeStamp Actions

  1. #11
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Quote Originally Posted by JShor View Post
    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?

  2. #12
    Join Date
    Mar 2011
    Posts
    2,170
    Thanks
    60
    Thanked 120 Times in 116 Posts
    Blog Entries
    4

    Default

    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 Code:
    <?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.

  3. #13
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    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.
    - Josh

  4. #14
    Join Date
    Aug 2011
    Posts
    18
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    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.

  5. #15
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Quote Originally Posted by keyboard1333 View Post
    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 Code:
    <?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.


    Quote Originally Posted by JShor View Post
    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:
    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.


    Quote Originally Posted by Brillig View Post
    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.

  6. #16
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    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:
    PHP Code:
    $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().

    PHP Code:
    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
    - Josh

  7. #17
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Quote Originally Posted by JShor View Post
    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:
    PHP Code:
    $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().

    PHP Code:
    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.

  8. #18
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    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.
    - Josh

  9. #19
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    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?

  10. #20
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    To delete the row:
    PHP Code:
    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.
    - Josh

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •