Run two queries within a table

DanTipple · November 28, 2018, 9:50am

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 ‘
’;
}
}

phdr · November 28, 2018, 3:48pm

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

astonecipher · November 28, 2018, 5:14pm

$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.

DanTipple · November 29, 2018, 9:07am

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

DanTipple · November 29, 2018, 9:08am

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

phdr · November 29, 2018, 12:53pm

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.

astonecipher · November 29, 2018, 6:50pm

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

DanTipple · December 13, 2018, 12:01pm

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

astonecipher · December 13, 2018, 2:26pm

So you want the last purchase date from all suppliers?

DanTipple · December 13, 2018, 2:51pm

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

astonecipher · December 13, 2018, 2:59pm

$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.

DanTipple · December 13, 2018, 3:28pm

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

astonecipher · December 13, 2018, 7:23pm

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