count all records & records meeting certain criteria?

mysql count all records & records meeting certain criteria?

[propertyireland2]

Hi all,
I am trying to find the no. of records in a table that macth a certain name.
Below is the code that returns the total active properties, but I also need to return the total active Dublin, Mayo, Cork and so on...(32 in total).
Can anyone tellme how this is done without creating 32 seperate mysql queries?
property_location is where Mayo, Cork, Dublin and so on is stored.
Any help is greatly appreciated.
Thanks.

$ireland_total_count_query = mysql_query("select property_location, count(*) from " . TABLE_PROPERTY . " where property_status = '1' GROUP BY 'property_location'");
$ireland_total_count = mysql_fetch_array($ireland_total_count_query);

$ireland_total_count_is += $ireland_total_count['total'];

[SeanW]

The query you have should work, you just have to iterate over each row returned (one for each property_location)

You may also want Count(*) AS total to ensure the column is named correctly in your mysql_fetch_array.

Sean

[propertyireland2]

Thanks for the reply, but that is where I am stuck.

"you just have to iterate over each row returned (one for each property_location)"

any idea how i do this?

[Habtom]

You can use PHP to help you on that:

$property_location = array("Dublin", "Cork", . . . );

$count_location = count($property_location);

for ($i=0; $i < $count_location; $i++) {
$ireland_total_count_query = mysql_query("select count(*) as total from " . TABLE_PROPERTY . " where property_status = '1' GROUP BY ". $property_location[$i] ."");
$ireland_total_count = mysql_fetch_array($ireland_total_count_query);

$ireland_total_count_is += $ireland_total_count['total'];
}

Hope this helps

[propertyireland2]

thanks Habtom, but doesn't this way involve doing 32 db queries? - 1 for each county in the array.

if possible, i only want to do 1 query and asign a count variable to each county name returned.

any other ideas?

[Habtom]

If you just wanted the last figure for all the cities in one:

$ireland_total_count_query = mysql_query("SELECT * from " . TABLE_PROPERTY . " where property_status = '1' AND (location = 'Dublin' OR location = 'london' OR . . . )

echo mysql_num_rows($ireland_total_count_query);

If not we need to roll up our sleeves for a bigger query (if it can be done :) )

Habtom

[Woz]

If you think you will need to retrieve this information on a regular basis, perhaps it would be better to create a view inside your mySQL database and then query the results filtering only those locations you need at the moment.

Something like

"select Count(propertyID`) AS PropertyCount, PropertyLocation from PropertyTable group by PropertyLocation having property_status = 1" (we will call this viewPropertyLocationCount)

You can then query the view with something like

"Select PropertyCount, PropertyLocation from viewPropertyLocationCount where PropertyLocation in (list,of,locations)"

Then parse and retrieve data.

Onya
Woz

[propertyireland2]

ok, thanks again for all the replies...
maybe i need to explain better what i actually need.

i need the total count of all active properties in the database
i also need the total of all active CORK properties in the database
i also need the total of all active DUBLIN properties in the database, and so on for the 32 counties.

Do you know if this can be done in one query?
the query below returns my first objective, i.e all active properties.

$ireland_total_count_query = mysql_query("select property_location, count(*) as total from " . TABLE_PROPERTY . " where property_status = '1' GROUP BY 'property_location'");
$ireland_total_count = mysql_fetch_array($ireland_total_count_query);

$ireland_total_count_is += $ireland_total_count['total'];

now, can i use anything like:
$mayo_total_count_is += $ireland_total_count['total(Mayo)'];
to return the Mayo count from that query?
it's driving me mad, but i presume it can be done somehow? - what does the GROUP BY 'property_location' do, can these groups be counted?

thanks again for any help.

[Habtom]

propertyireland2, it seems Woz's idea of creating temporarily tables on the fly is a way forward.

[edit] He suggested view, both applicable I think [/edit]

[edited by: Habtom at 10:15 am (utc) on Oct. 25, 2007]

[SeanW]

Guys, unless I'm missing something here, this is a straightforward aggregate query. There's no need for views, or for HAVING (which is processed after the rows are aggregated, so don't use it unless you plan on filtering on an aggregated column)

[perl]
$q = mysql_query("SELECT property_location, count(*) AS count
FROM " . TABLE_PROPERTY
. " WHERE property_status = '1'
GROUP BY 'property_location'");

while ($row = mysql_fetch_array($q)) {
$the_data[$row['property_location']] = $row['count'];
}

var_dump($the_data); // show the data structure
[/perl]

You'll end up with an array, with the key being the name of the property, and the value being the number of rows.

Sean

[propertyireland2]

Thanks Sean,
I knew there had to be an easy way...
Using your query, is it possible to display total mayo properties, total dublin properties and so on? & asign them to a variable for use later on that page?

[SeanW]

Yes, you'll have $thedata['dublin'] with the number of rows for dublin. Please, try the code out and see, the var_dump will show you everything.

Sean

[propertyireland2]

Hi Sean,
thanks for your help with this.
I did try it out and had tried echo $thedata['dublin']; and so on but still no joy...
var_dump($the_data) just produces:

array(1) { ["Kerry"]=> string(4) "1436" }

i don't know why it is only showing kerry and the 1436 is the total of all counties not just kerry.
playing with the sql query now to see if i can spot anything, but if you can see why i'm getting the above result it would be appreciated.

[Woz]

Re Having

SeanW you are correct, my mistake, correct query herewith.

"SELECT Count(propertyID`) AS PropertyCount, PropertyLocation FROM PropertyTable WHERE property_status = 1 Group by PropertyLocation"

re "no need for views"

Well, that depends. It is always best to use the correct tools for the task whenever possible and/or practical. Databases (mySQL, Access, MSSQL, whatever) are designed to store and manipulate Data. Scripting languages (PHP, ASP, whatever) are designed to retrieve/send/manipulate information. They can be used to manipulate data but that does incur an increased server overhead.

If propertyireland2's need to aggregate and count the data is a one-off instance, then setting up a query within PHP is perhaps warranted. However, if this is to be done on a recurring basis, even once a day, then setting up a view would be far preferable.

Onya
Woz

[propertyireland2]

Woz,
the query needs to be run on each page load.
I am still stuck with the only result being produced as:

{ ["Kerry"]=> string(4) "1436" }

using:
$q = mysql_query("SELECT property_location, count(*) AS count
FROM " . TABLE_PROPERTY
. " WHERE property_status = '1'
GROUP BY 'property_location'");

while ($row = mysql_fetch_array($q)) {
$the_data[$row['property_location']] = $row['count'];
}

var_dump($the_data); // show the data structure

[Habtom]

Try the following:

$x = 0;
while ($row = mysql_fetch_array($q)) {
$the_data[$row['property_location']][$x] = $row['count'];
$x++;
}

[propertyireland2]

thanks Habtom, but still no joy...
i get the following error with it.

Cannot use a scalar value as an array in /filename....

[Habtom]

Sry, I can't test and give you a solid answer now, but I hope this works for you:

Replace this:
$the_data[$row['property_location']][$x] = $row['count'];

With this:
$property = $row['property_location'];
$the_data[$property][$x] = $row['count'];

[SeanW]

I'll suggest changing your loop:

while ($row = mysql_fetch_array($q)) {
$the_data[$row['property_location']] = $row['count'];
var_dump($the_data); // am I getting the data?
}

Are you **sure** the query works outside of php? Try from the command line. You're getting one row, which tells me there's only one row to be seen, or your query isn't returning the data you think it should.

Sean

[propertyireland2]

Hi Habtom, still no joy with that, but I just figured it out, thanks to all those that helped out here.

If anyone else is stuck with a similar type of query, here is what worked for me.

$query = "SELECT property_location, COUNT(property_id) FROM " . TABLE_PROPERTY . " where property_status = '1' GROUP BY property_location";
$result = mysql_query($query);

while($row = mysql_fetch_array($result)){
$the_data[$row['property_location']] = $row['COUNT(property_id)'];
}
echo $the_data['Dublin']; //show dublin data
var_dump($the_data); // show the data structure

Thanks again all.