I have a custom function called "query" with PDO statement in the functions.php file:
/**
* Executes SQL statement, possibly with parameters, returning
* an array of all rows in result set or false on (non-fatal) error.
*/
function query(/* $sql [, ... ] */)
{
// SQL statement
$sql = func_get_arg(0);
// parameters, if any
$parameters = array_slice(func_get_args(), 1);
// try to connect to database
static $handle;
if (!isset($handle))
{
try
{
// connect to database
$handle = new PDO("mysql:dbname=" . DB_NAME . ";host=" . DB_SERVER, DB_USERNAME, DB_PASSWORD);
// ensure that PDO::prepare returns false when passed invalid SQL
$handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch (Exception $e)
{
// trigger (big, orange) error
trigger_error($e->getMessage(), E_USER_ERROR);
exit;
}
}
// prepare SQL statement
$statement = $handle->prepare($sql);
if ($statement === false)
{
// trigger (big, orange) error
trigger_error($handle->errorInfo()[2], E_USER_ERROR);
exit;
}
// execute SQL statement
$results = $statement->execute($parameters);
// return result set's rows, if any
if ($results !== false)
{
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
else
{
return false;
}
}
I'm trying to rewrite/update my old script. Now, how do I use the above to modify the following old mysql_query and mysql_num_rows statements I have in my old script?:
$sql = "SELECT al_id, al_name, al_image, COUNT(im_album_id) AS al_numimage
FROM tbl_album al LEFT JOIN tbl_image im ON al.al_id = im.im_album_id
GROUP by al_id
ORDER BY al_name";
$result = mysql_query($sql) or die('Error, list product category failed. ' . mysql_error());
if (mysql_num_rows($result) == 0) {
echo "No product category yet";
}
And again, how do I rewrite this, to be in line with PDO?:
while ($row = mysql_fetch_assoc($result)) {
if ($i % $colsPerRow == 0) {
// start a new row
echo '<tr>';
}