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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Displaying results from database

    Hi everyone

    I have created my query that returns the results I require however I need to change how its displayed

    Company Spec
    company 1 spec1
    company 1 spec2
    company 1 spec3
    company 1 spec1
    company 1 spec2
    company 2 spec4
    company 2 spec5
    company 2 spec1
    company 2 spec5
    company 2 spec4

    I need this to be displayed like this:



    Company Spec
    company 1 spec1
    spec2
    spec3
    company 2 spec1
    spec4
    spec5

    So I need the company name distinct but I want the spec values to show under the company as well rather than on each row shown above.
    I know it looks like the values a duplicated but they're not - theres other columns in the database with unique values

    I hope that makes sense... can anyone help me please?

    Many thanks

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,471
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    You'll be using GROUP BY ...

    View this tutorial, starting on this page:
    MySQL Tutorial - Group By

    Click the 'continue' buttons to advance pages.

    You'll be best-off to learn this on your own. It's a really important part of MySQL.

  • #3
    New Coder
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi

    Thanks for your suggestion - I am aware of the GROUP BY function but then I don't get all the spec values listed under the company.

    Is there a solution for this?

    "SELECT * FROM DB WHERE id = '$id' GROUP BY company";

    Gives the result: -
    company 1 spec1
    company 2 spec4

    Any suggestions?

    Thanks

    Quote Originally Posted by mlseim View Post
    You'll be using GROUP BY ...

    View this tutorial, starting on this page:
    MySQL Tutorial - Group By

    Click the 'continue' buttons to advance pages.

    You'll be best-off to learn this on your own. It's a really important part of MySQL.

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    While you are looping over the results to display them, you need to remember (in a php variable) and detect when the company value changes. Each time it changes (including the initial time it becomes set), output the company name, then remember the new value.

    Code:
    $company = '';
    
    while( $row = your database fetch statement here ){
        if($company != $row['company']){
            // the company changed, output it
            echo $company = $row['company']; // remember the new value
        }
        echo $row['spec']; // output the data under each company
    }
    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
    New Coder
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi

    Thank you for your response.

    For some I am I getting the first value of "spec" and not all of them...

    Is there an example I can see maybe?

    I am using the loop to retrieve other columns from the database as well not just company and spec - so I'm not sure if this changes anything?

    Can you help me? Many thanks for your time.

    Quote Originally Posted by CFMaBiSmAd View Post
    While you are looping over the results to display them, you need to remember (in a php variable) and detect when the company value changes. Each time it changes (including the initial time it becomes set), output the company name, then remember the new value.

    Code:
    $company = '';
    
    while( $row = your database fetch statement here ){
        if($company != $row['company']){
            // the company changed, output it
            echo $company = $row['company']; // remember the new value
        }
        echo $row['spec']; // output the data under each company
    }
    Last edited by dips045; 04-30-2014 at 10:37 PM.

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    The code I posted is an example. It's one variable and a conditional statement to only perform an action when a value changes. The only requirement is that your query orders the rows by the company column so that the rows for any one company are together in the result set.

    You would need to post the code you tried to get any help with it.
    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.

  • #7
    New Coder
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Here is my code:

    Code:
    <?php 
    $result = "SELECT * FROM mydatabase WHERE id = '$id' GROUP BY company";
    
    $companyresult = mysql_query($result); 
    
    while($row = mysql_fetch_array($companyresult)) {
    ?>
    <ul>
     <?php
       if($company != $row['company']){
            // the company changed, output it
            echo $company = $row['company']; // remember the new value
        }?>
        
        <li> <?php echo $row['spec1']; ?> </li>
    </ul>
    }
    
    ?>
    Quote Originally Posted by CFMaBiSmAd View Post
    The code I posted is an example. It's one variable and a conditional statement to only perform an action when a value changes. The only requirement is that your query orders the rows by the company column so that the rows for any one company are together in the result set.

    You would need to post the code you tried to get any help with it.
    Last edited by dips045; 05-01-2014 at 10:51 PM.

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,027
    Thanks
    2
    Thanked 315 Times in 307 Posts
    The GROUP BY company in the query consolidates rows having the same company value into ONE row per company value. Remove the GROUP BY company from the query statement.
    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.

  • #9
    New Coder
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    When I remove group by, it goes back to how it was....

    Company on each row with the spec like this...
    company 1 spec1
    company 1 spec2
    company 1 spec3

    Any ideas what I am doing wrong?

    Thanks for your help and time


    Quote Originally Posted by CFMaBiSmAd View Post
    The GROUP BY company in the query consolidates rows having the same company value into ONE row per company value. Remove the GROUP BY company from the query statement.

  • #10
    Regular Coder
    Join Date
    Sep 2011
    Posts
    410
    Thanks
    18
    Thanked 26 Times in 26 Posts
    When you're looping through them, save the last one you used and check if the new one is the same as the last. If it is, don't print the company. If it's different, print it. Just like CFMaBiSmAd said earlier, however you need to modify what it's doing in the process.

    PHP Code:
    $comapny false;

    while(
    $row /*your database fetch statement here*/)
    {
        
    #Check if the company is different from the last
        
    if($company != $row['company'])
        {
            
    #If it is, output it
            
    echo $row['company'];
            
    $company $row['company'];//Remember the new value for the next row
        
    }
        echo 
    $row['spec'];//Print out any other data you want

    It's basically the same thing they posted above, I just added some comments and tweaked it to my liking.

  • #11
    New Coder
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I tried this way but it returns only company 1 and seems to return the whole spec column rather than the spec of company 1.

    Am I doing something wrong?


    Quote Originally Posted by Dubz View Post
    When you're looping through them, save the last one you used and check if the new one is the same as the last. If it is, don't print the company. If it's different, print it. Just like CFMaBiSmAd said earlier, however you need to modify what it's doing in the process.

    PHP Code:
    $comapny false;

    while(
    $row /*your database fetch statement here*/)
    {
        
    #Check if the company is different from the last
        
    if($company != $row['company'])
        {
            
    #If it is, output it
            
    echo $row['company'];
            
    $company $row['company'];//Remember the new value for the next row
        
    }
        echo 
    $row['spec'];//Print out any other data you want

    It's basically the same thing they posted above, I just added some comments and tweaked it to my liking.

  • #12
    Regular Coder
    Join Date
    Sep 2011
    Posts
    410
    Thanks
    18
    Thanked 26 Times in 26 Posts
    Post what code you used to do it. Also, post a var_export of the rows you get.
    PHP Code:
    $list = array();
    while(
    $row /*row fetch command here*/)
        
    $list[] = $row;
    var_export($list); 

  • #13
    New Coder
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Here is my code so far
    PHP Code:
    <?php

    $result 
    "SELECT * FROM mydatabase WHERE name = '$name'";
    $companyresult mysql_query($result); 
      
    $company false

    while(
    $row mysql_fetch_array$companyresult ))

        
    #Check if the company is different from the last 
        
    if($company != $row['company']) 
        { 
            
    #If it is, output it 
            
    echo $row['company']; 
            
    $company $row['company'];//Remember the new value for the next row 
        
    }     
        echo 
    $row['spec'];//Print out any other data you want 
    }  
    ?>
    When I added the var_export code I get a very long list of the table contents....

    Quote Originally Posted by Dubz View Post
    Post what code you used to do it. Also, post a var_export of the rows you get.
    PHP Code:
    $list = array();
    while(
    $row /*row fetch command here*/)
        
    $list[] = $row;
    var_export($list); 

  • #14
    Regular Coder
    Join Date
    Sep 2011
    Posts
    410
    Thanks
    18
    Thanked 26 Times in 26 Posts
    Quote Originally Posted by dips045 View Post
    When I added the var_export code I get a very long list of the table contents....
    Do the rows say the correct company for them?

  • #15
    New Coder
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Its showing all the rows - all the data from the table is being returned not just the company

    Quote Originally Posted by Dubz View Post
    Do the rows say the correct company for them?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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