Give it a try.
- You should create only one PDO instance, e.g. connection. It should be passed as argument to each function that uses it.
- Of course, transfer
style attributes to classes. I implemented my own html.
Some recommendations:
- Try to move to OOP.
- Apply exception handling and activate error reporting/handling. I gave you a general view by throwing only
Exception. Normally you should throw and handle the SPL (Standard PHP Library) subtypes of it.
- Always read in the PHP Manual what PHP functions are returning so that you can correctly apply handle cases (exceptions, bools, etc).
Resources:
Here is the code, spread (as it should) on four PHP pages. index.php is the main page, the others are included in it.
index.php
<?php
require_once 'configs.php';
require_once 'generalFunctions.php';
require_once 'ordersFunctions.php';
// Activate error reporting (only on development).
activateErrorReporting();
try {
// Create db connection.
$connection = createConnection(
MYSQL_HOST
, MYSQL_DATABASE
, MYSQL_USERNAME
, MYSQL_PASSWORD
, MYSQL_PORT
, MYSQL_CHARSET
);
$takeawayId = 1;
$paid = 1;
// Fetch paid orders by takeaway.
$fetchedOrders = fetchPaidOrdersByTakeaway($connection, $takeawayId, $paid);
// For testing.
// printData($fetchedOrders, TRUE);
// Close connection
closeConnection($connection);
} catch (PDOException $pdoException) {
// On development.
printData($pdoException, TRUE);
// On production.
// echo $pdoException->getMessage();
exit();
} catch (Exception $exception) {
// On development.
printData($exception, TRUE);
// On production.
// echo $exception->getMessage();
exit();
}
// Group orders by users.
$ordersByUsers = groupOrdersByUsers($fetchedOrders);
// For testing.
// printData($ordersByUsers, TRUE);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Orders</title>
<!-- Bootstrap -->
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" type="text/css" rel="stylesheet" />
<!-- jQuery -->
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript" integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=" crossorigin="anonymous"></script>
<!-- Bootstrap -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" type="text/javascript"></script>
</head>
<body>
<div class="container" style="margin-top: 40px;">
<div class="row">
<div class="col-xs-12">
<?php
foreach ($ordersByUsers as $userId => $item) {
$username = $item['username'];
$ordersPrice = $item['ordersPrice'];
$orders = $item['orders'];
?>
<div class="row">
<div class="col-xs-12 col-md-10" style="background-color: #FAFAF8; border: 1px solid #ddd; padding: 20px; margin-bottom: 20px;">
<div class="row" style="padding: 20px;">
<div class="col-xs-12 col-sm-3">
<div class="row">
<a href="#">
<img src="http://santetotal.com/wp-content/uploads/2014/05/default-user.png" class="img-responsive" alt="Restaurant logo" height="92" width="102">
</a>
</div>
<div class="row" style="padding-top: 10px;">
<a href="#" style="text-transform: uppercase;">
<?php echo $username; ?>
</a>
</div>
</div>
<div class="col-xs-12 col-sm-9">
<?php
foreach ($orders as $order) {
$orderId = $order['orderId'];
$itemname = $order['itemname'];
$price = $order['price'];
$date = $order['date'];
?>
<div class="row">
<div class="col-xs-12" style="background-color: #FFF; border: 1px solid #ddd; padding: 20px; margin-bottom: 20px;">
<h4>
<a href="profile.html" style="color: orange;">
<?php echo $itemname; ?>
</a>
</h4>
<div>
<div>
<span>
<?php echo $date; ?>
</span>
</div>
<div class="ratings">
<span>5 STARS</span>
</div>
</div>
</div>
</div>
<?php
}
?>
</div>
</div>
</div>
<div class="col-xs-12 col-md-2" style="padding: 20px; padding-top: 40px; font-size: 20px; color: #666; text-transform: uppercase;">
Total: <?php echo $ordersPrice; ?>
</div>
</div>
<?php
}
?>
</div>
</div>
</div>
</body>
</html>
orderFunctions.php
<?php
/*
* ---------------------
* Orders functions
* ---------------------
*/
/**
* Fetch paid orders by takeaway.
*
* @param PDO $connection Connection instance.
* @param integer $takeawayId Takeaway ID.
* @param integer $paid Paid.
* @throws Exception
*/
function fetchPaidOrdersByTakeaway($connection, $takeawayId, $paid) {
if (!isset($takeawayId)) {
throw new Exception('Takeaway ID not provided!');
}
if (!isset($paid)) {
throw new Exception('Paid not provided!');
}
// Sql statement.
$sql = 'SELECT
ord.id AS orderId,
usr.id AS userId,
usr.username,
ord.itemname,
ord.price,
ord.date
FROM orders AS ord
LEFT JOIN users AS usr ON usr.id = ord.user_id
WHERE
takeawayid = :takeawayid
AND paid = :paid
ORDER BY
usr.username ASC,
ord.date DESC';
// Prepare and check sql statement (returns PDO statement).
$statement = $connection->prepare($sql);
if (!$statement) {
throw new Exception('The SQL statement can not be prepared!');
}
// Bind values to sql statement parameters.
$statement->bindValue(':takeawayid', $takeawayId, getInputParameterDataType($takeawayId));
$statement->bindValue(':paid', $paid, getInputParameterDataType($paid));
// Execute and check PDO statement.
if (!$statement->execute()) {
throw new Exception('The PDO statement can not be executed!');
}
// Fetch data.
$fetchedData = $statement->fetchAll(PDO::FETCH_ASSOC);
if ($fetchedData === FALSE) {
throw new Exception('Fetching data failed!');
}
return $fetchedData;
}
/**
* Group orders by users.
*
* @param array $orders [optional] Orders list.
* @return array Orders list grouped by users.
* @throws Exception
*/
function groupOrdersByUsers(array $orders = array()) {
$groupedOrders = array();
foreach ($orders as $order) {
$userId = $order['userId'];
$username = $order['username'];
$price = $order['price'];
// Check and add user name as key, if not already.
if (!array_key_exists($userId, $groupedOrders)) {
$groupedOrders[$userId] = array(
'username' => $username,
'orders' => array(),
'ordersPrice' => 0,
);
}
// Add order to grouped orders list.
$groupedOrders[$userId]['orders'][] = $order;
$groupedOrders[$userId]['ordersPrice'] += $price;
}
return $groupedOrders;
}
configs.php
<?php
/*
* ----------------
* Database configs
* ----------------
*/
define('MYSQL_HOST', '...');
define('MYSQL_PORT', '3306');
define('MYSQL_DATABASE', '...');
define('MYSQL_CHARSET', 'utf8');
define('MYSQL_USERNAME', '...');
define('MYSQL_PASSWORD', '...');
generalFunctions.php
<?php
/*
* ---------------------
* Data access functions
* ---------------------
*/
/**
* Create a new db connection.
*
* @param string $host Host.
* @param string $dbname Database name.
* @param string $username Username.
* @param string $password Password.
* @param string $port [optional] Port.
* @param array $charset [optional] Character set.
* @param array $options [optional] Driver options.
* @return PDO Db connection.
*/
function createConnection($host, $dbname, $username, $password, $port = '3306', $charset = 'utf8', $options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_PERSISTENT => true,
)) {
$dsn = getDsn($host, $dbname, $port, $charset);
$connection = new PDO($dsn, $username, $password);
foreach ($options as $key => $value) {
$connection->setAttribute($key, $value);
}
return $connection;
}
/**
* Create a mysql DSN string.
*
* @param string $host Host.
* @param string $dbname Database name.
* @param string $port [optional] Port.
* @param array $charset [optional] Character set.
* @return string DSN string.
*/
function getDsn($host, $dbname, $port = '3306', $charset = 'utf8') {
$dsn = sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s'
, $host
, $port
, $dbname
, $charset
);
return $dsn;
}
/**
* Close a db connection.
*
* @param PDO $connection Db connection.
* @return void
*/
function closeConnection($connection) {
$connection = NULL;
}
/**
* Get the data type of a binding value.
*
* @param mixed $value Binding value.
* @return mixed Data type of the binding value.
*/
function getInputParameterDataType($value) {
$dataType = PDO::PARAM_STR;
if (is_int($value)) {
$dataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$dataType = PDO::PARAM_BOOL;
}
return $dataType;
}
/*
* ---------------
* Print functions
* ---------------
*/
/**
* Print data on screen.
*
* @param mixed $data Data to print.
* @param bool $preformatted Print preformatted if TRUE, print normal otherwise.
* @return void
*/
function printData($data, $preformatted = FALSE) {
if ($preformatted) {
echo '<pre>' . print_r($data, true) . '</pre>';
} else {
echo $data;
}
}
/*
* -------------------------
* Error reporting functions
* -------------------------
*/
/**
* Toggle error reporting.
*
* @param integer $level Error level.
* @param bool $displayErrors Display errors if TRUE, hide them otherwise.
* @return void
*/
function activateErrorReporting($level = E_ALL, $displayErrors = TRUE) {
error_reporting($level);
ini_set('display_errors', ($displayErrors ? 1 : 0));
}
Used tables:
`users` table
`items` table
`orders` table
Result:
