Simple MySQL Question - How to update/insert

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

ezenu
Forum Newbie
Posts: 12
Joined: Tue Aug 23, 2005 2:15 pm

Simple MySQL Question - How to update/insert

Post 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...
ezenu
Forum Newbie
Posts: 12
Joined: Tue Aug 23, 2005 2:15 pm

Post 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 );
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
AnarKy
Forum Contributor
Posts: 119
Joined: Tue Nov 02, 2004 1:49 am
Location: South Africa

30 minutes

Post 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?
ezenu
Forum Newbie
Posts: 12
Joined: Tue Aug 23, 2005 2:15 pm

Post 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
Sander
Forum Commoner
Posts: 38
Joined: Sat Aug 06, 2005 12:43 pm

Post 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
ezenu
Forum Newbie
Posts: 12
Joined: Tue Aug 23, 2005 2:15 pm

Post by ezenu »

ok, that looks very promising. thx a bunch :) looking into it now
Stewsburntmonkey
Forum Commoner
Posts: 44
Joined: Wed Aug 24, 2005 2:09 pm

Post 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. :)
ezenu
Forum Newbie
Posts: 12
Joined: Tue Aug 23, 2005 2:15 pm

Post 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
Stewsburntmonkey
Forum Commoner
Posts: 44
Joined: Wed Aug 24, 2005 2:09 pm

Post by Stewsburntmonkey »

Generally INSERT ... ON DUPLIATE KEY UPDATE ... Is better than REPLACE, but both work. :)
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post 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.
Stewsburntmonkey
Forum Commoner
Posts: 44
Joined: Wed Aug 24, 2005 2:09 pm

Post 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. :)
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post 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....
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
blacksnday
Forum Contributor
Posts: 252
Joined: Sat Jul 30, 2005 6:11 am
Location: bfe Ohio :(

Post 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
Post Reply