limit to 1 per where or clause

[ianhaneybs]

I have managed to get blog posts displayed from each blog category but I want to limit it to 1 article from each blog category but unsure how to do it in the sql query I have, below is what I have so far

(SELECT BP.postID,postTitle,postSlug,postDesc,postDate,postImage
        FROM 
          blog_posts BP, blog_post_cats BPC 
    WHERE 
       BPC.catID = 6 AND BPC.postID = BP.postID OR BPC.catID = 5 AND BPC.postID = BP.postID OR BPC.catID = 4 AND BPC.postID = BP.postID OR BPC.catID = 1 AND BPC.postID = BP.postID
     )
    UNION
    (SELECT BP.postID,postTitle,postSlug,postDesc,postDate,postImage
        FROM 
          blog_posts BP, blog_post_cats BPC
    WHERE 
       BPC.catID = BPC.postID = BP.postID
     )

[rproffitt]

I see you used the UNION so that would be my first thought. Now you have that, maybe you only need to add a LIMIT.
Tutorial Links follow:
https://www.tutorialspoint.com/sql/sql-unions-clause.htm
https://www.tutorialspoint.com/sql/sql-top-clause.htm

[ianhaneybs]

Hi

I tried LIMIT but that just shows a total of 1 article from the whole blog articles db table rather than 1 from each blog category

[rproffitt]

If your SQL final statement only returned 1 item total then that might be correct but I can't see what you tried. In psuedocode it should look like:

SQL something something LIMIT 1
UNION
SQL something something LIMIT 1

If the query of each fetched 1 then you should end up with two rows.

[ianhaneybs]

Ahh ok not sure if that will display 1 blog article from each blog category

[rproffitt]

Sorry but your reply above seemed to state your SQL returned one row. That is what I addressed using what you have revealed so far about your new SQL query. Maybe you need to share your code here.

Moving on, I didn't design your table as well as can't see the design so I can't address if the design supports your other goal but hey, I would work this in pieces. That is I'd get my query to return 1 row per your need before I added UNION and the next query for that next row.

I can't guess how many blog categories you have so that problem may have to coded to something more advanced such as a query to get how many and what these categories are, then those row results are fed into a new query that iterates over the categories. I would not do that right now but work the issue about the single row first along with a query that can pick up each category with the 1 from each category. Once I have that I can roll back to the top to make a query to find how many and what categories there are, THEN and only then tackle the full on solution. The end solution as I see it won't be a single query. Maybe someone else can do this in a single query?

[ianhaneybs]

The code is below, I updated the sql query to try something but don't think it's working still as it's still outputting more than 1 blog article from the blog categories

There are 6 blog categories in total

<?php
            $blogctr=1;
            $bdb = new PDO('mysql:host=localhost;port=8889;charset=utf8;dbname=dbname', 'username', 'password');
            $stmt = $bdb->prepare('SELECT blog_posts.postID FROM blog_posts, blog_post_cats WHERE blog_posts.postID = blog_post_cats.postID AND blog_post_cats.catID = :catID');

            $stmt->execute(array(':catID' => $row['catID']));

            $stmt = $bdb->prepare('

            SELECT bp.postID, bpc.postID, bpc.catID, bp.postTitle, bp.postImage, bp.postSlug, MAX(bp.postDate) most_recent
  FROM blog_posts bp RIGHT JOIN
       blog_post_cats bpc ON bp.postID = bpc.postID
 WHERE bpc.catID = 6 OR bpc.catID = 5 OR bpc.catID = 4
 GROUP BY bp.postID

                                        ');


            $stmt->execute(array(':catID' => $row['catID']));
            while($post =$stmt->fetch()):
                ?>

<div class="col-lg-4 col-md-6 col-sm-12">
                    <div class="single_blog text-center mb-4 wow fadeInUp" data-wow-delay="0.3s">
                            <a class="single_blog_thumb" href="../blog/<?php echo $post['postSlug'];?>"><img data-src="../blog/<?php echo $post['postImage'];?>" alt="<?php echo $post['postTitle'];?>" class="lazyload" /></a>
                        <?php if($blogctr==1) { ?>
                        <div class="single_blog_date single_blog_date_inner">
                                <h3><?php echo date("j", strtotime($post['postDate']));?></h3>
                                <span><?php echo date("M", strtotime($post['postDate']));?></span>
                                <span class="years"><?php echo date("Y", strtotime($post['postDate']));?></span>
                        </div>
                        <?php } elseif($blogctr==2) { ?>
                        <div class="single_blog_date color3">
                            <div class="single_blog_date_inner">
                                <h3><?php echo date("j", strtotime($post['postDate']));?></h3>
                                <span><?php echo date("M", strtotime($post['postDate']));?></span>
                                <span class="years"><?php echo date("Y", strtotime($post['postDate']));?></span>
                            </div>
                        </div>
                        <?php } elseif($blogctr==3) { ?>
                        <div class="single_blog_date color2">
                            <div class="single_blog_date_inner">
                                <h3><?php echo date("j", strtotime($post['postDate']));?></h3>
                                <span><?php echo date("M", strtotime($post['postDate']));?></span>
                                <span class="years"><?php echo date("Y", strtotime($post['postDate']));?></span>
                            </div>
                        </div>
                        <?php } ?>
                        <div class="single_blog_content pt-4 pl-4 pr-4">
                            <div class="blog_page_title pb-1">
                                <h3><a href="../blog/<?php echo $post['postSlug'];?>"><?php echo $post['postTitle'];?></a></h3>
                            </div>
                                <p class="blog_description">
                                    <?php echo $post['postDesc'];?>
                                </p>
                                <?php if($blogctr==1) { ?>
                                <a class="blog_page_button style_two pb-2 mb-4" href="../blog/<?php echo $post['postSlug'];?>">View Article <i class="fa fa-long-arrow-right"></i></a>
                                <?php } elseif($blogctr==2) { ?>
                                <a class="blog_page_button style_two pb-2 mb-4" href="../blog/<?php echo $post['postSlug'];?>">Read More <i class="fa fa-long-arrow-right"></i></a>
                                <?php } elseif($blogctr==3) { ?>
                                <a class="blog_page_button style_two pb-2 mb-4" href="../blog/<?php echo $post['postSlug'];?>">Read Article <i class="fa fa-long-arrow-right"></i></a>
                                <?php } ?>
                        </div>
                    </div>
                </div>
            <?php $blogctr++; endwhile; ?>

[rproffitt]

Remember I am reverse engineering here and will most likely misunderstand your code.

It looks like you are trying to collect the blog categories in line 4. I wonder if you need to add the DISTINCT word there?
https://www.w3schools.com/sql/sql_distinct.asp for reference.

I bet I misunderstood that but hey, the method I'd deploy would be to collect the categories (nod to DISTINCT) then use another query to find one per query (LIMIT?)

[ianhaneybs]

I'm trying to collect the actual blog article from the blog db table and the blog categories are in a separate db table, for example say the is a blog category called computers and there are 5 blog articles in that blog category and there is another blog category called laptops and there are 5 blog articles in that blog category and I want to get the very last blog article from the computers blog category and I also want to get the very last blog article from the laptops blog category so I end up with 2 displayed, one blog article from the computers blog category and one blog article from the laptops blog category

Hopefully that makes it clearer in what I need to do

[rproffitt]

I was unclear that I make no attempt to write your code. I will discuss methods that I use to get the queries I use in my systems.

I would get my first query working before I added more rows with the UNION. So far it sounds like you haven't refined that first step and are getting more than one row in the result. That is where two SQL concepts come into play. 1. The LIMIT and 2. If it's the wrong item such as not the very last, the ORDER can be used.

For your first attempt you get a single category working and then can extend it with the UNION or maybe a loop in PHP and further SQL queries to output the table or what you want.

[ianhaneybs]

Sorry yeah was not expecitng to write the code for me, def would not expect that and better to do it myself so I learn.

I'll focus on the first part then extend it and get the second part working and go from there

[le5691]

select distinct or use top