SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Searching for more than one similar and then count

    I have a table with people (tbl_people).
    Containing name, address, city, country.

    I would like to search this mysql db for all people who lives in either france, germany or belgium.
    Then I would like the code to count all the hits. If it finds 20 people in france, 30 in germany and then 10 in belgium, I would like it to tell me it found 60 people.

    I'm not sure how to do this, but it's probably very simple.
    What is the shortest way to do it (and quickest)?

    When I try this type of code I always tend to do it a very, very stupid way and I end up with a lot of lines in my code.

    But with all people out here, there is usually a very quick and simple answer to my question.

  2. #2
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Oh, forgot to say. I think the countries will be selected from an array from checkboxes in a form.
    This is all in my head right now... just planning and try to figure out what to do...

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    My first guess is something like this:

    PHP Code:
    SELECT COUNT(*) FROM tbl_people WHERE country='france' OR country='germany' OR country='belgium' 
    But I guess it could be done in a better way?
    Especially if the user is selecting one or maybe five countries from the checkboxes.
    The above example wouldn't work...

  4. #4
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    for the query you should replace all the or for

    Code MySQL:
    SELECT COUNT(*) FROM tbl_people WHERE country IN('france','germany','belgium')

    one more thing is that what you are doing with count, will give you the total of results not separated by each case, if you do only need the count then execute three queries, one for each country.
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Well, I just need the total here. But whatif the user select more or less than three?
    Is there a simple way to add an array to this?
    If I have a form with checkboxes from 1–20 or whatever it might end up with.

  6. #6
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    count will not return you an array but just a total of the results, there is no option as far as I know, but you could always read the documentation to make sure.

    As far as getting the records you can query the database for them instead of the count and then using a loop count them as you may also want to do something with the actual data, canīt say as you did not mention what you are trying to do.

    And also as I mentioned, if you do need only the count then you can run a query for each of the countries in the list

    Code PHP:
    $dbh = new PDO('mysql:dbname=youdatabasename;host=yourdatabasehostname.com', $user, $password);
    $query="SELECT COUNT(*) FROM tbl_people WHERE country=:country";
    $sth = $dbh->prepare($query);
    foreach($countries as $country){
        $sth->execute(array(':country' => $country));
        $rows = $sth->fetch(PDO::FETCH_NUM);
        echo "$country has {$rows[0]} people living in it";//This is the number of rows in that country
    }
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  7. #7
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Oh, some new things I never seen before. "prepare" - have never used that one. I will check this out next time I'm in front of the right computer.
    Actually what I will do when using this is to count the number of users in the selected countries. If there are more than, let's say, 50 from the selected ones, then the user will be passed on to a new area.
    If there are less than 50, then the user have to pick more countries or other countries.

    So, actually I just want it to count the total of the selected countries.
    With your example I guess I will get that result from $rows(0), right?

  8. #8
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    right
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  9. #9
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    First, I wouldn't run the query multiple times, just run it the once. If you implode your array you can build a list (I don't think you can bind an array otherwise). Then add:
    Code:
    GROUP BY country
    to your query. You will get a row per country. You can add the counts in PHP as you process each line, or you can use MySQL's WITH ROLLUP to get a total in the final row returned

  10. #10
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The last one from Antnee seems like a clever solution, but I just didn't understand how to do that one.

  11. #11
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Antnee View Post
    First, I wouldn't run the query multiple times, just run it the once. If you implode your array you can build a list (I don't think you can bind an array otherwise). Then add:
    Code:
    GROUP BY country
    to your query. You will get a row per country. You can add the counts in PHP as you process each line, or you can use MySQL's WITH ROLLUP to get a total in the final row returned
    Yes you are right, I guess I did not think of doing

    Code MySQL:
    SELECT COUNT(*) FROM tbl_people WHERE country IN(:country) GROUP BY country
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  12. #12
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I don't think that works. I'm fairly sure you can't bind an array.

    Sorry, I meant to get you an example of how to do it but haven't had the time yet. I'll get it to you soon, assuming nobody beats me to it

  13. #13
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I can wait... ;-)

  14. #14
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Apologies for being really tardy with my example. As you'll see, it's a bit longer than @tlacaelelrl 's but I based it on his code. I'll break it down to explain what I did as I go.

    Code PHP:
    <?php
    $dbUser = 'MySQL User';
    $dbPass = 'MySQL User Password';
    $dbHost = 'HostOrIp';
    $dbName = 'DatabaseName';
     
     
    $dbh = new PDO("mysql:dbname=$dbName;host=$dbHost", $dbUser, $dbPass);
     
     
    $countries = array(
        'United Kingdom',
        'United States of America',
        'France',
        'Brazil'
    );
     
     
    $conditions = array();
    $params     = array();
    for ($i=0; $i<count($countries); $i++){
        $paramKey           = ':country' . $i;
        $conditions[]       = $paramKey;
        $params[$paramKey]  = $countries[$i];
    }
     
     
    $condition = count($conditions)
            ? 'WHERE country IN(' . implode(',', $conditions) . ')'
            : '';
     
     
    $query = "
        SELECT IFNULL(country, 'Total') AS country
             , COUNT(*) AS count
          FROM tbl_people
          " . $condition . "
      GROUP BY country
          WITH ROLLUP";
    $sth = $dbh->prepare($query);
     
     
    $sth->execute($params);
     
     
    $rows = $sth->fetchAll(PDO::FETCH_ASSOC);
    var_dump($rows);

    OK, so here's what everything does:
    Code PHP:
    $dbUser = 'MySQL User';
    $dbPass = 'MySQL User Password';
    $dbHost = 'HostOrIp';
    $dbName = 'DatabaseName';
     
     
    $dbh = new PDO("mysql:dbname=$dbName;host=$dbHost", $dbUser, $dbPass);
    This simply creates a PHP Data Object that we'll use to connect to the MySQL database. Change the variables to suit your needs

    Code PHP:
    $countries = array(
        'United Kingdom',
        'United States of America',
        'France',
        'Brazil'
    );
    Simply a list of countries that we're going to search on. Note that it's bad practice to store countries like this. Really you should use either a lookup value and utilise foreign keys (ie UK = 0, USA = 1, France = 2) or you should use ISO country codes for consistency. But this will do for the example.

    OK, so you can't bind an array to a single parameter, so we can't do this:

    Code MySQL:
    SELECT COUNT(*) FROM tbl_people WHERE country IN(:country) GROUP BY country
    and then
    Code PHP:
    $sth->execute(array('country' => $countries));
    That will throw an error complaining about an array-to-string conversion. So we've worked around it with this:
    Code PHP:
    $conditions = array();
    $params     = array();
    for ($i=0; $i<count($countries); $i++){
        $paramKey           = ':country' . $i;
        $conditions[]       = $paramKey;
        $params[$paramKey]  = $countries[$i];
    }
    What this does is build an array of parameter names, and their associated values in another correctly-keyed array that can be passed straight to the PDO execute function. So your SQL condition will end up looking something like this:
    Code MySQL:
    WHERE country IN(:country0,:country1,:country2,:country3)
    This is all done automagically. The $params array will then hold the values for each. You'll see why I put them all in the $conditions array in the next piece of code.

    Code PHP:
    $condition = count($conditions)
            ? 'WHERE country IN(' . implode(',', $conditions) . ')'
            : '';
    I put this in purely to support cases where no country is provided, in which case it will return all countries. Not necessary, but wanted to be a but more thorough. In here you can see that I used implode() on the $conditions array. I like to use this as it will correctly return each value with a separator, in this case a comma, which is the exact syntax that we need. There are other ways to do this, but I find this way the most robust, and it's fast too.

    Code PHP:
    $query = "
        SELECT IFNULL(country, 'Total') AS country
             , COUNT(*) AS count
          FROM tbl_people
          " . $condition . "
      GROUP BY country
          WITH ROLLUP";
     
     
    $sth = $dbh->prepare($query);
    So here's your SQL, fully built. Note that $condition is included in here and will only have a value if you passed in any countries, otherwise it'll be empty. Look at the last couple of lines as this is where the real differences are: Firstly, we "GROUP BY country", which means that you'll get a single row per country. Simple. Then the last line, where we apply the modifier "WITH ROLLUP". What this does is to create a running total for each grouping level. Since we've only used one (country) you will get a single total at the end. The way that MySQL returns this row will be with a NULL value for the country name. You can intercept and manage with PHP if you like, but instead I've used the IFNULL() MySQL function on the first line, if you notice. Basically, to replace a NULL value with the text "Total". You don't need to do this if you don't want to.

    Code PHP:
    $sth->execute($params);
     
    $rows = $sth->fetchAll(PDO::FETCH_ASSOC);
    var_dump($rows);
    Lastly, we execute the prepared statement, passing in $params as built above. PDO will correctly substitute each :country* parameter with a value from this array. We then call the PDO fetchAll() function to get all rows back at once (you may not want to do this - you may want to return a row at a time, depending on memory considerations and the size of your result set). I've just dumped the result via var_dump() for simplicity, but you can do with it as you please.

    Any questions?

  15. #15
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Oh, a lot of code for this. I will check it out next time. :-)

  16. #16
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Not a lot of code, I've just broke it down to explain after the original post. Just look at the first piece and that's all you need

    Specifically, the bit you need is the simple SQL code


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
  •