Databases
StackOverflow2013
Postsdbo
POQueries with conditional queries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQueries with conditional queries
    primarykey
    data
    text
    <p>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</p> <pre><code>$query = query here; if ($query) { return $query-&gt;result(); } else { return false; } } </code></pre> <p>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().</p> <pre><code>foreach ($results as $item) { $some_array = array(); $some_id = $item-&gt;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. } </code></pre> <p>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</p> <pre><code>$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-&gt;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-&gt;result); return $qresult; } else { return false; } } </code></pre> <p>This doesn't work for obvious reasons, does any one have any ideas? </p> <p>Also if there's a way to make these conditions and queries in the $query itself i'd love you forever.</p> <p>Ps: A demo query would be something like </p> <pre><code> $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.. </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSaff
    1. USSaff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POQueries with conditional queries
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained and the [deprecation process](http://j.mp/Rj2iVR) has begun on it. See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ).
      singulars
      1. POQueries with conditional queries
      1. USNeal
    2. COThanks 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?
      singulars
      1. POQueries with conditional queries
      1. USSaff
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload