phpmysqliprepared-statementlast-insert-idinsert-id

Insert_id is null when used directly in next prepared statement


Finally getting around to learning prepared statements. I'm getting a maddening error when trying to run a pair of simple inserts:

$p_stmt = $mysqli->prepare("INSERT INTO ww_pages (page_key) VALUES (?)");
$p_stmt->bind_param('s', $page_key);
$p_stmt->execute();
        
$pv_stmt = $mysqli->prepare("INSERT INTO ww_page_versions (page_id, page_title, page_content, version_notes, version_timestamp) VALUES (?, ?, ?, ?, ?)");
$pv_stmt->bind_param('issss', $p_stmt->insert_id, $page_title, trim($_POST["page_content"]), trim($_POST["version_notes"]), date("Y-m-d H:i:s"));
$pv_stmt->execute();
echo $pv_stmt->error;

The echo $pv_stmt->error; gives this error: Column 'page_id' cannot be null

As I'm sure you can interpret, I'm trying to assign page_id the insert_id of the first statement. I'm 100% sure that this value is non-null, and returns an integer value. I tested it directly with this:

echo "NEW ID: ".$p_stmt->insert_id."::".is_int($p_stmt->insert_id);

The output? NEW ID: 13::1

What am I doing wrong? Why am I getting "column cannot be null" when the column isn't null? The only solutions I can find online are involving unexpectedly null values.


Solution

  • Put the value of insert_id into a temporary variable and bind that variable instead.

    $p_stmt = $mysqli->prepare("INSERT INTO ww_pages (page_key) VALUES (?)");
    $p_stmt->bind_param('s', $page_key);
    $p_stmt->execute();
    $insert_id = $p_stmt->insert_id;
    
    $pv_stmt = $mysqli->prepare("INSERT INTO ww_page_versions (page_id, page_title, page_content, version_notes, version_timestamp) VALUES (?, ?, ?, ?, ?)");
    $pv_stmt->bind_param('issss', $insert_id, $page_title, trim($_POST["page_content"]), trim($_POST["version_notes"]), date("Y-m-d H:i:s"));
    $pv_stmt->execute();