MySql count

[Akhenaten]

The following snippet (for whatever reason) returns no value for the
count. Suggestions?
$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client) {
$count = mysql_query('SELECT COUNT(*) from table where columnA =
$client');
echo "$client has $count";
echo '<br>';
}

[Christoph Burschka]

Akhenaten wrote:

The following snippet (for whatever reason) returns no value for the
count. Suggestions?
$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client) {
$count = mysql_query('SELECT COUNT(*) from table where columnA =
$client');
echo "$client has $count";
echo '<br>';
}

1. You can't pass variables within single quotes.

Instead of '... $client' you either need "... $client" or '... '.$client

2. Strings in MySQL queries need to be surrounded by quotes (either single or
double).

This will work:

"SELECT COUNT(*) from table where columnA = '$client'"

as will this:

'SELECT COUNT(*) from table where columnA = "'.$client.'"'

--
Christoph Burschka

[Michael Fesser]

..oO(Christoph Burschka)

>Akhenaten wrote:

>The following snippet (for whatever reason) returns no value for the
count. Suggestions?
[...]

1. You can't pass variables within single quotes.
[...]

2. Strings in MySQL queries need to be surrounded by quotes (either single or
double).
[...]

3. mysql_query() just returns a resource ID. You have to use one of the
mysql_fetch_* functions to get the actual results.

Micha

[Andy Hassall]

On Mon, 20 Nov 2006 18:48:39 +0100, Michael Fesser <ne*****@gmx.dewrote:

>.oO(Christoph Burschka)

>>Akhenaten wrote:

>>The following snippet (for whatever reason) returns no value for the
count. Suggestions?
[...]

1. You can't pass variables within single quotes.
[...]

2. Strings in MySQL queries need to be surrounded by quotes (either single or
double).
[...]

3. mysql_query() just returns a resource ID. You have to use one of the
mysql_fetch_* functions to get the actual results.

4. Always check for errors, as the database can tell you what went wrong via
mysql_error().

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Akhenaten]

Actually fixed it using the following:

$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client) {
$query = mysql_query("SELECT * from table where columnA = '$client' ");
$num_rows = mysql_num_rows($query);
echo "$client has $num_rows";
echo '<br>';
Unsure as why but for whatever reason I simply can't get a value using
count <pounding head on keyboard>.

On Nov 20, 11:32 am, Christoph Burschka
<[e-mail]:

Akhenaten wrote:

The following snippet (for whatever reason) returns no value for the
count. Suggestions?

$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client) {
$count = mysql_query('SELECT COUNT(*) from table where columnA =
$client');
echo "$client has $count";
echo '<br>';
}1. You can't pass variables within single quotes.

Instead of '... $client' you either need "... $client" or '... '.$client

2. Strings in MySQL queries need to be surrounded by quotes (either single or
double).

This will work:

"SELECT COUNT(*) from table where columnA = '$client'"

as will this:

'SELECT COUNT(*) from table where columnA = "'.$client.'"'

--
Christoph Burschka

[Sanders Kaufman]

Akhenaten wrote:

The following snippet (for whatever reason) returns no value for the
count. Suggestions?
$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client) {
$count = mysql_query('SELECT COUNT(*) from table where columnA =
$client');
echo "$client has $count";
echo '<br>';
}

I see no single-quotes around $client in the SELECT
query.
That would make "$client" seem to be a field or
variable name, instead of a string value.

That probably means you're querying for where
columnA = the value of a variable named "$client"
instead of what you meant to query for which was
where columnA = the string in "$client".

I do that so much - it's one of the first errors I
check for anymore!

[Michael Fesser]

..oO(Akhenaten)

>Actually fixed it using the following:

$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client) {
$query = mysql_query("SELECT * from table where columnA = '$client' ");
$num_rows = mysql_num_rows($query);
echo "$client has $num_rows";
echo '<br>';
Unsure as why but for whatever reason I simply can't get a value using
count <pounding head on keyboard>.

As said, it requires a mysql_fetch_* function to get the results from a
query. Your "fix" above is just an ugly hack. Additionally with some
more SQL and a GROUP BY clause you could drop the foreach loop and do it
all with a single query, something like

SELECT columnA, COUNT(*) AS count
FROM table
WHERE columnA IN ('A', 'B', 'C', 'D', 'E')
GROUP BY columnA
ORDER BY columnA

Micha

[Pedro Graca]

Andy Hassall wrote:

On Mon, 20 Nov 2006 18:48:39 +0100, Michael Fesser <ne*****@gmx.dewrote:

>>.oO(Christoph Burschka)

>>>Akhenaten wrote:
The following snippet (for whatever reason) returns no value for the
count. Suggestions?
[...]

code re-inserted

>>>$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client) {
$count = mysql_query('SELECT COUNT(*) from table where columnA =
$client');
echo "$client has $count";
echo '<br>';
}

>>1. You can't pass variables within single quotes.

2. Strings in MySQL queries need to be surrounded by quotes

3. mysql_query() just returns a resource ID.

4. Always check for errors,

5. Indent your code. Always. Even for a very small example posted to
usenet.

--
I (almost) never check the dodgeit address.
If you *really* need to mail me, use the address in the Reply-To
header with a message in *plain* *text* *without* *attachments*.

[Christoph Burschka]

Michael Fesser wrote:

.oO(Akhenaten)

>Actually fixed it using the following:

$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client) {
$query = mysql_query("SELECT * from table where columnA = '$client' ");
$num_rows = mysql_num_rows($query);
echo "$client has $num_rows";
echo '<br>';
Unsure as why but for whatever reason I simply can't get a value using
count <pounding head on keyboard>.

As said, it requires a mysql_fetch_* function to get the results from a
query. Your "fix" above is just an ugly hack. Additionally with some
more SQL and a GROUP BY clause you could drop the foreach loop and do it
all with a single query, something like

SELECT columnA, COUNT(*) AS count
FROM table
WHERE columnA IN ('A', 'B', 'C', 'D', 'E')
GROUP BY columnA
ORDER BY columnA

Micha

And since one of the bottle-necks in a web application is the time it takes for
the database to process the query and return the result set, it makes sense to
minimize the number of queries. The above is the best way to go. Use it in this
code:

$sql = "...[shown above]";
$res = mysql_query($sql);
$counts=array();
while ($row=mysql_fetch_array($res)) $counts[$row['columnA']]=$row['count'];

in the end, $counts will be an array containing all the letters as keys and the
associated counts as values.

--
Christoph Burschka

[BKDotCom]

I created a function get_query_rows() that makes the repetitive task of
using mysql_query() and looping through abnd getting the results easier
(for me anyhow).
$arr = array ("A", "B", "C", "D", "E");
foreach ($arr as $client)
{
$query = 'SELECT COUNT(*) as count from table where columnA =
"'.$client.'"';
$rows = get_query_rows($query);
$count = $rows[0]['count'];
echo "$client has $count<BR />";
}

function get_query_rows($query,$resource=null)
{
$rows = false;
$result = ( $resource )
? @mysql_query($query,$resource)
: @mysql_query($query);
if ( $result )
{
$rows = array();
$num_rows = mysql_num_rows($result);
for ( $i=0; $i<$num_rows; $i++ )
$rows[] = mysql_fetch_assoc($result);
}
else
trigger_error(mysql_error());
return $rows;
}