Multiple Rows Query and displaying data

[ngreenwood6]

Ok so I am pretty good with mysql and performing queries to get the data that I need. However, I have a question for any of you gurus out there that may be able to help me with an issue that I always run into with certain types of queries. Say I have 2 tables like this:

 

Galleries

id

name

1

My Photos

 

Gallery Photos

id

gallery_id

photo

1

1

photo1.jpg

2

1

photo2.jpg

3

1

photo3.jpg

 

Now usually when I pull this info from the database I have to do 2 separate queries in order to get the data and then link them so I would do something like.

 

<?php
//make the gallery query
$query = "SELECT * FROM galleries";
$results = mysql_query($query);

//setup an array for the galleries data
$gallery_data = array();

//loop through the galleries
for($i=0;$i<mysql_num_rows($results);$i++){
//add the gallery info to the gallery data array
$gallery_data[$i] = mysql_fetch_array($results);

//make the photos query with the gallery id
$photo_query = "SELECT * FROM gallery_photos WHERE gallery_id='".mysql_real_escape_string($gallery_data[$i]['id'])."'";
$photo_results = mysql_query($photo_query);

//setup the photos array
$photo_data = array();

//put the photos in the array
for($n=0;$n<mysql_num_rows($photo_results);$n++){
	$photo_data[$n] = mysql_fetch_array($photo_results);
}

//add the photos to the gallery array
$gallery_data[$i]['photos'] = $photo_data;
}

//now to display it is like this
if(is_array($gallery_data)){
foreach($gallery_data as $gallery){
	echo $gallery['name'];

	//show the photos
	if(is_array($gallery['photos'])){
		foreach($gallery['photos'] as $photo){
			echo $photo['photo'];
		}
	}
}
}
?>

 

So my question is there a way to get all of this data at one time. I know how to do multiple queries in one and to do joins but they can only return one row as far as I know of. The only other way that I know how to do this is by ordering them by name and selecting them directly from the photos table and then just getting the gallery name like this:

 

SELECT *,(SELECT name FROM galleries WHERE id=gallery_photos.id) AS gallery_name FROM gallery_photos ORDER BY gallery_id

 

Then I could just do one loop and see if the name has changed and if so to output the new name. But I would like to know if there is a way to get a second set of results as an array from a query so I could just select the galleries and photos all in one query. Any help is appreciated.

[MadTechie]

Have a look at SQL JOIN,

 

IE

SELECT *
FROM `Galleries`
LEFT JOIN `Gallery Photos` ON `Galleries`.id=`Gallery Photos`.gallery_id
WHERE `Galleries`.id = 1

[ngreenwood6]

I know how to use joins and I have used them before but it is the same issue with having to go through the gallery names and checking if it is different and then if it is outputting the next gallery name. I was more or less looking for a solution that would select the each of the galleries and create and array under it called photos or whatever and have all the photo information under it. This creates a row for each of the photos where I just want one for each of the galleries. Is it not possible?

[MadTechie]

If your saying you want to do one query and have it create separate arrays for each set without you having to write logic yourself then the answer is no

The logic is pretty simple personally i don't see a problem !

[ngreenwood6]

The question was pretty much can a sql query have an sql query inside of it that returns a second set of results with more than one item, kinda like the example I provided. I have no problem with the logic and have been doing it this way for years but was just wondering if there was a more efficient or better way to do it, because sometimes the queries that I have to make have 3 or more tables linked to them and have to go through 3 different sets of results.

[MadTechie]

If your referring to this

SELECT *,(SELECT name FROM galleries WHERE id=gallery_photos.id) AS gallery_name FROM gallery_photos ORDER BY gallery_id

then it will probably work.. have you tried it ?

 

however i would use a Join

[ngreenwood6]

No I know that would work...That is the same thing as your join pretty much. I was talking about querying just galleries table and then inside of that query getting the photos as its own array returned via mysql, usually it will only let me return one result from a query withing a query. Just wondering if there is a way to return all the result from within another result.

[MadTechie]

as you say you know about joins I assume these results are not what your looking for so i have so say no..

personally i think your over complicating the problem,