Viewed   81 times

I've looked for similar questions with no success.

I have this piece of code:


$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?



Create a UNIQUE key on the fields you care about, and detect the integrity error after the fact.

Thursday, August 18, 2022

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.

Saturday, November 5, 2022

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 {
} catch (Exception $e) {
    echo "<p>Failed to Request ".$_POST['imdbid']."!</p>";
    $duplicate = true;

if (!$duplicate)
    echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";
Wednesday, November 30, 2022

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 
Wednesday, August 17, 2022

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


$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...
Monday, October 31, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :