HI Everyone
Hope anyone here can help me. I have a query that i normally use in SQL Workbench to get hourly updates on reworks done in our warehouse. However i am trying to automate this process via cron / phpmailer. The php code i have now is working well but the results in my 'Hour" table is proving to be the issue. I need to get the Hour in an acceptable format for our reporting (HH:MM) but when using the query i only get a single digit return for the hour.
Here is my original query i use in Workbench
SELECT HOUR(requested) AS `Hour`
, `ManualAction_L`.`name` AS `Rework Type`
, requestedByUsername AS `User`, COUNT(`KitManualAction_T`.`id`) AS `Count`
FROM KitManualAction_T, ManualAction_L
WHERE `KitManualAction_T`.`manualActionID`= `ManualAction_L`.`id` AND requested BETWEEN '2013-07-24 05:00:00' AND '2013-07-24 14:00:00'
GROUP BY `Hour`, requestedByUsername, manualActionID
ORDER BY `Hour` ASC;
This returns a result:
Hour Rework Type User Count
5 Create heidis 70
5 Create lilly 27
5 Reprint Label lilly 7
5 Cease lilly 6
6 Create heidis 133
6 Reprint Label heidis 3
6 Create lilly 105
Here is my php code
$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
or die ('Could not connect to the database server' . mysqli_connect_error());
echo "<h2> ".gmdate("l F d Y")." :: 311 Warehouse :: Hourly Reworks Update - Dayshift" , "</h2>";
//echo "<br />";
echo "<h3> 311 Wallet Reworks :: Created Wallets</h3>";
echo "<table cellpadding=10 border=1 align=left width=300px>";
$query = "SELECT HOUR(requested)AS 'Hour' ,
`ManualAction_L`.`name` AS 'ReworkType' ,
requestedByUsername AS 'User',
COUNT(`KitManualAction_T`.`id`) AS 'Count'
FROM KitManualAction_T, ManualAction_L WHERE `KitManualAction_T`.`manualActionID`= `ManualAction_L`.`id`
AND requested BETWEEN '" . gmdate("Y-m-d") . " 05:00' AND '".gmdate("Y-m-d"). " 14:00'
and manualActionID = '1'
GROUP BY `Hour`, requestedByUsername, manualActionID
ORDER BY `Hour` ASC";
if ($stmt = $con->prepare($query)) {
$stmt->execute();
$stmt->bind_result($Hour, $ReworkType, $User, $Count);
while ($stmt->fetch()) {
printf("
<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
</tr>", $Hour, $ReworkType, $User, $Count);
}
$stmt->close();
}
echo "</table>";
it works but still only gives me the hour as a single digit.
How can i go about getting the time in acceptable format?