How to display thumbnails with category by category

Question

http://pastie.org/962429

I have the above SQL.

There are three categories, webdesign, logos and prints.

I want to display them as following html.

http://pastie.org/962432

Basically pulling thumbnail image and name from database category by category.

I am not really sure how to do it.

I will appreciate any inputs or help.

Thanks in advance.

Answer 1

Well, first query the DB for your categories (I assume you have a categories table with the names in it:

$res = mysql_query("SELECT c.name AS cname, p.name AS pname, p.image 
                   FROM omc_products p 
                   LEFT JOIN categories c ON p.category_id = c.id
                   ORDER BY c.name");
if ($res)
     {
     echo '<ul>';
     while ($row = mysql_fetch_array($res))
           {
           // Echo your HTML here e.g.
           echo '<li><img src="'.$row['image'].'" />".$row['pname'].' - '.$row['cname']</li>';
           }

     echo '</ul>';
     }

Answer 2

If you want to group the results by category. Try pulling all the results and then building an array of the categories. Then using this array to loop through each of the categories.

It's quite an intensive PHP method, there maybe a better way using MySQL to do it.

//do your query
    $query = mysql_query("SELECT id, name, category_id FROM omc_product"); 

    //INIT CATEGORIES AND IMAGES ARRAY
    $cateogriesPresent = array();
    $images = array();

    //LOOP TRHOUGH RESULTS
    while ($row=mysql_fetch_assoc($query)) {

        //BUILD ARRAY OF IMAGES
        $images[] = array('name' => $row['name'], 'category' => $row['id'], 'category' => $row['category_id']);

            //ADD EACH UNIQUE CATEGORY TO CATEGORY ARRAY
            if(!in_array($row['category_id'], $cateogriesPresent)){
                array_push($cateogriesPresent, $row['category_id']);
            }

        }



    //IMAGES BY CAT
    foreach ($cateogriesPresent as $cat){

        //ECHO HTML FOR EACH CAT HERE
        echo "";
        foreach ($images as $image){
            if($image['category_id'] == $cat){
                //ECHO YOUR HTML HERE FOR EACH IMAGE
                echo ""

            }       
        echo "";
        }
    }