Jump to content

get 10 records from each category

nudl3s

By nudl3s
in PHP Coding Help

 Share
Go to solution Solved by Psycho,

Recommended Posts

nudl3s Member

nudl3s

Posted
Posted

Hello to all, 

Till now I made it like that. But as you can see, these two products are from the same category, but the category shows every time there is product from it.

How to make it displays only one category and 10 products from it.

 six_only_best_price.png

 

Here is the code, do I have to make another for and how, where to put it, if anyone can help me:

<?php $this->view("common/header"); ?>

 <link rel="canonical" href="<? echo site_url() ?>" />
<!-- <link rel="stylesheet" href="/css/bx_styles/bx_styles.css" type="text/css" /> -->
<!-- <script type="text/javascript" src="/js/jquery.bxSlider.js"></script> -->
<!-- <script type="text/javascript" src="/js/jquery.bxslider.min.js"></script> -->

<div class="inner">
    <div class="inner shadow">
        <?php 
            $query = "SELECT p.id,
                            p.title_".$this->config->config['language']." as Title,
                            p.text_".$this->config->config['language']." as Text,
                            c.title_".$this->config->config['language']." as CatTitle,
                            b.title_".$this->config->config['language']." as BrandTitle,    
                            p.price,
                            p.package,
                            p.discount,
                            img.id as FileID,
                            img.ext
                        FROM
                            products as p
                        LEFT JOIN
                            products_pictures as img ON(img.object_id = p.id )
                        LEFT JOIN
                            categories as c ON( c.id = p.category_id )
                        LEFT JOIN
                            brands as b ON( p.brand_id = b.id)
                        WHERE
                            (p.best_price = 1 OR c.best_price = 1 OR b.best_price = 1)
                            AND p.promo_page = 1
                            AND p.is_active = 1
                            AND b.is_active = 1
                            AND c.is_active = 1
                        ORDER BY c.id
                        LIMIT 10";
            $result = $this->db->query($query);
        ?>

             <?php foreach($result->result() as $row) : ?>
                <?php   $img_src = site_url();
                        $img_formats = "jpg,jpeg,png,gif";
                        if(strstr($img_formats, $row->ext))
                        {
                            $img_src .= "files/products/".$row->id."/".$row->FileID."_1.".$row->ext;
                        }
                        elseif($row->ext=="flv")
                        {
                            $img_src .= "images/video.png";
                        }
                        else
                        {
                            $img_src .= "files/default.jpg";
                        }
                        $title = character_limiter(stripslashes($row->Title), 55);
                        $title_url = getLinkTitle($title);
                        $link = site_url()."products/product/".$row->id."/{$title_url}";
                        $title_cat = stripslashes($row->CatTitle);
                    ?>
            <div class="prod-sec">
                <div class="prod-head">
                    <h2><?= $title_cat?></h2>
                </div>
                
                    <div class="gall-products">
                        <div>
                            <h2 itemprop="name"><a target="_blank" itemprop="url" href="<?=$link?>" title="<?=$title?>" rel="overdiv_hidden_1_<?=$row->id?>"><?=$title?></a></h2>
                        </div>
                        <div class="gall-img">
                           
                            <a target="_blank" href="<?=$link?>" title="<?=$title?>" rel="overdiv_hidden_1_<?=$row->id?>"><img itemprop="image" src="<?=$img_src?>" alt="Съдържание » Цена » Прием » <?=$title?>" title="<?=$title?>" width="100%" /></a>
                        </div>
                            
                        <div class="price">
                        <?=product_price($row, array('show_discount' => false, 'show_label' => false))?>
            
                        </div>
                        <div class="view-product">
                            <div><a href="javascript:to_basket(<?=$row->id?>);" class="btn-cart">Добави в количка</a></div>
                            <div><button class="backcolr" onclick="parent.location='<?=$link?>';">Разгледай</button></div>
                            №:<span itemprop="productID"><?=$row->id?></span>
                        </div>
                    </div>
            
            </div>    
               
            <?php endforeach; ?>

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

<?php $this->view("common/footer"); ?>
Link to comment
https://forums.phpfreaks.com/topic/302921-get-10-records-from-each-category/
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted

the easiest way of producing this type of output is to index the data using the category value when you retrieve the data, by fetching the data into a multi-dimensional array, using the category value as the main array index, and storing each row of data in a sub-array under that index value. then just loop over the main array, which will give you the category and a sub-array of the rows for that category. then just loop over the sub-array to produce the output.

 

after you execute the query - 

$data = array();
foreach($result->result() as $row)
{
    $data[$row['CatTitle']][] = $row;
}


// to produce the output -
foreach($data as $CatTitle=>$sub_array)
{
    // use $CatTitle here to produce the category heading

    foreach($sub_array as $row)
    {

        // use the elements in $row to produce the output for each product under the category

    }
}
Psycho Prolific Member

Psycho

Posted
Posted

Pro tip: Don't mix your PHP code within the HTML. Put your PHP logic at the top of the script and generate the dynamic output into variable. Then output the variables within the HTML. It will make management of your code much, much easier. Ideally yuo will separate the logic (PHP) and presentation (HTML) in different files, but separating them in the same file is a good first step.

 

Pro tip #2: Use comment in your code! It may make sense to you as you write it, but when you have to go back to the code after a few days, weeks, months the comments will save you a ton of time. plus, it makes it much easier when you are asking people for help with your code :)

 

If you are ONLY going to output records from one category, you can just have a flag/counter to determine the first record being processed and only output the category on the first record. But, I would recommend building the functionality to be more flexible. For example, what if you want to show the output for multiple categories and only show the category header at the start of each section. This ensure you do not program yourself into a corner.

 

But, your query doesn't have any condition to limit by one category. So, I'm not sure if you only want one category in the results (in which case there should be a WHERE condition) or if you want the output to contain all categories with only 10 products each.

 

There are different approaches. Typically you can just "test" the category value to see if it is the same as the previous value. If it is different, output the category. If it is the same as the last record, do not output the category. But, since you need to wrap the multiple products for the category within a div, an easier approach is to pre-process the query results into a multi-dimensional array - then use that array to create the output.

 

FYI: I can think of no legitimate reason why you should be using strip_slashes() in your code. If you "need" it,it is because of some other problem that you should resolve.

 

The following would work for output for multiple categories. Not tested

 

<?php
 
$this->view("common/header");
 
//Run query to get results
$query = "SELECT p.id,
p.title_".$this->config->config['language']." as Title,
p.text_".$this->config->config['language']." as Text,
c.title_".$this->config->config['language']." as CatTitle,
b.title_".$this->config->config['language']." as BrandTitle,    
p.price,
p.package,
p.discount,
img.id as FileID,
img.ext
FROM
products as p
LEFT JOIN
products_pictures as img ON(img.object_id = p.id )
LEFT JOIN
categories as c ON( c.id = p.category_id )
LEFT JOIN
brands as b ON( p.brand_id = b.id)
WHERE
(p.best_price = 1 OR c.best_price = 1 OR b.best_price = 1)
AND p.promo_page = 1
AND p.is_active = 1
AND b.is_active = 1
AND c.is_active = 1
ORDER BY p.id
LIMIT 10";
$result = $this->db->query($query);
 
//Loop over result set and put into structured array
$resultsAry = array();
foreach($result->result() as $row)
{
$resultsAry[stripslashes($row->CatTitle)] = $row;
}
 
//Store current site url in variable (no need to call it multiple times)
$siteUrl = site_url();
//Define the valid image formats (do not do in loop)
$img_formats = array('jpg', 'jpeg', 'png', 'gif');
 
//Create variable to store the output
$outputHtml = '';
//Iterate over results to create html output
foreach($resultsAry as $category => $products)
{
//Start new category section
$outputHtml .= "<div class="prod-sec">\n";
$outputHtml .= " <div class="prod-head">\n";
$outputHtml .= " <h2>{$title_cat}</h2>\n";
$outputHtml .= " </div>\n";
//Create output for each product
foreach($products as $product)
{
//Determine the image source
if(in_array($row->ext, $img_formats)) {
$img_src = "{$siteUrl}files/products/{$row->id}/{$row->FileID}_1.{$row->ext}";
} elseif($row->ext=="flv") {
$img_src = "{$siteUrl}images/video.png";
} else {
$img_src = "{$siteUrl}files/default.jpg";
}
//Determine other needed values
$title     = character_limiter(stripslashes($row->Title), 55);
$title_url = getLinkTitle($title);
$link      = "{$siteUrl}products/product/{$row->id}/{$title_url}";
$price     = product_price($row, array('show_discount' => false, 'show_label' => false));
//Create the product html output
$outputHtml .= " <div class='gall-products'>\n";
$outputHtml .= " <div>\n";
$outputHtml .= " <h2 itemprop='name'><a target='_blank' itemprop='url' href='{$link}' title='{$title}' rel='overdiv_hidden_1_{$row->id}'>{$title}</a></h2>\n";
$outputHtml .= " </div>\n";
$outputHtml .= " <div class='gall-img'>\n";
$outputHtml .= " <a target='_blank' href='{$link}' title='{$title}' rel='overdiv_hidden_1_{$row->id}'><img itemprop='image' src='{$img_src}' alt='Съдържание » Цена » Прием » {$title}' title='{$title}' width='100%' /></a>\n";
$outputHtml .= " </div>\n";
$outputHtml .= " <div class='price'>{$price}</div>\n";
$outputHtml .= " <div class='view-product'>\n";
$outputHtml .= " <div><a href='javascript:to_basket({$row->id});' class='btn-cart'>Добави в количка</a></div>\n";
$outputHtml .= " <div><button class='backcolr' onclick='parent.location='{$link}';'>Разгледай</button></div>\n";
$outputHtml .= " №:<span itemprop='productID'>{$row->id}</span>\n";
$outputHtml .= " </div>\n";
$outputHtml .= " </div>\n";
}
//Close category section
$outputHtml .= "</div>\n";
}
 
?>
 
<link rel="canonical" href="<?=$siteUrl?>" />
<!-- <link rel="stylesheet" href="/css/bx_styles/bx_styles.css" type="text/css" /> -->
<!-- <script type="text/javascript" src="/js/jquery.bxSlider.js"></script> -->
<!-- <script type="text/javascript" src="/js/jquery.bxslider.min.js"></script> -->
 
<div class="inner">
    <div class="inner shadow">
<?=$outputHtml?>
    </div>
    <div class="clear"></div>
 
<?php $this->view("common/footer"); ?>
Psycho Prolific Member

Psycho

Posted
Posted

Another observation. LEFT JOINs are slow, you should not use them unless you know you need them and in this case you don't since yu depend on the data in those joined tables. This should get you the same results more efficiently.

 

 

$lng = $this->config->config['language'];
$query = "SELECT p.id, p.title_{$lng} as Title, p.text_{$lng} as Text,
p.price, p.package, p.discount,
img.id as FileID, img.ext
c.title_{$lng} as CatTitle,
b.title_{$lng} as BrandTitle,    
 
FROM products as p
JOIN products_pictures as img
   ON(img.object_id = p.id AND p.promo_page = 1 AND p.is_active = 1)
JOIN categories as c
   ON( c.id = p.category_id AND c.is_active = 1)
JOIN brands as b
   ON( p.brand_id = b.id AND b.is_active = 1)
 
WHERE (p.best_price = 1 OR c.best_price = 1 OR b.best_price = 1)
 
ORDER BY p.id
LIMIT 10";
nudl3s Member

nudl3s

Posted
  • Author
Posted (edited)

Psycho thanks a lot for your comments I need more time to be a good php programmer, I tried with your code but the page cannot be open shows up. About mac_gyver I am trying with your code and it helps a lot. 

 

All I wanted is to show all categoryes that have products with "promo_page = 1" and the maximum for each category to be 10.

Example: Category 1: maximum 10 products from each category 

                    - product from cat 1 

                    - product from cat 1

                    - product from cat 1

                Category 2:

                    - product from cat 2

                    - product from cat 2

                    - product from cat 2

                    

 

 

For the moment is this with little issues. Products from the third category have to be in category 1 and these from category 2 goes in category 3. How to repair that, and how to put limit of 10 to products in each category.

last.png

 

The Code : 


<?php $this->view("common/header"); ?>
<?php error_reporting(E_ALL); ?>
 <link rel="canonical" href="<? echo site_url() ?>" />
<!-- <link rel="stylesheet" href="/css/bx_styles/bx_styles.css" type="text/css" /> -->
<!-- <script type="text/javascript" src="/js/jquery.bxSlider.js"></script> -->
<!-- <script type="text/javascript" src="/js/jquery.bxslider.min.js"></script> -->

<div class="inner">
    <div class="inner shadow">
        <?php 
            $query = "SELECT p.id,
                            p.title_".$this->config->config['language']." as Title,
                            p.text_".$this->config->config['language']." as Text,
                            c.title_".$this->config->config['language']." as CatTitle,
                            b.title_".$this->config->config['language']." as BrandTitle,    
                            p.price,
                            p.package,
                            p.discount,
                            img.id as FileID,
                            img.ext
                        FROM
                            products as p
                        LEFT JOIN
                            products_pictures as img ON(img.object_id = p.id )
                        LEFT JOIN
                            categories as c ON( c.id = p.category_id )
                        LEFT JOIN
                            brands as b ON( p.brand_id = b.id)
                        WHERE
                            p.promo_page = 1
                            AND p.is_active = 1
                            AND b.is_active = 1
                            AND c.is_active = 1
                        ORDER BY c.id";
            $result = $this->db->query($query);
        ?>
        
        <?php $data = array(); ?>
        <?php foreach($result->result() as $row) : ?>
            <?php  $data[$row->CatTitle][] = $row; ?>
        <?php endforeach; ?>
            <!-- to produce the output - -->
            <?php foreach($data as $CatTitle=>$sub_array): ?>
             <!-- // use $CatTitle here to produce the category heading -->
                <?php $title_cat = stripslashes($row->CatTitle); ?>

                <div class="prod-sec">
                    <div class="prod-head">
                        <h2><?= $title_cat?></h2>
                    </div>
                    
                    <?php foreach($sub_array as $row): ?>
                    
                        
                        <!-- // use the elements in $row to produce the output for each product under the category -->
                        <?php   
                            $img_src = site_url();
                            $img_formats = "jpg,jpeg,png,gif";
                            if(strstr($img_formats, $row->ext))
                            {
                                $img_src .= "files/products/".$row->id."/".$row->FileID."_1.".$row->ext;
                            }
                            elseif($row->ext=="flv")
                            {
                                $img_src .= "images/video.png";
                            }
                            else
                            {
                                $img_src .= "files/default.jpg";
                            }
                            $title = character_limiter(stripslashes($row->Title), 55);
                            $title_url = getLinkTitle($title);
                            $link = site_url()."products/product/".$row->id."/{$title_url}";
                            
                        ?>
                        <div itemscope itemtype="http://schema.org/Product">
                            <div class="gall-products">
                                <div>
                                    <h2 itemprop="name"><a target="_blank" itemprop="url" href="<?=$link?>" title="<?=$title?>" rel="overdiv_hidden_1_<?=$row->id?>"><?=$title?></a></h2>
                                </div>
                                <div class="gall-img">
                                   
                                    <a target="_blank" href="<?=$link?>" title="<?=$title?>" rel="overdiv_hidden_1_<?=$row->id?>"><img itemprop="image" src="<?=$img_src?>" alt="Съдържание » Цена » Прием » <?=$title?>" title="<?=$title?>" width="100%" /></a>
                                </div>
                                    
                                <div class="price">
                                <?=product_price($row, array('show_discount' => false, 'show_label' => false))?>
                    
                                </div>
                                <div class="view-product">
                                    <div><a href="javascript:to_basket(<?=$row->id?>);" class="btn-cart">Добави в количка</a></div>
                                    <div><button class="backcolr" onclick="parent.location='<?=$link?>';">Разгледай</button></div>
                                    №:<span itemprop="productID"><?=$row->id?></span>
                                </div>
                            </div>
                        </div>

                    <?php endforeach; ?>
                    

                </div>

            <?php endforeach; ?>

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

<?php $this->view("common/footer"); ?>
Edited by nudl3s
  • Solution
Psycho Prolific Member

Psycho

Posted
  • Solution
Posted

I did say that code was not tested. I don't have your database or other required files to run that code. I expect you to check for any typos I made and fix them or provide the errors and I can assist.

 

 

 

For the moment is this with little issues. Products from the third category have to be in category 1 and these from category 2 goes in category 3. How to repair that, and how to put limit of 10 to products in each category.

 

Well, your current query is sorting by p.id. If you want things to be grouped by category then you need to put logic in place to do that. The logic in the code I provided previously would do that. But, even once we get that working, you have another issue. If you want all the categories to be returned with only 10 products per category, you are going to need a somewhat more complex query. Give me some time. If you can provide a dump of the relevant tables, then I could actually test the code I provide.

nudl3s Member

nudl3s

Posted
  • Author
Posted

I did say that code was not tested. I don't have your database or other required files to run that code. I expect you to check for any typos I made and fix them or provide the errors and I can assist.

 

 

 

 

Well, your current query is sorting by p.id. If you want things to be grouped by category then you need to put logic in place to do that. The logic in the code I provided previously would do that. But, even once we get that working, you have another issue. If you want all the categories to be returned with only 10 products per category, you are going to need a somewhat more complex query. Give me some time. If you can provide a dump of the relevant tables, then I could actually test the code I provide.

How to make contact with you more private, you have skype or something

nudl3s Member

nudl3s

Posted
  • Author
Posted

All done Thanks all for the help.

The limit of 10 i made like:

<?php foreach($sub_array as $break=>$row): ?>
                    
                        <?php if ($break == 10): ?>
                        	<?php break; ?>
                        <?php endif ?>

And the issue for the product from same category:

This

<?php $title_cat = stripslashes($row->CatTitle); ?>

Replace with :

<?php $title_cat = $sub_array[0]->CatTitle ?>
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.

×
 Share
Go to topic listing
×
×
  • 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.