Logic problem

Help with the logistics of a loop

[feralo]

**** THE SITUATION ****
I have a mysql table structure:
NEWS
- id
- title
- news

IMAGES
- id
- image
- description
- newsid

This structure allows multiple images to be associated with one news entry.

**** GOAL *****
What i would like to do is to query the DB and print out an HTML table for each news entry that has associated images.

**** PLAN ****
i have tried several approaches, but i keep getting messed up results. I am not sure what would be the best way to loop through the results (and get the desired result). for(), foreach(),while()...
any suggestions would be appreciated!

[jatar_k]

I usually use while loops for mysql results

do you get all images in the initial query for the news stories or do you need to query for each story to see if there are images?

[feralo]

i was going to query the images table and loop through the 'newsid' column....
But where do i put the while loop?

Here is the code that i used to pull all of the images out and put them into one table:

********************************************************
$smarty->compile_check = true;
$self = $_SERVER['PHP_SELF'];

// Database connection and selection
$link = mysql_connect("$host", "$dbUser", "$dbPass" );
if(! $link)die ("no MySQL Connection!");
mysql_select_db("$dbName") or die ("Couldn't open Database:".mysql_error() );

$sql= "SELECT newsid,name, title,images.id as imageid,date_created FROM `images` INNER JOIN `news` ON news.id = newsid ORDER BY `newsid` DESC,`title`";
$rs= @mysql_query ($sql) or die( "Could not execute query");
$i=0;
$imagesperrow = 4;

$list.= "<table border='1' cellpadding='5'>\n";
// Grab the results from the DB
while ($row = mysql_fetch_array ($rs)){
$y=$i+1;
if($i%$imagesperrow==0){$list.="<tr>\n";}
$list .= "\t<td>\n";
$list .= "\t\t<strong>".substr($row['title'],0,20)."... </strong>\n\t\t<br/>\n";
$list .= "\t\t<img src='".NEWSIMAGES.$row['name']."' height='75px' border='0' alt='".$row['name']."' />\n\t\t<br/>\n";
$list .= "\t</td>\n";
if($y%$imagesperrow==0){$list.="</tr>\n";}
$i=$i+1;
}
$list.= "</table>";

if (isset($_SESSION['message'])) {$message = $_SESSION['message']; $smarty->assign("message","$message"); unset($_SESSION['message']);}

$smarty->assign("center", $list);
$smarty->display('dcms.tpl');

*********************************************************

again, any tips would be appreciated

[jatar_k]

you should be able to do it using your existing loop

while ($row = mysql_fetch_array ($rs)){

in this loop

don't close the row unless the newsid is different from the newsid in the previous iteration, otherwise it just means it is another pic from the same story

if the newsid is different close the previous row/cell and start a new one

this based on the thought that if there are 3 pics for a story then 3 rows should show up in your query result

make sense?

[feralo]

Thank you I was able to figure it out on my bike ride home!
Not only was it a PHP logic problem, but i also had some issues with each of the queries!

Thanks for the help!