[PHP-DB] GROUP BY
Is there a way in the query below that the “LEFT OUTER JOIN” connects with only the most recently added entry in `verse_of_the_day_Bible_trivia` for each category ( `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` ) based on the column `verse_of_the_day_Bible_trivia`.`date_added` ? The purpose of this query is to compare the most recently added Bible trivia questions ( `verse_of_the_day_Bible_trivia`.`date_added` ) from each category ( `Bible_trivia_category`.`reference` ) with the last time the category handout was created ( `verse_of_the_day_bible_trivia_ready_made_handouts`.`created` ). If there are new questions since the last time the handout was created ( `verse_of_the_day_Bible_trivia`.`date_added` ) OR the category now has 10 or more questions then the handout will be re-created (through a cron job) based on the results of this query. The HAVING condition is to eliminate categories with less than 10 questions. - See table structures below Thank you for your help. Ron === SELECT `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` , `verse_of_the_day_Bible_trivia`.`date_added` , COUNT( `verse_of_the_day_Bible_trivia`.`reference` ) AS question_count, `verse_of_the_day_bible_trivia_ready_made_handouts`.`filename` FROM ( `verse_of_the_day_Bible_trivia` INNER JOIN `Bible_trivia_category` ON `Bible_trivia_category`.`reference` = `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` ) LEFT OUTER JOIN `verse_of_the_day_bible_trivia_ready_made_handouts` ON `Bible_trivia_category`.`reference` = `verse_of_the_day_bible_trivia_ready_made_handouts`.`Bible_trivia_category_reference` WHERE `verse_of_the_day_Bible_trivia`.`live` = 1 AND `verse_of_the_day_Bible_trivia`.`date_added` `verse_of_the_day_bible_trivia_ready_made_handouts`.`created` GROUP BY `Bible_trivia_category`.`reference` HAVING question_count =10 ORDER BY `verse_of_the_day_Bible_trivia`.`reference` ASC === `Bible_trivia_category` CREATE TABLE IF NOT EXISTS `Bible_trivia_category` ( `reference` int(3) NOT NULL AUTO_INCREMENT, `category` varchar(45) NOT NULL, PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ; `verse_of_the_day_Bible_trivia` CREATE TABLE IF NOT EXISTS `verse_of_the_day_Bible_trivia` ( `reference` int(5) NOT NULL AUTO_INCREMENT, `Bible_trivia_category_reference` int(3) NOT NULL DEFAULT '0', `trivia_question` varchar(300) NOT NULL, `trivia_answer_1` varchar(150) NOT NULL, `trivia_answer_2` varchar(150) NOT NULL, `trivia_answer_3` varchar(150) DEFAULT NULL, `trivia_answer_4` varchar(150) DEFAULT NULL, `answer` int(1) NOT NULL DEFAULT '0', `explanation` varchar(1000) DEFAULT NULL, `Bible_verse_reference` varchar(60) DEFAULT NULL, `seasonal_use` int(1) NOT NULL DEFAULT '0', `date_added` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `assigned_date` date NOT NULL DEFAULT '-00-00', `store_catalog_reference` int(3) NOT NULL DEFAULT '0', `teaching_devotional_messages_reference` int(3) NOT NULL DEFAULT '0', `live` int(1) NOT NULL DEFAULT '0', `user_hits` int(25) NOT NULL DEFAULT '0', `user_hits_answer` int(25) NOT NULL DEFAULT '0', PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=410 ; `verse_of_the_day_bible_trivia_ready_made_handouts` CREATE TABLE IF NOT EXISTS `verse_of_the_day_bible_trivia_ready_made_handouts` ( `reference` int(5) NOT NULL AUTO_INCREMENT, `Bible_trivia_category_reference` int(3) NOT NULL, `filename` varchar(100) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `live` int(1) NOT NULL, `views` int(25) NOT NULL, PRIMARY KEY (`reference`), UNIQUE KEY `verse_of_the_day_Bible_trivia_reference` (`Bible_trivia_category_reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ; The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Group by
Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson philthath...@gmail.com wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` always depends on how many records you have, if you have 100-1000 records is very diferent to 10-50 Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Julio Araya C.Linux User #386141 Memorista de Ingeniería Civil Informática Ubuntu User #14778 Universidd Técnica Federico Santa María Valparaíso - Chile -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
Hi i recommend always use indexes when programming. developers tend not to.. and when the databases grows it's difficult to modify or make them modify the code. El mié, 09-12-2009 a las 14:22 -0300, Julio Araya escribió: On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson philthath...@gmail.com wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` always depends on how many records you have, if you have 100-1000 records is very diferent to 10-50 Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Julio Araya C.Linux User #386141 Memorista de Ingeniería Civil Informática Ubuntu User #14778 Universidd Técnica Federico Santa María Valparaíso - Chile -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
Also you may not see a difference because your not even using 'History_field' anywhere in your SELECT statement. I am a little surprised that you didn't receive a error. On Wed, Dec 9, 2009 at 9:31 AM, Juan Pablo Ramirez ramirez.juanpa...@gmail.com wrote: Hi i recommend always use indexes when programming. developers tend not to.. and when the databases grows it's difficult to modify or make them modify the code. El mié, 09-12-2009 a las 14:22 -0300, Julio Araya escribió: On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson philthath...@gmail.com wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` always depends on how many records you have, if you have 100-1000 records is very diferent to 10-50 Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Julio Araya C.Linux User #386141 Memorista de Ingeniería Civil Informática Ubuntu User #14778 Universidd Técnica Federico Santa María Valparaíso - Chile -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- a href=http://www.c28.com/?adid=stiid=19467; img src=http://www.c28.com/images/banner_88x31.gif; border=0 width=88 height=31/a
Re: [PHP-DB] Group by
The only SELECT is on MAX('timestamp'). There is really nothing to Group BY in this query. Dewey Philip Thompson wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
On Dec 9, 2009, at 12:58 PM, h...@deweywilliams.com wrote: The only SELECT is on MAX('timestamp'). There is really nothing to Group BY in this query. Dewey Philip Thompson wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` Thanks, ~Philip Well, that was just an example query. My real one is SELECT `h`.* FROM ( SELECT MAX(`history_timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` ) AS `max` INNER JOIN `history` `h` ON `max`.`max_ts` = `h`.`history_timestamp` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` This returns the results I need. The explain (split up) from this query is... ++-++--++ | id | select_type | table | type | possible_keys | ++-++--++ | 1 | PRIMARY | h | ref | req_id_history_record_id,history_timestamp | | 1 | PRIMARY | derived2 | ALL | NULL | | 2 | DERIVED | history| ref | req_id_history_record_id | ++-++--++ --+-+-+--+--+ key | key_len | ref | rows | Extra | --+-+-+--+--+ req_id_history_record_id | 8 | const,const |3 | Using temporary; Using filesort | NULL | NULL| NULL|2 | Using where | req_id_history_record_id | 8 | |3 | Using where; Using temporary; Using filesort | --+-+-+--+--+ 3 rows in set (0.01 sec) There's only 10 records in table right now... but the # of rows it's going to traverse before find the results is very small. Do I need to include `history_field` in the inner select? Thanks, ~Philip
Re: [PHP-DB] Group by
Philip Thompson wrote: On Dec 9, 2009, at 12:58 PM, h...@deweywilliams.com wrote: The only SELECT is on MAX('timestamp'). There is really nothing to Group BY in this query. Dewey Philip Thompson wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. You won't with only 10 rows in the table. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` I'd index req_id, history_record_id, history_field, timestamp If you're using myisam tables then all the data can be fetched directly from the index instead of hitting the data table as well. If you're using innodb, it'll at least use this index to search for req_id and history_record_id (assuming these fields are normally in your queries). There's only 10 records in table right now... but the # of rows it's going to traverse before find the results is very small. In theory. Sometimes databases don't work that way and instead of choosing a particular index you'd expect it to, it'll pick another one. Fill up the table(s) and make sure it does what you expect. Mysql isn't great at subselects either. Do I need to include `history_field` in the inner select? No, you don't have to. You could do a query like: select count(id) from table group by another_field; so you get a count per another_field of how many records there are. Not a great example as normally you would include another_field in the select, but you don't have to. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] GROUP BY
ASC after GROUP BY ?? didn't mysql throw an error ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] GROUP BY
The query I am using: SELECT COUNT(`member_reference`), `email_delivery_hour` FROM `member_subscriptions` WHERE `list` =1 and `email_delivery_hour` = 6 GROUP BY `email_delivery_hour` ORDER BY COUNT(`member_reference`) ASC LIMIT 1 - Original Message - From: kranthi kranthi...@gmail.com To: Chris dmag...@gmail.com Cc: Ron Piggott ron.pigg...@actsministries.org; php-db@lists.php.net Sent: Monday, August 17, 2009 6:00 AM Subject: Re: [PHP-DB] GROUP BY ASC after GROUP BY ?? didn't mysql throw an error ? No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.392 / Virus Database: 270.13.58/2306 - Release Date: 08/16/09 06:09:00 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] GROUP BY
I run a once daily e-mail list. The users are able to choose the time of day they receive their daily e-mail. Their preference is stored in the email-delivery_hour field. I am trying to determine which email_delivery_hour between 0 and 6 has the lowest number of subscribers. (I am trying to make it so the same number of e-mails are sent out every hour by offering this as the default when people sign up.) The GROUP BY section on mySQL's web site didn't make sense to me. I have attempted to start making the query (below). What additional changes to do I need to make? SELECT * FROM `member_subscriptions` WHERE `list` =1 AND `email_delivery_hour` =6 GROUP BY `email_delivery_hour` ASC ORDER BY `email_delivery_hour` ASC LIMIT 1 Ron
Re: [PHP-DB] GROUP BY
Ron Piggott wrote: I run a once daily e-mail list. The users are able to choose the time of day they receive their daily e-mail. Their preference is stored in the email-delivery_hour field. I am trying to determine which email_delivery_hour between 0 and 6 has the lowest number of subscribers. (I am trying to make it so the same number of e-mails are sent out every hour by offering this as the default when people sign up.) The GROUP BY section on mySQL's web site didn't make sense to me. I have attempted to start making the query (below). What additional changes to do I need to make? group by is for aggregation. eg you want to know the number of articles per category: select category_id, count(article_id) from articles group by category_id; so you have to use an aggregate function (count, sum, avg) and group by your unit field. In your case you want to get the number of subscribers (what you want to aggregate on) per email_delivery_hour (your unit field) which translates to: select count(member_id), -- guessed the fieldname. Adjust it email_delivery_hour from member_subscriptions where list=1 and email_delivery_hour = 6 group by email_delivery_hour order by email_delivery_hour ; -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] group by day
I have never used the DISTINCT clause, so I won't attempt to help in any detail, but look it up in the manual. I think it will help you. Regards ... Ross Larry Sandwick wrote: Can you help me with this query below Mgr's could circumvent the process by login 20 times in 1 day and change the login attempts. This is a rolling 30 window my upper mgmt would like to track. select user, count(user) as num from LoginTrack where user and t='M' and DATE_SUB(CURDATE(),INTERVAL 30 DAY) =date group by user order by num desc; I have a dilemma trying to combine the data below. The problem is that when users login several times a day I only want to count it a 1 login not 5 or more. I tried group by date and because the time is in the date that did not work. Any help would be appreciated !!! Ideally the data below should return mgrtft 1 mgrschultz 2 mgrreid1 // MySql Table data below: varchardatetimechar user date t mgrtft 2005-06-21 10:17:00 M mgrtft 2005-06-21 10:16:00 M mgrschultz 2005-06-21 09:12:00 M mgrschultz 2005-06-21 08:56:00 M mgrschultz 2005-06-21 08:26:00 M mgrreid 2005-06-21 08:26:00 M mgrschultz 2005-05-21 08:16:00 M mgrschultz 2005-06-21 08:07:00 M mgrtft 2005-06-21 07:46:00 M Larry Sandwick Sarreid, Ltd. www.sarreid.com Network/System Administrator P:(252) 291-1414 223 F:(252) 237-1592 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] group by day
Can you help me with this query below Mgr's could circumvent the process by login 20 times in 1 day and change the login attempts. This is a rolling 30 window my upper mgmt would like to track. select user, count(user) as num from LoginTrack where user and t='M' and DATE_SUB(CURDATE(),INTERVAL 30 DAY) =date group by user order by num desc; I have a dilemma trying to combine the data below. The problem is that when users login several times a day I only want to count it a 1 login not 5 or more. I tried group by date and because the time is in the date that did not work. Any help would be appreciated !!! Ideally the data below should return mgrtft 1 mgrschultz 2 mgrreid1 // MySql Table data below: varchardatetimechar user date t mgrtft 2005-06-21 10:17:00 M mgrtft 2005-06-21 10:16:00 M mgrschultz 2005-06-21 09:12:00 M mgrschultz 2005-06-21 08:56:00 M mgrschultz 2005-06-21 08:26:00 M mgrreid 2005-06-21 08:26:00 M mgrschultz 2005-05-21 08:16:00 M mgrschultz 2005-06-21 08:07:00 M mgrtft 2005-06-21 07:46:00 M Larry Sandwick Sarreid, Ltd. www.sarreid.com Network/System Administrator P:(252) 291-1414 223 F:(252) 237-1592 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] group by
Hello, Let's say I have an app used in car garages. I have two tables: table cars id make model table work_done id carid details work_date I need to pull out the last work order for each car. This pulls them all: select c.make, c.model, c.id, wd.details from cars c join work_done wd on wd.carid=c.id I can do this to get one per car: select c.make, c.model, c.id, wd.details from cars c join work_done wd on wd.carid=c.id order by c.id but that doesn't always pull the most recent one. How can I group by carid so I only get one row returned per car and ensure that row contains the most recent work row? I can't used subqueries as I haven't updated to MySQL 4.1 yet. Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] GROUP BY? Urgent help needed with selection list
if you have kind of geo id number you could use that, failing to have that info, you could re-arrange the data to have Akron - Central, Akron - SE (so that all is in a standard format) Bastien From: Chris Payne [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] GROUP BY? Urgent help needed with selection list Date: Sun, 23 Jan 2005 00:46:18 -0500 Hi there everyone, Im using the following code to populate cities from a huge database: select name=fm_city[] id=fm_city[] multiple option value=0Show All/option ? $sqla = SELECT DISTINCT(Area) FROM MLS_Listings ORDER BY Area; $sql_resulta = mysql_query($sqla,$connection) or die(Couldn't execute query.); while ($row = mysql_fetch_array($sql_resulta)) { $Area = $row[Area]; ? option value=?=$Area? ?=$Area? /option ? }; ? /select This works great, no problems BUT the client now needs is so the cities are grouped, but its not so simple. For example, say you have Akron, Akron Central etc . They need them so that ALL Akrons appear together, the problem is, that also includes some which are SE Akron etc . So, of course that appears further down the list under S. How can I group this way? I am pretty lost on this. Any help would REALLY be appreciated. Chris -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 1/21/2005 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] GROUP BY? Urgent help needed with selection list
Hi there, The problem is, the database is imported from a huge properties database and can only be imported in the format from the central database of estate agents, so I can't reformat it in the tables itself. Each table has the same fields, but one is for condo's, one is for residential etc . however, the client need to be able to do a search all tables query, and bring the results up as though you are only search 1 table. I've never searched multiple tables before without a relative ID, what I need is to search all of them as though it is just searching 1, so I don't think multiple queries would work, hence why I'm trying to do it all in a single query. Chris if you have kind of geo id number you could use that, failing to have that info, you could re-arrange the data to have Akron - Central, Akron - SE (so that all is in a standard format) Bastien From: Chris Payne [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] GROUP BY? Urgent help needed with selection list Date: Sun, 23 Jan 2005 00:46:18 -0500 Hi there everyone, Im using the following code to populate cities from a huge database: select name=fm_city[] id=fm_city[] multiple option value=0Show All/option ? $sqla = SELECT DISTINCT(Area) FROM MLS_Listings ORDER BY Area; $sql_resulta = mysql_query($sqla,$connection) or die(Couldn't execute query.); while ($row = mysql_fetch_array($sql_resulta)) { $Area = $row[Area]; ? option value=?=$Area? ?=$Area? /option ? }; ? /select This works great, no problems BUT the client now needs is so the cities are grouped, but its not so simple. For example, say you have Akron, Akron Central etc . They need them so that ALL Akrons appear together, the problem is, that also includes some which are SE Akron etc . So, of course that appears further down the list under S. How can I group this way? I am pretty lost on this. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 1/21/2005 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] GROUP BY? Urgent help needed with selection list
Chris Payne wrote: Hi there, The problem is, the database is imported from a huge properties database and can only be imported in the format from the central database of estate agents, so I can't reformat it in the tables itself. Each table has the same fields, but one is for condo's, one is for you need UNION - use of the UNION clause assumes 2 things: 1. all the tables in question do indeed have identical columns 2. the UNION clause is supported by the version of your DB software having said that if you are importing the data and each 'table' is of the same format why not just import each file/table into 1 table in your DB? residential etc . however, the client need to be able to do a search all tables query, and bring the results up as though you are only search 1 table. I've never searched multiple tables before without a relative ID, what I need is to search all of them as though it is just searching 1, so I don't think multiple queries would work, hence why I'm trying to do it all in a single query. Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] GROUP BY? Urgent help needed with selection list
Ah, that's ugly. Non-standard data is a bitch to make work. What i would do then is create a virtual column with a case-when-then construct based on an instring of area (like akron). Have a look at the mysql manual (http://dev.mysql.com/doc/mysql/en/case-statement.html) for the syntax. Is there anything preventing you from palying with the data and manipulating it on the way into your site? Trying to make the virtual column in to a real column that could provide the geo reference for future queries. Bastien From: Chris Payne [EMAIL PROTECTED] To: php-db@lists.php.net Subject: RE: [PHP-DB] GROUP BY? Urgent help needed with selection list Date: Sun, 23 Jan 2005 14:03:53 -0500 Hi there, The problem is, the database is imported from a huge properties database and can only be imported in the format from the central database of estate agents, so I can't reformat it in the tables itself. Each table has the same fields, but one is for condo's, one is for residential etc . however, the client need to be able to do a search all tables query, and bring the results up as though you are only search 1 table. I've never searched multiple tables before without a relative ID, what I need is to search all of them as though it is just searching 1, so I don't think multiple queries would work, hence why I'm trying to do it all in a single query. Chris if you have kind of geo id number you could use that, failing to have that info, you could re-arrange the data to have Akron - Central, Akron - SE (so that all is in a standard format) Bastien From: Chris Payne [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] GROUP BY? Urgent help needed with selection list Date: Sun, 23 Jan 2005 00:46:18 -0500 Hi there everyone, Im using the following code to populate cities from a huge database: select name=fm_city[] id=fm_city[] multiple option value=0Show All/option ? $sqla = SELECT DISTINCT(Area) FROM MLS_Listings ORDER BY Area; $sql_resulta = mysql_query($sqla,$connection) or die(Couldn't execute query.); while ($row = mysql_fetch_array($sql_resulta)) { $Area = $row[Area]; ? option value=?=$Area? ?=$Area? /option ? }; ? /select This works great, no problems BUT the client now needs is so the cities are grouped, but its not so simple. For example, say you have Akron, Akron Central etc . They need them so that ALL Akrons appear together, the problem is, that also includes some which are SE Akron etc . So, of course that appears further down the list under S. How can I group this way? I am pretty lost on this. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 1/21/2005 -- 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] GROUP BY? Urgent help needed with selection list
Hi there everyone, Im using the following code to populate cities from a huge database: select name=fm_city[] id=fm_city[] multiple option value=0Show All/option ? $sqla = SELECT DISTINCT(Area) FROM MLS_Listings ORDER BY Area; $sql_resulta = mysql_query($sqla,$connection) or die(Couldn't execute query.); while ($row = mysql_fetch_array($sql_resulta)) { $Area = $row[Area]; ? option value=?=$Area? ?=$Area? /option ? }; ? /select This works great, no problems BUT the client now needs is so the cities are grouped, but its not so simple. For example, say you have Akron, Akron Central etc . They need them so that ALL Akrons appear together, the problem is, that also includes some which are SE Akron etc . So, of course that appears further down the list under S. How can I group this way? I am pretty lost on this. Any help would REALLY be appreciated. Chris -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 1/21/2005
Re: [PHP-DB] GROUP BY? Urgent help needed with selection list
Well, first of all.. your subject line mentions GROUP BY which is a database function for telling the query what to use when performing aggregate functions like sum, count, average, etc type functions. That's really not what you're looking at doing here it sounds like. Sounds like you just want similar items alphebetized together while still keeping a 'region' like 'SE Akron' to display. Probably the best thing you can do is have a City and have a Region. City would be the major city that the region is near, like Akron and the Region (or whatever you want to call it) would be the area of the city that the data represents. Then just do an ORDER BY on your City instead of Region. you never have to display the City if you don't want to. But without having a giant cross reference database (that can't ever be complete) that can do the lookup of SE Akron and know that's actually Akron and not some suburb of Chicago or something, then you're going to have to tell it yourself. I think this is the best solution for you. Best of luck! -TG = = = Original message = = = Hi there everyone, I~m using the following code to populate cities from a huge database: select name=fm_city[] id=fm_city[] multiple option value=0Show All/option ? $sqla = SELECT DISTINCT(Area) FROM MLS_Listings ORDER BY Area; $sql_resulta = mysql_query($sqla,$connection) or die(Couldn't execute query.); while ($row = mysql_fetch_array($sql_resulta)) $Area = $row[Area]; ? option value=?=$Area? ?=$Area? /option ? ; ? /select This works great, no problems BUT the client now needs is so the cities are grouped, but it~s not so simple. For example, say you have Akron, Akron Central etc ~. They need them so that ALL Akrons appear together, the problem is, that also includes some which are SE Akron etc ~. So, of course that appears further down the list under S. How can I group this way? I am pretty lost on this. Any help would REALLY be appreciated. Chris -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 1/21/2005 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Group inner join results
Hi all, I thought I saw the answer to this question on here but can not find the answer .. So, I am doing an inner join on 2 tables and displaying the result as follows: Code= table cellspacing=2 cellpadding=2 border=0 tr tdfont face=Arial size=-1bCat#/b/font/td tdfont face=Arial size=-1bCategory/b/font/td tdfont face=Arial size=-1bModel/b/font/td tdfont face=Arial size=-1bDescription/b/font/td tdfont face=Arial size=-1bPrice/b/font/td /tr ? $Info = mysql_query(SELECT Table1.CatID, Table1.ProdCategory, Table2.ItemID, Table2.ItemDescription, Table2.ItemModel, Table2.ItemPrice FROM Table1 LEFT OUTER JOIN Table2 ON Table1.CatID=Table2.CatID ORDER BY Table2.ItemID); while ($SRecord = mysql_fetch_array($Info, MYSQL_ASSOC)) { $CatID = htmlspecialchars($SRecord[CatID]); $Category = htmlspecialchars($SRecord[ProdCategory]); $Model = htmlspecialchars($SRecord[ItemModel]); $Description = htmlspecialchars($SRecord[ItemDescription]); $Price = htmlspecialchars($SRecord[ItemPrice]); ? tr Tdfont face=Arial size=-1? echo ($CatID); ?/FONT/A/td tdfont face=Arial size=-1? echo($Category); ?/font/td tdfont face=Arial size=-1? echo($Model); ?/font/td tdfont face=Arial size=-1? echo($Description); ?/font/td tdfont face=Arial size=-1? echo($Price); ?/font/td ? } ? /tr/table Code== The output of above is - | Cat# | Category| Model | Description | Price - | $CatID | $Category | $Model | $Description | $Price -- What I need to do is build a table display where the output is grouped by Category. Like this: --- | Category = Ist $Category --- | $Model - $Description - $Price --- | Category = 2nd $Category --- | $Model - $Description - $Price --- | Category = 3rd $Category --- | $Model - $Description - $Price - I think that I can do this with a nested array... anyone have an idea?? THanks in advance... Aleks
Re: [PHP-DB] group by get last record
On Sun, Mar 16, 2003 at 08:02:02AM +, Daniel Harik wrote: Hello, Guys i try to join to tables slides: id userid file moment users id username As there few slids per user and i want to get only last one, i use following sql query, but it fetches me first slide. How can i make it fetch last one please? SELECT slides.file, slides.moment, users.id, users.username FROM slides, users where users.id=slides.userid GROUP BY users.id desc This isn't a PHP question. This relates to the DBMS. You should specify which DBMS you are using. However, MySQL is the only DBMS I know of that will let you run that query, so I'll assume that you're using MySQL. I'll also assume that the Moment column is a time. If it is not, replace Moment with whichever column identifies the last slide. SELECT u1.UserName, u1.UserId, s1.File, s1.Moment FROM Users u1, Users u2, Slides s1, Slides s2 WHERE u1.UserId = s1.UserId AND u2.UserId = s2.UserId AND u1.UserId = u2.UserId GROUP BY u1.UserId, s1.SlideId HAVING Max(s1.Moment) = Max(s2.Moment); BTW, you can't assume that your original statement will always return the first slide. People who have tested GROUP BY statements say that the value returned from columns with no aggregate function is somewhat random. Bob Hall -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] group by get last record
Hello, Guys i try to join to tables slides: id userid file moment users id username As there few slids per user and i want to get only last one, i use following sql query, but it fetches me first slide. How can i make it fetch last one please? SELECT slides.file, slides.moment, users.id, users.username FROM slides, users where users.id=slides.userid GROUP BY users.id desc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] group by day (unix timestamp provided)
How can I get mySQL to group stuff by the day? my date coloumn is a UNIX timestamp. SELECT whatever FROM my_table GROUP BY FROM_UNIXTIME(timestamp_col, '%Y-%m-%d') Regards Joakim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] group by day (unix timestamp provided)
How can I get mySQL to group stuff by the day? my date coloumn is a UNIX timestamp. -- JJ Harrison [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
Mike, Sorry, I haven't been keeping close track of the list - fortunately you have solved your problem. However you do not appear to have taken on-board Jason's comment. His/our concern would be that there are two mechanisms for extracting the results of the query from the MySQL resultset: DBfetch_array() which is presumably a local wrapper, and (later in the code) a 'native' call to mysql_fetch_array(). These two were both present before the switch from WHILE to DO...WHILE. Well done for spotting the error! I too have built myself 'wrapper' routines to handle db queries and both the subsequent extraction and loop control. They looks like: Fetch( $dbConnection, $NumRows, etc, $ResultSet ) while ( TheresAnotherRowToScan( $RowList, $ResultSet ) ) { etc [in fact, I stole the arg lists from the routines themselves - the language used in the calling routines is much more topical/self-documenting] =Thus there is no need for the 'double' resultset extractions... =Regards, =dn - Original Message - From: Mike Gifford [EMAIL PROTECTED] To: Mike Gifford [EMAIL PROTECTED] Cc: DL Neil [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 11 January 2002 20:21 Subject: Re: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently It may have been obvious to many, but I stumbled across the solution (eventually).. Changing the while statement to a do statement did the trick: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); $i=0; do { $InvMonth[$i] = $monthly_row[BilledMonth]; $InvCount[$i] = $monthly_row[count]; echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] . /strongbr; ++$i; } while($monthly_row = mysql_fetch_array($monthly_result)); On Fri, 2002-01-11 at 11:20, Mike Gifford wrote: Hello, Thanks for your quick reply.. I'm trying to improve the stats feature for gcdb (a pretty decent little PHP/MySQL accounting package on SF.net) On Fri, 2002-01-11 at 05:24, DL Neil wrote: Have you posted all of the relevant code - for example, how the 'result' is limited to three month's worth of data??? I didn't provide all of the code in the initial response as it was using a wrapper so I didn't know how relevant it would be.. However, your note made me realize that I could rewrite the code without the wrapper.. It still worked the same way. The code stands as: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); while($monthly_row = mysql_fetch_array($monthly_result)) { ++$i; $InvMonth[$i] = $monthly_row[BilledMonth]; $InvCount[$i] = $monthly_row[count]; echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] . /strongbr; } RESULTS: Month: December Number of Invoices: 22 Month: November Number of Invoices: 17 Month: October Number of Invoices: 21 Have you extracted the SQL from the PHP and applied it directly to the command line or used it in a MySQL Management package? Was the result any different? Also a damn good idea (I haven't had root access to MySQL until recently, hadn't thought of that either) mysql SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; +---+-++ | count | BilledMonth | BilledYear | +---+-++ |15 | 1 | 2002 | |22 | 12 | 2001 | |17 | 11 | 2001 | |21 | 10 | 2001 | +---+-++ 4 rows in set (0.00 sec) Ok.. So the problem seems to be with my code.. $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); $i=0; while($monthly_row = mysql_fetch_array($monthly_result)) { $InvMonth[$i] = $monthly_row[BilledMonth]; $InvCount[$i] = $monthly_row[count]; echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] . /strongbr; ++$i; } RESULTS: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 I can't see the bug in the PHP I've got, but there certainly
[PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
Hello I've got the following SQL Query, which consistently pulls up only 3 out of 4 months from the database: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC; This results in: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 But when I do change the order of the query from ASC to DESC like this: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC;$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; I get: Month: December Number of Invoices: 22 Month: November Number of Invoices: 17 Month: October Number of Invoices: 21 (I gained October and Lost January) The date format in the DB is like this: 2001-12-05 I've tried a whole stack of variations on the above query, but I still seem to be coming up one short. Any idea why I'm not getting a display of all of the months? Thanks. Mike -- Mike Gifford, OpenConcept Consulting, http://www.openconcept.ca Supporting progressive organizations in online campaigns and tools. Feature: Women's Learning Partnership http://learningpartnership.org Truth is that which confirms what we already believe. Northrop Frye -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
Mike, Have you posted all of the relevant code - for example, how the 'result' is limited to three month's worth of data??? Have you extracted the SQL from the PHP and applied it directly to the command line or used it in a MySQL Management package? Was the result any different? Please advise, =dn - Original Message - From: Mike Gifford [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 11 January 2002 08:12 Subject: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently Hello I've got the following SQL Query, which consistently pulls up only 3 out of 4 months from the database: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC; This results in: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 But when I do change the order of the query from ASC to DESC like this: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC;$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; I get: Month: December Number of Invoices: 22 Month: November Number of Invoices: 17 Month: October Number of Invoices: 21 (I gained October and Lost January) The date format in the DB is like this: 2001-12-05 I've tried a whole stack of variations on the above query, but I still seem to be coming up one short. Any idea why I'm not getting a display of all of the months? Thanks. Mike -- Mike Gifford, OpenConcept Consulting, http://www.openconcept.ca Supporting progressive organizations in online campaigns and tools. Feature: Women's Learning Partnership http://learningpartnership.org Truth is that which confirms what we already believe. Northrop Frye -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
Hello, Thanks for your quick reply.. I'm trying to improve the stats feature for gcdb (a pretty decent little PHP/MySQL accounting package on SF.net) On Fri, 2002-01-11 at 05:24, DL Neil wrote: Have you posted all of the relevant code - for example, how the 'result' is limited to three month's worth of data??? I didn't provide all of the code in the initial response as it was using a wrapper so I didn't know how relevant it would be.. However, your note made me realize that I could rewrite the code without the wrapper.. It still worked the same way. The code stands as: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); while($monthly_row = mysql_fetch_array($monthly_result)) { ++$i; $InvMonth[$i] = $monthly_row[BilledMonth]; $InvCount[$i] = $monthly_row[count]; echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] . /strongbr; } RESULTS: Month: December Number of Invoices: 22 Month: November Number of Invoices: 17 Month: October Number of Invoices: 21 Have you extracted the SQL from the PHP and applied it directly to the command line or used it in a MySQL Management package? Was the result any different? Also a damn good idea (I haven't had root access to MySQL until recently, hadn't thought of that either) mysql SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; +---+-++ | count | BilledMonth | BilledYear | +---+-++ |15 | 1 | 2002 | |22 | 12 | 2001 | |17 | 11 | 2001 | |21 | 10 | 2001 | +---+-++ 4 rows in set (0.00 sec) Ok.. So the problem seems to be with my code.. $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); $i=0; while($monthly_row = mysql_fetch_array($monthly_result)) { $InvMonth[$i] = $monthly_row[BilledMonth]; $InvCount[$i] = $monthly_row[count]; echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] . /strongbr; ++$i; } RESULTS: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 I can't see the bug in the PHP I've got, but there certainly must be one.. Any suggestions would be appreciated! Mike - Original Message - From: Mike Gifford [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 11 January 2002 08:12 Subject: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently Hello I've got the following SQL Query, which consistently pulls up only 3 out of 4 months from the database: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC; This results in: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 But when I do change the order of the query from ASC to DESC like this: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC;$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; I get: Month: December Number of Invoices: 22 Month: November Number of Invoices: 17 Month: October Number of Invoices: 21 (I gained October and Lost January) The date format in the DB is like this: 2001-12-05 I've tried a whole stack of variations on the above query, but I still seem to be coming up one short. Any idea why I'm not getting a display of all of the months? Thanks. Mike -- Mike Gifford, OpenConcept Consulting, http://www.openconcept.ca Supporting progressive organizations in online campaigns and tools. Feature: Women's Learning Partnership http://learningpartnership.org Truth is that which confirms what we already believe. Northrop Frye -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- Mike
Re: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
On Saturday 12 January 2002 00:20, Mike Gifford wrote: Hello, Thanks for your quick reply.. I'm trying to improve the stats feature for gcdb (a pretty decent little PHP/MySQL accounting package on SF.net) On Fri, 2002-01-11 at 05:24, DL Neil wrote: Have you posted all of the relevant code - for example, how the 'result' is limited to three month's worth of data??? I didn't provide all of the code in the initial response as it was using a wrapper so I didn't know how relevant it would be.. However, your note made me realize that I could rewrite the code without the wrapper.. It still worked the same way. The code stands as: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); You've already used the first row of results (without processing it). -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* I'd love to go out with you, but I did my own thing and now I've got to undo it. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
It may have been obvious to many, but I stumbled across the solution (eventually).. Changing the while statement to a do statement did the trick: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); $i=0; do { $InvMonth[$i] = $monthly_row[BilledMonth]; $InvCount[$i] = $monthly_row[count]; echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] . /strongbr; ++$i; } while($monthly_row = mysql_fetch_array($monthly_result)); On Fri, 2002-01-11 at 11:20, Mike Gifford wrote: Hello, Thanks for your quick reply.. I'm trying to improve the stats feature for gcdb (a pretty decent little PHP/MySQL accounting package on SF.net) On Fri, 2002-01-11 at 05:24, DL Neil wrote: Have you posted all of the relevant code - for example, how the 'result' is limited to three month's worth of data??? I didn't provide all of the code in the initial response as it was using a wrapper so I didn't know how relevant it would be.. However, your note made me realize that I could rewrite the code without the wrapper.. It still worked the same way. The code stands as: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); while($monthly_row = mysql_fetch_array($monthly_result)) { ++$i; $InvMonth[$i] = $monthly_row[BilledMonth]; $InvCount[$i] = $monthly_row[count]; echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] . /strongbr; } RESULTS: Month: December Number of Invoices: 22 Month: November Number of Invoices: 17 Month: October Number of Invoices: 21 Have you extracted the SQL from the PHP and applied it directly to the command line or used it in a MySQL Management package? Was the result any different? Also a damn good idea (I haven't had root access to MySQL until recently, hadn't thought of that either) mysql SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; +---+-++ | count | BilledMonth | BilledYear | +---+-++ |15 | 1 | 2002 | |22 | 12 | 2001 | |17 | 11 | 2001 | |21 | 10 | 2001 | +---+-++ 4 rows in set (0.00 sec) Ok.. So the problem seems to be with my code.. $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC; $monthly_result = mysql_query($sql, $db); $monthly_row = DBfetch_array($monthly_result); $i=0; while($monthly_row = mysql_fetch_array($monthly_result)) { $InvMonth[$i] = $monthly_row[BilledMonth]; $InvCount[$i] = $monthly_row[count]; echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] . /strongbr; ++$i; } RESULTS: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 I can't see the bug in the PHP I've got, but there certainly must be one.. Any suggestions would be appreciated! Mike - Original Message - From: Mike Gifford [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 11 January 2002 08:12 Subject: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently Hello I've got the following SQL Query, which consistently pulls up only 3 out of 4 months from the database: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC; This results in: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 But when I do change the order of the query from ASC to DESC like this: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC;$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; I get: Month: December Number of Invoices
[PHP-DB] GROUP BY with a string column
Can the SUM keyword in SELECT be generalized to strings? A tipical use for the SUM keyword is: SELECT column1, SUM(column2) FROM table GROUP BY column1 This works only if column2 contains numbers (and SUM is the mathematical sum). What about obtain the same behaviour, when column2 contains strings (and SUM is string concatenation)? Anyone help me please? Thank you Mauro Boscarol http://www.boscarol.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] GROUP BY with a string column
On Mié 28 Nov 2001 19:42, you wrote: Can the SUM keyword in SELECT be generalized to strings? A tipical use for the SUM keyword is: SELECT column1, SUM(column2) FROM table GROUP BY column1 This works only if column2 contains numbers (and SUM is the mathematical sum). What about obtain the same behaviour, when column2 contains strings (and SUM is string concatenation)? With which database engine? -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Group By problem
I am trying to caregorize to group at the same time, but its not working. Any hints? Here's my code: $temp=Dummy; while ($row = mysql_fetch_array($result)) { if($temp !=$row[group_name]){ $temp=$row[group_name] ; echo bGroup:$tempbr/b; } $temp1=blah; if($temp1 !=$row[service_cat]){ $temp1=$row[service_cat]; echo Services:$temp1br; } $machine_name=$row[machine_name]; echo a href=view_server.php?machine_name=$machine_name$machine_name/abr; } This is displaying : Group:Group_Name Services:Email Machine_Name Services:Email Machine_name2 Services: Backup Machine_name Group:Group_name2 I dont want to print services multiple times. Just like the group name catagorize it. Thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Group By problem
- Original Message - From: Sharif Islam [EMAIL PROTECTED] To: Hugh Bothwell [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, August 13, 2001 11:50 AM Subject: Re: [PHP-DB] Re: Group By problem But its displaying the same as my code: The SQL was just for reference, to make sure we were both working on the same basis... I don't think you looked at the PHP code I gave, as it should produce exactly what you are asking for. Group:Desktop Service:BACKUP AITSBKUP Service:E-Mail AITSMAIL JEEVES Group:Unix Service:Database APOLLO Service:FIREWALL Console try defining style body { font-family: Verdana, Arial, sans-serif; } .group { font-weight: bold; font-size: 120%; color: #44; } .service { font-weight: bold; color: #77; } .machine { font-size: 80%; color: #77; } /style ?php function PrintNewGroup($grp) { echo br\nspan class='group'$grp/span; } function PrintNewService($svc) { echo br\n\tspan class='service'$nbsp;$svc/spanbr; } function PrintMach($mach, $first) { if ($first) echo nbsp;nbsp; else echo nbsp;|nbsp;; echo span class='machine'$mach/span; } ? $grp=; $svc=; $firstmach = true; while ($row=mysql_fetch_array($result)) { if ($grp != $row[grp]) { $grp = $row[grp]; PrintNewGroup($grp); $svc = ;// force new-svc when switching groups } if($svc != $row[svc]) { $svc = $row[svc]; PrintNewService($svc); $firstmach = true; } PrintMach($row[mach], $firstmach); $firstmach = false; } How's that? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]