Re: Count Many Records
| From: | Kevin Murphy | Date: | Thu, 29 Jun 2006 20:54:18 +0000 |
| Subject: | Re: Count Many Records | ||
| References: | 1 2 3 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-42136@lists.php.net to get a copy of this message | ||
Thanks for your help, Chris. You certainly got me going in the right direction. In case anyone is interested, below is the completed solution, with design elements, turned into a function so I can easily call it several times.
function counter_maker ($database,$name,$link)
{
echo "<tr>";
echo "<td align=\"left\" valign=\"top\"><a href=\"$link\" target=\"content\"><b>$name</b></a></td>";
$query = "select status, count(id) AS count from $database where status in ('', 'h', 'p') group by status";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
$status_code = $row['status'];
if ($status_code == "")
{ $status_code = "n"; }
$count_array[$status_code] = $row['count'];
}
echo "<td align=\"center\" valign=\"top\"";
if (isset($count_array['n']))
{ echo " style=\"background-color: #F3CF45; color: #00599E; font-weight: bold;\">";
echo $count_array['n']; }
else
{ echo "> 0"; }
echo "</td>";
echo "<td align=\"center\" valign=\"top\"";
if (isset($count_array['h']))
{ echo " style=\"background-color: #00599E; color: #F3CF45; font-weight: bold;\">";
echo $count_array['h']; }
else
{ echo "> 0"; }
echo "</td>";
echo "<td align=\"center\" valign=\"top\">";
if (isset($count_array['p']))
{ echo $count_array['p']; }
else
{ echo "0"; }
echo "</td></tr>";
}
counter_maker ("data1","Form Name","link.php");
--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326
On Jun 28, 2006, at 5:39 PM, Chris wrote:
> Kevin Murphy wrote:
>> Actually the design is in the code below.... I need to display the
>> counts from all of those queries in a grid. Basically, its just a
>> summary of a bunch of information. Another way to look at it would
>> be a several lines that say something like this:
>> You have $data1_count NEW records in data1,
>> You have $data1p_count PROCESSED records in data1,
>> etc.
>
> Instead of
>
> $data1_query = "select id from data1 WHERE status = ''";
> $data1_results = mysql_query($data1_query);
> $data1_count = mysql_num_rows($data1_results);
>
> $data1p_query = "select id from data1 WHERE status = 'p'";
> $data1p_results = mysql_query($data1p_query);
> $data1p_count = mysql_num_rows($data1p_results);
>
> $data1h_query = "select id from data1 WHERE status = 'h'";
> $data1h_results = mysql_query($data1h_query);
> $data1h_count = mysql_num_rows($data1h_results);
>
>
> You could do it all in one query:
>
> $query = "select status, count(id) AS count from data1 where status
> in ('', 'p', 'h') group by status";
> $result = mysql_query($query);
> while($row = mysql_fetch_assoc($result)) {
> echo "status: " . $row['status'] . "<br/>";
> echo "count: " . $row['count'] . "<br/>";
> }
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/