Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts

    how to do this in php

    Hi Friends,

    Table 1

    catid category
    1 fruits
    2 meats


    table 2
    foodid catid food
    1 1 banna
    2 1 apple
    3 1 tomato
    4 2 beef
    5 1 grape
    6 2 chicken


    now i want to count the number of food under each category in SINGLE select, is it possible to do? which means to get " Category fruits have 4 fruits" and "category meat have 2 meats".

    My currently my way is to select the category, then stored as array, then loop through the array, then select again from the food, i think this is not efficient at all.
    flying dagger

  • #2
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    Use the WHERE clause in the mysql query when you count the rows in table2.

    PHP Code:
    WHERE catid 

    -------------
    Leonard Whistler

  • #3
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    what do you mean, this is my way of doing

    $sql=mysql_query("SELECT * FROM category ORDER BY catid DESC");
    $sum=array();
    while($r=mysql_fetch_array($sql)){
    $sql2=mysql_query("SELECT * FROM food WHERE catid=".$r['catid']);
    $total=mysql_num_rows($sql2);
    $sum[$r['category']]=$total;
    }

    this is my code, i want to short cut in to one query, possible?
    flying dagger

  • #4
    Regular Coder
    Join Date
    Dec 2007
    Posts
    145
    Thanks
    5
    Thanked 5 Times in 5 Posts
    PHP Code:
    <?php
    $query 
    mysql_query("SELECT `category.catid`,`category.category`,`COUNT(foodid)` FROM `food`, `category` WHERE `food.catid` = `category.catid`");
    while(
    $row mysql_fetch_array($query)){
    echo 
    "Category " $row['category'] . " has " $row['COUNT(foodid)'] . " fruits.";
    }
    ?>
    Erm...something like that. 0.0

    Good luck!

  • #5
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts
    cool, damn, i should attend the Microsoft SQL lesson in my school
    flying dagger


  •  

    Posting Permissions

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