Does SELECT COUNT(*) work with MySQLi prepared statements?

Question

I'm working on a test page and am using MySQLi prepared statements in my queries after reading they make my code safe from SQL injection. I have been successful with prepared statements so far with retrieving data from my DB, that all works great.

What I want to do now is count the number of galleries within a project using SELECT COUNT(*). That's it.

Without using a prepared statement, my old query looked like this:

// count number of galleries per project
$conn = dbConnect('query');
$galNumb = "SELECT COUNT(*) FROM pj_galleries WHERE project = {$pjInfo['pj_id']}";
$gNumb = $conn->query($galNumb);
$row = $gNumb->fetch_row();
$galTotal = $row[0];

But for all my reading and searching the internet, I can not find out the proper way to write this as a prepared statement.

Answer 1

Yes, it should work just fine.

However, keep in mind that doing a COUNT(primary_key) usually gives better performance.

So your above query would look like

// first, setup your DB-connection
$mysqli = new mysqli('example.com', 'user', '********', 'database');

// Preparing the statement
$stmt = $mysqli->prepare('SELECT COUNT(*) FROM pj_galleries WHERE project = ?');

// binding the parameters
$stmt->bind_param('i', $pjInfo['pj_id']); // 'i' signals an integer

// Executing the query
if ( ! $stmt->execute()) {
    trigger_error('The query execution failed; MySQL said ('.$stmt->errno.') '.$stmt->error, E_USER_ERROR);
}

// fetching the results
$col1 = null;
$stmt->bind_result($col1); // you can bind multiple colums in one function call
while ($stmt->fetch()) { // for this query, there will only be one row, but it makes for a more complete example
    echo "counted {$col1} records\n";
}

$stmt->close(); // explicitly closing your statements is good practice

For a better and more complete explanation, please take a look at: http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php (examples should get you up to speed).

Also keep in mind that you can execute a prepared statement multiple times, if needed. You could also bind new parameters before re-executing your query.

Answer 2

You may be over-thinking things, because it's not different than any other prepared statement:

$conn = new mysqli;
$sql = "SELECT COUNT(*) FROM pj_galleries WHERE project = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $pjInfo['pj_id']);
$stmt->execute();
$row = $stmt->get_result()->fetch_row();
$galTotal = $row[0];