Member Avatar for Fernando_4

Hi all, I've made a query that gathers info from several tables, after it was working, there was the need to integrate yet another table, however I can't seem to put it to work. I've seen some of this "witchcraft" before, but can't seem to find a tutorial, basically because I don't know what to call it and google doesn't guess for me xD

This block is working as intended:

SELECT 
car.carID, car.carBAR, car.carTYPE, 
car.carNAME, car.paxMIN, car.paxMAX, 
car.babySEAT, car.boosterSEAT, car.maxLUGGAGE, 
car.carMARKUP, car.carSEASON, car.carOVERTIME, 
price.PRICE, price.DISCOUNT, toll.tollsPRICE
FROM `AcarCONFIG` AS car 
INNER JOIN `ptFAOatt` AS price ON car.carID = price.carID
LEFT JOIN `ptTOLLS` AS toll ON toll.tollsID = price.tollsID
WHERE car.serviceID = 1
AND car.paxMIN <= 2
AND car.paxMAX >= 2
AND ((price.zoneA = 2 AND price.zoneB = 7) OR (price.zoneA = 7 AND price.zoneB = 2))
AND car.carSTATUS > 0
AND price.STATUS > 0
ORDER BY car.carSTATUS ASC

This is the bit I need to implement with the previous query. I tried chaining them as I've seen around the web, but it keeps giving me errors and I can't seem to find solutions.

(SELECT x.extraType, x.extraFree, x.extraMax, x.extraPrice
FROM `AcarExtras`
WHERE x.status > 0
AND x.carID = car.carID)

So we have 3 tables on the fist query, AcarCONFIG that has a list of cars and their specs, ptFAOatt that has prices from given zoneA and zoneB by carID (order is not important A->B or B->A) and ptTOLLS that gives me the highway toll price.
Now each car is going to have certain extras with associated free and maximum amounts, a price and a type to select the html block.

So for every car that comes out of the 1st query I'd want to list all of it's extras in one go. I know I can take the 1st query's carID's and run them through a simple query on the extras table. But I'm sure there's some way to get this done in one hit.

Any links to documentation on these selects enclosed in ()'s would help me figure out what they're really for. Although seeing it in action might do the trick as well.

  • 2 Contributors
  • 3 Replies
  • 484 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Fernando_4

Dani AI

Generated

Nice progress — the core issue was the 1-to-many extras producing repeated car rows, and the LEFT JOIN + grouping in PHP that you ended up with is a safe, easy fix. Two alternatives can reduce work on the PHP side and return one row per car directly from SQL: server-side string aggregation, or server-side JSON aggregation so PHP just decodes the extras field.

Server-side string aggregation (works on all MySQL versions): use GROUP_CONCAT to squash each car’s extras into a single text field, then split or parse in PHP. Example pattern:

SELECT c.carID,
       c.carName,
       GROUP_CONCAT(CONCAT_WS('|', x.extraType, x.extraFree, x.extraMax, x.extraPrice)
                    ORDER BY x.status SEPARATOR ';') AS extras
FROM AcarCONFIG c
LEFT JOIN AcarExtras x ON x.carID = c.carID AND x.status > 0
... -- filters
GROUP BY c.carID;

Remember GROUP_CONCAT has a max length controlled by group_concat_max_len (adjust if needed). (dev.mysql.com)

If your server supports it (MySQL 5.7.22+ / 8.x), produce proper JSON from SQL and json_decode in PHP — cleaner and less error-prone:

SELECT c.carID,
       c.carName,
       JSON_ARRAYAGG(JSON_OBJECT('type', x.extraType, 'free', x.extraFree, 'max', x.extraMax, 'price', x.extraPrice)
                     ORDER BY x.status) AS extras_json
FROM ...
GROUP BY c.carID;

That returns a JSON array per car you can json_decode directly. (dev.mysql.com)

If you prefer PHP-side grouping, PDO offers FETCH_GROUP so you can fetch rows already grouped by the first column, or fetchAll and reduce them into a nested array (faster than repeated queries). Example hint: use fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC) and then map/reset as needed to get one car => extras structure. Also add an index on the extras join column (carID) and use EXPLAIN to validate plans when joins get heavier. (php.net)

All three approaches are valid; choose based on server version and whether you want parsing work in SQL or PHP. Good job getting the LEFT JOIN working — that gave you the correct rows to optimize from.

Recommended Answers

All 3 Replies

Member Avatar for Szabi Zsoldos

Can you post an sqlfiddle ?

Member Avatar for Fernando_4

Ok, I've been trying to get this together and it seems to be working. Different approach though. Give me some suggestions as to how to better this plz.

I'm using prepared statements to run the query then building an array in the while loop, which then is used to build the client's output.

My while loops was something like this:

while($stmt->fetch()) {
    $temp =  array(
        "id" => $id,
        "bar" => $bar,
        "type" => $type,
        "name" => $name,
        "pMin" => $pMin,
        "pMax" => $pMax,
        "bbSeat" => $bbSeat,
        "boSeat" => $boSeat,
        "lugg" => $lugg,
        "markup" => $markup,
        "season" => $season,
        "over" => $over,
        "price" => $price,
        "discount" => $discount,
        "toll" => $toll
    );
    $carList[$id] = $temp;
}

So now I guess I just need to create a secondary temp array to store the extras and then put it into $carList[$id]['extras'] turning into something like this (the result array):

$carList =  array(
    $id => array(
        "id" => $id,
        "bar" => $bar,
        "type" => $type,
        "name" => $name,
        "pMin" => $pMin,
        "pMax" => $pMax,
        "extras" => array(
            0 => $free,
            1 => $max,
            2 => and so forth...
        ),
        "markup" => $markup,
        "season" => $season,
        "over" => $over,
        "price" => $price,
        "discount" => $discount,
        "toll" => $toll
);

Here is the SQLFiddle you asked for Szabi.

Member Avatar for Fernando_4

Looks like I got it working... seeing other people's queries made me process thoughts another way which weren't working. The Fiddle posted above is almost there though, here's the fix:

SELECT 
car.carID, car.carBAR, car.carTYPE, 
car.carNAME, car.paxMIN, car.paxMAX, 
car.babySEAT, car.boosterSEAT, car.maxLUGGAGE, 
car.carMARKUP, car.carSEASON, car.carOVERTIME, 
price.PRICE, price.DISCOUNT, toll.tollsPRICE,

x.extraType, x.extraFree, x.extraMax, x.extraPrice

FROM `AcarCONFIG` AS car 
INNER JOIN `ptFAOatt` AS price ON car.carID = price.carID
LEFT JOIN `ptTOLLS` AS toll ON toll.tollsID = price.tollsID

LEFT JOIN `AcarExtras` AS x ON x.carID = car.carID AND x.status > 0

WHERE car.serviceID = 1
AND car.paxMIN <= 2
AND car.paxMAX >= 2
AND ((price.zoneA = 2 AND price.zoneB = 7) OR (price.zoneA = 7 AND price.zoneB = 2))
AND car.carSTATUS > 0
AND price.STATUS > 0

ORDER BY car.carSTATUS ASC, x.status ASC

Just adding those 2 lines with the edit on the last made it all work^^

Edited by Fernando_4 because: Left some values unfilled due to copy+paste
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.