SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    May 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do i execute different query using loop or whatever?

    Hi guys,I just started learning PHP. I'm trying to retrieve the number of counts a value have.
    Example my I have a field called "find_out_about_us" which stores different value like friends,social_media,search_engine. So lets say i got 5 rows of friends found in that column so my sql query will return me a value of 5.

    Now my problem is... i need to count the value for different values. like i got friends,social_media,search_engine and a lot more.. How do i automate this process?? Instead of keep manually editing the where clause value?

    Here is my query:
    Code:
    $count_query = "SELECT COUNT(`find_out_about_us`) AS counted 
                            FROM polls 
                           WHERE `find_out_about_us` =  'friends' "; 
                  
            $result = mysqli_query($link, $count_query) or die(mysqli_error($link)); 
            $count_result = mysqli_fetch_array($result);

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT find_out_about_us
         , COUNT(*) AS counted 
      FROM polls 
    GROUP
        BY find_out_about_us
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT find_out_about_us
         , COUNT(*) AS counted 
      FROM polls 
    GROUP
        BY find_out_about_us

    OMG! i all the way think is php problem.. why i never thought of that.. haiz. A big thanks to you! Life saver!

  4. #4
    SitePoint Member
    Join Date
    May 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    erm sorry,i faced problem again..

    When using the query you gave, values which never appear in database before will not be counted right.. but i need it to be counted also.
    For example.. i have a value "search engine" in my radio button if no one clicks it, "search engine" wont be appear in the database and wont be grouped.
    How do i make it show:
    search engine : 0 polls

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    #1: Welcome to SitePoint Forums.

    #2: There's a database way to do it, and a PHP way to do it.
    The database way involves creating a table of your potential answers and then joining the two tables together.
    The PHP way is a little cleaner imo for your situation.
    PHP Code:
    $counts = array('friends' => 0,'social_media' => ,'search_engine' => 0); //add entries as necessary.
    $count_query "SELECT find_out_about_us, COUNT(`find_out_about_us`) AS counted 
                            FROM polls GROUP BY find_out_about_us ORDER BY find_out_about_us"

                  
            
    $result mysqli_query($link$count_query) or die(mysqli_error($link)); 
            while(
    $row mysqli_fetch_assoc($result)) {
                
    $counts[$row['find_out_about_us']] = $row['counted'];
            }

    Then you can foreach your $counts array to get the results.
    (PS: Added an ORDER BY because you should always have one :P)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  6. #6
    SitePoint Member
    Join Date
    May 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    #1: Welcome to SitePoint Forums.

    #2: There's a database way to do it, and a PHP way to do it.
    The database way involves creating a table of your potential answers and then joining the two tables together.
    The PHP way is a little cleaner imo for your situation.
    PHP Code:
    $counts = array('friends' => 0,'social_media' => ,'search_engine' => 0); //add entries as necessary.
    $count_query "SELECT find_out_about_us, COUNT(`find_out_about_us`) AS counted 
                            FROM polls GROUP BY find_out_about_us ORDER BY find_out_about_us"

                  
            
    $result mysqli_query($link$count_query) or die(mysqli_error($link)); 
            while(
    $row mysqli_fetch_assoc($result)) {
                
    $counts[$row['find_out_about_us']] = $row['counted'];
            }

    Then you can foreach your $counts array to get the results.
    (PS: Added an ORDER BY because you should always have one :P)
    Thanks

    I am getting a error:
    Undefined index: 'find_out_about_us'

    The error occur at this line:
    $counts[$row['find_out_about_us']] = $row['counted'];

    Hmm just to summaries see if i am on the right track.. your logic for this is:
    1.You list the value i am searching for and give a count value of 0
    2. You loop through the database to find if there is the value and it will add the the count value.
    3. i need to use foreach loop to print out all the updated values?

    Lastly, the above code u gave me is just counting for the value of friends right? If i am correct.. how do i count for other value like search_engine etc without adding much length to my code?

    Thanks for the help! I learning a lot from you guys.

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    have you updated your query correctly?

    1. I prime the results by putting 0's in for default values.
    2. I pull all results from the database, grouped so that it counts the correct amount. Then write out the values into the primed array, so any values that were actually returned by the query get set. Any that arnt are left at 0.
    3. You would then foreach the array to output the results.

    EDIT: Not sure where the extra } came from, it's unnecessary.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  8. #8
    SitePoint Member
    Join Date
    May 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks! It work now. Sorry i actually missed out the group by statement.. lol.

    Will continue exploring.. Thanks for big help, really appreciate 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
  •