[Solved] How to do do multiple selects using data from a previous query?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

How to do do multiple selects using data from a previous query?

Database type:

I'm trying to write a php function that gets all the basic food data. Then get's the ingredients for that food.

So, for the first query, Each row received has a "food id".

Then, I want to take each food id... (this might be where my problem is)

Then Select ingredients from the food_ing table that have the ids I provide. (there might also be an issue with multiple selects)

It'll be returning multiple ingredients for each food. This will be a pretty big array itself.

So, to give some more context to my setup I have 3 tables. food, food_ratings, food_ing.

food_ing is full of ingredients, each row has the count, the ingredient, then the food_id it's associated with.

So what im asking is...what would be the best way to query and return this information?

-An array with each food, it's id, description, type...

-An array with all the ingredients for all the foods originally queried. (I figure I can sort them to their respective food on the AS3 side after I get the data back)

I was thinking something like this. But it's not working. I can't seem to get each foods id to work in my loop that's building my select ingredients loop thing. I suppose this is because im using

What would be awesome if someone could explain how to do this all in 1 query.

function getFoods($start, $limit) {

    $qOne = mysql_query("SELECT a.id, a.name, a.type, AVG(b.r) AS fra, COUNT(b.id) as tvotes FROM `foods` a LEFT JOIN `foods_ratings` b ON a.id = b.id GROUP BY a.id ORDER BY fra DESC, tvotes DESC LIMIT $start, $limit;");

    $arrOne = mysql_fetch_array($qOne);

    $i = 0;
    $qry = "";
    foreach ($arrOne as $value) {
        $fid = $value['id'];
        if ($i > 0)
            $qry .= " UNION ";
        $i++;
        $qry .= "SELECT fid, ing, amount FROM foods_ing WHERE fid='$fid'";
    }

    $qTwo = mysql_query($qry);

            /*
            if this helps anyone...I'm using amfphp to communicate with an AS3 project.
            also returning $qOne works. so no issues there. The issue is just with $qTwo.
            Unless the reccomendation is to somehow do this with 1 query.
            */

    return array($qOne, $qTwo);
}

Actually I might have figured out why it was looking weird. It seems that was only getting 1 row. I tried this, but im still having trouble. now nothing is being returned

EDIT: sharing another loop i tried. Now the first result is not working...Does using this while loop ruin my result or something

but at least with this loop, its getting the query correct, and returning the proper result.(for the second array)

function getFoods($sort, $start, $limit) {

            $qOne = mysql_query("SELECT a.id, a.name, a.type, AVG(b.r) AS fra, COUNT(b.id) as tvotes FROM `foods` a LEFT JOIN `foods_ratings` b ON a.id = b.id GROUP BY a.id ORDER BY fra DESC, tvotes DESC LIMIT $start, $limit;");

            $i = 0;
        $qry = "";
        while ($row = mysql_fetch_array($qOne)) {
            $fid = $row['id'];
            if ($i > 0)
                $qry .= " UNION ";
            $i++;
            $qry .= "SELECT fid, ing, amount FROM foods_ing WHERE fid='$fid'";
        }

            $qTwo = mysql_query($qry);

            return array($qOne, $qTwo);
        }

EDIT:

Here's another attempt at trying to do it all with 1 query. But it's just returning the first values it gets for ing and amount (It should be an array that's coming back for ing because there are at least a few ingredients

SELECT c.ing, c.amount, a.id, a.name, a.type, AVG(b.r) AS fra, 
COUNT(b.id) as tvotes 
FROM foods a 
LEFT JOIN foods_ratings b ON a.id = b.id, foods_ing c 
GROUP BY a.id 
ORDER BY fra DESC, tvotes DESC 
LIMIT $start, $limit

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OFFSET In LIMIT Clause (query line: 19): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `foods` ADD INDEX `foods_idx_id` (`id`);
ALTER TABLE `foods_ratings` ADD INDEX `foods_ratings_idx_id` (`id`);
The optimized query:
SELECT
        c.ing,
        c.amount,
        a.id,
        a.name,
        a.type,
        AVG(b.r) AS fra,
        COUNT(b.id) AS tvotes 
    FROM
        foods a 
    LEFT JOIN
        foods_ratings b 
            ON a.id = b.id,
        foods_ing c 
    GROUP BY
        a.id 
    ORDER BY
        fra DESC,
        tvotes DESC LIMIT $start,
        $limit

Related Articles



* original question posted on StackOverflow here.