Page 1 of 1

Need some help with a WHILE loop

Posted: Mon Aug 08, 2005 6:43 am
by DarthNunzio
hello all,

i surfed around and couldn't quite find a solution to this, so hopefully some kind and knowledgable soul out there can help me with this.

i have two tables, project_name and project_images. for the purposes of this question, they're defined like so:

project_name
project_id (pri key)
project_name

project_images
image_id (pri key)
imagename
...
project_id (sec key)

now, what i'm trying to do is display the images PER PROJECT in a row. sounds very simple, and it probably is, but i've been struggling with it all afternoon.

here's what i have:

Code: Select all

$sql_projects = "select * from project_name";
$result_projects = @mysql_query($sql_projects,$connection) or die(mysql_error());
while ($row = mysql_fetch_array($result_projects)) {
$project_id = $row['project_id'];
$project_name = $row['project_name'];

$sql_image = "select * from project_images where image_id in (select image_id from project_images where project_id = $project_id)";
$result_image = @mysql_query($sql_image,$connection) or die(mysql_error());
while ($row = mysql_fetch_array($result_image)) {
$imagename = $row['imagename'];

$images .= "<img src='../images/project_images/$imagename' class='imgDisplay' />";

} // end get images

$display .="<tr><td class='underline'>$project_name</td><td class='underline' colspan=2>$images</td></tr>";

}
what this should give me is something like the following:

project 1 | image1 image2 image3...
project 2 | image4 image5...
project 3 | image6 image7 image8...

and so on. however, what it's giving me is this:

project 1 | image1 image2 image3...
project 2 | image1 image2 image3 image4 image5
project 3 | image1 image2 image3 image4 image5 image6 image7 image8...

i've tried everything i can think of and i'm at wit's end. can anyone help? many thanks in advance.

--mike


feyd | mmm look at the lovely php colors...

Code: Select all

tags help [/color]

Posted: Mon Aug 08, 2005 6:46 am
by Sander
This is probably the query you want:

Code: Select all

SELECT * FROM project_images AS i, project_names AS n WHERE i.project_id = n.project_id
I didn't test it so I'm not sure if it works, but I think this should at least get you into the right direction.

Posted: Mon Aug 08, 2005 6:49 am
by feyd
inner join is a bad idea, unless you want all projects without images to not show...

try this single query:

Code: Select all

SELECT * FROM project_name LEFT JOIN project_images ON project_images.project_id = project_name.project_id)

Posted: Mon Aug 08, 2005 7:45 am
by DarthNunzio
first, many thanks to those who replied.

okay, here's the new code snippet using the LEFT JOIN:

Code: Select all

$sql_projects = "select * from project_name LEFT JOIN project_images ON project_images.project_id = project_name.project_id";
		$result_projects = @mysql_query($sql_projects,$connection) or die(mysql_error());
		while ($row = mysql_fetch_array($result_projects)) {
			$project_id = $row['project_id'];
			$project_name = $row['project_name'];
			$imagename = $row['imagename'];
				
			$images .= "<img src='../images/project_images/$imagename' class='imgDisplay' />";
			
			$display .="<tr><td class='underline'>$project_name</td><td class='underline' colspan=3>$images</td></tr>";
			
			}
and what it's giving me is something like this:

project1 | image1-1 (where '1-1' indicated the first image that should go with project 1)
project1 | image1-1 image 1-2
...
project1 | image1-1 image 1-2 ... image 1-5
project2 | image1-1 image 1-2 ... image 1-5 image 2-1
project2 | image1-1 image 1-2 ... image 1-5 image 2-1 image 2-2

looking at the code, this makes sense... i just don't know how to PLACE stuff... any new ideas?

thanks all!

Posted: Mon Aug 08, 2005 7:52 am
by feyd
this result is more due to your usage of string concatenation. Here's a way to get more of what you want:

Code: Select all

<?php
$sql_projects = "select * from project_name LEFT JOIN project_images ON project_images.project_id = project_name.project_id";
        $result_projects = @mysql_query($sql_projects,$connection) or die(mysql_error());
        $last = null;
        while ($row = mysql_fetch_array($result_projects)) {
            $project_id = $row['project_id'];
            $project_name = $row['project_name'];
            $imagename = $row['imagename'];
            if($last !== $project_id)
            {
                if($last !== null)
                {
                    $display .= "</td></tr>";
                }
                $display .= "<tr><td class='underline'>$project_name</td><td class='underline' colspan=3>";
                $last = $project_id;
            }
            else
            {
                $display .= "<img src='../images/project_images/$imagename' class='imgDisplay' />";
            }
        }
?>

Posted: Mon Aug 08, 2005 8:05 am
by DarthNunzio
so, so close. the only thing wrong is that each project is missing ONE image - i checked and the missing image is the first entered in the db. so image_id (primary key for the images table) doesn't have anything to do with it, which makes sense since it's not involved in the query... it's simply the first image per project, as listed in the project_images table, that doesn't show.

btw i salute your genius. i wish i were a programmer...

Posted: Mon Aug 08, 2005 8:06 am
by feyd
my bad..

Code: Select all

<?php
$sql_projects = "select * from project_name LEFT JOIN project_images ON project_images.project_id = project_name.project_id";
        $result_projects = @mysql_query($sql_projects,$connection) or die(mysql_error());
        $last = null;
        while ($row = mysql_fetch_array($result_projects)) {
            $project_id = $row['project_id'];
            $project_name = $row['project_name'];
            $imagename = $row['imagename'];
            if($last !== $project_id)
            {
                if($last !== null)
                {
                    $display .= "</td></tr>";
                }
                $display .= "<tr><td class='underline'>$project_name</td><td class='underline' colspan=3>";
                $last = $project_id;
            }
            $display .= "<img src='../images/project_images/$imagename' class='imgDisplay' />";
        }
?>
that should sort it.

Posted: Mon Aug 08, 2005 8:18 am
by DarthNunzio
sir, you are my hero. thank you.

btw your screen name rocks. i was gonna use muad'dib but i thought it might seem pretentious. muad'dib would hardly need to go to the forums to get help.

Posted: Mon Aug 08, 2005 8:56 am
by feyd
glad I could help. :)

and yes.. muad'dib would be a bit silly. :)