Why can't I get the last insert ID in PHP and MySQL?

Question

I have a function that creates a course. I am trying to get the Last Insert ID but it doesn't work:

public  function createCourse()
{
    require "/mysqli_connect.php";
    $course_q = "INSERT INTO course (....) VALUES (.....)";

    $course_r = mysqli_query($mysqli, $course_q);
    $course_n = mysqli_affected_rows($mysqli);
    if($course_n == 1)
    {
        mysqli_close($mysqli);
        return true;
    }
    mysqli_close($mysqli);
    return false;
}

This is the function to retrieve the last insert ID that I created in the same class as the function createCourse:

public function getLastInsertID()
{
    require "/../mysqli_connect.php";

    $course_q= "SELECT LAST_INSERT_ID()";

    $course_r = mysqli_query($mysqli, $course_q);
    $course_n = mysqli_num_rows($course_r);

    //var_dump($course_n);
    if($course_n)
    {
        $c = mysqli_fetch_assoc($course_r);
        mysqli_close($mysqli);

        return $c;
    }
    mysqli_close($mysqli);
    return NULL;
}   

This is how I call the functions:

require "/mysqli_connect.php";
$course = new Course();
$c = $course->createCourse();
$id = $course->getLastInsertID();
var_dump($id);

"$id" is always "int(0)"

I've also tried:

require "/mysqli_connect.php";
$course = new Course();
$c = $course->createCourse();
**$id = mysqli_insert_id($mysqli);**

and I've also tried:

$course_q= "SELECT LAST_INSERT_ID() from course";

but that doesn't work as well. Can you guys see what the problem is? :( The function createCourse itself is fine. It creates what I need and it's there in the database but I can't get the last insert id.

Answer 1

Although the proper way to retrieve the insert id with MySQLi is to use mysqli_insert_id(), since you're doing an associative fetch, you would need a column alias for the LAST_INSERT_ID()

 $course_q= "SELECT LAST_INSERT_ID() AS insert_id";

 // Later...
 $c = mysqli_fetch_assoc($course_r);
 echo $c['insert_id'];

However, that isn't going to work because you have already closed the connection with mysqli_close() in your createCourse() function. Instead, get the insert id inside createCourse() and return it.

public  function createCourse()
{
    // Note: You should not establish the connection in each function call.
    // it only needs to be done once per script, and you can pass the connection
    // into the class constructor or into methods that use it.
    require "/mysqli_connect.php";
    $course_q = "INSERT INTO course (....) VALUES (.....)";

    $course_r = mysqli_query($mysqli, $course_q);
    $course_n = mysqli_affected_rows($mysqli);
    if($course_n == 1)
    {
        // Get the insert id before closing the connection
        $insert_id = mysqli_insert_id($mysqli);


        // Note that there probably isn't a good reason to explicitly close the
        // connection here.  It will be closed when the script terminates.
        mysqli_close($mysqli);

        // And return it.
        return $insert_id;
    }
    mysqli_close($mysqli);
    return false;
}

require "/mysqli_connect.php";
$course = new Course();
$c = $course->createCourse();
echo $c;
// $c contains the id

Design classes to use one common connection:

The class receives a connection in its constructor.

// Make classes accept the connection as a param:
class MyClass
{
  // property to hold the connection object
 public $db;

  // constructor receives the connection
  public function __construct($connection) {
    $this->db = $connection;
  }
  
  // Methods call it as $this->db
  public function createCourse() {
    $course_r = mysqli_query($this->db, $course_q);
  }
      
}

Include the connection only once on the controlling script

require_once("/mysqli_connect.php");
// $mysqli is now defined...

// Pass it into the constructor
$course = new MyClass($mysqli);