Is it possible to avoid mysql query within a loop

cadkins23

I'm a self taught PHP developer... which to me means that there is probably a lot that I am doing wrong. In addition, I've only felt relatively confident for a year or so but often still run into problems and questions.

I've been coding social network's for a few years now for my own use. To produce a list of 20 members and simply show their display name, I would have a mysql select statement to select members that matched search criteria. I would then use a while loop to loop through the returned array and use the user_id from each to perform at minimum another query to get the user's display_name...

That are a LOT of queries... Luckily I keep most of the important stuff in the same table as my member information, such as display name, zip code, default picture url, location, etc. so I don't have to perform very many queries.

Is that how most people do it or would they cut down the number of queries drastically?

It seems there may be a better way, to produce less queries but I've never done this and don't know if it's possible...

I would select all user_id's that match the criteria, and perform a second query that will gather the essential information for all the user_id's that were returned in the first query (ie. where user_id = '$user[$n][user_id]' AND `user_id = '$user[$n][user_id]' ) etc..... or something like that?

Next I'll need to figure out a way to put all the user_id's that match search criteria into an array and use php to return a random row to avoid using MySql's RAND

Kudose

The way I understand it is ... Each call to mysql_query is one query that returns an identifier to the result set. You use mysql_fetch_* to iterate the result set.

So I would say that if you aren't using mysql_query within a loop, you're probably doing it right. If you are using mysql_query within a loop, you could get the entire table and use PHP to iterate and find what you want, but with proper indexing MySQL would be exponentially faster.

If you have to loop and use mysql_query, I would limit the initial result set to something MySQL generates a quick result set on. You can use EXPLAIN on your queries to see how MySQL is handling your query.