1
\$\begingroup\$

I wrote a piece of code. and to be honest it seems a bit messy so I would like some opinions on it, and if I can make it cleaner. Or if there are better ways to do it:

<?php

include "Databaseconnect.php";

$result = $database->prepare("SELECT * FROM user WHERE Username=?");
$result->bindParam(1, $_SESSION['username']);

$result->execute();
$fetchedData = $result->fetch(PDO::FETCH_ASSOC);

$result = $database->prepare("SELECT * FROM garage WHERE Garage_Naam=?");
$result->bindParam(1, $fetchedData['Company']);

$result->execute();
$fetchedDataCompany = $result->fetch(PDO::FETCH_ASSOC);

$result = $database->prepare("SELECT * FROM factuur WHERE Garage_Nummer=?");
$result->bindParam(1, $fetchedDataCompany['Garage_Nummer']);

$result->execute();

$countRowsExistence = $result->rowCount();

if($countRowsExistence != 0) {
    $fetchedDataInvoice = $result->fetch(PDO::FETCH_ASSOC);

    $result = $database->prepare("SELECT * FROM factuur_regel WHERE 
Factuur_Nummer=?");
    $result->bindParam(1, $fetchedDataInvoice['Factuur_Nummer']);

    $result->execute();
    $fetchedDataInvoiceRegel = $result->fetchAll(PDO::FETCH_ASSOC);

    foreach($fetchedDataInvoiceRegel as $key => $item) {
            $stmt = $database->prepare("SELECT * FROM product WHERE 
Product_Nummer=?");
            $stmt->bindParam(1, $item['Product_Nummer']);

            $stmt->execute();
            $fetchedDataProduct[$key] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
    } else {
    header("location: landingpage.php");
}
\$\endgroup\$
7
  • 1
    \$\begingroup\$ It should be just a single query with multiple JOINs. \$\endgroup\$ Commented Dec 18, 2017 at 5:24
  • \$\begingroup\$ @YourCommonSense please add an answer instead of a comment. Refer to the section When shouldn't I comment? on Comment everywhere. \$\endgroup\$ Commented Dec 18, 2017 at 19:30
  • 1
    \$\begingroup\$ @SamOnela if I'll find the time. I's not a code review though, but a basic SQL education in my opinion. \$\endgroup\$ Commented Dec 19, 2017 at 7:13
  • \$\begingroup\$ @YourCommonSense thx for the notice tho ill try to create querry with joins \$\endgroup\$
    – H. Brendan
    Commented Dec 19, 2017 at 13:55
  • \$\begingroup\$ @YourCommonSense uhm. i know this is more a stack overflow question. but how would i put those in joins. sql/pdo is kind of new to me :S fiddled a bit around with it but cant get it to work. \$\endgroup\$
    – H. Brendan
    Commented Dec 19, 2017 at 15:24

2 Answers 2

0
\$\begingroup\$

As @YourCommonSense wrote, this is way too complicated, instead of doing everything in PHP you better learn SQL.

Your nested executes can be transformed into joins, but without knowledge about Primary Keys it's easier as a bunch of nested Subqueries:

SELECT * 
FROM product 
WHERE Product_Nummer IN
 (
   SELECT * 
   FROM factuur_regel 
   WHERE Factuur_Nummer IN
    (
      SELECT Factuur_Nummer 
      FROM factuur 
      WHERE Garage_Nummer IN
       (
         SELECT Garage_Nummer
         FROM garage 
         WHERE Garage_Naam IN
          (
            SELECT Company 
            FROM user 
            WHERE Username=?
          )
       )
    )
 )
\$\endgroup\$
1
  • \$\begingroup\$ oh i see. thank you for this i almost got this in my second version but this seems cleaner, \$\endgroup\$
    – H. Brendan
    Commented Dec 20, 2017 at 11:29
2
\$\begingroup\$

Trying my hand at inferring primary/foreign keys based on your original code, and to expand on @dnoeth's answer, a JOIN query would be perfect. It looks like you are ultimately showing products to the user, so a single query should do the trick:

include "Databaseconnect.php";

$product_sql = "SELECT product.*
FROM garage
    JOIN factuur
        ON factuur.garage_nuumer = garage.garage_nuumer
    JOIN factuur_regel
        ON factuur_regel.factuur_nuumer = factuur.factuur_nuumer
    JOIN product
        ON product.product_nuumer = factuur_regel.product_nuumer
WHERE garage.garage_naam = ?";

$result = $database->prepare($product_sql);
$result->bindParam(1, $_SESSION['username']);
$result->execute();
$productsData = $result->fetch(PDO::FETCH_ASSOC);
\$\endgroup\$
1
  • \$\begingroup\$ i see, thanks for the respond. and yes i have put it in one querry now :) \$\endgroup\$
    – H. Brendan
    Commented Dec 25, 2017 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.