[SOLVED] MySql result loop problems only first record shown

[allmambo]

Can anyone tell what i'm doing wrong?

Is there another way to do this?

 

Here's my problem, i have a few categories on a page,

the first SQL query, that works fine however i'd like to add products belonging to the category,

right under the header, but i'm only getting one record returned, seems like the second loop doesn't work at all.

PS. i'm using an accordion script for this, the "dhtmlgoodies_question" is the header, (the category)

and the "dhtmlgoodies_answer" is the products information

 

Here's the code:

<?php
$query  = "SELECT * FROM collection WHERE mcat_id='$cat_id'";
$result = mysql_query($query);
while($row = mysql_fetch_array( $result )) {

$c_name = $row['name'];
$c_desc =$row['desc'];
$c_id =$row['collection_id'];

$query_prod  = "SELECT * FROM products WHERE c_id='$c_id' ORDER BY name DESC";
$result_prod = mysql_query($query_prod);
while($row = mysql_fetch_array( $result_prod )) {

$p_name = $row['name'];
$p_desc =$row['desc'];
$p_pic =$row['pic'];
}

echo '
<div class="dhtmlgoodies_question"><strong>'.$c_name.'</strong><br />
'.$c_desc.'<br />
<div class="dt_button">Show Products</div>
</div>
<div class="dhtmlgoodies_answer"><div>
Product Name: <b>'.$p_name.'</b><br>
Product Desc: '.$p_desc.'<br>

<br />
  </div>
</div>' ;
}
?>

 

 

[lur]

You overwrite $row in your second while() loop.

 

The second while() loop doesn't print anything, it just loops over the result rows and assigns variables until there are no more results. If you want to print all results you'll need to do so inside the loop.

[wildteen88]

You will want to place the echo statement within the second while loop, but remove the first line of html from the echo statement and echo it out outside of the secound while loop.

<?php

$query  = "SELECT * FROM collection WHERE mcat_id='$cat_id'";
$result = mysql_query($query);

while($row = mysql_fetch_array( $result ))
{
    $c_name = $row['name'];
    $c_desc = $row['desc'];
    $c_id   = $row['collection_id'];

    // Only want to the header to show once
    echo '
    <div class="dhtmlgoodies_question"><strong>' . $c_name . '</strong><br />
    ' . $c_desc . ' <br />';

    $query_prod  = "SELECT * FROM products WHERE c_id='$c_id' ORDER BY name DESC";
    $result_prod = mysql_query($query_prod);

    while($row = mysql_fetch_array( $result_prod ))
    {
        $p_name = $row['name'];
        $p_desc = $row['desc'];
        $p_pic  = $row['pic'];

        // show all items that belong to the header
        echo '
        <div class="dt_button">Show Products</div>
        </div>
        <div class="dhtmlgoodies_answer"><div>
        Product Name: <b>'.$p_name.'</b><br>
        Product Desc: '.$p_desc.'<br>

         <br />
          </div>
        </div>';
    }
}

?>

[soycharliente]

Also, you may be able to rewrite the query so you don't need to use a while loop inside of another while loop. Using JOIN might save some server stress and give you everything you need in one query.

[hostfreak]

You overwrite $row in your second while() loop.

 

It doesn't matter as the values are assigned to variables.

[allmambo]

@wildteen88

THANKS A LOT, here's what i did and it works like a charm..

i guess i was just looking at it too long and didn't see the obvious problem,

which was my <div> placements....

 

@charlieholder

I've tried with JOIN but in this instance with the AJAX accordion it wouldn't work properly,

because the category name would repat itself for every product....

 

FIXED CODE....

<?php
// CATEGORY
$query  = "SELECT * FROM collection WHERE mcat_id='$cat_id'";
$result = mysql_query($query);
while($row = mysql_fetch_array( $result )) { 

$c_name = $row['name'];
$c_desc =$row['desc'];
$c_id =$row['collection_id'];
// Header info....
echo '
<div class="dhtmlgoodies_question"><strong>'.$c_name.'</strong><br />
'.$c_desc.'<br />
<div class="dt_button">Show Products</div>
</div>';

// PRODUCTS
?>
<div class="dhtmlgoodies_answer"><div>
<?php
$query_prod  = "SELECT * FROM products WHERE c_id='$c_id' ORDER BY name DESC";
$result_prod = mysql_query($query_prod);
while($row = mysql_fetch_array( $result_prod )) {

$p_name = $row['name'];
$p_desc =$row['desc'];
$p_pic =$row['pic'];
// Product info....
echo '
Product Name: <b>'.$p_name.'</b><br>
Product Desc: '.$p_desc.'<br>
<br />';
}
?>
  </div>
</div>
<?php
}
?>

 

THANKS ALL