Laura Morris Laura Morris - 19 days ago 5
MySQL Question

changing from mysql to mysqli standard proceedure - prepared statements?

I am editing a tutorial I found on inline editing. It uses mysql but obviously I want to use mysqli as mysql is depreciated. I have changed most of it but one part is causing me difficulty. There is a seperate file that is called to connect to the database and run queries so the main page I will declare at the top at the top of my page I declare

require_once("dbcontroller.php");
$db_handle = new DBController();
$sql = "SELECT * from php_interview_questions";
$faq = $db_handle->runQuery($sql);


The relevant part of the dbcontroller.php is:

function runQuery($query) {
$result = mysqli_query($conn, $query);
while($row=mysqli_fetch_assoc($result)) {
$resultset[] = $row;
}
if(!empty($resultset))
return $resultset;
}

function numRows($query) {
$result = mysqli_query($conn,$query);
$rowcount = mysqli_num_rows($result);
return $rowcount;
}


Am I right in thinking that I need to use prepared statements? If so how would this cope with working with a select query that could involve any number of columns or any number of conditions in the WHERE clause?

Answer

numRows() is a strange function since it runs a query only to find out how many rows it returns. I would consider it a questionable practice. If it is necessary to find out how many rows a SELECT would return, then one could change SELECT * to SELECT COUNT(*). MySQL would be able to optimize that for MyISAM tables, plus there is a saving on not having to transfer the result set to the client.

Otherwise there is no need to use prepared statements, since a prepared statement only increases the amount of work a DBMS has to do when a statement is executed only once (it has to handle two requests - prepare, execute, -- instead of one -- execute, plus extra work to clean up the cached statement when connection is destroyed).

Comments