How to find out if rows exist in database when inserting multiple rows by one submit

Question

A newbie is asking help here. I’ve created a multiple inserting form, where’s 3 fields. Let’s say that fields’ name are: “name”, “surname” and “age”. I’m trying to insert them into database in the case if there’s not already same data in database. How can I check if there’s already exist data there?

When I'm trying to insert just one record to database it's quite easy job to check if the same record is saved already in the database or not. The problem appears when I'll try to insert multiple records to the same database. In multiple record case I've to use foreach loop to succeed, but I don't know how to do that. Thanks in advance for any help.

<?php

include('../mysqli_connect3.php');
include 'includes/header.html';
$page = 'quest_insert.php';

for($i = 0; $i < $_POST['numbers']; $i++) {
    if (!empty($_POST["name"][$i]) && !empty($_POST["surname"][$i]) && !empty($_POST["age"][$i])) {
        $sql = mysqli_query($dbc, "SELECT name, surname, age FROM users WHERE 'name' = '".$_POST['name'][$i]."' AND 'surname' = '".$_POST['surname'][$i]."' AND 'age' = '".$_POST['age'][$i]."'");
        if (mysqli_num_rows($sql) > 0) {
        echo "Data '" . $name[$i] . "' and '" . $surname[$i] ."' are already saved in database!";
        mysqli_close($dbc);
        header("refresh:5; url=$page");

        } else {
        $name[$i] = mysqli_escape_string($dbc, $_POST["name"][$i]);
        $surname[$i] = mysqli_escape_string($dbc, $_POST["surname"][$i]);
        $age[$i] = ($_POST["age"][$i]);
        mysqli_query($dbc, "INSERT INTO users (name, surname, age) VALUES ('".$_POST['name'][$i]."', '".$_POST['surname'][$i]."','".$_POST['age'][$i]."')");
        echo "Data '" . $name[$i] . "' and '" . $surname[$i] ."' are inserted into database! <br />";
        header("refresh:6; url=$page");
    }} else {
        echo "You forgot to fill in all fields!";
        header("refresh:2; url=$page");
        exit();
    }
    }

?>

Alots of things are wrong in this code.. 1) Race contitions are possible because you select to check for a duplicate and then insert if it does not exists ideally you should are a unique key in the database 2) SQL injections are possible `mysqli_escape_string()` does not fully protect 3) `echo "You forgot to fill in all fields!"; header("refresh:2; url=$page");` doing output before a HTTP header which is not allowed. — Raymond Nijland, Mar 26 '19 at 13:28
you need to show an error if already inserted or you just want to skip? — Karan Sadana, Mar 26 '19 at 13:28
Also you should ask yourself the question if looping is a good idea ideally you want to do `INSERT INTO .... SELECT .. WHERE ... IN(1, 2 , 3)` that fixes the multiple database queries and the race condition as well. — Raymond Nijland, Mar 26 '19 at 13:30
One quick fix is to add an unique index on your 3 columns and use INSERT IGNORE,that way a duplicate wont be inserted. — Mihai, Mar 26 '19 at 13:31
using `INSERT IGNORE` is a very bad suggestion @Mihai because if something else goes very wrong you never know why.. it's like PHP's `@` error disabling.. — Raymond Nijland, Mar 26 '19 at 13:32
My table has unique index for 3 colums. I just can't imagine how to write foreach part of code. — Fariborz Firuzabadi, Mar 26 '19 at 13:36

score 0 · Answer 1 · answered Mar 26 '19 at 14:06

MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

this should point you where you need to go.

Bare in mind, using the rowCount for on duplicate can be misleading as it returns 1 for an insert and 2 for an update ( with PDO at least) - I'll let someone else give you the lecture eon using prepared statements

Fariborz Firuzabadi · Answer 2 · 2019-03-28T13:45:47.490

Appreciate for all advises. I’ve done some changes to code as following and need to get more feedback on it:

<?php

include('../mysqli_connect3.php');
include 'includes/header.html';
$page = 'quest_insert.php';

for($i = 0; $i < $_POST['numbers']; $i++) {
    if (!empty($_POST["name"][$i]) && !empty($_POST["surname"][$i]) && !empty($_POST["age"][$i])) {
        $sql = mysqli_query($dbc, "INSERT INTO fi_fa (name, surname, age) VALUES '".$_POST['name'][$i]."', 'surname' = '".$_POST['surname'][$i]."', 'age' = '".$_POST['age'][$i]."' WHERE NOT EXISTS( SELECT name, surname, age FROM fi_fa WHERE name = '".$_POST['name'][$i]."' AND surname = '".$_POST['surname'][$i]."' AND age = '".$_POST['age'][$i]."') LIMIT 1");
        echo "Data has been saved successfully.";
        }
    } else {
        echo "You forgot to fill in all fields!";
header("refresh:2; url=$page");
}
?>

How to find out if rows exist in database when inserting multiple rows by one submit

2 Answers2