Can this query be done without a loop?

Question

So, basically, I have a MySQL table called 'topics' and another one called 'replies', for example. In table 'topics', there's a field called 'relforum' which relates this topic to a forum section. And in the table 'replies', there's a field called 'reltopic', which relates the reply to a topic. Both tables have an id field, auto_increment primary key.

Now, I want to select all replies from a certain forum. Since 'replies' has no 'relforum' field, my way would be:

• Select all topics with 'relforum' equal to that certain forum and loop through them
• While in the loop, select all replies from the topic that is being 'processed' right now
• Merge all fetch_array results in one multidimensional array, then loop through them.

That's something like this:

$query = mysql_query("SELECT * FROM `topics` WHERE `relforum` = '1'");
while($array = mysql_fetch_array($query)) {
    $temp = mysql_query("SELECT * FROM `replies` WHERE `reltopic` = {$array['id']}");
    $results[] = mysql_fetch_array($temp);
}

Is there a way to merge all that into fewer queries? Because this process would basically run one query per topic in that forum plus one. That would be too much :P

Adding the relforum field to the replies table is a solution (I'm still designing the DB Part so it's not a problem to add it), but I would like to see if there's a solution.

I'm really not good at SQL things, I only know the basic SELECT/INSERT/UPDATE, and I usually generate the last two ones using PHPMyAdmin, so... I guess I need some help.

Thanks for reading!

Answer 1

You basically need a join of two tables.

SELECT * FROM `replies`, `topics` WHERE `replies`.`reltopic` = `topics`.`id` AND `topics`.`relforum` = '1';

Answer 2

SELECT r.* FROM replies r, topics t
WHERE t.relforum = 1 AND r.reltopic = t.id

get rid of the backquotes. they're nonstandard and clutter the code

Answer 3

You need to learn to use joins. The link below is for SQL server but the theory for mySQl is pretty much the same for basic joins. Please do not use comma-based joins as they are 18 years outdated and are a porr pracitce. Learn to use the ANSII standard joins.

http://www.tek-tips.com/faqs.cfm?fid=4785

In accessing a database, you almost never want to use any looping. Databases are designed to perform best when asked to operate on sets of data not individual rows. So you need to stop thinking about looping and start thinking about the set of the data you need.

Answer 4

Yes, you should use a join here. However you will need to take greater care processing your result set.

Joins are the essential query in a relational database schema. Add them to your arsenal of knowledge :)

Answer 5

SELECT 
      r.*
FROM 
      replies r
INNER JOIN
      topics t
ON 
      r.reltopic = t.id
WHERE 
      t.relforum = 1;