I've looked for similar questions with no success.
I have this piece of code:
form1.php
$query = "INSERT INTO table1 ";
$query .= "(fname, lname, mail)";
$query .= " VALUES ";
$query .= "('".$_POST[fname]."', '".$_POST[lname]."', '".$_POST[mail]."')";
$result = mysql_query($query) or die ("Query Failed: " . mysql_error());
And I want that the script will check if the value inserted exists in the corresponding column, and throw an error if it does. any ideas?
I would say just build it yourself. You can set it up like this:
$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
$query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query
Don't forget to escape quotes if it's necessary.
Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.
It's better to set a constraint on your columns to prevent duplicate data instead of checking and inserting.
Just set a UNIQUE constraint on imdbid:
ALTER TABLE `requests` ADD UNIQUE `imdbid_unique`(`imdbid`);
The reason for doing this is so that you don't run into a race condition.
There's a small window between finishing the check, and actually inserting the data, and in that small window, data could be inserted that will conflict with the to-be-inserted data.
Solution? Use constraints and check $DBH->error() for insertion errors. If there are any errors, you know that there's a duplicate and you can notify your user then.
I noticed that you are using this, $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);. In this case, you don't need to check ->error() because PDO will throw an exception. Just wrap your execute with try and catch like this:
$duplicate = false;
try {
$STH->execute();
} catch (Exception $e) {
echo "<p>Failed to Request ".$_POST['imdbid']."!</p>";
$duplicate = true;
}
if (!$duplicate)
echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";
The function you're looking for is find_in_set:
select * from ... where find_in_set($word, pets)
for multi-word queries you'll need to test each word and AND (or OR) the tests:
where find_in_set($word1, pets) AND find_in_set($word2, pets) etc
preferred way, using MySQLi extension:
$mysqli = new mysqli(SERVER, DBUSER, DBPASS, DATABASE);
$result = $mysqli->query("SELECT id FROM mytable WHERE city = 'c7'");
if($result->num_rows == 0) {
// row not found, do stuff...
} else {
// do other stuff...
}
$mysqli->close();
deprecated:
$result = mysql_query("SELECT id FROM mytable WHERE city = 'c7'");
if(mysql_num_rows($result) == 0) {
// row not found, do stuff...
} else {
// do other stuff...
}
Create a UNIQUE key on the fields you care about, and detect the integrity error after the fact.