Jump to content

Need to display 10 products from each category


nudl3s

Recommended Posts

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 by nudl3s
Link to comment
Share on other sites

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;                                                                    --+
 
  • Like 1
Link to comment
Share on other sites

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 

FHL.png

 

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 by nudl3s
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.