Retrieve multiple rows from multiple tables

Question

I am trying retrieve multiple rows from multiple tables but I think I am not doing it in the right way. The project is kind of a shop online, I have 3 tables in it: orders, orderdetails and services, which all are linked with an ID:

I have Order ID and Service ID in orderdetails' table, it means I inserts a row for each item on the basket linked to Service ID to see which service is, and Order Id to check for which order are. Example:

services table
-
service_id|name   |price
------------------------
2         |Tech   |100
------------------------
4         |Support|150
------------------------
10        |Mainten|50
------------------------

orders table
-
order_id|customer_id|name|lastname
----------------------------------
10      |16         |John|Smith
----------------------------------

orderdetails table
-
orderdetails_id|order_id|service_id|price|quantity
--------------------------------------------------
1              |10      |2         |100  |4
--------------------------------------------------
2              |10      |4         |150  |2
--------------------------------------------------
3              |10      |10        |50   |1
--------------------------------------------------

I inserts service's price on orderdetails table because maybe the services price can change AFTER a customer order it.

At this moment I have this query:

$query = $this->db->prepare(
'SELECT orders.*, orderdetails.*, services.*
FROM  orders
LEFT JOIN orderdetails
ON orderdetails.order_id = orders.order_id
LEFT JOIN services
ON orderdetails.service_id = services.service_id
WHERE orders.order_id = ?
AND orders.customer_id = ?');

And I got this result:

    stdClass Object
(
    [order_id] => 10
    [customer_id] => 16
    [name] => Tech
    [lastname] => Smith
    [orderdetails_id] => 1
    [service_id] => 2
    [price] => 100
    [quantity] => 4
)
stdClass Object
(
    [order_id] => 10
    [customer_id] => 16
    [name] => Support
    [lastname] => Smith
    [orderdetails_id] => 2
    [service_id] => 4
    [price] => 150
    [quantity] => 2
)
stdClass Object
(
    [order_id] => 10
    [customer_id] => 16
    [name] => Mainten
    [lastname] => Smith
    [orderdetails_id] => 3
    [service_id] => 10
    [price] => 50
    [quantity] => 1
)

I have two problems. The 1st problem is I have the same column name in orders table and services table. The 2nd is the query returns all the information (because I know I am not querying well), but I expect to receive something like this:

stdClass Object
(
    [order_id] => 10
    [customer_id] => 16
    [name] => John
    [lastname] => Smith
    [orderdetails_id] => 1
    [service_id] => 10
    [price] => 50
    [quantity] => 1
    [service_name] => Mainten
    [orderdetails_id2] => 2
    [service_id2] => 4
    [price2] => 150
    [quantity2] => 2
    [service_name2] => Support
    [orderdetails_id3] => 3
    [service_id3] => 2
    [price3] => 100
    [quantity3] => 4
    [service_name3] => Tech
)

I mean, I am not an expert in SQL Queries, and I read a lot, but I think you guys could help me to figure it out this because I have other two tables to link with: customer-service-worker who will get the order to process, and area's table who will receive the order.

I use this code for getting the objects:

$array = array(); 
while($loop = $result->fetch_object()){ $array[] = $loop; }
return $array;

The query should return more or less what you want, so it doesn't seem a problem with SQL but with PHP. How do you get that objects from the query results? It seems that the code you use discard or merge duplicate columns names. — Alberto Martinez, Oct 30 '17 at 22:36
I use this: `$array = array(); while($loop = $result->fetch_object()){ $array[] = $loop; } return $array;` — kh0d3x, Oct 30 '17 at 22:40

score 0 · Accepted Answer · answered Oct 30 '17 at 22:58

0

The problem is that you are using fetch_object() for getting the result but you are not renaming in the query the columns with the same name, and since you can't have two different object attributes with the same name the rest of columns are discarded.

You can either use other method for getting the values like fetch_row() or change the query to rename columns with the same name, for example:

SELECT orders.*, orderdetails_id, service_id, orderdetails.price as detail_price, quantity,
  services.name as service_name, services.price as service_price
FROM orders
LEFT JOIN orderdetails ON orderdetails.order_id = orders.order_id
LEFT JOIN services ON orderdetails.service_id = services.service_id
WHERE orders.order_id = ?

As a side note if order_id is the primary key of orders you don't need to use customer_id in the where condition, and if the primary key is composed of both columns (i.e., you can have the same order ID for different customers) I recommend change it and use only order_id.

answered Oct 30 '17 at 22:58

Alberto Martinez

2,620
4
25
28

I totally agree with your answer. That solve the 1st problem, and what about the 2nd one? I mean, maybe I am in the right way but I am not sure. If the customer bought 3 items, that means 3 rows in orderdetails' table. Is there anyway or solution to receive one object will the 3 items that the customer bought? Maybe with group_concat? This is only for aesthetic issues. Something like this topic: https://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql – kh0d3x Oct 30 '17 at 23:28
Sorry, I didn't noticed about the second question. Regarding that, it could be possible merge the three results in the same object but it's not a good idea, its better having an array of objects as you have now, because with only one object you have different names for the same concepts, so you have to use dynamic code (which defeat the purpose of putting the results in an object instead of in an associative array). As a rule of thumb, when you have something like 'name1, name2, name3...' for an unlimited number of items you should refactor the data. – Alberto Martinez Oct 30 '17 at 23:42
So, you recommend use the same object that I have right now? Here is why I said "Maybe I am in the right way, but I am not sure". I am not sure if retrieving the customer name and lastname in every loop for orderdetails's table (who has the items that customer bought) is a good practice. Maybe is redundant, but I don't like to make one query to get order, and a second query to get items, and a third one to get customer data, I think that is not good. Thanks a lot, I will use your recommendation. – kh0d3x Oct 30 '17 at 23:56
Yes, I recommend using one object for each row, it has several benefits, e.g. it's easier use it in a loop. Regarding using one query vs multiples queries it depends on the data, if you don't "duplicate" a lot of columns and the results are usually a few rows you save a call to the DB, but if you include a lot of columns of the master row in each detail result and the query can return a lot of rows it would be better having separate queries. I personally use separate queries most of the time, but sometimes I include master fields in order to avoid a query for just a row with one/two columns. – Alberto Martinez Oct 31 '17 at 00:14

Retrieve multiple rows from multiple tables

1 Answers1