Need to display 10 products from each category

[nudl3s]

I want to display 10 products from each category from database, I started to display the categories but how to make displaying the products from each one.

Here is the code I make so far. I'm not sure am I doing it right with doble sql select , or it can be done only with one.

<div class="inner shadow">
            <?php
                $query = 'SELECT id,
                                 title_bg AS `title_cat`
                                 FROM categories';
                $result = $this->db->query($query);
            ?>
           
            <?php foreach ($result->result() as $row): ?>
                <?php $title_cat = stripslashes($row->title_cat); ?>

                <div class="prod-sec">
                    <div class="prod-head">
                        <h2><?= $title_cat?></h2>

                        <div class="clear"></div>
                    </div>
                    <?php
                        $query1 = 'SELECT t1.id,
                                         t1.title_bg AS `Title`,
                                         t1.text_bg AS `Text`,
                                         t1.price,
                                         t1.discount,
                                         t1.category_id,
                                         t1.promo_page,
                                         t2.id AS FileID,
                                         t2.ext,
                               
                              FROM products t1
                              LEFT JOIN products_pictures t2 ON t1.id = t2.object_id
                              LEFT JOIN categories t3 ON t3.id = t1.category_id
                              WHERE t1.promo_page = 0
                              AND t1.is_active = 1
                              AND t3.title_bg = '$title_cat'
                              ORDER BY RAND()
                              LIMIT 10';

                              $result1 = $this->db->query($query1); 
                    ?>
                    <div id="classeslist2">
                        <ul class="home_middle_products">  
                          
                            <?PHP
                            
                                foreach($result1->result() as $row1)
                                {
                                    $f = 'files/products_first_page/' . $row1->id . '.jpg';
                   
                                    if(is_file(dirname(__FILE__) . '/../../' . $f)) {
                                       
                                        $img = site_url() . "files/products_first_page/".$row1->id.".jpg";
                                       
                                    } else {
                                   
                                        $img = site_url() . "files/products/".$row1->id."/".$row1->FileID."_2.".$row1->ext;
                                   
                                    }
                               
                                    $title = stripslashes($row1->Title);
                                    $text = character_limiter(strip_tags(stripslashes($row1->Text)),250);
                                
                                    $title_url = getLinkTitle($title);
                                    $link = site_url()."products/product/".$row1->id."/{$title_url}";
                               
                            ?>
                                <li style="width: 185px; height: 270px; margin-left: 3px; margin-top: 10px;">
                                    <div class="thumb">
                                        <a href="<?=$link?>"><img src="<?=$img?>" alt="<?=$title?>" width="182" /></a>
                                        <div class="price"><?PHP echo product_price($row1, array('show_discount' => false,"show_old_price"=>false, 'show_label' => false, 'view' => 'no')); ?>
                                           
                                        </div>
                                    </div>
                                    <h2></h2>
                                    <h2><a href="<?=$link?>"><?=$title?></a></h2>
                                    <? $text = substr($text, 0, 100); ?>
                                    <? if (strlen($text) == 100) $text .= '...' ; ?>
                                    <p><?=$text?></p>
                                </li>
                                <?php } ?>
                            <div class="clear"></div>
                        </ul>
                    </div>
                </div>

            <?php endforeach; ?>

               
          
            <div class="clear"></div>
            </div>

Edited January 4, 2017 by nudl3s

[ginerjm]

How about putting the limit clause on the join'ed table selection?

 

To paraphrase: select your categories in the main table and join the table that pertains to those categories, limiting that selection to 10.

[Barand]

I used a test table of mine which contains 20 types. Each type has 9000+ rows. This query selects 10 of each type. I have annotated the purpose of the subqueries.

SELECT type                                                                        --+
  , id                                                                               |
FROM (                                                                  --+          |
    SELECT id                                                             |          |
     , @row := IF(@prev=type, @row + 1, 0) as row                         |          |
     , @prev := type as type                                              | apply    |
     FROM (                                            --+                | row      | get 10
        SELECT id, type                                  | select data    | count    | of 
        FROM votes                                       | order by       | within   | each
            JOIN (SELECT @row:=0,@prev:=null) init       | type           | each     | type
        ORDER BY type                                    |                | type     |
        ) votes                                        --+                |          |
     ) data                                                             --+          |
WHERE row < 10;                                                                    --+
 

[nudl3s]

Okey here is my code till now I have done...

<div class="inner shadow">
            <?php 
                $query = 'SELECT id, 
                                 title_bg AS `title_cat` 
                                 FROM  categories';
                $result = $this->db->query($query);
            ?>
            

            <?php foreach ($result->result() as $row): ?>
                <?php $title_cat = stripslashes($row->title_cat); ?>
				<?php $id = $row->id; ?>
                <div class="prod-sec">
                    <div class="prod-head">
                        <h2><?= $title_cat?></h2>
                    </div>
                    <?php $query1 = 'SELECT id,
                    						title_bg AS `Title`,
                    						text_bg AS `Text`,
                    						price,
                    						discount,
                    						category_id,
                    						promo_page
                    				FROM products 
                    				ORDER BY id 
                    				LIMIT 10'; 
                    ?>
                    <?php $result1 = mysql_query($query1); ?>
                    <?php $resultset = array();
						while ($set = mysql_fetch_array($result1)) {
						  $resultset[] = $set;
						} ?>
                    <div id="classeslist2">
                        <ul class="home_middle_products">   
                            <?php foreach ($resultset as $key): ?>
                           		<?php  $title = $key['Title']; ?>
                                <?php  $text = $key['Text']; ?>
                                <?php  $price = $key['price']; ?>
                                <?php  $discount = $key['discount']; ?>
                                <?php  $prod_id = $key['id']; ?>
	

                           		<li style="width: 185px; height: 270px; margin-left: 3px; margin-top: 10px;">
                                    <div class="thumb">
                                        <a href=""><img src="" alt="<?=$id?>" width="182" /></a>
                                        <div class="price">
                                            
                                        </div>
                                    </div>
                                    <h2></h2>
                                    <h2><a href=""><?=$title?></a></h2>
                                    
                                </li>

                           <?php endforeach; ?>
                            
                            <div class="clear"></div>
                        </ul>
                    </div>
                </div>
            <?php endforeach; ?>

                
           
            <div class="clear"></div>
            </div>

And this is the screenshot 

 

The numbers are the ID of the product, but on each category they are the same, what I need to change or make to make them display only from that category are in.

I tried to put WHERE clause in second SELECT (category_id = '$id') but it didnt help

Edited January 6, 2017 by nudl3s

[nudl3s]

sorry little mistake I make it work now