Simple MySQL Question - How to update/insert
Moderator: General Moderators
Simple MySQL Question - How to update/insert
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...
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...
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 );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.");
}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.
30 minutes
Won't this fail if you have set a primary key constraint on IP and the poster hasscrotaye wrote: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.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."); }
not posted within the last 30 minutes?
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:
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
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 );
}Yup, there is a way.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
Replace Into
-
Stewsburntmonkey
- Forum Commoner
- Posts: 44
- Joined: Wed Aug 24, 2005 2:09 pm
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.
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.
-
Stewsburntmonkey
- Forum Commoner
- Posts: 44
- Joined: Wed Aug 24, 2005 2:09 pm
- blacksnday
- Forum Contributor
- Posts: 252
- Joined: Sat Jul 30, 2005 6:11 am
- Location: bfe Ohio :(
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.
My database setup is
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.
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; }
}
}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`)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
- blacksnday
- Forum Contributor
- Posts: 252
- Joined: Sat Jul 30, 2005 6:11 am
- Location: bfe Ohio :(
changing first to be
still returns false.
Is there any better way to restrict form submits to
a time constraint?
....without using javascript....
Code: Select all
mysql_query("INSERT INTO news ON DUPLIATE KEY UPDATE (name, email, news, lova, ip) VALUES('$user', '$email', '$bash', '$lova', '$ip','".time()."')");Is there any better way to restrict form submits to
a time constraint?
....without using javascript....
- blacksnday
- Forum Contributor
- Posts: 252
- Joined: Sat Jul 30, 2005 6:11 am
- Location: bfe Ohio :(
using the posted
throws the error
when I changed it to
the error ceased
Code: Select all
$query = mysql_query("SELECT * FROM news WHERE ip = '$ip' AND date > '".time()-1800."'");Code: Select all
Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRINGCode: Select all
$query = mysql_query("SELECT * FROM news WHERE ip = '$ip' AND date > 'time()-1800'");