...

View Full Version : need help with repeat from mysql query



alapimba
03-12-2007, 11:43 PM
hello

lets say i have 2 tables:

table1:
id | name
1 | roger
2 | mike
3 | bob
...

table2:
id | id_table1 | foto
1 | 1 | roger.jpg
2 | 1 | roger2.jpg
3 | 1 | roger3.jpg
4 | 2 | mike.jpg
5 | 3 | bob.jpg
6 | 3 | bob2.jpg
...

i have this query:
select *
from table1, table2
where table1.id = table2.id_table1

this will return:
1 | roger | 1 | 1 | roger.jpg
1 | roger | 2 | 1 | roger2.jpg
1 | roger | 3 | 1 | roger3.jpg
2 | mike | 4 | 2 | mike.jpg
3 | bob | 5 | 3 | bob.jpg
3 | bob | 6 | 4 | bob2.jpg
...

Now i want to print this:

Roger, roger.jpg, roger2.jpg, roger3.jpg
mike, mike.jpg
bob, bob.jpg, bob2.jpg
...


How can i do this?

Thanks in advance

guelphdad
03-13-2007, 02:40 AM
look into the use of the GROUP_CONCAT function.

alapimba
03-13-2007, 11:18 PM
once again thanks for your tips
The query for my question is:


SELECT *,
GROUP_CONCAT(foto)
FROM table1, table2
WHERE id = id_table1
GROUP BY id


But i realise that this is not what i need...:rolleyes:

the print from the foto colum will be url to img src... that is what i need to loop. With the GROUP_CONCAT it just returns all results separated with a ,

How can i do it? :confused:

CFMaBiSmAd
03-14-2007, 12:41 AM
What you need to do is a one-shot on the name. Save the last value of the name and detect when there is a change in the value.

<?php
$last_value = ""; // create variable to hold the last value
while($row = mysql_fetch_assoc($result)){
if($row['name'] != $last_value){
// a new name was detected
// do the new name processing here
$last_value = $row['name']; // save the new name as the last value
}
// process the common $row fields here...
}
?>

guelphdad
03-14-2007, 02:48 PM
There are many things incorrect with your query above as well as your initial description. In your initial description you said you wanted a name with a list of images and not all columns.

Don't use SELECT * at any time, it will cause you many problems as it does here. Actually name the columns you want. Since you named all columns in both tables, all of those must appear in your GROUP BY clause, when they don't you can get incorrect data in your results.

If you had only selected the name and did a group concat on the photos you would have ended up with this:



select
name,
group_concat(photo)
from table1
inner join table2
on id=table1_id
group by name


and it would have produced the exact output that you asked for in your example.

alapimba
03-15-2007, 12:04 AM
What you need to do is a one-shot on the name. Save the last value of the name and detect when there is a change in the value.

<?php
$last_value = ""; // create variable to hold the last value
while($row = mysql_fetch_assoc($result)){
if($row['name'] != $last_value){
// a new name was detected
// do the new name processing here
$last_value = $row['name']; // save the new name as the last value
}
// process the common $row fields here...
}
?>

I'm not understanding :(

i tried this... but it don't work:


<?php
$last_value = ""; // create variable to hold the last value
while($row_rs_derivados = mysql_fetch_assoc($rs_derivados)){
if($row_rs_derivados['foto'] != $last_value){
// a new name was detected
// do the new name processing here
$last_value = $row_rs_derivados['foto']; // save the new name as the last value
}
// process the common $row fields here...
echo $row_rs_derivados['name'];
<img src='$row_rs_derivados['foto'];' alt='' />
}
?>

What i want to print is:

roger<br>
<img src="roger.jpg" alt='' />
<img src="roger2.jpg" alt='' />
<img src="roger3.jpg" alt='' />
mike<br>
<img src="mike.jpg" alt='' />
bob<br>
<img src="bob.jpg" alt='' />
<img src="bob2.jpg" alt='' />

help me please.. i'm lost :mad:

guelphdad
03-15-2007, 12:23 AM
so what you want to print out is something entirely different than you have told us so far?

this isn't really a mysql question but more of a formatting question for PHP. I'll move the thread to that forum.

the-dream
03-15-2007, 12:51 AM
try




<?php
$last_value = ""; // create variable to hold the last value
while($row_rs_derivados = mysql_fetch_assoc($rs_derivados)){
if($row_rs_derivados['foto'] != $last_value){
// a new name was detected
// do the new name processing here
$last_value = $row_rs_derivados['foto']; // save the new name as the last value
}
// process the common $row fields here...
echo $row_rs_derivados['name'];
echo "<img src="$row_rs_derivados['foto'];" alt='' />";
}
?>

CFMaBiSmAd
03-15-2007, 01:08 AM
I'm wondering how you switched the "name" field around from what I posted, given how many times I refered to it as "name"?

This should work (untested) -

<?php
$last_value = ""; // create variable to hold the last value
while($row_rs_derivados = mysql_fetch_assoc($rs_derivados)){
if($row_rs_derivados['name'] != $last_value){
// a new name was detected
// do the new name processing here
echo "{$row_rs_derivados['name']}<br />";
$last_value = $row_rs_derivados['name']; // save the new name as the last value
}
// process the common $row fields here...
echo "<img src='{$row_rs_derivados['foto']}' alt='' />";
}
?>

CFMaBiSmAd
03-15-2007, 01:13 AM
<?php
echo "<img src="$row_rs_derivados['foto'];" alt='' />";
?>While there are many ways to echo an <img tag with a PHP variable in it, the way you guessed is not one of the ways that works... :eek:

alapimba
03-15-2007, 10:22 AM
so what you want to print out is something entirely different than you have told us so far?

this isn't really a mysql question but more of a formatting question for PHP. I'll move the thread to that forum.

sorry but as the subtitle of the mysql section is "Get help on mySQL administration and syntax here. PHP questions as they relate to mySQL should also be posted here." i thought i should post on that forum.

alapimba
03-15-2007, 02:58 PM
no luck yet :/

sorry about typing the wrong names before but was i'm testing with tables with diferent names i forgot to type the corrects ones.
anyway.. right now with the following code:

<?php
$last_value = ""; // create variable to hold the last value
while($row_rs_derivados = mysql_fetch_assoc($rs_derivados)){
if($row_rs_derivados['modelo'] != $last_value){
// a new name was detected
// do the new name processing here
echo "{$row_rs_derivados['modelo']}<br />";
$last_value = $row_rs_derivados['modelo']; // save the new name as the last value
}
// process the common $row fields here...
echo $row_rs_derivados['texto_foto'];
}
?>

it prints:
roger
roger3.jpgmike
mike2.jpgbob
bob.jpgbob2.jpgroger
roger.jpg

:/

alapimba
03-15-2007, 10:10 PM
well my problem is solved.. i just guess i have more code than i should. but it's working was i wanted:

<?php
$x = -1;
$i = 0;
$first = 0; // 1a coluna
while ($row_rs_derivados = mysql_fetch_assoc($rs_derivados)) {
if($x != $row_rs_derivados['id']){ //se e 1 carro novo
if(($i % 3) == 0){ // se ja foram feitas 3 colunas (ou 0 -> 1a linha)
if($first != 0){ // se nao e a 1a linha
$first = 0;
}
}
$x = $row_rs_derivados['id'];
if($first != 0){ // se nao e a 1a coluna
}
else {
$first = 1;
}
$i = $i + 1;
?>
<hr size='1' noshade>
<?php echo $row_rs_derivados['modelo']; ?>
<br>
<?php } ?>
<span class="style5"><?php echo $row_rs_derivados['texto_foto']; ?></span> <br>
<?php } ?>


mysql query:

SELECT * FROM derivados inner join derivados_img where derivados.id=derivados_img.id_derivado_geral

the table names isn't the same but the base is exactly the same...

anyone to help me clean the php code? :D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum