SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  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
    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
  •