[RESOLVED] Gouping a result, need help!

dassyka

Hi everybody,

I have a list of my categories and sub-cat but when it shows, it's not group. I want my sub-cat to be with the categories but not the categorie to repeat for each sub-cat...

Here's my code:

<?php

$db = new mysqli('xxx','xxx','','xxx');

$query = "select * from categorie, sous_categorie where categorie.id_cat = sous_categorie.id_cat";

$result = $db -> query($query);

echo "<ul>";
while($row = $result -> fetch_assoc())
{

echo "<li>" . $row['cat_name'] . " ";
echo "</li>";
echo "<ul>";
echo "<li>" . $row['sous_cat_name'] ." ";
echo "</li>";
echo "</ul>";
}
echo "</ul>";
?>

And here's the result i have right now:

Dishwasher

* undercounter dishwasher

Crepes Machine

* Double Crepes Machines

Crepes Machine

* Single Crepes Machines

Paninis

* Sirman Paninis

Paninis

* Eurodib Paninis

Molds

* Warner Brother Molds

Molds

* Relief Mats

Molds

* Cake Mold

Molds

* Jelly Flex Mold

Can somebody tell me how to display this result for my categories not repeat but to have under it all the sub-cat related to it.

Thank you very much!

devphp12

hi
u can do it with two query

$q_1="select * from category order by cat_name asc";

while($a=fetch_Array)
{
echo "Main Cate Nmae";

      $q2="select * from category where parent_id=".$a['cat_id'];
       while($a2=fetch_array())
      {
                       echo "Subcateg Cate Nmae";


      }

}

dassyka

I follow your advise and here what i coded:

<?php

$db = new mysqli('localhost','root','','eurodib');

$query_1 = "select * from categorie order by cat_name asc";
$a = $db -> query($query_1);

echo "<ul>";
while($row = $a ->fetch_Array())
{

echo "<li>" . $row['cat_name'] . " ";
echo "</li>";
echo "<ul>";
$query_2 = "select * from sous_categorie where parent_id = .$a[id_cat]";
$a2 = $db -> query($query_2);
while ($row = $a2 ->fetch_Array)
{
echo "<li>" . $row['sous_cat_name'] ." ";
echo "</li>";
echo "</ul>";

}
}
echo "</ul>";
?>

but here my result:

Crepes Machine

  Fatal error: Cannot use object of type mysqli_result as array in C:\Program Files\wamp\www\index.php on line 32

Does somebody see what's wrong because i don't! 😕
Thank you...

devphp12

ur query syntax is wrong

write this one :

$query_2 = "select * from sous_categorie where parent_id =".$a[id_cat];

dassyka

Sorry but i'm doing it and i still get the same result!

Crepes Machine

  Fatal error: Cannot use object of type mysqli_result as array in C:\Program Files\wamp\www\index.php on line 32

and i did the code you told me :

<?php

$db = new mysqli('localhost','root','','eurodib');

$query_1 = "select * from categorie order by cat_name asc";
$a = $db -> query($query_1);

echo "<ul>";
while($row = $a ->fetch_Array())
{

echo "<li>" . $row['cat_name'] . " ";
echo "</li>";
echo "<ul>";
$query_2 = "select * from sous_categorie where parent_id =".$a[id_cat];
$a2 = $db -> query($query_2);
while ($row = $a2 ->fetch_Array)
{
echo "<li>" . $row['sous_cat_name'] ." ";
echo "</li>";
echo "</ul>";

}
}
echo "</ul>";
?>

Does somedoby have an other solution? Please!

laserlight

I would suggest an approach like this:

<?php

$db = new mysqli('localhost', 'root', '', 'eurodib');

$query = "SELECT categorie.cat_name, sous_categorie.*
    FROM sous_categorie, categorie
    WHERE sous_categorie.parent_id=categorie.id_cat
    ORDER BY categorie.cat_name";
$result = $db->query($query);

echo "<ul>\n";
$cat_name = '';
while ($row = $result->fetch_array())
{
    if ($row['cat_name'] != $cat_name)
    {
        // End previous category, if any.
        if ($cat_name != '')
        {
            echo "</ul>\n</li>\n";
        }

    // Begin the new category.
    $cat_name = $row['cat_name'];
    echo "<li><h2>" . htmlspecialchars($cat_name) . "</h2>\n<ul>\n";
}

echo "<li>" . htmlspecialchars($row['sous_cat_name']) . "</li>\n";
}
// End category, if any.
if ($cat_name != '')
{
    echo "</ul>\n</li>\n";
}
echo "</ul>";

?>

For one thing, notice that it only uses one query instead of executing queries in a loop. The query orders by the cat_name, and then checks to see if the cat_name retrieved is different from the current cat_name. If it is, then presumably we are at the start of the next category. When printing strings to be displayed by the browser, you should escape them as I have done with [man]htmlspecialchars/man.

EDIT:
Incidentally, I have removed the line breaks from your list items. You should style your lists with CSS, not HTML. I have also fixed the incorrect nesting of lists and placed your main category names in level 2 headings (but you may want to change that).

dassyka

Thank you!

It seems to be very good but when i take your code, here's the result i have now:

Fatal error: Call to a member function fetch_array() on a non-object in C:\Program Files\wamp\www\index.php on line 29

and here's the line 29:

while ($row = $result->fetch_array())

What can i correct to make it work?

laserlight

You probably need to fix the SQL statement itself, I was guessing based on the information provided.

To help you figure out what is wrong with the SQL statement (or if there is some other database related error), change:

$result = $db->query($query);

to:

$result = $db->query($query) or die($db->error);

In production quality code, you should not abort the script like that, but instead handle the error gracefully.

dassyka

THANK YOU!!!!!!

You don't know how you just save my life right now!!!!!😃

That's the solution i was searching for days!!!!!

Thank you thousand time and god bless you!!!!!!