Need help getting last insert id from mysql

[sicklermd]

i'm using a function to call my query.  here is my function to do so.  i'm having trouble figuring out how to call the mysqli_insert_id() when calling my query from a function.

 

$dbCon = mysqli_connect('localhost', 'user', 'pass', 'database');

function query($sql) {
    
    //get database connection
    global $dbCon;
    
    //run mysqli query held in var $sql
    return mysqli_query($dbCon, $sql);

    
}

 

where i'm trying to get the last inserted id or newly inserted id i have the following.

 

$sql = "INSERT INTO table (blah blah) VALUES (blah blah)";

$result = query($sql);

 

from here how would i get what i'm looking for......

[benanamen]

Your approach is flawed. You need to use Prepared Statements. I highly suggest you use PDO as well. This tutorial will get you going.

https://phpdelusions.net/pdo

[gizmola]

Benanamen provided you with sound advice that I agree with 100%.

With that said, the answer to your specific question is that after your insert query, you would need to call mysqli_insert_id.

Using your example:

$sql = "INSERT INTO table (blah blah) VALUES (blah blah)";
$result = query($sql);

$id = mysqli_insert_id($dbCon);

It does not matter that you have a wrapper function around the msqli_query, as the inserted id is bound to the mysql connection.

[sicklermd]

thanks for the help.  

as far my approach, why do you strongly suggest the PDO approach?

[ginerjm]

Besides switching to PDO simply because it is easy and better, why not examine why you have this 'query' function.  Functions are great for tasks that may be repetitive or complex and you will benefit by writing a block of code that does the work for you and can be relied on at multiple times.

In your case you have a function that executes ONE LINE OF CODE!  What is the point of that?  How do the 6 lines of code in your function (including the call line itself) help you when you could have simply written that query call line in place of it all?   If your function actually accomplished some real work it would be great.  You could have validated that the query call actually runs or that it actually ran before blindly returning, but you don't.  So why the extra overhead here? 

[gizmola]

The PDO api is easier to use and more natural than mysqli.  There are some really annoying things in mysqli that you quickly come to with things like prepared statements.  Prepared statements are essentially mandatory for handling all query parameters and data, if you want to write quality code that does not have intrinsic SQL injection flaws or risks.  

You can read this article for a discussion on issues with mysqli prepared statements if you want to see one of the major annoyances with it.

As for PDO, the same guy wrote a great tutorial on PDO with mysql that we point people to all the time.  Read through it, and you will become a highly proficient PDO developer very quickly.

If you really want a database class to use that wraps PDO, then you can use the Doctrine2 ORM which relies upon PDO/MySQL.   You don't have to go the full ORM route with it, but just use the DBAL (Database Abstraction Layer) portion which is a simple and effective database class that offers value for many people.  I would highly recommend using that rather than trying to create your own database wrapper class or function library.