Re: [PHP-DB] SELECT COUNT - result from two tables
From: "Boyan Nedkov" <[EMAIL PROTECTED]> > Putting more than one table in the FROM clause means tables are joined, > then at least following problems could arise: > > - using WHERE clause you can have empty recordset returned and then > COUNT conflicts with it because there is actually no any data to be > returned; There won't be a conflict, COUNT(*) will just return zero. When you use COUNT(*) there will _always_ be a row returned, either zero or the count. > - joining two (or more) tables without using aliases to the equally > named columns in the SELECT/WHERE/COUNT clauses will produce error > message instead of expecting data; You don't need an alias and the columns don't have to be equally named, but yes, you have to "join" them somehow against some column. The original query had this. > - COUNT(*) wont work if u have equal table names in the tables; I have no idea what you mean by that. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT COUNT - result from two tables
Putting more than one table in the FROM clause means tables are joined,
then at least following problems could arise:
- using WHERE clause you can have empty recordset returned and then
COUNT conflicts with it because there is actually no any data to be
returned;
- joining two (or more) tables without using aliases to the equally
named columns in the SELECT/WHERE/COUNT clauses will produce error
message instead of expecting data;
- COUNT(*) wont work if u have equal table names in the tables;
If you give us some more detail description of the tables then it will
be easier to find where the problem is
Boyan
--
John W. Holmes wrote:
Mark Gordon wrote:
Yes, query is definitely working without COUNT(*). Even in the most
stripped down form, the query fails:
$sql = "SELECT COUNT(bandid), genre
FROM bands, genre";
$result=mysql_query($sql);
while ($gen=mysql_fetch_row($result)) {
echo $gen[1];
}
Fails how? If it echos zero, it's not failing; your query just isn't
returning any rows (regardless whether you think it should or not).
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT COUNT - result from two tables
Mark Gordon wrote:
Yes, query is definitely working without COUNT(*). Even in the most stripped down form, the query fails:
$sql = "SELECT COUNT(bandid), genre
FROM bands, genre";
$result=mysql_query($sql);
while ($gen=mysql_fetch_row($result)) {
echo $gen[1];
}
Fails how? If it echos zero, it's not failing; your query just isn't
returning any rows (regardless whether you think it should or not).
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT COUNT - result from two tables
Yes, query is definitely working without COUNT(*). Even in the most stripped down
form, the query fails:
$sql = "SELECT COUNT(bandid), genre
FROM bands, genre";
$result=mysql_query($sql);
while ($gen=mysql_fetch_row($result)) {
echo $gen[1];
}
"John W. Holmes" <[EMAIL PROTECTED]> wrote:
Mark Gordon wrote:
> I cannot seem to get a SELECT COUNT for a query from fields in two different tables
> and a WHERE clause. Does anyone know if this is not possible with php/mysql or am I
> doing something wrong? I have tried a number of variations on the following code:
>
> $sql = "SELECT COUNT(*), bandid, bandname, genre
> FROM bands, genre
> WHERE genre.genreid=$g
> AND bands.genreid=genre.genreid
> ORDER BY bandname ASC";
> $gen = mysql_fetch_row(mysql_query($sql));
> echo $gen[0];
>
> I know from documentation that COUNT works with WHERE clauses...but also from two
> tables?
There's no reason it shouldn't work. The best way to troubleshoot these
things is to get the query working without the COUNT(*) and make sure
it's returning the right number of rows.
Are you even sure the query is executing? Maybe it's failing...
$result = mysql_query($sql) or die(mysql_error());
$gen = mysql_fetch_row($result);
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
-
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
Re: [PHP-DB] SELECT COUNT - result from two tables
Mark Gordon wrote: I cannot seem to get a SELECT COUNT for a query from fields in two different tables and a WHERE clause. Does anyone know if this is not possible with php/mysql or am I doing something wrong? I have tried a number of variations on the following code: $sql = "SELECT COUNT(*), bandid, bandname, genre FROM bands, genre WHERE genre.genreid=$g AND bands.genreid=genre.genreid ORDER BY bandname ASC"; $gen = mysql_fetch_row(mysql_query($sql)); echo $gen[0]; I know from documentation that COUNT works with WHERE clauses...but also from two tables? There's no reason it shouldn't work. The best way to troubleshoot these things is to get the query working without the COUNT(*) and make sure it's returning the right number of rows. Are you even sure the query is executing? Maybe it's failing... $result = mysql_query($sql) or die(mysql_error()); $gen = mysql_fetch_row($result); -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals – www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SELECT COUNT - result from two tables
I cannot seem to get a SELECT COUNT for a query from fields in two different tables and a WHERE clause. Does anyone know if this is not possible with php/mysql or am I doing something wrong? I have tried a number of variations on the following code: $sql = "SELECT COUNT(*), bandid, bandname, genre FROM bands, genre WHERE genre.genreid=$g AND bands.genreid=genre.genreid ORDER BY bandname ASC"; $gen = mysql_fetch_row(mysql_query($sql)); echo $gen[0]; I know from documentation that COUNT works with WHERE clauses...but also from two tables? Thanks everyone - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
