Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    120
    Thanks
    1
    Thanked 0 Times in 0 Posts

    need help with repeat from mysql query

    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    look into the use of the GROUP_CONCAT function.

  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    120
    Thanks
    1
    Thanked 0 Times in 0 Posts
    once again thanks for your tips
    The query for my question is:
    Code:
    SELECT *,
    GROUP_CONCAT(foto)
    FROM table1, table2
    WHERE id = id_table1
    GROUP BY id
    But i realise that this is not what i need...

    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?

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,077
    Thanks
    2
    Thanked 320 Times in 312 Posts
    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 Code:
    <?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...
    }
    ?>
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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:

    Code:
    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.

  • #6
    Regular Coder
    Join Date
    Jul 2006
    Posts
    120
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    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 Code:
    <?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:

    Code:
    <?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

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #8
    Regular Coder the-dream's Avatar
    Join Date
    Mar 2007
    Location
    Northamptonshire, UK
    Posts
    477
    Thanks
    8
    Thanked 4 Times in 4 Posts
    try

    PHP Code:

    <?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='' />";
    }
    ?>

  • #9
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,077
    Thanks
    2
    Thanked 320 Times in 312 Posts
    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 Code:
    <?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='' />";
    }
    ?>
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #10
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,077
    Thanks
    2
    Thanked 320 Times in 312 Posts
    Quote Originally Posted by the-dream View Post
    PHP Code:
    <?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...
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #11
    Regular Coder
    Join Date
    Jul 2006
    Posts
    120
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    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.

  • #12
    Regular Coder
    Join Date
    Jul 2006
    Posts
    120
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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 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

    :/

  • #13
    Regular Coder
    Join Date
    Jul 2006
    Posts
    120
    Thanks
    1
    Thanked 0 Times in 0 Posts
    well my problem is solved.. i just guess i have more code than i should. but it's working was i wanted:
    PHP Code:
    <?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:
    Code:
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •