[SOLVED] loop mysql query

[alapimba]

Hello

I have 2 mysql tables. lets say One table has id and name, the other has id_pic, foto, description and name_id

I have a query that join both tables and make a comparation so i can have the name and the fotos and descriptions. I did this because each person can have multiple fotos. So one table has name of the persons and the other has pictures.

 

My query is: $query_rs_fotos = "SELECT * FROM table 1, table 2 WHERE table 1.id = table 2.name_id";

 

Now i want to have the result of this query looped but i don't know how to loop it and separate the data.

Lets say i have this data:

:::.table 1:::::

id | name

1 | john

2 | bob

 

 

::::table 2:::::

id_pic | foto | description | name_id

1 | bla.jpg | bla | 1

2 | buh.jpg | buh | 2

3 | yah.jpg | yah | 1

4 | boing.jpg | boing | 1

5 | bang.jpg | bang | 2

 

i want the loop to return something like:

<table>

<tr><td> john</td></tr>

<tr><td> bla.jpg</td><td> yah.jpg</td></tr>

<tr><td> boing.jpg</td></tr>

<tr><td> bob</td></tr>

<tr><td> buh.jpg</td><td> bang.jpg</td></tr>

</table>

 

How can i do this?  ???

[GingerRobot]

Give this a go:

 

<?php
$sql = "SELECT table1.name,table2.foto FROM table1,table2 WHERE table1.id=table2.name_id";//only select the bits you want - more efficient
$result = mysql_query($sql) or die(mysql_error());
$curr_name = '';
$cols = 2;//number of columns in picture display
$x=0;
echo '<table>';
while(list($name,$foto) = mysql_fetch_row($result)){
if($curr_name != $name){//if the name of the row is not the same as the last one, we need to echo out the new name
 	if($curr_name != ''){//if this isn't the first name change, we need to finish off the last row of data';
		if($i < $max_columns){
			for($i=$x; $i<$cols;$i++){
				echo "<td> </td>";
			}
		}
		$x = 0;
	}
	echo '<tr><td colspan="'.$cols.'">'.$name.'</td></tr>';
	$curr_name = $name;
}
if($x==0){//new row
	echo '<tr>';
}
echo '<td>'.$foto.'</td>';
    if(++$x == $cols){
        echo "</tr>";
        $x=0;
    }   
}
if($x < $cols){
for($i=$x; $i<$cols;$i++){
	echo "<td> </td>";
}
}
echo '</tr></table>';
?>

[alapimba]

almost perfect.

it's giving the result that i want but it giving also an error:

 

Notice: Undefined variable: i in c:\programas\e-novative\WAMP\www\pedro\fotos2.php on line 289

 

Notice: Undefined variable: max_columns in c:\programas\e-novative\WAMP\www\pedro\fotos2.php on line 289

 

 

line 289 it's : if($i < $max_columns){

 

can you help?

Thanks

[GingerRobot]

Whoops. Try:

 

<?php
$sql = "SELECT table1.name,table2.foto FROM table1,table2 WHERE table1.id=table2.name_id";//only select the bits you want - more efficient
$result = mysql_query($sql) or die(mysql_error());
$curr_name = '';
$cols = 2;//number of columns in picture display
$x=0;
echo '<table>';
while(list($name,$foto) = mysql_fetch_row($result)){
if($curr_name != $name){//if the name of the row is not the same as the last one, we need to echo out the new name
	if($curr_name != ''){//if this isn't the first name change, we need to finish off the last row of data';
		if($x < $cols){
			for($i=$x; $i<$cols;$i++){
				echo "<td> </td>";
			}
		}
		$x = 0;
	}
	echo '<tr><td colspan="'.$cols.'">'.$name.'</td></tr>';
	$curr_name = $name;
}
if($x==0){//new row
	echo '<tr>';
}
echo '<td>'.$foto.'</td>';
if(++$x == $cols){
    	echo "</tr>";
    	$x=0;
    }   
}
if($x < $cols){
for($i=$x; $i<$cols;$i++){
	echo "<td> </td>";
}
}
echo '</tr></table>';
?>

[alapimba]

perfect. Thanks a lot.

By the way.. can you explain me a bit that code?

thanks

[alapimba]

anothing thing

 

is this correct: for($i=$x; $i<$cols;$i++){ ?

Where the $i come from?

I'm asking this because i'm getting one empty row on the first results. Is this correct?

 

Thanks

[GingerRobot]

Just had a thought - im quite surprised this has been working perfectly, since the data really ought to be ordered by the name. Perhaps you did that anyway.

 

Explanation? Sure thing. Each row of returned data from the database will contain the name of the user, and the photo. We cycle through this row by row. All of the rows for one person will be together (since we should have been ordering by the name). Therefore, we have a loop which checks to see if the name of the current row is different from the name during the last run of the loop. If it is different, then we show the new name. Either way, we always show the photo - otherwise we'd be missing some.

 

The code is a little more complicated because of the multiple rows. The basic idea behind that is that we will place the images into the same row, until our counter ($x) is equal to the maximum number of images we want in one row ($cols).

 

I've also commented the code some more.

 

<?php
$sql = "SELECT table1.name,table2.foto FROM table1,table2 WHERE table1.id=table2.name_id ORDER BY table1.name";//only select the bits you want - more efficient
$result = mysql_query($sql) or die(mysql_error());
$curr_name = '';//we need to define $curr_name because we need to know if the loop is being run for the first time
//also prevents any undefined variable nitces
$cols = 2;//number of columns in picture display
$x=0;//this is our counter that we use so we know how many images have been echoed in each row of the table

echo '<table>';
while(list($name,$foto) = mysql_fetch_row($result)){
//the list function assigns a name to each of the elements of a given array

if($curr_name != $name){//if the name of the row is not the same as the last one, we need to echo out the new name
	if($curr_name != ''){//if this isn't the first name change, we need to finish off the last row of data
		if($x < $cols){//we need to check to see if the number of images in the previous row is less than the number
		//there are supposed to be in a row
			for($i=$x; $i<$cols;$i++){//we use a for loop to echo out blank cells to fill up the row in the table
				echo "<td> </td>";
			}
		}
		$x = 0;//we must reset the counter if a new name has been found
	}
	echo '<tr><td colspan="'.$cols.'">'.$name.'</td></tr>';//echo out the new name
	$curr_name = $name;//assign this name to $curr_name, so we know when this changes
}
if($x==0){//new row - we create a new row whenever $x is 0
	echo '<tr>';
}
echo '<td>'.$foto.'</td>';//show the photo
if(++$x == $cols){//we must check to see if the current row is now full. If it is, we close the row, and reset $x
   	echo "</tr>";
   	$x=0;
   }   
}
if($x < $cols){//once the while loop is finished, we tidy up the table by finishing off the last row if it needs it
for($i=$x; $i<$cols;$i++){
	echo "<td> </td>";
}
}
echo '</tr></table>';//close the table
?>

 

Between the comments and the above description, i hope that helps you understand. If not, tell me which bits you're not sure of, and ill try and explain a bit more. If you do now understand, can you mark the topic as solved?

[GingerRobot]

anothing thing

 

is this correct: for($i=$x; $i<$cols;$i++){ ?

Where the $i come from?

I'm asking this because i'm getting one empty row on the first results. Is this correct?

 

Thanks

 

The $i is defined by the for loop. The idea behind that is that we set it equal to the current value of $x. The value of $i will then be increased by one each time the loop runs, until it reaches the number of columns. It should be correct.

 

As for the problem, do you think you could post up the code you are now using, just to be clear? If you've got the code running on a server i could see, that'd be a useful too.

[alapimba]

i have the site still offline so let me show you only the code for now.

 

my php code:

<?php
mysql_select_db($database_pedrograncha, $pedrograncha);
$sql = "SELECT fotos_datas.nome, fotos.big, fotos.thumb FROM fotos, fotos_datas WHERE fotos_datas.id_data=fotos.id_nome";
$result = mysql_query($sql, $pedrograncha) or die(mysql_error());
?>

 

<?php
$curr_name = '';
$cols = 4;//number of columns in picture display
$x=0;
echo '<table width="100%" border="0" cellspacing="0" cellpadding="10">';
while(list($nome,$big,$thumb) = mysql_fetch_row($result)){
if($curr_name != $nome){//if the name of the row is not the same as the last one, we need to echo out the new name
 	if($curr_name != ''){//if this isn't the first name change, we need to finish off the last row of data';
		if($x < $cols){
			for($i=$x; $i<$cols;$i++){
				echo "<td> </td>";
			}
		}
		$x = 0;
	}
	echo '<tr><td colspan="'.$cols.'" class="style3">'.$nome.'</td></tr>';
	$curr_name = $nome;
}
if($x==0){//new row
	echo '<tr>';
}
echo '<td><img src='.$thumb.'></td>';
    if(++$x == $cols){
        echo "</tr>";
        $x=0;
    }   
}
if($x < $cols){
for($i=$x; $i<$cols;$i++){
	echo "<td> </td>";
}
}
echo '</tr></table>';
?>

 

result:

<table width="100%" border="0" cellspacing="0" cellpadding="10"><tr><td colspan="4" class="style3">2007</td></tr><tr><td><img src=images/galeria/thumb_41201017675AMARELO.jpg></td><td><img src=images/galeria/thumb_41201017670abarth_set.jpg></td><td><img src=images/galeria/thumb_41201017680austin sprite 1958.jpg></td><td><img src=images/galeria/thumb_41201017686banshee_scream.jpg></td></tr><td> </td><td> </td><td> </td><td> </td><tr><td colspan="4" class="style3">dakar</td></tr><tr><td><img src=images/galeria/thumb_31201017644a4037je.jpg></td><td><img src=images/galeria/thumb_31201109988Image(248).jpg></td><td><img src=images/galeria/thumb_31201017633600.jpg></td><td><img src=images/galeria/thumb_31201017627043.jpg></td></tr><tr><td><img src=images/galeria/thumb_31201017623041.jpg></td><td><img src=images/galeria/thumb_312010176197bb3_1.jpg></td><td><img src=images/galeria/thumb_312010915561716_1.jpg></td><td><img src=images/galeria/thumb_31201109994montblanc.jpg></td></tr><tr><td><img src=images/galeria/thumb_31201110001xbox1.jpg></td><td> </td><td> </td><td> </td></tr></table>

[GingerRobot]

Ah ok, i see what's happening. Firstly, i suggest you add the order by clause:

 

<?php
mysql_select_db($database_pedrograncha, $pedrograncha);
$sql = "SELECT fotos_datas.nome, fotos.big, fotos.thumb FROM fotos, fotos_datas WHERE fotos_datas.id_data=fotos.id_nome ORDER BY fotos_datas.nome";
$result = mysql_query($sql, $pedrograncha) or die(mysql_error());
?>

 

Then, we just need to add a little piece in:

<?php
$curr_name = '';
$cols = 4;//number of columns in picture display
$x=0;
echo '<table width="100%" border="0" cellspacing="0" cellpadding="10">';
while(list($nome,$big,$thumb) = mysql_fetch_row($result)){
if($curr_name != $nome){//if the name of the row is not the same as the last one, we need to echo out the new name
 	if($curr_name != ''){//if this isn't the first name change, we need to finish off the last row of data';
		if($x < $cols && $x != 0){
			for($i=$x; $i<$cols;$i++){
				echo "<td> </td>";
			}
		}
		$x = 0;
	}
	echo '<tr><td colspan="'.$cols.'" class="style3">'.$nome.'</td></tr>';
	$curr_name = $nome;
}
if($x==0){//new row
	echo '<tr>';
}
echo '<td><img src='.$thumb.'></td>';
    if(++$x == $cols){
        echo "</tr>";
        $x=0;
    }   
}
if($x < $cols){
for($i=$x; $i<$cols;$i++){
	echo "<td> </td>";
}
}
echo '</tr></table>';
?>

 

The problem was that if the new name coincided with a new row , we were filling in blank row with blank cells.

[alapimba]

Perfect

I'll now try to understand the explanation that you gave for each line.

 

Thanks a lot.