How do I check if name exists in table before inserting into MySQL

Question

I'm adding a new area, I want to check whether the name that is being inserted by user already exists in the areas table.

I have tried using select to check the data but when I nest my if statement containing the insert inside the while loop nothing happens no error, no output either.

$campus_area_name = mysqli_real_escape_string($conn, $_POST["campus_area_fk"]);
$area_name = mysqli_real_escape_string($conn, $_POST["area_name"]);

$query_names = "SELECT name FROM areas WHERE idCampus = '".$campus_area_name."'";
$name_result = mysqli_query($conn, $query_names);
while ($row = mysqli_fetch_array($name_result)) {
    if($row["name"] == $area_name){
        echo "Area Name Already Exists";
    } else {
        $query = "INSERT INTO areas(name, idCampus) VALUES ('".$area_name."', '".$campus_area_name."')";
        if (mysqli_query($conn, $query)){
            $last_id = $conn->insert_id;
            $query_retrieve = "SELECT idareas, name FROM areas WHERE idareas = '".$last_id."'";
            $my_result = mysqli_query($conn, $query_retrieve);

            while ($rows = mysqli_fetch_array($my_result)) {
               $output["area_name_retrieve"] = $rows["name"];
               $output["area_id_retrieve"] = $rows["idareas"];
            }
             echo json_encode($output);
        } else {
            echo "failed" .  mysqli_error($conn);
        }
    }
}

I expect the output to be, if area name exists print out "Area Name Already Exists" if its not used insert row

why not just one sql statement https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql? — xdtTransform, Jul 30 '19 at 11:11

score 5 · Accepted Answer · answered Jul 24 '19 at 08:00

You can simplify your code a bit - the last select-statement is redundant, as you already have the information before the query. You should also be using a prepared statement instead of using real_escape_string().

<?php 

$stmt = $conn->prepare("SELECT name FROM areas WHERE idCampus = ?");
$stmt->bind_param("s", $_POST["campus_area_fk"]);
$stmt->execute();
$exists = $stmt->fetch();
$stmt->close();

if ($exists)
    echo "Area name already exists";
} else {
    $stmt = $conn->prepare("INSERT INTO areas (name, idCampus) VALUES (?, ?)");
    $stmt->bind_param("ss", $_POST["area_name"], $_POST["campus_area_fk"]);
    $stmt->execute();

    echo json_encode(['area_name_retrieve' => $_POST["area_name"], 'area_id_retrieve' => $stmt->insert_id]);
    $stmt->close();
}

Beware that this can cause race-conditions - you should instead put the name as a unique constraint, and try to insert it without doing any select query first. Then check against the errorcode you got back to see if it existed or not.

thank you for your answer, this will help me out a bit especially the prepared statements. but i dont get why you say the second select is redundant as they have deferent conditions the last one is just to view areas depending on the last insert. but the first one's condition is based on the foreign key for e campus table — bots_developer_fix, Jul 24 '19 at 08:06
Your last SELECT fetches the rows you just inserted - but you have that data already, as you just inserted it - all you need is the newly generated ID, and you get that from the `$stmt->insert_id` property. — Qirel, Jul 24 '19 at 08:08
ow thanks mate you are the best, wish i could give a post score but my reputation is still low, thanx a lot Qirel, i just implemented it, and it works perfectly — bots_developer_fix, Jul 24 '19 at 08:20

How do I check if name exists in table before inserting into MySQL

1 Answers1