[PHP-DB] mysql COUNT row results
Is there a way that SELECT COUNT(auto_increment) as total_subscribers , `email` FROM `table` may exist within the same query and provide more than 1 row of search results? When I run a query like this the COUNT portion of the result is allowing only 1 to be selected. My desire is to have the the COUNT result appended to each row. Thoughts anyone? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] Re: COUNT() returns 0 if there were no matching rows. .... really?!
Here's an example (snip) from a var_dump of that
$BuildPerUniqueDateArray:
(note that the 'aweber_7solar_aw' table does NOT have a record for
the
date '2009-07-28', so I would expect to see that 1 to be a 0
there.)
If a table doesn't have a record for a given date, I wouldn't expect
to see 1
or 0 -- I would expect not to see any row at all for that date/table
combination. You're not looping through all possible dates, you're
looping
through the result-set of your query.
Are you sure that the table in question doesn't have any 2009-07-28
records?
You could add the following column to each SELECT to help
troubleshoot:
GROUP_CONCAT(date_column) AS `all_timestamps_for_date`
This will give your result-set an additional column, which will
contain a
comma-separated list of all the records that GROUP BY is gathering
together in
each row (and therefore all the records that COUNT() is counting).
I'm wondering if some sort of timezone discrepancy is maybe causing
a timestamp
record to be attributed to 2009-07-28 unexpectedly.
Ben
Ben
I tried to implement your troubleshooting column like so:
$query = SELECT GROUP_CONCAT(date(solarLandingDateTime)) AS
`all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS
`tableAlias` FROM t7solar_landing GROUP BY date(solarLandingDateTime)
UNION ALL SELECT GROUP_CONCAT(date(solarAweberConfDateTime)) AS
`all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY
date(solarAweberConfDateTime) UNION ALL SELECT
GROUP_CONCAT(date(solarAWDateTime)) AS `all_timestamps_for_date`
COUNT(*) AS `CountRowsThisDateThisTBL`, date(solarAWDateTime) AS
`uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw
GROUP BY date(solarAWDateTime) ORDER BY uniqueDate DESC LIMIT 300;
it returns this error:
query failed: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarLandingDateTime) AS `uniqueDat' at line 1
..So not to sit here helpless, I troubleshoot according to my current
level of skill this way:
I added this lower down on my page:
$tableDump = SELECT solarAWDateTime FROM aweber_7solar_aw ORDER BY
solarAWDateTime DESC;
$tableDumpResult = mysql_query($tableDump) or die(query failed:
.mysql_error());
echo hr /pre\n;
while ($row = mysql_fetch_assoc($tableDumpResult)) {
print_r($row);
}
echo /pre\n;
echo hr /\n;
it returns this:
Array
(
[solarAWDateTime] = 2009-08-06 13:33:57
)
Array
(
[solarAWDateTime] = 2009-08-06 09:41:54
)
Array
(
[solarAWDateTime] = 2009-08-06 06:06:55
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:27
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:25
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:02
)
Array
(
[solarAWDateTime] = 2009-08-05 06:55:58
)
Array
(
[solarAWDateTime] = 2009-08-04 06:46:42
)
...
Array
(
[solarAWDateTime] = 2009-07-30 06:48:56
)
Array
(
[solarAWDateTime] = 2009-07-29 16:11:20
)
I did not clip my paste here ^^^ the last entry is indeed '2009-07-29
16:11:20'.
..so you can see there is no record in this table with a timestamp on
the date '2009-07-28'.. so HOW in the world does my array get that
element, for that date, as if the while loop was iterating on a record
in this table with that date, when none exists?? (You still have my
OP on this?.. to see the code and var_dump for that array I build from
the iterating results of the original query?)
John Butler (Govinda)
govinda.webdnat...@gmail.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Updating count on record results
I am setting up a table to log a count on individual records for every time they are returned in a results return. Just so to illustrate Record1 First search brings up Record1 (counter is set too 1) Second search brings up Record1 (counter is set too 2) Third search brings up Record1 (counter is set too 3) ...etc. So I have a vauge idea of the sql statement but where to place is the question. My thoughts are that it should be in the loop that generates the result rows return. In other words - Maybe here : ?php echo $row_rsVJ['JobTitle']; ?/div/td Or maybe here ?: ?php } while ($row_rsVJ = mysql_fetch_assoc($rsVJ)); ? Hope what I'm asking is clear. Thank you , Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Updating count on record results
this is how I did it for a client's site // update the number of times the vehicle has been viewed mysql_db_query($dbname, UPDATE vehicle_inventory SET viewed=viewed+1 WHERE ccode='$ccode', $link); bastien From: Stuart Felenstein [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [PHP-DB] Updating count on record results Date: Fri, 19 Nov 2004 04:59:20 -0800 (PST) I am setting up a table to log a count on individual records for every time they are returned in a results return. Just so to illustrate Record1 First search brings up Record1 (counter is set too 1) Second search brings up Record1 (counter is set too 2) Third search brings up Record1 (counter is set too 3) ...etc. So I have a vauge idea of the sql statement but where to place is the question. My thoughts are that it should be in the loop that generates the result rows return. In other words - Maybe here : ?php echo $row_rsVJ['JobTitle']; ?/div/td Or maybe here ?: ?php } while ($row_rsVJ = mysql_fetch_assoc($rsVJ)); ? Hope what I'm asking is clear. Thank you , Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Updating count on record results
--- Bastien Koert [EMAIL PROTECTED] wrote: this is how I did it for a client's site // update the number of times the vehicle has been viewed mysql_db_query($dbname, UPDATE vehicle_inventory SET viewed=viewed+1 WHERE ccode='$ccode', $link); Where did this code go though ? Meaning, was it in a seperate script / page or was it part of a bigger query that first got the results based on certain criteria , the did the update? Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Updating count on record results
its a separate query on the same page, its activated after the data retreival query runs. ? ... // get the information for this vehicle $result = mysql_db_query($dbname, SELECT * FROM vehicle_inventory WHERE ccode='$ccode', $link); $data = mysql_fetch_array($result); $data[comments] = nl2br($data[comments]); // update the number of times the vehicle has been viewed mysql_db_query($dbname, UPDATE vehicle_inventory SET viewed=viewed+1 WHERE ccode='$ccode', $link); ...? bastien From: Stuart Felenstein [EMAIL PROTECTED] To: Bastien Koert [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: [PHP-DB] Updating count on record results Date: Fri, 19 Nov 2004 07:28:48 -0800 (PST) --- Bastien Koert [EMAIL PROTECTED] wrote: this is how I did it for a client's site // update the number of times the vehicle has been viewed mysql_db_query($dbname, UPDATE vehicle_inventory SET viewed=viewed+1 WHERE ccode='$ccode', $link); Where did this code go though ? Meaning, was it in a seperate script / page or was it part of a bigger query that first got the results based on certain criteria , the did the update? Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Count unique visits in PHP/MySQL
take a look at this:
http://otn.oracle.com/oramag/oracle/04-mar/o24asktom.html
and search for the Analytics to the Rescue example. Instead of 3 seconds
you want 1800 and instead of sum you want count.Don't forget to group by
ip,of course... And you're done. No need for an extra table.
Hope it helps you out.
Rui Cunha
Kim Steinhaug writes:
Whatabout creating a table containing online users,
where you log every activity with IP, BrowserSession and Timestamp.
You also create a table to track the accual unique visits.
So my logic to solve it :
Update the online table like this (Some rough coding below, not tested at
all, read the logic).
30 minutes = 60sec*30 = 1800
1delete from online where timestamp . (time() - 1800); // Delete
inactive users / uniqe ghosts or whatever
2Update online set timestamp = ' . time() . ' where ip=' . $ip . '
and browsersession = ' . $browsersession . ';
3If (!mysql_affected_rows()){
// Update the Unique visitor table
// Insert new entry with IP, Browsersession and time() into the
online database
}
As far as my midnight brain would see it this would work nicely.
--
--
Kim Steinhaug
--
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
--
www.steinhaug.com - www.easywebshop.no - www.webkitpro.com
--
[EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
ast.net...
I am making a PHP/MySQL traffic report page from a table that records some
user activity using PHP referrer information.
I have a table with three rows: IP, page_name, and timestamp. The IP row
records the user's IP address, page_name records the name of the page that
the user loaded, and the timestamp row records in Unix timestamp format the
time of day that the user requested the page.
I want to be able to count unique visits per IP according to Internet
Advertising Bureau standards, which count a Unique Visit as a log in by
the same IP once every thirty minutes.
IAB verbatim definition: Visit - One or more text and/or graphics
downloads from a site qualifying as at least one page, without 30
consecutive minutes of inactivity, which can be reasonably attributed to a
single browser for a single session. A browser must pull text or graphics
content to be considered a visit.
So I need to make a MySQL query that will count how many times an IP
logged a timestamp within a given time period.
For example, the publisher checking traffic could request a date between
May 1 and May 31, and I'd like to be able to return a page that counted
unique users (count distinct IP), pages viewed (list distinct pages) and how
many times they visited in that period. I have the first two down, but not
the unique visits. Any ideas?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Count unique visits in PHP/MySQL
Whatabout creating a table containing online users,
where you log every activity with IP, BrowserSession and Timestamp.
You also create a table to track the accual unique visits.
So my logic to solve it :
Update the online table like this (Some rough coding below, not tested at
all, read the logic).
30 minutes = 60sec*30 = 1800
1delete from online where timestamp . (time() - 1800); // Delete
inactive users / uniqe ghosts or whatever
2Update online set timestamp = ' . time() . ' where ip=' . $ip . '
and browsersession = ' . $browsersession . ';
3If (!mysql_affected_rows()){
// Update the Unique visitor table
// Insert new entry with IP, Browsersession and time() into the
online database
}
As far as my midnight brain would see it this would work nicely.
--
--
Kim Steinhaug
--
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
--
www.steinhaug.com - www.easywebshop.no - www.webkitpro.com
--
[EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
ast.net...
I am making a PHP/MySQL traffic report page from a table that records some
user activity using PHP referrer information.
I have a table with three rows: IP, page_name, and timestamp. The IP row
records the user's IP address, page_name records the name of the page that
the user loaded, and the timestamp row records in Unix timestamp format the
time of day that the user requested the page.
I want to be able to count unique visits per IP according to Internet
Advertising Bureau standards, which count a Unique Visit as a log in by
the same IP once every thirty minutes.
IAB verbatim definition: Visit - One or more text and/or graphics
downloads from a site qualifying as at least one page, without 30
consecutive minutes of inactivity, which can be reasonably attributed to a
single browser for a single session. A browser must pull text or graphics
content to be considered a visit.
So I need to make a MySQL query that will count how many times an IP
logged a timestamp within a given time period.
For example, the publisher checking traffic could request a date between
May 1 and May 31, and I'd like to be able to return a page that counted
unique users (count distinct IP), pages viewed (list distinct pages) and how
many times they visited in that period. I have the first two down, but not
the unique visits. Any ideas?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Row count in a query
Hi, Is it possible to have an incrementing row count in my query that is not part of the table data? i.e. 1 data data 2 data data 3 data data ... This has to be done in the query not the PHP!! Thanks for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Row count in a query
The best way I can think of is: - create a temporary table T with an autoincrement field + desired output column structure - perform a INSERT INTO T SELECT 0, desired output in the temp table - you now have the desired result in your temp table HTH Ignatius _ - Original Message - From: Shaun [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 31, 2004 11:44 Subject: [PHP-DB] Row count in a query Hi, Is it possible to have an incrementing row count in my query that is not part of the table data? i.e. 1 data data 2 data data 3 data data ... This has to be done in the query not the PHP!! Thanks for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Row count in a query
Shaun wrote: Is it possible to have an incrementing row count in my query that is not part of the table data? i.e. 1 data data 2 data data 3 data data ... This has to be done in the query not the PHP!! If you _have_ to get this in your query I'd say you have a flaw in your logic somewhere. However, you can do it in MySQL using these two queries. SELECT @a:=0; SELECT @a:[EMAIL PROTECTED], * FROM table; -- ---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] Row count in a query
El Dom 01 Feb 2004 12:54, John W. Holmes escribi: Shaun wrote: Is it possible to have an incrementing row count in my query that is not part of the table data? i.e. 1 data data 2 data data 3 data data ... This has to be done in the query not the PHP!! If you _have_ to get this in your query I'd say you have a flaw in your logic somewhere. However, you can do it in MySQL using these two queries. SELECT @a:=0; SELECT @a:[EMAIL PROTECTED], * FROM table; This isn't very good SQL coding. If you use a database with sequences, built a temptable to put the data in temporarly, with an INT field at the begining, and a sequence to have the autoincremental. Very easy, and compatile with any relational DB. :-) -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martn Marqus | Programador, DBA Centro de Telemtica| Administrador Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Row count in a query
Hmmm... I would not bet money on John Holmes bad coding. (disclaimer: I have no financial stake in PHP|A, other than being a happy subscriber) More likely the original question was not well formulated. cheers Ignatius _ - Original Message - From: Martn Marqus [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Shaun [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, January 31, 2004 18:00 Subject: Re: [PHP-DB] Row count in a query El Dom 01 Feb 2004 12:54, John W. Holmes escribi: Shaun wrote: Is it possible to have an incrementing row count in my query that is not part of the table data? i.e. 1 data data 2 data data 3 data data ... This has to be done in the query not the PHP!! If you _have_ to get this in your query I'd say you have a flaw in your logic somewhere. However, you can do it in MySQL using these two queries. SELECT @a:=0; SELECT @a:[EMAIL PROTECTED], * FROM table; This isn't very good SQL coding. If you use a database with sequences, built a temptable to put the data in temporarly, with an INT field at the begining, and a sequence to have the autoincremental. Very easy, and compatile with any relational DB. :-) -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martn Marqus | Programador, DBA Centro de Telemtica | Administrador Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Row count in a query
Martn Marqus wrote: El Dom 01 Feb 2004 12:54, John W. Holmes escribi: Shaun wrote: Is it possible to have an incrementing row count in my query that is not part of the table data? If you _have_ to get this in your query I'd say you have a flaw in your logic somewhere. However, you can do it in MySQL using these two queries. SELECT @a:=0; SELECT @a:[EMAIL PROTECTED], * FROM table; This isn't very good SQL coding. Sure it is. It's the question that's not very good. If you use a database with sequences, built a temptable to put the data in temporarly, with an INT field at the begining, and a sequence to have the autoincremental. Very easy, and compatile with any relational DB. :-) Exactly. That's why I gave a solution for MySQL. -- ---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
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
[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
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
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:
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
[PHP-DB] SQL COUNT vs mysql_num_rows
maybe mysql cannot COUNT the result from more than 1 table, hence the mysql_num_rows function - but isn't it good programming practice to get the SQL to do as much work up front? - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
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] SQL COUNT vs mysql_num_rows
if tables are joined correctly it shouldn't be any problem to get count of a column, and yes - delegating that task to the database should be more efficient concerning the execution time boyan -- [EMAIL PROTECTED] wrote: maybe mysql cannot COUNT the result from more than 1 table, hence the mysql_num_rows function - but isn't it good programming practice to get the SQL to do as much work up front? - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SELECT Count - solved
Thanks for the debug advice - I will start using my_sql_error
First I got this error:
Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if
there is no GROUP BY clause
So the correct code ended up:
$sql = SELECT COUNT(bandid), genre
FROM bands, genre
GROUP BY genre;
$result=mysql_query($sql) or die(mysql_error());
$num=mysql_fetch_row($result);
echo $num[0];
while ($gen=mysql_fetch_row($result)) {
echo $gen[1];
}
Thanks guys
-
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
[PHP-DB] Column count error?
I am not sure how to resolve this type of error, any help is appreciated.
TIA
Jas
/* Error message */
Column count doesn't match value count at row 1
/* Code to query db for username and password */
require '/home/bignickel.net/scripts/admin/db.php';
$db_table = 'auth_users';
$sql = SELECT * from $db_table WHERE un = \$u_name\ AND pw =
password(\$p_word\);
$result = @mysql_query($sql,$dbh) or die('Cannot execute query, please try
again later or contact the system administrator by email at
[EMAIL PROTECTED]');
/* Loop through records for matching pair */
$num = mysql_numrows($result);
if ($num !=0) {
print You have a valid username and password combination;
} else {
header(Location: blank.php); }
/* Table structure of db */
CREATE TABLE auth_users (
user_id int(11) NOT NULL auto_increment,
f_name varchar(255) default NULL,
l_name varchar(255) default NULL,
email_addy varchar(255) default NULL,
un text,
pw text,
PRIMARY KEY (user_id)
) TYPE=MyISAM;
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Column count error?
Did you copy and paste the code?
There is no such function as mysql_numrows, it has to be mysql_num_rows.. I
don't know why you didn't get an error for it?
Gurhan
Jas [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
I am not sure how to resolve this type of error, any help is
appreciated.
TIA
Jas
/* Error message */
Column count doesn't match value count at row 1
/* Code to query db for username and password */
require '/home/bignickel.net/scripts/admin/db.php';
$db_table = 'auth_users';
$sql = SELECT * from $db_table WHERE un = \$u_name\ AND pw =
password(\$p_word\);
$result = @mysql_query($sql,$dbh) or die('Cannot execute query, please
try
again later or contact the system administrator by email at
[EMAIL PROTECTED]');
/* Loop through records for matching pair */
$num = mysql_numrows($result);
if ($num !=0) {
print You have a valid username and password combination;
} else {
header(Location: blank.php); }
/* Table structure of db */
CREATE TABLE auth_users (
user_id int(11) NOT NULL auto_increment,
f_name varchar(255) default NULL,
l_name varchar(255) default NULL,
email_addy varchar(255) default NULL,
un text,
pw text,
PRIMARY KEY (user_id)
) TYPE=MyISAM;
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Word Count, rounding and missing zeros
G'day All, I'm using php to query a mysql db and I need to display a word count from one of the columns and multiply it to get a quote. So far I have this: $thewords = count(split ( ,$adtext)); $thequote = round(($thewords * .78),2); In an example with 35 words it returns 27.3 but I'd like it to say 27.30. If the total doesn't end in a zero it displays fine. Is there a way to force the zero to display? Cheers and thanks kim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: count
I know how to get the results for the total number of records select count(*) from $table but how do I put them into a variable for me to use later??? David Robley [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... I would like to count all the total records from a database. How would I go about doing that??? Do a COUNT * on each table and total the results? Although the results may actually not mean much depending on your DB structure. Or do you really mean count records in a _table_? -- David Robley Temporary Kiwi! Quod subigo farinam -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: count
In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... I know how to get the results for the total number of records select count(*) from $table but how do I put them into a variable for me to use later??? David Robley [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... I would like to count all the total records from a database. How would I go about doing that??? Do a COUNT * on each table and total the results? Although the results may actually not mean much depending on your DB structure. Or do you really mean count records in a _table_? Aliases. SELECT COUNT(*) AS howmany FROM table Then use the variable $howmany -- David Robley Temporary Kiwi! Quod subigo farinam -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: count
What is the Aliases used for??? Aliases. SELECT COUNT(*) AS howmany FROM table Then use the variable $howmany -- David Robley Temporary Kiwi! Quod subigo farinam -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: count
In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... What is the Aliases used for??? Aliases. SELECT COUNT(*) AS howmany FROM table Then use the variable $howmany At this stage I refer you to The Fine (mysql) Manual - or anything on the SQL language. -- David Robley Temporary Kiwi! Quod subigo farinam -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] a Count() ?
select cat_id, count(prod_id) from some_table order by cat_id; Best regards, Andrey Hristov - Original Message - From: Dave Carrera [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, March 16, 2002 12:46 PM Subject: [PHP-DB] a Count() ? Hi All I am trying to count how many product names in my db have the same category id and then show it ie: Catid 1 Product 1 Catid 1 Product 2 Catid 2 Product 3 Catid 3 Product 4 Catid 3 Product 5 Result would be Catid1 has 2 products Catid2 has 1 products Catid3 has 2 products I think it has something to do with the GROUP command but the mysql doc dose not make it clear how to achive this task. Code examples, pointers to web resources or any info thankfully received. Thank you in advance Dave Carrera Php Developer http://davecarrera.freelancers.net http://www.davecarrera.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] a Count() ?
Hi Dave, I am trying to count how many product names in my db have the same category id and then show it ie: Catid 1 Product 1 Catid 1 Product 2 Catid 2 Product 3 Catid 3 Product 4 Catid 3 Product 5 Result would be Catid1 has 2 products Catid2 has 1 products Catid3 has 2 products I think it has something to do with the GROUP command but the mysql doc dose not make it clear how to achive this task. Code examples, pointers to web resources or any info thankfully received. Let's take it a step at a time. First of all assemble the SELECT to produce your first list: SELECT * FROM tblNm; then pull in the GROUP BY clause to collect the row-results together in some like-minded fashion. In this case you want to collect all or the rows pertaining to one category (ID) together. (you will need to be more specific about what in the manual is making you uncertain): SELECT * FROM tblNm GROUP BY Catid1; Oops! All of a sudden we only get one line for each CatId (and the rest of the columns produce fairly unpredictable data taken from only one of the rows with that CatId). Get rid of the * (all columns) and replace it with the CatId colNm. Now follow your instincts and check out COUNT() in the manual, and try something like: SELECT Catid1, count(*) FROM tblNm GROUP BY Catid1; As I said 'follow your instincts' and take it one step at a time: Code the simplest query first, then try making it more complicated by adding/amending one clause at a time, crafting the result until it suits your purposes... Let us know how you get on! =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] a Count() ?
On Sat, 16 Mar 2002 10:46:38 - Dave Carrera [EMAIL PROTECTED] wrote: Hi All I am trying to count how many product names in my db have the same category id and then show it ie: Catid 1 Product 1 Catid 1 Product 2 Catid 2 Product 3 Catid 3 Product 4 Catid 3 Product 5 Result would be Catid1 has 2 products Catid2 has 1 products Catid3 has 2 products I think it has something to do with the GROUP command but the mysql doc dose not make it clear how to achive this task. Code examples, pointers to web resources or any info thankfully received. Simply by a sql query : SELECT count( Catid ) as total, Catid FROM t_product GROUP BY Catid; the result set will be something like that totalCatID 21 12 23 hth pa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] a Count() ?
On Sat, 16 Mar 2002 11:50:11 - DL Neil [EMAIL PROTECTED] wrote: Hi Dave, SELECT * FROM tblNm; SELECT * FROM tblNm GROUP BY Catid1; SELECT Catid1, count(*) FROM tblNm GROUP BY Catid1; As I said 'follow your instincts' and take it one step at a time: Code the simplest query first, then try making it more complicated by adding/amending one clause at a time, crafting the result until it suits your purposes... Nothing to say except you have to avoid use '*'. That makes your queries easier to read and will safe your db engine :). pa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Difficult count statement. Need some sql advice
Hi there, I would like to querry my db for following result - Count the number of topics listed in the table fo_topics, but only the topics where the belonging forum is not in the archive mode ( this is when table fo_forums field archive is 1) I tryed following querry but this is for sure wrong: SELECT COUNT(t*) AS c, farchive, fforum_id FROM fo_topics t, fo_forums f WHERE farchive != 1 Can this be that difficult? Thanx for any help Andy -- PHP Database Mailing List (http://wwwphpnet/) To unsubscribe, visit: http://wwwphpnet/unsubphp
Re: [PHP-DB] Difficult count statement. Need some sql advice
On Sat, Mar 02, 2002 at 05:36:41PM +0100, Andy wrote: Hi there, I would like to querry my db for following result - Count the number of topics listed in the table fo_topics, but only the topics where the belonging forum is not in the archive mode ( this is when table fo_forums field archive is 1) I tryed following querry but this is for sure wrong: SELECT COUNT(t*) AS c, farchive, fforum_id FROM fo_topics t, fo_forums f WHERE farchive != 1 Can this be that difficult? Thanx for any help Andy You need to join the two tables together: select count(t*) as c, farchive, fforum_id from fo_topics t, fo_forums f where tforum_id = fforum_id and farchive != 1 might work I assume you have a foreign key in fo_topics linking to fo_forums Bill -- PHP Database Mailing List (http://wwwphpnet/) To unsubscribe, visit: http://wwwphpnet/unsubphp
[PHP-DB] Re: count from the results
Hi Barry!
you can do it like this for example:
$query = SELECT * FROM artist WHERE artist_name LIKE 'b%' ORDER BY artist
ASC;
$count = mysql_query(SELECT COUNT(artist) AS count FROM artist WHERE
artist_name LIKE 'b%',$db);
$x = mysql_fetch_array($count);
$result = mysql_query($query) or die(Select Failed!);
$number = $x[count];
echo h3Total Number Of Artists In \B\:nbsp;nbsp;;
echo $number;
echo brbr/h3;
if (mysql_num_rows($result)) {
echo table;
echo trthArtists/th;
while ($qry = mysql_fetch_array($result)){
echo tr;
echo td;
echo $qry[artist];
}}?
Barry Rumsey [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]...
I have the following code:
$query = SELECT * FROM artist WHERE artist LIKE 'b%' ORDER BY artist
ASC;
$result = mysql_query($query) or die(Select Failed!);
echo h3Total Number Of Artists In \B\:nbsp;nbsp;;
echo mysql_num_rows($result);
echo brbr/h3;
if (mysql_num_rows($result)) {
echo table;
echo trthArtists/th;
while ($qry = mysql_fetch_array($result)){
echo tr;
echo td;
echo $qry[artist];
}}?
What I would like to know is how do I do a count on each result
returned.e.g. Benny(4) , Bill(10)
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Count(*)
Thank you everyone. The "COUNT(*) AS c" worked great. ---Original Message--- From: Kai Voigt Date: Friday, 25 January 2002 11:11:06 a. To: Barry Rumsey Cc: [EMAIL PROTECTED] Subject: Re: Count(*) Barry Rumsey wrote: I have this small query on a page:emit source="sql" host=xoops query= SELECT COUNT(*) FROM xp_topics WHERE artist='artist' ORDER BY topicid DESC LIMIT 1"sql.artist;/emitUse "SELECT COUNT(*) AS C FROM ..." instead. Then you can access thecount value as sql.c;Kai-- dreiecksplatz 8, d-24105 kiel, +49-431-22199869, http://k.123.org/. IncrediMail - Email has finally evolved - Click Here
