Is it OK to run the WHILE loops in MySQL?

Question

Is it ok to a mysql query inside a while loop using the ID of each row passed to fetch results from another table? OR is there a better way to do it?

$q = $__FROG_CONN__->query("SELECT cms_page.id, cms_page.title, cms_page.slug, cms_page_part.* FROM cms_page LEFT JOIN cms_page_part ON cms_page_part.page_id=cms_page.id WHERE cms_page.parent_id='8'");
$r = $q->fetchAll(PDO::FETCH_ASSOC);

echo '<ul id="project-list">';
  foreach ($r as $row) { 
    echo '<li>';
    echo '<a href="'.$row["slug"].'.html"><img src="<img src="phpThumb/phpThumb.php?src=public/images/'.$row[0].'/th.jpg&w=162" alt="" /></a>';
    echo '<div class="p-text">';
    echo '<h4>'.$row["location"].'<span>'.$row["project_date"].'</span></h4>';
    echo '<p>'.$row["body"].'</p>';
    echo '</div>';
    echo '</li>';
  }
echo '</ul>';

I am trying to pull the project_date, body and location fields from another table where the sql query matches. The title and slug are held in another table. There should only be a maximum of eight or so results but im getting alot more.

Answer 1

To reduce the overhead of preforming a query, you may want to look at getting all the data in a single query. In which case you may want to take a look at IN(), e.g.

SELECT * WHERE x IN (1, 2);

There is also BETWEEN()

SELECT * WHERE x BETWEEN 1 AND 2;

See the mysql docs for more information

Answer 2

In a small application / small result set, this might be okay, but it results in a lot of (small) calls to the database.

If you can find an alternative way (perhaps see Yacoby's suggestion?) in which you can do one call to the database, this is probably better.

EDIT

If you are only interested in the IDs from one table, in order to get the correct results out of another table, perhaps a JOIN is what you are looking for?

SELECT t1.fieldA, t1.fieldB
FROM table1 t1
JOIN table2 t2 ON t1.ID = t2.ID
WHERE t2.someField = 'someValue'

Answer 3

I would try to build the query in a way where I only need to pass it once. Something like WHERE ID=1 OR ID=2 OR ... Passing multiple queries and returning multiple recordsets is expensive in terms of processing and network traffic.

Answer 4

Is it ok to a mysql query inside a while loop using the ID of each row passed to fetch results from another table? OR is there a better way to do it?

You should reformulate your query in SQL. Say, put the ids into a memory table and use it in a JOIN:

SELECT  *
FROM    idtable
JOIN    mytable
ON      mytable.id = idtable.id

This way, MySQL will make the loops for you but will make them in (usually) more efficient way.

SQL is a language designed to work with sets.

So if you have a set of ids and a table (which is a set of tuples), your first thought should be "how do I apply the set-based operations to these sets using SQL"?

Of course it is possible to run a bunch of simple queries in a loop but this way you just do extra work which SQL engine developers most probably have already done for you (and usually have done it in more efficient way).

You may want to read this article in my blog:

• Double-thinking in SQL

Answer 5

The suggestions using IN are fine, but if you are getting the ids from another query, it might be better to combine these two queries into one query using a join.

Instead of:

SELECT id FROM users WHERE age <30
SELECT id, x FROM userinfo WHERE userid IN ($id1, $id2, ..., $idn)

do:

SELECT users.id, userinfo.x
FROM users
LEFT JOIN userinfo ON userinfo.userid = users.id
WHERE age < 30

Answer 6

This will be very inefficient, what you want is to join the tables on the ID

SELECT * FROM table1 LEFT JOIN table2 ON (table1.ID = table2.ID) WHERE condition

Mysql join documentation

This will return one set of rows with all the information you need, returned from both tables.