My code already works 100%, just trying to figure out if there is an easier, better or speedier way to do this. Outputs to an HTML table to display results.
Something that took me a while to figure out was how to select the correct parts of the following arrays ($name, $business, $email, $orderstatus) because you end up with the same number of keys in all arrays, except for $orderid -- which you are filtering down to only unique results. So you end up with less keys/rows in the $orderid array compared to the others. This creates the need for "as $key => $value" in the display section to match up the correct order ID's with the correct customer information.
I was also wondering if this is a secure way to handle log ons.
<?php
session_start();
if (!isset($_SESSION['username'])){
header("location:login.php");
}
// DB Credentials
$dbusername = 'xxxx';
$dbpassword = 'xxxx';
// Connect to order database
$conn = new PDO('mysql:host=localhost;dbname=xxxx_orders', $dbusername, $dbpassword);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
// Select Order IDs from database
try {
$stmt = $conn->prepare('SELECT * FROM orders');
$stmt->execute();
}
catch (Exception $e) {
echo '<p>', $e->getMessage(), '</p>';
}
// Add results to array $orderid
foreach ($stmt as $row) {
// If array $orderid does not exist, create it. Otherwise, add results to existing array
if (is_array($orderid)) {
$orderid[] = $row['orderid'];
}
else {
$orderid = array($row['orderid']);
}
// Filter out duplicate order ID numbers from the array.
$orderid = array_unique($orderid);
// If array $email does not exist, create it. Otherwise, add results to existing array
if (is_array($email)) {
$email[] = $row['email'];
}
else {
$email = array($row['email']);
}
// If array $orderstatus does not exist, create it. Otherwise, add results to existing array
if (is_array($orderstatus)) {
$orderstatus[] = $row['orderstatus'];
}
else {
$orderstatus = array($row['orderstatus']);
}
}
// Close PDO connection
$conn = null;
// Connect to customer database
$conn = new PDO('mysql:host=localhost;dbname=xxxx_customers', $dbusername, $dbpassword);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
// For each e-mail in the array $email, run the following code.
foreach ($email as $value) {
// Select all first names, last names, and business names from customers database by email provided in $email
$stmt = $conn->prepare('SELECT fname,lname,business FROM customers WHERE email = :email');
$stmt->bindParam(':email',$value,PDO:: PARAM_INT);
$stmt->execute();
// For each result found in database
foreach ($stmt as $row) {
// If array $name does not exist, create it. Otherwise, add results to existing array.
// Also appends first name and last name from database together to create 1 full name
if (is_array($name)) {
$name[] = $row['fname'] . " " . $row['lname'];
}
else {
$name = array($row['fname'] . " " . $row['lname']);
}
// If array $business does not exist, create it. Otherwise, add results to existing array
if (is_array($business)) {
$business[] = $row['business'];
}
else {
$business = array($row['business']);
}
}
}
?>
<?php
// For each order ID (which has been filtered to unique IDs...) run the following code. $key is equal to the key in array $orderid
// and $value is equal to the actual string/value from the array $orderid
foreach ($orderid as $key => $value) {
echo "<tr>";
// Display First/Last name from array $name with $key being equal to the corresponding key from array $orderid.
// The key is necessary to properly display the correct result
echo "<td align=center>" . $name[$key] . "</td>";
// Display business name from array $name with $key being equal to the corresponding key from array $orderid.
// The key is necessary to properly display the correct result
echo "<td align=center>" . $business[$key] . "</td>";
// Display email from array $name with $key being equal to the corresponding key from array $orderid.
// The key is necessary to properly display the correct result
echo "<td align=center>" . $email[$key] . "</td>";
// Display order status from array $name with $key being equal to the corresponding key from array $orderid.
// The key is necessary to properly display the correct result
echo "<td align=center>" . $orderstatus[$key] . "</td>";
// Display First/Last name from array $name with $key being equal to the corresponding key from array $orderid.
// The key is necessary to properly display the correct result
echo "<td align=center><a href=editorder.php?q=" . $orderid[$key] . ">" . $orderid[$key] . "</a></td>";
echo "</tr>";
}
?>
1 Answer 1
You should look at how your accessing the data from both orders and customers. Your code is currently looping through the result of SELECT * FROM orders to retreive additional data from customers by SELECT fname,lname,business FROM customers WHERE email = :email.
You can perform these queries by using a JOIN and use less overhead on all this looping / variable array creation.
Improved Query
SELECT o.*, c.fname, c.lname, c.business
FROM xxx_orders.orders o, xxx_customers.customers c
WHERE c.email = o.email;
From here, you can loop through it from $stmt->execute() and have all your data in one single array to output in your DOM.