Thursday, March 7

SQL queries vs iterating through PHP

So I was playing with code today.  A very common loop in PHP is the foreach loop, which is especially useful for iterating through arrays. However, when dealing with more complex multidimensional arrays, like those you get from an SQL query they are big and bloaty so I figured there must be a better way to get the exact information I wanted from the database in one SQL query.

Here is what I started with..

$getArray = get_results("SELECT ID FROM dbns1.field WHERE fid='3' AND field_value='true'");
$prefix = '';
foreach ($getArray as $Array){
   foreach($Array as $getID){
      $IDList .= $prefix . $getID ;
      $prefix = ', ';

This is only the first of three table queries. I stopped here because I would have to call another database query using the $IDList as a modifier. Than another query based on that... and so on. Silly and unnecessary. I figured there must be a better way so I spent about two hours researching the interwebs. Here is the solution I found that gets me all my database tables in one beautiful query.

FROM pictures
JOIN gallery ON ( = gallery.previewpic)
JOIN field ON ( =
WHERE field.fid = '3' AND field.field_value='true'

Join & Inner Join commands make SQL queries a lot easier to handle. In my research I also learned about subquries which I also want to learn more about as well.

No comments:

Post a Comment