specific query question

mashnote

Hi all, first of all i wanna say hi to everyone, 'cause this is my first post here!

Ok, so i have a music-related website with pictures,...
now i have a table in my DB wich stores pictures with the name of the band, place of the venue,etc..
like this ::

name | place | date | img_url

with a loop i can get all the thumbnails into a html table.
But my problem is : there are some (different) pictures of the same band which have the same venue name, same date,etc..

Now i want to display the thumbnails with the same date,bandname and place together (next to eachother) and only display the details that are the same (name, place,..) once! Like a title.

So currently i have different tables for every picture..

ps.i'm kinda a newbie to php & sql , and i'm sorry if my explanation is too long.

sfullman

Well, you're new so if there's a faster way, it still won't hurt you to do this cause you'll learn

There are two approaches: 1) let mysql handle the grouping and let PHP do the work, or 2) do two queries with mysql doing the work.

First query for method 2)

select Title, Venue, Count(Images) as ImgCount from Table group by Title [, Venue if you want to split further]

This will give you distinct Titles and maybe venues.

if ImgCount is greater than 1, run a second query within the query:

select * from Table where Title = {current title} [and Venu = {current venue}]

... shooting from the hip, but try it out.

Sam

mashnote

Hey Sam, thanx a lot for your reply!

So i tried what you said, and what i got now is...:

i can display every title, place,... once with one thumbnail.

This is just with query number 1

If i try query number 2

select * from Table where Title = {current title} [and Venu = {current venue}]

I get the same thing, with the exception that my first result from the query is gone , so one item is not displayed.

here's my current code :

<?

$query = "SELECT name, place, date, path, thumbnail, photographer, Count(name) as ImgCount from mash_images_bands GROUP BY name";
$result = mysql_query($query) or die (mysql_error());
$rij = mysql_fetch_array($result);
$name= $rij["name"];

if(ImgCount >1){
$query = "SELECT * from mash_images_bands WHERE name='$name'";
$result = mysql_query($query) or die (mysql_error());
$rij = mysql_fetch_array($result);
}

while($rij = mysql_fetch_array($result)) {

<?echo ($rij["name"]);?>
<?echo ($rij["place"]);?>
<?echo ($rij["thumbnail"]);

?>

hope this is usefull...

thanx again!

sfullman

Your problem is that you have an inner and outer loop, and you're using the same result for both. You need to have a nested WHILE LOOP inside of another one, and the name of the $result needs to be say $result2 on the inner. I copied your code and did a few changes but I don't see the nested while loop.

Take some time to think about the concept of what's happening before you start coding again. The key point is you're working with two resources simultaneously.

Sam

<? 

$query = "SELECT name, place, date, path, thumbnail, photographer, Count(name) as ImgCount from mash_images_bands GROUP BY name"; 
$result = mysql_query($query) or die (mysql_error()); 
$rij = mysql_fetch_array($result); 
$name= $rij["name"]; 

if(ImgCount >1){ 
$query = "SELECT * from mash_images_bands WHERE name='$name'"; 
/** you need result 2 here -- otherwise you'll conflict **/
$result2 = mysql_query($query) or die (mysql_error()); 
/**  don't think you need this next line **/
# $rij = mysql_fetch_array($result); 
} 


while($rij = mysql_fetch_array($result2)) { 


<?echo ($rij["name"]);?> 
<?echo ($rij["place"]);?> 
<?echo ($rij["thumbnail"]); 

?>

mashnote

thanx again!

but no matter what i try , i always get this :

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Maybe i'm just plain dumb, but i don't what i'm doing wrong now.
I did get your explanation and applied changes as said. I tried basicly everything that i can with my limited knowledge....🙁

But my quest will continue ! I justed posted this to let you know
that i'm working on it 😉 i'll try some more.