Queries with conditional queries

Question

Let's say i have a query with quite a number of joins and subqueries in one php file that handles queries. Nb: i put an example of what $query looks like at the bottom

$query = query here;
if ($query) {
            return $query->result();
        } else {
            return false;
        }
}

Then in my php file that handles the html, i have the usual foreach loop with some conditions that require making other queries e.g; Note: result houses object $query->result().

foreach ($results as $item) {
        $some_array = array();
        $some_id = $item->id;
        if ($some_id != 0) {
            //id_return_other_id is a function that querys a db table and returns the specified column in the same table, it returns just one field
            $other_id = id_return_other_id($some_id);
            $some_query = another  query that requires some joins and a subquery;
            $some_array = the values that are returned from some_query in an array
            //here i'm converting obj post into an array so i can merge the data in $some_array to item(Which was converted into an array) then convert all of it back into an object
            $item = (object)array_merge($some_array, (array)$item);
        }

//do the usual dynamic html stuff here.
}

This works perfectly but as i don't like the way i'm doing lot's of queries in a loop, is there a way to add the if $some_id != 0 in the file that handles queries? I've tried

$query = query here;
//declaring some array as empty when some_id is 0
$some_array = array();
if ($query) {
            if ($some_id != 0) {
            //same as i said before
            $other_id = $this->id_return_other_id($some_id);
            $some_query = some query;
            $some_array = array values gotten from some query;

        }
          $qresult = (object)array_merge($some_array, (array)$query->result);
          return $qresult;
        } else {
            return false;
        }
}

This doesn't work for obvious reasons, does any one have any ideas?

Also if there's a way to make these conditions and queries in the $query itself i'd love you forever.

Ps: A demo query would be something like

    $sql = "SELECT  p.*,up.*,upi.someField,etc..
                    FROM    (
                             SELECT  (another subquery)
                             FROM    table1
                             WHERE   table1_id = 3
                             UNION ALL
                             SELECT  $user_id
                            ) uf
                    JOIN    table2 p
                    ON      p.id = uf.user_id
                    LEFT JOIN   table3 up
                    ON     .....
                    LEFT JOIN table4
                    ON     ....
                    LEFT JOIN table5
                    ON     ....
                    And so on..etc..
                    ORDER BY p.date DESC";
$query = mysql_query..

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — Naftali
– Naftali, Commented Dec 3, 2012 at 17:40
Thanks Neal, i didn't actually use mysql_ persay, i'm using active record and i just put that on there to explain what i mean :) Do you have any idea how i could go about the problem i have? — Saff
– Saff, Commented Dec 3, 2012 at 18:23

Tommy Adey · Accepted Answer · 2012-12-04 09:22:56Z

2

It seems like you just need to run two queries in your query file. The first query would get a broad set of what you’re looking for. The second query would query an id that’s in the result and perform a new query to get any details about that particular id. I use something similar to this in the customer search page for my application.

$output = array();

$query1 = $this->db->query("SELECT * FROM...WHERE id = ...");

foreach ($query->result_array() as $row1)
{
    $output[$row1['some_id']] = $row1;

    $query2 = $this->db->query("SELECT * FROM table WHERE id = {$row1['some_id']}");

    foreach ($query2->result_array() as $row2)
    {
        $output[$row1['some_id']]['data_details'][$row2['id']] = $row2;
    }
}

Then in your page that displays html, you’ll just need two foreaches:

foreach($queryresult as $key=> $field)
{
    echo $field['some_field'];

    foreach($child['data_details'] as $subkey => $subfield)
    {
        echo $subfield['some_subfield'];
    }
}

I know you’re using objects, but you could probably convert this to use that format. I hope this makes sense/helps.

edited Dec 4, 2012 at 9:22

answered Dec 4, 2012 at 8:50

Tommy Adey

8166 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Saff Over a year ago

Thank youu, it worked!!. Doesn't having a loop within a loop slow down the query?

echo_Me · Accepted Answer · 2012-12-03 17:49:51Z

0

use this

 if ($some_id !== 0) {

instead of

 if ($some_id != 0) {

answered Dec 3, 2012 at 17:49

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

1 Comment

Saff Over a year ago

Nah, it's not the problem. Thanks tho :)

Collectives™ on Stack Overflow

Queries with conditional queries

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related