CODEWITHSUNDEEP
phpmysqlcountrows
Norse
Norse

Reputation: 5757

Counting number of rows with a parameter

I have this query:

$queryf = "SELECT * FROM classifieds 
WHERE city IN ('$cities') AND state IN ('$states') 
ORDER BY FIELD (city, '$cities'), datetime DESC";

$cities is an array of nearby cities within 5 miles. Same with states.

Basically, I want to search my user information table that has user-inputted information (like a craigslist/classifieds type table), and COUNT the number of rows PER CITY. So that it's possible to echo something like:

10 results found within 5 miles:
Dublin, CA (8 records)
Hayward, CA (2 records)

Does anyone know how I'd be able to track/return the amount of rows where the city is identical?

Upvotes: 1

Views: 287

Answers (3)

cmbuckley
cmbuckley

Reputation: 42458

Try using GROUP BY:

SELECT city, COUNT(1) AS count FROM classifieds 
WHERE city IN ('$cities') AND state IN ('$states') 
GROUP BY city

Upvotes: 0

Tudor Constantin
Tudor Constantin

Reputation: 26861

A GROUP BY, together with COUNT() should give you what you need:

$queryf = "SELECT *, count(classifieds.id) AS num_cities FROM classifieds 
WHERE city IN ('$cities') AND state IN ('$states') 
GROUP BY city
ORDER BY FIELD (city, '$cities'), datetime DESC";

Upvotes: 0

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385104

Yes.

MySQL has a whole bunch of functions and keywords for this sort of aggregation.

Read the documentation for your SELECT statement, especially when it talks about the GROUP BY clause.

Upvotes: 1

Related Questions