CODEWITHSUNDEEP
phpmysqlmysqli
Buffalo
Buffalo

Reputation: 4042

mysqli_insert_id returning 0

I have to use a reference to a record in another table (FK). The record in the other table may or not exist, so I can either use its ID or insert a new one and then use its ID.

I have the following code: 

$con=mysqli_connect("localhost","root","test","db_site");

// get existing levels
$levelIdSQL = "SELECT idlevel from levels where levelstring = $level";
$levels = mysqli_query($con, $levelIdSQL);      

$levelID = -1;
if ($levels['num_rows'] > 0) {
    echo "<br><br>some results<br><br>";
    // we already have a level, use its id when updating
    $row = $levels->fetch_assoc();
    $levelID = $row['idlevel'];
} else {
    // we must insert this string in the levels table, then use its id
    echo "<br>running query: " . "INSERT INTO `db_site`.`levels` ('levelstring') values ('$level');"; 
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    } else {
        echo "<br><br>connected OK<br><br>";
    }       
    $rez = mysqli_query($con, "INSERT INTO `db_site`.`levels` ('levelstring') values ('$level');");
    $levelID =  mysqli_insert_id($con);                 
}
echo "<br><br>LEVEL ID: " . $levelID;

My levels table has an autoinc "idlevel" field and running the same SQL with MySQL Workbench/command line interface inserts the record just fine. However, mysqli_insert_id returns 0 and no records are inserted.

What am I doing wrong?

Edit: after jterry's suggestion, I called die() and it returned: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''levelstring')". So I changed this: 

$rez = mysqli_query($con, "INSERT INTO `db_site`.`levels` ('levelstring') values ('$level');");

into this: 

$rez = mysqli_query($con, "INSERT INTO `db_site`.`levels` (levelstring) values ('$level');");

Everything is ok now.

Upvotes: 0

Views: 2411

Answers (1)

echo_Me
echo_Me

Reputation: 37233

try this , you should use backticks around column name not quotes.

$rez = mysqli_query($con, "INSERT INTO `db_site`.`levels` ( `levelstring`) 
                           VALUES ('$level') ");

Upvotes: 1

Related Questions