[PHP-DB] Help! With MySQL CASE problem
Hello, all. I'm having a problem with a php/mysql app which is probably in the SQL, so please don't get angry at me. Basically, I have a CASE statement that works on my localhost, but doesn't seem to get recognised on the production server (i'm running php/mysql 5.x on my localhost, 4.x of both on the production server). Query looks like this: SELECT DISTINCT e.exhibition_id, e.title, e.begin_date, CASE 'heading' WHEN UNIX_TIMESTAMP( ) = e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END 'heading', e.end_date, special FROM exhibition e WHERE e.end_date = UNIX_TIMESTAMP() ORDER BY heading DESC , e.begin_date ASC On my localhost, the results look like this: exhibition_id - title - begin_date - heading - end_date - special 84 20/21 Vision1159599600 Now Showing 1161154800 1 85 David S 1161327600 Coming Up 1162972800 0 86 Yang H 1161327600 Coming Up 1162972800 0 but on the production server looks like this: 85 David S 1161327600 Coming Up 1162972800 0 84 20/21 Vision1159599600 Now Showing 1161154800 1 86 Yang H 1161327600 Coming Up 1162972800 0 I need it to sort like the localhost, and can't figure out what's happening. I can't seem to ORDER BY at all on the production server. Any ideas? I'm going nuts. Thanks, Andrew -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Help! With MySQL CASE problem
Good people of php-db, I think I have this solved. For those keeping score, repeating the CASE condition in the ORDER BY seems to work, i.e., SELECT DISTINCT e.exhibition_id, e.title, e.begin_date, CASE 'heading' WHEN UNIX_TIMESTAMP( ) = e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END 'heading', e.end_date, special FROM exhibition e WHERE e.end_date = UNIX_TIMESTAMP( ) ORDER BY CASE WHEN UNIX_TIMESTAMP( ) = e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END , e.begin_date ASC I don't know why, however. Thanks for your indulgence, Andrew On 10/15/06, Andrew Darby [EMAIL PROTECTED] wrote: Hello, all. I'm having a problem with a php/mysql app which is probably in the SQL, so please don't get angry at me. Basically, I have a CASE statement that works on my localhost, but doesn't seem to get recognised on the production server (i'm running php/mysql 5.x on my localhost, 4.x of both on the production server). Query looks like this: SELECT DISTINCT e.exhibition_id, e.title, e.begin_date, CASE 'heading' WHEN UNIX_TIMESTAMP( ) = e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END 'heading', e.end_date, special FROM exhibition e WHERE e.end_date = UNIX_TIMESTAMP() ORDER BY heading DESC , e.begin_date ASC On my localhost, the results look like this: exhibition_id - title - begin_date - heading - end_date - special 84 20/21 Vision1159599600 Now Showing 1161154800 1 85 David S 1161327600 Coming Up 1162972800 0 86 Yang H 1161327600 Coming Up 1162972800 0 but on the production server looks like this: 85 David S 1161327600 Coming Up 1162972800 0 84 20/21 Vision1159599600 Now Showing 1161154800 1 86 Yang H 1161327600 Coming Up 1162972800 0 I need it to sort like the localhost, and can't figure out what's happening. I can't seem to ORDER BY at all on the production server. Any ideas? I'm going nuts. Thanks, Andrew -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
See this query? I need a separate query that will return ONLY the total record count that it would come up with. I've tried replacing the select part with COUNT() but I still get a series of records in my return result. I just need ONE return value -- the total COUNT of rows returned by this query originally. SELECT e.id, e.title, e.subheading, eta.start_timestamp, eta.end_timestamp, e.zip, e.bold, e.outline, e.color, e.subheading, COUNT(esa.id) AS subcat_count FROM demo_event_time_assoc eta, demo_events e, demo_event_subcategory_assoc esa, demo_zip_codes z WHERE eta.event_id=e.id AND esa.event_id=e.id AND z.zip=e.zip AND e.active=1 AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10) AND ( (eta.start_timestamp = 1162281600 AND eta.start_timestamp = 1162368000) OR (eta.end_timestamp 1162281600 AND eta.end_timestamp 1162285200)) AND (3963.20477315*(2*asin(sqrt(POWER(sin((0.59533458956 -z.lat_radians)/2),2) + cos(0.59533458956)*cos(z.lat_radians)*POWER(sin((-2.06592416764 - z.long_radians)/2),2 50) GROUP BY eta.id ORDER BY subcat_count DESC, eta.id -- View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fairly-elaborate-query-tf2449123.html#a6826536 Sent from the Php - Database mailing list archive at Nabble.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
sneakyimp wrote: See this query? I need a separate query that will return ONLY the total record count that it would come up with. I've tried replacing the select part with COUNT() but I still get a series of records in my return result. I just need ONE return value -- the total COUNT of rows returned by this query originally. Doing this is actually rather easy. Replace this: SELECT e.id, e.title, e.subheading, eta.start_timestamp, eta.end_timestamp, e.zip, e.bold, e.outline, e.color, e.subheading, COUNT(esa.id) AS subcat_count With: SELECT COUNT(e.id) AS count Or am I completely missing the point? -- 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] Re: Help! With MySQL CASE problem
Andrew Darby wrote: Good people of php-db, I think I have this solved. For those keeping score, repeating the CASE condition in the ORDER BY seems to work, i.e., SELECT DISTINCT e.exhibition_id, e.title, e.begin_date, CASE 'heading' WHEN UNIX_TIMESTAMP( ) = e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END 'heading', e.end_date, special FROM exhibition e WHERE e.end_date = UNIX_TIMESTAMP( ) ORDER BY CASE WHEN UNIX_TIMESTAMP( ) = e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END , e.begin_date ASC I don't know why, however. I think that's an sql standard thing.. because the heading column is being made up (by the case statement) you can't use the alias in an order by or group by. -- 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] urgent: Trying to get COUNT for fairly elaborate query
sneakyimp wrote: chris smith-9 wrote: Doing this is actually rather easy. Replace this: SELECT e.id, e.title, e.subheading, eta.start_timestamp, eta.end_timestamp, e.zip, e.bold, e.outline, e.color, e.subheading, COUNT(esa.id) AS subcat_count With: SELECT COUNT(e.id) AS count Or am I completely missing the point? I've tried that. It doesn't work for two reasons: 1) the ORDER BY subcat_count in the original query would cause an error in the SQL 2) removing that ORDER BY clause to make valid SQL still results in a set of rows - one for each of the original rows - rather than a single COUNT value for the entire query. The values for COUNT range from 1 to 4 depending on how many subcategory ASSOC records (esa) are connected to a particular e.id. Ah - that would be the group by doing that. Removing those: GROUP BY eta.id ORDER BY subcat_count DESC, eta.id Does that get you what you want? If it gives you one result - make sure it's right. Change a few id's, make sure they match up to what your other query returns. -- 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] urgent: Trying to get COUNT for fairly elaborate query
chris smith-9 wrote: Ah - that would be the group by doing that. Removing those: GROUP BY eta.id ORDER BY subcat_count DESC, eta.id Does that get you what you want? If it gives you one result - make sure it's right. Change a few id's, make sure they match up to what your other query returns. I really appreciate your help by the way. I've also tried that. I do get a single value when I do that but the count is too high! Each of the esa associations gets counted separately rather than just once. The original COUNT and GROUP BY parts limit the number of records by aggregating all the esa records per event into a single count - which is what i want. -- View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fairly-elaborate-query-tf2449123.html#a6827251 Sent from the Php - Database mailing list archive at Nabble.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
sneakyimp wrote: chris smith-9 wrote: Ah - that would be the group by doing that. Removing those: GROUP BY eta.id ORDER BY subcat_count DESC, eta.id Does that get you what you want? If it gives you one result - make sure it's right. Change a few id's, make sure they match up to what your other query returns. I really appreciate your help by the way. I've also tried that. I do get a single value when I do that but the count is too high! Each of the esa associations gets counted separately rather than just once. The original COUNT and GROUP BY parts limit the number of records by aggregating all the esa records per event into a single count - which is what i want. I don't understand. What results do you get from the original query (just the 2-3 columns we need please) ? What does the new query (that doesn't work) give you? What do you want to get from the count query? -- 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] urgent: Trying to get COUNT for fairly elaborate query
The original query results (minus most of the fields but including the COUNT(esa.id) part) would look something like this: id title subcat_count 60 Another Halloween Party 4 50 Satan's Midnight October Bash 1 61 Halloween IPN Testing party 1 19 test 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 If I adjust the query, using only a COUNT(*) in the select part and leaving the GROUP BY stuff (which is necessary to avoid counting each ESA table association more than once) then I get a query like this: === SELECT COUNT(*) AS count FROM demo_event_time_assoc eta, demo_events e, demo_event_subcategory_assoc esa, demo_zip_codes z WHERE eta.event_id=e.id AND esa.event_id=e.id AND z.zip=e.zip AND e.active=1 AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10) AND ( (eta.start_timestamp = 1162281600 AND eta.start_timestamp = 1162368000) OR (eta.end_timestamp 1162281600 AND eta.end_timestamp 1162285200)) GROUP BY eta.id === which gives me this as a result: count 1 4 -- for this particular e.id, there are multiple entries in the ESA table 1 1 1 1 1 1 1 1 1 NOTE: there is one row in this result for each of the original rows. so the ROWCOUNT is the same and still correct at 11 rows. Ultimately, what I want is a query that returns ONLY THE ROW COUNT OF THE ORIGINAL QUERY. In this case, 11. If I remove the GROUP BY part of the query then i can get a single result...this is what i want, HOWEVER, the count doesn't match the number of rows in the original query. that one event that has 4 subcategories associated with is counted once for each subcategory association which means my count is too high be the extra 3 records. the query like this: === SELECT COUNT( * ) AS count FROM demo_event_time_assoc eta, demo_events e, demo_event_subcategory_assoc esa, demo_zip_codes z WHERE eta.event_id = e.id AND esa.event_id = e.id AND z.zip = e.zip AND e.active =1 AND esa.subcategory_id IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ) AND ( ( eta.start_timestamp =1162281600 AND eta.start_timestamp =1162368000 ) OR ( eta.end_timestamp 1162281600 AND eta.end_timestamp 1162285200 ) ) === returns this: count 14 -- View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fairly-elaborate-query-tf2449123.html#a6827498 Sent from the Php - Database mailing list archive at Nabble.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] month
Is there a slick and easy way to convert January to 01 ? (and February to 02, etc) Ron
Re: [PHP-DB] month
Ron Piggott (PHP) wrote: Is there a slick and easy way to convert January to 01 ? (and February to 02, etc) Ron You asked this on the general list and got a few responses. -- 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] month
I have completely missed it and need to try again.
Re: [PHP-DB] month
search the archive. On 10/16/06, Ron Piggott (PHP) [EMAIL PROTECTED] wrote: I have completely missed it and need to try again. -- GMail Rocks!!!
RE: [PHP-DB] month
use an array
$months = array('01'='January','02'='February'...);
$thisMonths = array_flip($months);
$month = $thisMonths['January'];
echo $month;
though its easier to just create the array the other way and the just
reference it
$months = array(''January''='01','February'='02...');
$month = $months[January'];
Bastien
From: Ron Piggott (PHP) [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: PHP DB php-db@lists.php.net
Subject: [PHP-DB] month
Date: Sun, 15 Oct 2006 22:50:48 -0400
Is there a slick and easy way to convert January to 01 ? (and February
to 02, etc) Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
