[PHP-DB] GROUP BY

2011-05-10 Thread Ron Piggott 

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

2009-12-09 Thread Philip Thompson 
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

2009-12-09 Thread Julio Araya 
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

2009-12-09 Thread Juan Pablo Ramirez 
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

2009-12-09 Thread Barry Stear 
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

2009-12-09 Thread h...@deweywilliams.com 
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

2009-12-09 Thread Philip Thompson 
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

2009-12-09 Thread Chris 

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

2009-08-17 Thread kranthi 
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

2009-08-17 Thread Ron Piggott 

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

2009-08-16 Thread Ron Piggott 
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

2009-08-16 Thread Chris 

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

2005-06-30 Thread Ross Honniball 
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

2005-06-29 Thread Larry Sandwick 
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

2005-05-24 Thread blackwater dev 
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

2005-01-23 Thread Bastien Koert 
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,

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
--
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

2005-01-23 Thread Chris Payne 
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

2005-01-23 Thread Jochem Maas 
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

2005-01-23 Thread Bastien Koert 
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,



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.

--
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

2005-01-22 Thread Chris Payne 
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

2005-01-22 Thread tg-php 
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

2004-03-08 Thread Aleks @ USA.net 
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

2003-03-16 Thread Bob Hall 
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

2003-03-15 Thread Daniel Harik 
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)

2002-10-25 Thread joakim . andersson 
 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)

2002-10-24 Thread Noodle Snacks 
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

2002-01-12 Thread DL Neil 

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

2002-01-11 Thread Mike Gifford 

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

2002-01-11 Thread DL Neil 

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

2002-01-11 Thread Mike Gifford 

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

2002-01-11 Thread Jason Wong 

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

2002-01-11 Thread Mike Gifford 

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

2001-11-28 Thread Mauro Boscarol 

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

2001-11-28 Thread Martín Marqués 

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

2001-08-13 Thread Sharif Islam 

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

2001-08-13 Thread Hugh Bothwell 


- 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]