Page 1 of 2

Simple MySQL Question - How to update/insert

Posted: Tue Aug 23, 2005 11:17 pm
by ezenu
I have a HTML form which uses PHP to process it, then I want to stick the user's IP & time() in a mySQL table so I can check it if they try to spam (allow like 30 min between submissions).

Anyways, I have the table with 2 fields, ip (as the primary key) and last_time (as a double).

Is there any way to insert/update the table in 1 step? so if the IP isn't in the table, it'll add it with the time... or if the IP is in the table, it'll just update the last_time field?

thanks

sorry this isn't more PHP related...

Posted: Tue Aug 23, 2005 11:27 pm
by ezenu
This is what I have so far. It seems to work, but doesn't seem clean & not the right way to do it. If the IP is already in table, then the first query will fail. Then second will update

Code: Select all

$query = "INSERT INTO `reservations_IP` ( `ip` , `last_time` ) VALUES ( '" . $ip . "', '" . $time . "' );";
mysql_query( $query );

$query2 = "UPDATE `reservations_IP` SET `last_time` = '" . $time . "' WHERE `ip` = '" .$ip . "' LIMIT 1 ;";
mysql_query( $query2 );

Posted: Wed Aug 24, 2005 1:49 am
by s.dot

Code: Select all

$ip = ipaddress;
$query = mysql_query("SELECT id FROM table WHERE ip = '$ip' AND time > '".time()-1800."'");

if(mysql_num_rows($query) < 1)
{
    mysql_query("INSERT INTO table (ip,time) VALUES('$ip','".time()."')");
} ELSE
{
    die("You've already posted within the last 30 minutes.");
}
That piece of code will check to see if the user has posted within the last 30 minutes. If they haven't, it will add them into the table, or if they have, it will tell them that they cannot post yet.

30 minutes

Posted: Wed Aug 24, 2005 1:58 am
by AnarKy
scrotaye wrote:

Code: Select all

$ip = ipaddress;
$query = mysql_query("SELECT id FROM table WHERE ip = '$ip' AND time > '".time()-1800."'");

if(mysql_num_rows($query) < 1)
{
    mysql_query("INSERT INTO table (ip,time) VALUES('$ip','".time()."')");
} ELSE
{
    die("You've already posted within the last 30 minutes.");
}
That piece of code will check to see if the user has posted within the last 30 minutes. If they haven't, it will add them into the table, or if they have, it will tell them that they cannot post yet.
Won't this fail if you have set a primary key constraint on IP and the poster has
not posted within the last 30 minutes?

Posted: Wed Aug 24, 2005 11:50 am
by ezenu
yes, I am fairly certain that code will fail when someone has already posted more than 30 min ago. Their IP would already be in the table & the time would be >30 min ago. However, INSERT doesn't seem to update the 'time' field if the key (IP) is already in the table.

thats why I needed to use INSERT & UPDATE, but my version isn't too neat since it seems to do 2 steps & 1 will always fail.

Just tried this:

Code: Select all

$resultAdd = "INSERT INTO `reservations_IP` ( `ip` , `last_time` ) VALUES ( '" . $ip . "', '" . $time . "' );";
    if( !mysql_query( $resultAdd ) ) {
       $resultUpdate = "UPDATE `reservations_IP` SET `last_time` = '" . $time . "' WHERE `ip` = '" .$ip . "' LIMIT 1 ;";
       mysql_query( $resultUpdate );
    }
This seems to work & is better. I was thinking there might be a way to do this in 1 step, sortof like C++ STL Maps

Posted: Wed Aug 24, 2005 2:24 pm
by Sander
ezenu wrote:This seems to work & is better. I was thinking there might be a way to do this in 1 step, sortof like C++ STL Maps
Yup, there is a way.

Replace Into

Posted: Wed Aug 24, 2005 2:26 pm
by ezenu
ok, that looks very promising. thx a bunch :) looking into it now

Posted: Wed Aug 24, 2005 8:25 pm
by Stewsburntmonkey
I'm fairly certain there is no way to do this in just one query. However, if I understand the problem you are doing the following:

1) check if poster has posted in the last 30 minutes
2) if not then update the database to record their IP and time stamp and allow submission.

If that is algorithm, then you can certainly do it in just two queries.

The first:

SELECT * FROM `reservations_IP` WHERE ip=$ip

This will either return 1 row or no rows.

If it returns 1 row check the timestamp and see if it is from the last 30 minutes, if it is tell the person to bugger off.

If the timestamp is more than 30 minutes old then accept the submission and do an UPDATE query.

If no rows were returned from the SELECT query then you can accept the submission and then do an INSERT query. :)

Posted: Wed Aug 24, 2005 10:52 pm
by ezenu
its already fixed.

REPLACE INTO worked great. its just like INSERT INTO except it updates fields if the primary key exists already in table

Posted: Wed Aug 24, 2005 11:06 pm
by Stewsburntmonkey
Generally INSERT ... ON DUPLIATE KEY UPDATE ... Is better than REPLACE, but both work. :)

Posted: Fri Aug 26, 2005 3:03 pm
by blacksnday
heya everyone :)

I'm trying to use the example given in this thread for my form.
As stated earlier, the example given from scotaye
always fails if the user IP is found in the database
regardless of the time they last submitted.

After readin the other articles I can't figure out how to get it
to work correctly with my code.
Here is my code that has scotaye's example in it.

Code: Select all

if (isset($user) && isset($email) && isset($bash) && isset($lova)) { 
   if(!empty($user) && !empty($email) && !empty($bash) && !empty($lova)) { // if it inst empty
   $query = mysql_query("SELECT * FROM news WHERE ip = '$ip' AND date > 'time()-1800'"); 
   if(mysql_num_rows($query) < 1) 
{ 
    mysql_query("INSERT INTO news (name, email, news, lova, ip) VALUES('$user', '$email', '$bash', '$lova',  '$ip','".time()."')"); 
} else  { 
    die("<div align=center><font size=\"6\" color=\"red\">Not so fast buddy!!!<br />You can only bash once every 30 minutes.</div></font>"); 
}
   echo $newsfunctions->emailnews();
   @mysql_query ("INSERT INTO news (name, email, news, lova, ip) VALUES ('$user', '$email', '$bash', '$lova', '$ip')");
   echo "<center><p>THANKS FOR YOUR BASH!</p></center>";
   echo $newsfunctions->RandomBash();
   echo $newsfunctions->show_last_entry();
} else {
                echo $backlink;
     if (empty($user))  { echo $emptyname;  } 
     if (empty($email)) { echo $emptyemail; }  
     if (empty($bash))  { echo $emptybash;  }
     if (empty($lova))  { echo $emptylove;  }
  } 
}
My database setup is

Code: Select all

`id` mediumint(9) NOT NULL auto_increment,
  `name` varchar(24) NOT NULL default '',
  `ip` varchar(255) NOT NULL default '',
  `email` varchar(24) NOT NULL default '',
  `news` text NOT NULL,
  `lova` char(2) NOT NULL default '1',
  `date` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
What is needed to correct this so that it does work correctly?
Since I only log stuff in the tables for submission purposes, I only need
to check per ip/date-time for any row and if the time matches for any ip found then
a deny is given.
Meaning that I do not update any existing row.

Posted: Fri Aug 26, 2005 3:53 pm
by Stewsburntmonkey
If you chage the first INSERT to an INSERT ... ON DUPLIATE KEY UPDATE query, then you can drop the second INSERT and it should work correctly. :)

Posted: Fri Aug 26, 2005 4:12 pm
by blacksnday
changing first to be

Code: Select all

mysql_query("INSERT INTO news ON DUPLIATE KEY UPDATE (name, email, news, lova, ip) VALUES('$user', '$email', '$bash', '$lova',  '$ip','".time()."')");
still returns false.

Is there any better way to restrict form submits to
a time constraint?
....without using javascript....

Posted: Fri Aug 26, 2005 4:33 pm
by feyd
that's what your SELECT query is for, however, it looks like you have an error in it, and at the least, logic problems. 'time() - 1800' is a string to MySQL, not an expression.

Posted: Fri Aug 26, 2005 4:38 pm
by blacksnday
using the posted

Code: Select all

$query = mysql_query("SELECT * FROM news WHERE ip = '$ip' AND date > '".time()-1800."'");
throws the error

Code: Select all

Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING
when I changed it to

Code: Select all

$query = mysql_query("SELECT * FROM news WHERE ip = '$ip' AND date > 'time()-1800'");
the error ceased