what is better to use php query set or mysql function?

Question

If you had data in table 1 that you need to use to return data in table 2 for each row returned in table 1. What is more efficient to use a set of querys in PHP one inbedded in the while loop of the other or an SQL function within a query?

for example:

$qry = mysql_query(SELECT date FROM table1)

while($res = mysql_fetch_array($qry)) { $qry = mysql_query("SELECT name FROM table2 WHERE date=$res['date']") }

or to do this as a function that returns the Id from table1 within the query.

Thanks

Answer 1

What is more efficient to use

a set of querys in PHP one inbedded in the while loop of the other

or

an SQL function within a query

Seems you answered your question yourself, didn't you?

Every query you send to the dbms has to be sent over the network, parsed, analyzed then executed. You may want to minimize the number of queries sent to the db.

There may be exceptions, for example if the processing of the data requires operations which the dbms is not capable of.

Answer 2

A (LEFT / RIGHT) JOIN?

Unless I've misunderstood the question...

Answer 3

I think you're looking for JOIN sql syntax. If you have 2 tables: messages and author and you want to return messages with authors. Then you can write following SQL statement:

SELECT m.body, a.name FROM message m 
LEFT JOIN author a ON (a.id=m.author_id)

This will return message body with corresponding author name

Table author:

• id - primary key
• name - name of the author

Table message:

• body - text of the message
• author_id - id of the author

UPD1:

This will be faster then looping each message and select an author. Because JOIN allows you to return all data in single query (not N x queries when using for loop).

UPD2:

With your tables the query will look like:

SELECT t1.date, t2.name FROM table1 t1 LEFT JOIN table2 t2 ON (t2.date=t1.date)

Answer 4

It depends on if the data is easier to find during the while loop or in the query itself.

So if the DB has to base the sub-query on the result of each row in the main query, and there are 1000 total rows and 100 results in the main query, it has to check all of the rows 100 times, so that's 100,000 sub-queries it runs.

So think it terms of the number of results of the main query. If your while loop has to query the DB 100 times while the DB can do it much faster and efficiently in one query, that's better. But if you want a subset of answers that you can say 'query only based on the last set of results' the while loop is better.