Run two queries within a table


#1

Hi,

I have working code which retrieves supplier data from a database, I then want to run another query within this to display the last order date from orders table. My current code pulls through the last order date but then it stops the rest of the suppliers data being pulled through… any help is appreciated

My code is;-

$query = “SELECT * FROM tbl_supp WHERE client_id= ‘{$_SESSION[‘client_id_of_user’]}’ ORDER BY id DESC”;
$result = mysqli_query($db, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($db), E_USER_ERROR);

if($result) {
while($row = mysqli_fetch_assoc($result)) {

echo ‘

’;
echo ‘’.$row[‘name’] . ‘’;
echo ‘’.$row[‘tel’] . ‘’;
echo ‘’.$row[‘cat’] . ‘’;
$status = $row[‘status’]==‘APPROVED’ ? ‘success’ : ‘danger’;
echo “”.$row[‘status’]."";

{
$rowname = $row[‘name’];
$order_query = “SELECT date FROM tbl_orders WHERE supplier = $rowname ORDER BY date DESC LIMIT 1”;
$result = mysqli_query($db, $order_query);
$row1 = mysqli_fetch_array($result, MYSQLI_ASSOC);
}
echo ‘

’.$row1[‘date’] . ‘’;

echo ‘

’.$row[‘sat_ser’] . ‘’;
echo ‘’.$row[‘last_updated’] . ‘’;

echo ‘

’;
}
}

#2

You should not run select queries inside of loops. It is extremely inefficient, takes a lot of unnecessary code, and since you are reusing php variables, your code is stopping when it tries to go back to execute the outer loop.

This is actually the same process as in your last thread on this forum. To get related data, use one appropriate type JOIN query that gets the data that you want in the order that you want it, then simply loop over the data from the single query and output the data the way you want.

If you only want the last order date, and no other order information, you can use MAX(date). However, if you actually want the row from the orders table corresponding to the last order, see this link - https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html


#3
$query = 'SELECT 
   ts.name
   ,ts.tel
   ,ts.cat
   ,ts.status
   ,to.date 
FROM 
   `tbl_supp` ts 
INNER JOIN 
   tbl_orders to 
WHERE 
   ts.`client_id` = ? 
ORDER BY `id` DESC';
$pdo->prepare($query);
$pdo->execute([$_SESSION['client_id_of_user']]);

No need to make this more complicated.


#4

Thanks astonecipher, the query is slightly complicated, hopefully this will make it more clear what I’m trying to achieve

I have a table called suppliers, I also have a table called orders. I want to display all of the suppliers where the logged in client_id = ‘2’ (for instance). The query needs to pull all of the suppliers details where the client_id =1 plus it then needs to join the orders and pull the last order date based on the supplier and client_id

tbl_orders

id supplier date client_id
6564 Supplier1 19/10/2015 2
6565 Supplier2 22/10/2015 2
6566 Supplier3 23/10/2015 2
6567 Supplier2 24/10/2015 2
6568 Supplier2 25/10/2015 2
6569 Supplier3 27/10/2015 2
6570 Supplier1 30/10/2015 2
6571 Supplier1 30/10/2015 1
6572 Supplier1 01/11/2015 1

tbl_suppliers

id name Cat status client_id
84 Supplier1 fixings supplier approved 2
85 Supplier2 paint supplier approved 2
90 Supplier3 parts manufacturer approved 2
91 Supplier3 manufacturer approved 1

Required result

name Cat status Last Order Date client_id
Supplier1 fixings supplier approved 30/10/2015 2
Supplier2 paint supplier approved 25/10/2015 2
Supplier3 parts manufacturer approved 27/10/2015 2

#5

thanks phdr, dont know where my brain was at when I started with this code!


#6

In addition to the information I posted above on how to solve this, your orders table needs to store the supplier id, not the supplier name (your current in-loop query would throw an error complaining about a missing column equal to the supplier name - I was hoping that it was the id and was just mis-named as ‘name’ since you stated it did work the first time through the loop), so that the data takes the least amount of storage, the query executes the fastest, and supplier names are editable without affecting all the related data, and the order date column needs to be a DATE data type, which would have a format of YYYY-MM-DD, so that the values are sortable/comparable by magnitude, take the least amount of storage, and the query executes the fastest.

Edit: and the client id’s you are showing in both tables don’t make sense. The suppliers table should hold the information about each supplier. The orders table should hold the information about each order. It would seem that orders are placed by clients, therefore, the orders table is where the client ids would be stored.


#7

I’ll respond when I have more time to go over it. I am too slammed at work presently.


#8

this is where I’m currently at with my code, at the moment I can join the two tables but as theres no limit on the last purchase date it pulls through all orders… This code doesnt work but I know I am close, its the (MAX)purchases.purchaseDate where its wrong - any ideas anyone?

$query = "SELECT supplyChain.supplier_id, supplyChain.name, supplyChain.supplyCat1, supplyChain.supplyCat2, supplyChain.phone, supplyChain.status, supplyChain.supplier_check, (max)purchases.purchaseDate as lastPurchase, purchases.supplier_id 
	FROM `supplyChain` 
	LEFT JOIN purchases 
	ON purchases.supplier_id = supplyChain.supplier_id 
	ORDER BY `name` DESC";

#9

So you want the last purchase date from all suppliers?


#10

yes thats correct astonecipher, the query should list all suppliers in the database and theres a column where its supposed to display the last purchase date but at the moment it displays all orders so it will say…

name last order
Supplier1 0000-00-00
Supplier1 0000-00-00
Supplier1 0000-00-00
Supplier2 0000-00-00
Supplier2 0000-00-00
Supplier2 0000-00-00
Supplier3 0000-00-00
Supplier3 0000-00-00

whereas I want to just display it like this…

name last order
Supplier1 0000-00-00
Supplier2 0000-00-00
Supplier3 0000-00-00

#11
$query = "
SELECT 
  supplyChain.supplier_id
  , supplyChain.name
  , supplyChain.supplyCat1
  , supplyChain.supplyCat2
  , supplyChain.phone
  , supplyChain.status
  , supplyChain.supplier_check
  , (max)purchases.purchaseDate as lastPurchase
  , purchases.supplier_id 
FROM 
   `supplyChain` 
   LEFT JOIN 
   purchases 
       ON purchases.supplier_id = supplyChain.supplier_id 
GROUP BY 
   supplyChain.supplier_id  
ORDER BY `name` DESC";

Should work.


#12

I thought so too, the error I get is…

[13-Dec-2018 15:26:58 Europe/London] PHP Fatal error: Query Failed! SQL:
SELECT
supplyChain.supplier_id
, supplyChain.name
, supplyChain.supplyCat1
, supplyChain.supplyCat2
, supplyChain.phone
, supplyChain.status
, supplyChain.supplier_check
, (max)purchases.purchaseDate as lastPurchase
, purchases.supplier_id
FROM
supplyChain
LEFT JOIN
purchases
ON purchases.supplier_id = supplyChain.supplier_id
GROUP BY
supplyChain.supplier_id
ORDER BY name DESC - Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘.purchaseDate as lastPurchase
, purchases.supplier_id
FROM
`supplyChai’ at line 9 in/public_html/supplyChain.php on line 68


#13

Hold on a sec I need to look at something

SELECT
supplyChain.supplier_id
, supplyChain.name
, supplyChain.supplyCat1
, supplyChain.supplyCat2
, supplyChain.phone
, supplyChain.status
, supplyChain.supplier_check
, MAX(purchases.purchaseDate) as lastPurchase
, purchases.supplier_id
FROM
supplyChain
LEFT JOIN
purchases
ON purchases.supplier_id = supplyChain.supplier_id
GROUP BY
supplyChain.supplier_id
ORDER BY name DESC