I need to run a quick count on a database table 99 times and therefore wanted to get as efficient a way of doing this as possible.
My current code is as follows:
<?php
require_once "mysql_connect.php";
function mysql_evaluate($query, $default_value="undefined") {
$result = mysql_query($query);
if (mysql_num_rows($result)==0) {
return $default_value;
} else {
return mysql_result($result,0);
}
}
$postcodeCount = mysql_evaluate("SELECT COUNT(*) FROM postcodesCompaniesAssigned");
echo "postcodeCount = " . $postcodeCount;
?>
Anyone recommend a more efficient way of getting a quick tally?
As a bit of background, basically I have a table that stores company names against postcodes and I need to limit each postcode to only 5 companies.
So when signing up for new postcodes, I display one checkbox for each of the 99 postcode prefixes for a postal area. E.g for the postal area TA, there will be:
TA0 - TA99
If there are 5 companies already signed up for a particular postcode e.g TA21 then I want to disable this checkbox.
Hence when I am displaying the 99 checkboxes, for each checkbox I am doing a query to check how many comapnies are assigned to it.
Thanks
Paul
Hi Anthony
I did just do the following test for BT98 which has 2 companies assigned and I am getting the value 1 returned:
<?php
require_once "mysql_connect.php";
$postcode = 'BT';
$sql = sprintf(
"SELECT
postcode
, COUNT(*) AS numberOfCompanies
FROM
postcodesCompaniesAssigned
WHERE
SUBSTRING(postcode, 1, %d) = '%s'
GROUP BY
postcode
",
strlen($postcode),
mysql_real_escape_string($postcode)
);
$res = mysql_query($sql);
if(true === is_resource($res)){
$record = mysql_fetch_assoc($res);
echo $record['numberOfCompanies']; #98
}
?>
You’re welcome buddy, and thanks! 
<?php
$postcode = 'TA';
$sql = sprintf(
"SELECT
postcode
, COUNT(*) AS numberOfCompanies
FROM
postcodesCompaniesAssigned
WHERE
SUBSTRING(postcode, 1, %d) = '%s'
GROUP BY
postcode
",
strlen($postcode),
mysql_real_escape_string($postcode)
);
$res = mysql_query($sql);
if(true === is_resource($res)){
$record = mysql_fetch_assoc($res);
echo $record['numberOfCompanies']; #45, or 56, or something like that.
}
?>
Thanks Anthony - I can see why you are the Mentor:)
Sorry for being dumb - but using the query in your last post, how can I then retrieve the number of results for say “TA21” from the result of the query assuming that the $postcode variable was equal to “TA”?
Thanks
Paul
You could probably simplify it a little…
$sql = sprintf(
"SELECT
postcode
, COUNT(*) AS numberOfCompanies
FROM
postcodesCompaniesAssigned
WHERE
SUBSTRING(postcode, 1, %d) = '%s'
GROUP BY
postcode
",
strlen($postcode),
mysql_real_escape_string($postcode)
);
Sorry posted before seen you had replied. Yes that was the exact code I tried in post 10 - however I did a bit of reading and figured that what I had put must be wrong. Hence my attempted update:)
Ah brill!
Oh, the # denotes the following text on that line is a comment. 
And a slight update to deal with when there are no records for a particular postcode
$postcodeCount = $data['BT98'];
if (!$postcodeCount > 0) {
$postcodeCount = 0;
}
echo $postcodeCount;
Ah thanks Anthony - I have always used // for comments and didn’t realise a hash was also a comment:)
Just in case your wanting a function Paul.
<?php
function get_postcode_metrics($postcodes = array()){
$conditional = null;
if(0 < count($postcodes)){
$conditional = sprintf(
"WHERE code IN ('%s')",
implode(
"','",
array_map(
'mysql_escape_string',
$postcodes
)
)
);
}
$res = mysql_query(sprintf(
'SELECT code, count(*) AS num FROM table %s GROUP BY code',
$conditional
));
$data = array();
if(true === is_resource($res)){
while($record = mysql_fetch_assoc($res)){
$data[$record['code']] = $record['num'];
}
}
return $data;
}
?>
<?php
$data = get_postcode_metrics(); #Gets *all* postcode count
?>
<?php
$data = get_postcode_metrics(array('TA1', 'TA2')); #Gets count for specified postcodes
?>
Thank you guys - that looks much more efficient indeed!
I would only ever be doing one postal area at a time so would the following be right?
Note my code caters for the fact that some postal areas are only one character and some are two.
So I would need a result set that enables me to get the count for each postcode within my loop from 0 - 99
For example when I am displaying the checkbox for TA21 I need to get the count of how many companies are assigned to TA21
for ($counter = 0; $counter <= 99; $counter++) {
$currentPostcode = $postalAreaId.$counter;
}
$postalAreaId = trim($_GET['postalAreaId']); // e.g TA
if (strlen($postalAreaId) == 1) {
$query="SELECT
postcode,
COUNT(*) AS numberOfCompanies
FROM postcodesCompaniesAssigned
WHERE SUBSTRING(postcode, 1, 1) = '$postalAreaId'
GROUP BY postcode";
} else if (strlen($postalAreaId) == 2) {
$query="SELECT
postcode,
COUNT(*) AS numberOfCompanies
FROM postcodesCompaniesAssigned
WHERE SUBSTRING(postcode, 1, 2) = '$postalAreaId'
GROUP BY postcode";
}
Thanks
Paul
Thanks Anthony for the swift reply
Is that #45 part of the code
i.e if I wanted to get the number of comapnies for TA45 would that line of code be the following?
echo $record['numberOfCompanies']; #45
Sorry, I haven’t come across syntax like that before
Thanks
Paul
SELECT
postcode
, COUNT(*) AS numberOfCompanies
FROM postcodesCompaniesAssigned
GROUP BY postcode
This gives you the number of companies assigned to each postcode, in 1 query
slap
SELECT code, COUNT(*) AS num FROM table WHERE code IN ('TA1', 'TA2') GROUP BY code
code | num
-----+-----
TA1 | 5
TA2 | 0
Hey think I have sussed it by grabbing some code from one of your earlier posts in this thread.
Does this look OK?
if(true === is_resource($res)){
$data = array();
while($record = mysql_fetch_assoc($res)){
$data[$record['postcode']] = $record['numberOfCompanies'];
}
echo $data['BT98'];
}
Hi Paul,
Is that the exact code you’re using? Your not checking with mysql_num_rows are you?