I am updating some old reports that are using mysql_mysql_ statements and trying to use the MySQL PDO stuff. I was told that PDO is far better since I was running into runtime issues with some of my reports. My old report took 91 seconds to run, and my PDO version takes 106 seconds. While 15 seconds may not seem like a big deal, this report is dealing with 1 week's worth of data, and other reports deal with a month up to a year. Additionally, my $cart_total$cart_total doesn't seem to work in the PDO version.
I am updating some old reports that are using mysql_ statements and trying to use the MySQL PDO stuff. I was told that PDO is far better since I was running into runtime issues with some of my reports. My old report took 91 seconds to run, and my PDO version takes 106 seconds. While 15 seconds may not seem like a big deal, this report is dealing with 1 week's worth of data, and other reports deal with a month up to a year. Additionally, my $cart_total doesn't seem to work in the PDO version.
I am updating some old reports that are using mysql_ statements and trying to use the MySQL PDO stuff. I was told that PDO is far better since I was running into runtime issues with some of my reports. My old report took 91 seconds to run, and my PDO version takes 106 seconds. While 15 seconds may not seem like a big deal, this report is dealing with 1 week's worth of data, and other reports deal with a month up to a year. Additionally, my $cart_total doesn't seem to work in the PDO version.
Optimize PHP and MySQL with PDO
I am updating some old reports that are using mysql_ statements and trying to use the MySQL PDO stuff. I was told that PDO is far better since I was running into runtime issues with some of my reports. My old report took 91 seconds to run, and my PDO version takes 106 seconds. While 15 seconds may not seem like a big deal, this report is dealing with 1 week's worth of data, and other reports deal with a month up to a year. Additionally, my $cart_total doesn't seem to work in the PDO version.
I would appreciate any help optimizing my queries (though I think they are pretty solid), and my PHP/PDO code.
<?php
$start_time = time();
require_once('db_configuration.php');
$db = new PDO('mysql:host=' . $db_host . ';dbname=' . $db_name . ';charset=utf8', $db_username, $db_password, array(PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$key_query_raw = "SELECT configuration_key, configuration_value FROM configuration WHERE configuration_group_id = 6501 AND configuration_key IN('RCS_BASE_DAYS', 'RCS_EMAIL_TTL', 'RCS_SKIP_DAYS') ORDER BY configuration_key ASC;";
try { $key_query = $db->query($key_query_raw); }
catch(PDOException $ex) { echo "An Error occured! <br><br>" . $ex; }
while($key = $key_query->fetch(PDO::FETCH_ASSOC))
{
if ($key['configuration_key'] == 'RCS_BASE_DAYS') { $base_days = $key['configuration_value']; }
elseif ($key['configuration_key'] == 'RCS_EMAIL_TTL') { $ttl_days = $key['configuration_value']; }
elseif ($key['configuration_key'] == 'RCS_SKIP_DAYS') { $skip_days = $key['configuration_value']; }
}
$key_query->closeCursor();
$skip_date = date('Ymd',strtotime('-'.$skip_days.' day',time()));
$base_date = date('Ymd',strtotime('-'.$base_days.' day',time()));
$ttl_date = date('Ymd',strtotime('-'.$ttl_days.' day',time()));
?>
<html>
<style type="text/css">
.row {
padding-left:5px;
padding-right:5px;
border-style:solid;
border-color:black;
border-width:1px;
border-width:0 0 1 0;
}
.header {
background-color:#C8C8C8;
text-align:left;
font-weight:bold;
padding-left:5px;
padding-right:5px;
border-style:solid;
border-color:black;
border-width:0 0 3 0;
}
</style>
<head>
<title>Recover Cart Sales Test</title>
<link rel="stylesheet" type="text/css" href="reports.css" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<table style="border-collapse:collapse;" width=100%>
<tr>
<td class="header">Contacted</td>
<td class="header">Date</td>
<td class="header">Customer Name</td>
<td class="header" colspan=2>Email</td>
<td class="header">Phone</td>
<td class="header"> </td>
</tr>
<tr>
<td class="header"> </td>
<td class="header">Item</td>
<td class="header" colspan=2>Description</td>
<td class="header" width=20>Qty</td>
<td class="header" width=20>Price</td>
<td class="header" width=20>Total</td>
</tr>
<?
$customer_query_raw = "SELECT DISTINCT cb.customers_id, cb.customers_basket_date_added, c.customers_firstname, c.customers_lastname, c.customers_email_address, c.customers_telephone, sc.datemodified AS last_contacted
FROM customers_basket cb
INNER JOIN customers c ON c.customers_id = cb.customers_id
LEFT JOIN scart sc ON cb.customers_id = sc.customers_id
WHERE cb.customers_basket_date_added < " . $skip_date . "
AND cb.customers_basket_date_added > " . $base_date . "
AND cb.customers_id NOT IN (SELECT sc.customers_id FROM scart sc WHERE sc.datemodified > " . $ttl_date . ")
ORDER BY cb.customers_basket_date_added DESC;";
try { $customer_query = $db->query($customer_query_raw); }
catch(PDOException $ex) { echo "An Error occured! <br><br>" . $ex; }
$customer_row_count = $customer_query->rowCount();
while($customer = $customer_query->fetch(PDO::FETCH_ASSOC))
{
$product_query_raw = "SELECT cb.customers_id, cb.products_id, p.products_model, pd.products_name, cb.customers_basket_quantity, p.products_price, (p.products_price * cb.customers_basket_quantity) AS product_total
FROM customers_basket cb, products p, products_description pd
WHERE cb.customers_id = " . $customer['customers_id'] . "
AND cb.products_id = pd.products_id
AND p.products_id = pd.products_id";
try { $product_query = $db->query($product_query_raw); }
catch(PDOException $ex) { echo "An Error occured! <br><br>" . $ex; }
$cart_total_query_raw = "SELECT SUM( p.products_price * cb.customers_basket_quantity ) AS cart_total
FROM customers_basket cb, products p
WHERE cb.customers_id = " . $customer['customers_id'] . "
AND cb.products_id = p.products_id;";
try { $cart_total_query = $db->query($cart_total_query_raw); }
catch(PDOException $ex) { echo "An Error occured! <br><br>" . $ex; }
$result = $cart_total_query->fetchAll(PDO::FETCH_ASSOC);
$cart_total = $result['cart_total'];
$cart_total_query->closeCursor();
$last_contacted = ($customer['last_contacted'] < $ttl_date || $customer['last_contacted'] == NULL) ? 'Uncontacted' : date('Y-m-d', strtotime($customer['last_contacted']));
?>
<tr>
<td class="row"><?= $last_contacted; ?></td>
<td class="row"><?= date('Y-m-d', strtotime($customer['customers_basket_date_added'])); ?></td>
<td class="row"><?= $customer['customers_firstname'] . ' ' . $customer['customers_lastname']; ?></td>
<td class="row" colspan=2><?= $customer['customers_email_address']; ?></td>
<td class="row"><?= $customer['customers_telephone']; ?></td>
<td class="row"> </td>
</tr>
<?
while($product = $product_query->fetch(PDO::FETCH_ASSOC))
{
?>
<tr>
<td> </td>
<td class="row"><?= $product['products_model']; ?></td>
<td class="row" colspan=2><?= $product['products_name']; ?></td>
<td class="row" width=20><?= $product['customers_basket_quantity']; ?>x </td>
<td class="row" width=20><?= $product['products_price']; ?></td>
<td class="row" width=20><?= $product['product_total']; ?></td>
</tr>
<?
}
?>
<tr>
<td colspan=7 style="font-weight:bold; text-align:right;">Cart Total: <?= $cart_total; ?></td>
</tr>
<tr>
<td> </td>
</tr>
<?
$product_query->closeCursor();
} // End While
$customer_query->closeCursor();
$db = NULL;
?>
</table>
<br><br>
<?
$end_time = time();
echo "Number of Records: " . $customer_row_count . "<br>";
echo "Start: " . $start_time . "<br>";
echo "End: " . $end_time . "<br>";
echo "Time Elapsed: " . ($end_time - $start_time);
?>
</body>
</html>
default