mysqli::prepare Fails Using ALTER TABLE in a Statement With Parameter Markers

Part of a project I’m working on requires re-setting a MySQL table by deleting most – but not all – rows. In PHP I’m using mysqli::prepare to prepare the MySQL statements.

$id = 5;
$query = $conn->prepare("DELETE FROM table WHERE id>?");

 

 

The MySQL statement includes a parameter marker – denoted by ? – which is then substituted for the value of the $id variable using mysqli::bind_param.

$id = 5;
$query = $conn->prepare("DELETE FROM table WHERE id>?");
$query->bind_param("i", $id);

 

 

To execute the statement, mysqli::execute is added.

$id = 5;
$query = $conn->prepare("DELETE FROM table WHERE id>?");
$query->bind_param("i", $id);
$query->execute();

 

 

I’ve heard that this is not best practice, but – having deleted rows – I wanted to update the table’s AUTO_INCREMENT value. The value can be obtained by adding 1 to table’s current row count.

$rows++;
$query = $conn->prepare("ALTER TABLE table AUTO_INCREMENT=?");
$query->bind_param("i", $rows);
$query->execute();

 

 

This throws the following error which suggests an issue with the parameters passed to $query‑>bind_param() on line 8.

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in /var/www/bookmarks/public_html/delete.php8 Stack trace: #0 {main} thrown in /var/www/bookmarks/public_html/delete.php on line 8

 

 

However, after some considerable head scratching the real culprit is $query = $conn‑>prepare(“ALTER TABLE table AUTO_INCREMENT=?”); which I discovered by testing the value returned by $conn->prepare().

$rows++;
$query = $conn->prepare("ALTER TABLE table AUTO_INCREMENT=?");
if ( $query===false) {
	echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
}

 

 

This displays the following error.

Prepare failed: (1064) 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 'table AUTO_INCREMENT=?' at line 10

 

 

It appears there’s an error in the MySQL statement, namely the ? at the end. But, this ? is the parameter marker to be substituted by mysql::bind_param with the value of the variable $rows. The parameter marker worked in the previous statement when deleting rows so why not in this one?

More head scratching. Then – having read the mysql::prepare documentation for the umpteenth time – I noticed this at the end of a paragraph I’d previously dismissed because it didn’t specifically mention ALTER TABLE or AUTO_INCREMENT.

In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

DELETE – it turns out – is a DML statement. However, ALTER TABLE is a DDL statement hence the reason mysqli::prepare failed in this instance when using a parameter marker.