Can this code be improved upon?

Question

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>";
}
?>

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.