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();
    }
    }

?>

Answer 1

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

Answer 2

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");
}
?>