Looping a query result

chronomantic

Hey everyone,

I was hoping to get some general feedback from some experienced PHP users (I'm fairly new to PHP) on whether or not what I'm doing here is inefficient or will become problematic at some point!

I'm using a while loop to display each record of my query as a row in a table. Each loop calls a function that also queries the database using the primary key of the initial query as a parameter in the function's query. So basically each loop of the while statement queries the database and I'm wondering if this will be an issue.

Is this common in PHP Web App development? It works fine now, but I'm wondering if that's just because there's like 5 rows of data in the database.

Thanks for the help! And let me know if I wasn't clear enough, I can provide more info 🙂

dagon

doesn't sound sensible, but its hard to know with out seeing actual code. At a guess you should be using a join not a separate query.

Weedpacket

chronomantic wrote:
So basically each loop of the while statement queries the database and I'm wondering if this will be an issue.

It will be before too long (only five rows? Try testing it with a thousand rows). Like dagon suggests, it's probably something the database is already capable of doing itself.

johanafm

And if it really is impossible to solve it with a single query, at least do

$firstResult = array();
$ids = '(';
while ($row = $result->fetch_assoc()) {
	$ids .= $row['id'] . ', ';		// or whatever you call the field used in your second query
	$firstResult[] = $row;
}
$ids = substr($ids, 0, -2) . ')';		// now contains: (1, 5, ... , 15)

$secondQry = 'SELECT somefields FROM sometable WHERE id IN ' . $ids;

chronomantic

Thanks for the replies!

I think I might have to do what Johanafm is suggesting, I'm not sure I can get a join to work, I tried.

This is a simple photo gallery. There are 2 tables.
album
album_id
name
description

photos
photo_id
img_url
album_id

album_id is a foreign key that just sorts out what album each photo record belongs to.

I am creating a simple list of all albums with their name, description, and their max(photo_id) photo as an album cover. The problem with a join query is that I want even albums without images in them to display in this list, and when I join them it omits any albums that have no photos assigned to their album id. I instead want it to display "no image", the album name, and album description.

Is there a way to include albums that have a null value for max(photo_id) and use an if condition to display "no image"? That's why I currently have a separate function to run the max(photo_id) query and handle the 'if no photos, print 'no image', else print 'the image' in the function.

Hope that make sense, thanks for the genius ideas.

johanafm

Use "LEFT JOIN" instead of "INNER JOIN"

chronomantic

ha. oh..

Damn.

Thanks for the help 🙂