Both sql LAST_INSERT_ID() and PHP insert_id return 0

Question

I've been searching google a lot for this issue and really found nothing. People just keep copying MySQL documentation on last_insert_id and nothing else.

I've got an issue regarding last_insert_id, because for both situations (php & sql) it returns 0.

YES: I've set a PRIMARY & UNIQUE field with AUTO_INCREMENT value YES: i've done some inserting before NO: Making double query with INSERT AND SELECT LAST... doesn't work.

I've created a class Db for maintaining connection & query:

class Db
{
private function connect()
{
    $db = new mysqli($this->db_host, $this->db_user, $this->db_pass, $this->db_name, $this->db_port);
    if(mysqli_errno($db))
    {
        file_put_contents(date('Y-m-d').'mysql_error.txt',mysqli_error($db),FILE_APPEND | LOCK_EX);
        echo "Connection error";
        exit();
    }
    else
    {
        return $db;
    }
}

    public function insert($i_what, $i_columns, $i_values, $i_duplicate='') { 
    $insert = $this->connect()->query("INSERT INTO ".$i_what.$i_columns.$i_values.$i_duplicate);
    $last_id = $this->connect()->insert_id;
    $this->connect()->close();
    return $last_id; }
}

id  int(11)         AUTO_INCREMENT PRIMARY UNIQUE
name            varchar(32) utf8_general_ci     
firstname   varchar(64) utf8_general_ci
    lastname    varchar(64) utf8_general_ci

And it doesn't work.

NO: I do not close connection before getting last_insert_id; I close it at the end of the script — jakubplus, Nov 02 '13 at 09:46
Another note. "Talk is cheap, show me the code!"(c). All we have is your words and rought sketch of code Without *concrete*, reproduceable code, including table definition and output, this question makes *especially* no sense. Just because while you elaborate on such a code, you'll find your mistake. — Your Common Sense, Nov 02 '13 at 09:51
Can you show your DB class, Am little worried about the statement $last_id = $this->connect()->insert_id; — Krish R, Nov 02 '13 at 10:04
No, you don't understand. **Complete code**, from table creation to id output. — Your Common Sense, Nov 02 '13 at 10:06

Your Common Sense · Accepted Answer · 2013-11-02T10:08:15.463

All you need is common sense.

Issue you described is an improbable one. So - you have to check again.
there are hundreds of questions already, where opening posters were 100% they did everything right. At first.
And in every one of them it turned out a silly mistake like inserting in one db and selecting from another.

So as I said. Yeah, you don't close your connection. For some reason you just open a completely NEW one for the every database call. No wonder brand new clean connection returns 0.

Instead of writing a class of your own, better use a ready made one, like https://github.com/colshrapnel/safemysql

It would be WAY safer and convenient. And it returns insert id

Thanks, bold note made me realize my mistake. Thanks a lot! – jakubplus Nov 02 '13 at 11:22 — jakubplus, Nov 02 '13 at 11:22

Both sql LAST_INSERT_ID() and PHP insert_id return 0

1 Answers1