PHP PDO statements

Question

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

• Your Common Sense: It should be just a single query with multiple JOINs.
• Sᴀᴍ Onᴇᴌᴀ: @YourCommonSense please add an answer instead of a comment. Refer to the section When shouldn't I comment? on Comment everywhere.
• Your Common Sense: @SamOnela if I'll find the time. I's not a code review though, but a basic SQL education in my opinion.
• H. Brendan: @YourCommonSense thx for the notice tho ill try to create querry with joins
• H. Brendan: @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.

Answer 1

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=?
 )
 )
 )
 )

• H. Brendan: oh i see. thank you for this i almost got this in my second version but this seems cleaner,

Answer 2

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);

• H. Brendan: i see, thanks for the respond. and yes i have put it in one querry now :)