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 6 of 6
  1. #1
    New Coder
    Join Date
    Jul 2010
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    How to get ALL results in while loop?

    Hi

    How do I get this code to print out ALL the category brands? At the moment it appears to only print out the last category brands.

    Code:
    // Make a MySQL Connection
    mysql_connect("", "root", "") or die(mysql_error());
    mysql_select_db("") or die(mysql_error());
    
    $query = "SELECT  * FROM affiliSt_products1 GROUP BY prodCategory ORDER BY prodCategory ASC"; 
    $result = mysql_query($query) or die(mysql_error());
    
    // Print out result
    while($row = mysql_fetch_array($result)){
    	$cats = $row['prodCategory'];
           echo $cats;
           echo '<br>';
    }
    
    $query = "SELECT  * FROM affiliSt_products1 WHERE prodCategory = '$cats' GROUP BY prodBrand ORDER BY prodBrand ASC"; 
    $result = mysql_query($query) or die(mysql_error());
    
    // Print out result
    while($row = mysql_fetch_array($result)){
    	$brands = $row['prodBrand'];
           echo $brands;
           echo '<br>';
    }
    Thanks

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,097
    Thanks
    23
    Thanked 594 Times in 593 Posts
    Your using the same query twice to retrive your information. One query will work. Don't use SELECT * only use what your need SELECT prodCategory, prodBrand.

    I've modified a display I use. See if it works, it should.

    Code:
    $query = "SELECT prodCategory, prodBrand FROM affiliSt_products1";
    $result = mysql_query($query);
    $colCount = mysql_num_fields($result);
    
    echo "<br /><TABLE BORDER=1>affiliSt_products1<br />";
    
    echo "<tr>";
    for($colNuber = 0; $colNuber < $colCount; $colNuber++)
    {
    	$fieldName = mysql_field_name($result, $colNuber);
    	echo "<th>$fieldName</th>";
    }
    echo "</tr>";
    
    while($row = mysql_fetch_row($result))
    {
    	echo "<tr align=left VALIGN=TOP>";
    	for($colNumber = 0; $colNumber < $colCount; $colNumber++)
    	echo "<td>$row[$colNumber]</td>";
    	echo "</tr>";
    }
    echo "</table><br />";

  • Users who have thanked sunfighter for this post:

    Mark-Jones (05-02-2012)

  • #3
    New Coder
    Join Date
    Jul 2010
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks - works great.

    I messed around a little with grouping and order with the results now exactly as I wanted. I will learn a fair bit from your solution.

    Regards
    Mark

  • #4
    New Coder
    Join Date
    Jul 2010
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Still can't get my head around this!

    Thanks for the help with the previous post. I wonder if you can sort this out too:

    Code:
    $cat = 'Fragrance';
    
    $query = "SELECT  * FROM affiliSt_products1 where prodCategory = '$cat' AND prodBrand IS NOT NULL GROUP BY prodBrand ASC"; 
    	
    $result = mysql_query($query) or die(mysql_error());
    
    
    // Print out result
    while($row = mysql_fetch_array($result )){
    	$fragrances = "". $row['prodBrand'] ."" . '(:)' ;
    }
    
    $cat = 'Gift Sets';
    
    $query = "SELECT  * FROM affiliSt_products1 where prodCategory = '$cat' AND prodBrand IS NOT NULL GROUP BY prodBrand ASC"; 
    	 
    $result = mysql_query($query) or die(mysql_error());
    
    // Print out result
    while($row = mysql_fetch_array($result)){
    	$giftsets = "". $row['prodBrand'] ."" . '(:)' ;
    }
    
    mysql_query("update `affiliSt_config` set value = 'Fragrance(:)$fragrances unbranded-:-Gift Sets(:)$giftsets unbranded' WHERE name like '%nav%' and name not like '%option%' ") or die(mysql_error());
    Same as before really. I need to output ALL the brands into the last part of the code, whereas now only the last brand is showing. I can't seem to figure out how to combine it all - yet again!

    Thanks
    Last edited by Mark-Jones; 05-03-2012 at 10:11 PM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Has nothing to do with MySQL.

    Look at this code, as an example:
    Code:
    $i = 0;
    while ( ++$i < 10 )
    {
        $j = $i;
    }
    echo $j;
    What would you expect that echo line to show?

    If you think it should show something like 123456789 then I guess your confusion in the code you show is understandable.

    In point of fact, the echo will show *ONLY* 9.

    That's because each time through the loop, you are REPLACING the prior value of $j with the new value of $i.

    So, at the end of the loop, the only thing $j remembers is the last value of $i that it saw.

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

    SAME THING with your code.

    Code:
    // Print out result
    while($row = mysql_fetch_array($result )){
    	$fragrances = "". $row['prodBrand'] ."" . '(:)' ;
    }
    EACH TIME through that while loop, you are *REPLACING* the value of $fragrances with that expression on the right side of the equal sign.

    So, at the end of the loop, you will have only ONE brand...the last one that was found via the looping.

    If you want *all* brands, you have to collect all brands.

    Possibly something like this:
    Code:
    // Print out result
    $fragrances = ""; // initialize!
    while($row = mysql_fetch_array($result )){
    	$fragrances = $fragrances . $row['prodBrand'] . '(:)' ;
    }
    *NOW*, at the end of the loop, your $fragrances variable will contain something like
    Code:
    Calvin Klein(:)Chanel No. 5(:)Jordache(:)
    I'm not clear what the purpose of the (:) is in there, but since you coded it explicitly, I left it alone.

    NOTE: PHP also has a "shorthand" way of writing that:
    Code:
    	$fragrances .= $row['prodBrand'] . '(:)' ;
    The .= operator means "append to the end of the existing string".
    Last edited by Old Pedant; 05-03-2012 at 11:17 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Mark-Jones (05-03-2012)

  • #6
    New Coder
    Join Date
    Jul 2010
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I understood that - at last. I placed a full stop before the equal sign as you said and the code then ran perfectly. Hopefully I will be able to work out any similar problem from now on.

    Regards
    Mark


  •  

    Posting Permissions

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