MySql Query - Check for data if it is exist, if yes get the id, if not, add the data and get the id

Question

I can do it with php/mysqli with multiple step.

So, table have only two column.
ID, Name

Both of then will be unique.
I want to check if Name is available in the database, get the ID if it is available.
If it is not available, add Name on the database and get the ID.

I can do it with php/mysql which need multiple sql query.
Is there a way do it (checking database, if not exist add it and get the ID) only with one mysql query and get the ID?

Thanks in advance!

My code (MySQLi Procedural)

function abc($name) {
    global $conn;
    $checkName = mysqli_query($con,"SELECT * FROM category WHERE name=".mysql_real_escape_string($name));
    if (mysqli_num_rows($checkName) > 0) {
        $inputName = mysqli_query($con,"INSERT INTO category (name) VALUES ('".mysql_real_escape_string($name)."')");
        if (!$inputName) { die(mysqli_error($conn)); }
        $checkName2 = mysqli_query($con,"SELECT * FROM category WHERE name=".mysql_real_escape_string($name));
        while($blahblah = mysqli_fetch_assoc($checkName2)) {
            $returnData[] = $blahblah;
        }
    } else {
        while($blahblah = mysqli_fetch_assoc($checkName)) {
            $returnData[] = $blahblah;
        }
    }
    return $blahblah;
}

You can add uniqe index for name field and do insert on update — sergio, Jan 31 '15 at 07:10
Since you're using `mysqli_` functions, you should use `mysqli_real_escape_string()` to escape input parameters, not `mysql_real_escape_string()`. Alternatively you could use prepared statements. — Arjan, Jan 31 '15 at 08:26

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

This can be done with just one line. Use "INSERT IGNORE INTO.." or "REPLACE INTO....". This page refers.

edited May 23 '17 at 11:43

Community

1
1

answered Jan 31 '15 at 07:57

Gavin Simpson

2,523
3
25
32

1

`REPLACE INTO` removes the existing record. That also means any references to the id of that record no longer work (or, if foreign keys are used, the delete will fail or might even cascade). – Arjan Jan 31 '15 at 08:30

score 0 · Answer 2 · answered Jan 31 '15 at 07:29

If you use the Object-Oriented MySQLi, this is how you do it:

$mysqli = new mysqli(...);
$name = "Something";

$query = $mysqli->prepare("SELECT id, name FROM table WHERE name=?");
$query->bind_param('s', $something);
$query->execute();
$query->bind_result($id, $name);
$query->store_result();
if($query->num_rows == 1) {
    return $id;
} else {
    $queryTwo = $this->mysqli->prepare("INSERT INTO table VALUES('', ?);");
    $queryTwo->bind_param('s', $name);
    $queryTwo->execute();
    $queryTwo->close();

    $queryThree = $this->mysqli->prepare("SELECT id FROM table WHERE name=?");
    $queryThree->bind_param('s', $name);
    $queryThree->execute();
    $queryThree->bind_result($id);
    $queryThree->store_result();
    while($queryThree->fetch()) {
        return $id;
    }
    $queryThree->free_result();
    $queryThree->close();
}
$query->free_result();
$query->close();

thanks for reply. i can do it with mutiple query. just checking if there is any way i can do it in one single query. :) — crazymoin, Jan 31 '15 at 07:45

MySql Query - Check for data if it is exist, if yes get the id, if not, add the data and get the id

2 Answers2