How do i execute different query using loop or whatever?

PHP
1

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:

$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 
SELECT find_out_about_us
     , COUNT(*) AS counted 
  FROM polls 
GROUP
    BY find_out_about_us
3

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

4

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

#1: Welcome to SitePoint Forums. :slight_smile:

#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.


$counts = array('friends' => 0,'social_media' => 0 ,'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)

6

Thanks :slight_smile:

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

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.

8

thanks! It work now. Sorry i actually missed out the group by statement… lol.

Will continue exploring… Thanks for big help, really appreciate it :slight_smile: