SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    619
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    show item from each category

    hi all

    i have 10-12 CATEGORIES and i want to show 1 most recent added product of each CATEGORY on my homepage

    There is "category_id" in product_table

    Code:
    <?
    $qry = "SELECT * FROM product_table where category_id=1 order by product_id desc LIMIT 0,1";
    $result = mysql_query($qry);
    while($row = mysql_fetch_array($result))
    {
    echo $row['product_id'] . "--". $row['product_name']. "<br>";
    }
    ?>
    So should i repeat this code 10 times and replace category id.

    Or is there any other shorter solution ??

    I mean can this be done in one single query ???

    vineet

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could try.....

    Code:
    SELECT DISTINCT(category_id), product_id, product_name FROM product_table order by product_id desc
    *Completely untested!
    Richard
    Resell SSL Certificates - API / WHMCS / HostBill / ClientExec
    ServerTastic - RapidSSL, Geotrust, Thawte, Symantec, SmarterTools and more

  3. #3
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The book I am reading right now would help you out greatly. It is called PHP & Mysql novice to ninja.

    First of all, you don't have to repeat the code 10 times. You need to actually use something called a placeholder. Also, you might want to configure your mysql database to have a hidden category and therefore you can hide all of the products that you don't want to show on any given page.

    Thirdly, from what I gather your code could be better in the fact that it is much more safer and secure if you use prepare statements as opposed to what you are using. Finally, it is best practices that anytime you query the database to wrap your code in TRY/CATCH statements.

    Here is an example:

    PHP Code:
    try
      {
        
    $sql 'SELECT id, name FROM category where id = :id'
        
    $s $pdo->prepare($sql);
        
    $s->bindValue(:id$_POST['id'])
        
    $s->execute();
      }
      catch (
    PDOException $e)
      {
        
    $error 'Error removing jokes from category.';
        include 
    'error.html.php';
        exit();
      } 
    As I see it, your most pressing concern is database design and NOT an sql statement. You must design flexibility into it.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •