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

Answer 1

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.

Answer 2

use this

 if ($some_id !== 0) {

instead of

 if ($some_id != 0) {