Re: [PHP-DB] MySQL: Alphabetizing title results in library format
Petr Tuma wrote: What error message it produce? Ok, table titles is composed of: +--+-++ | titlesid | releaseyear | title | +--+-++ mysql select *,(case when title like 'The %' then substring (title, 5, 255) when title like 'A %' then substring (title, 3, 255) when title like 'An %' then substring (title, 4, 255) else title end) as sort_col from titles order by sort_col; ERROR 1064: You have an error in your SQL syntax near 'when title like 'The %' then substring (title, 5, 255) when title like 'A %' the' at line 1 -- Ian Evans Digital Hit Entertainment - News and Information http://www.digitalhit.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] MySQL: Alphabetizing title results in library format
Hi Ian, Try this: mysql select *,(case when title like '%The%' then substring (title, 5, 255) when title like '%A%' then substring (title, 3, 255) when title like '%An%' then substring (title, 4, 255) else title end) as sort_col from titles order by sort_col; Hope it helps, Cami -Original Message- From: Ian Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 9:20 AM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySQL: Alphabetizing title results in library format Petr Tuma wrote: What error message it produce? Ok, table titles is composed of: +--+-++ | titlesid | releaseyear | title | +--+-++ mysql select *,(case when title like 'The %' then substring (title, 5, 255) when title like 'A %' then substring (title, 3, 255) when title like 'An %' then substring (title, 4, 255) else title end) as sort_col from titles order by sort_col; ERROR 1064: You have an error in your SQL syntax near 'when title like 'The %' then substring (title, 5, 255) when title like 'A %' the' at line 1 -- Ian Evans Digital Hit Entertainment - News and Information http://www.digitalhit.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] -- 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] MySQL: Alphabetizing title results in library format
Cami wrote: Try this: mysql select *,(case when title like '%The%' then substring (title, 5, 255) when title like '%A%' then substring (title, 3, 255) when title like '%An%' then substring (title, 4, 255) else title end) as sort_col from titles order by sort_col; Argh, that still produces the same syntax error. Our hosting company is using MySQL 3.22.32 if that's any help. -- Ian Evans Digital Hit Entertainment - News and Information http://www.digitalhit.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] MySQL: Alphabetizing title results in library format
Have you tried it like this: mysql select case when title like '%The %' then substring (title, 5, 255) when title like '%A %' then substring (title, 3, 255) when title like '%An %' then substring (title, 4, 255) else title end as sort_col from titles order by sort_col; I can't tested, but that's what the manual say. Cami -Original Message- From: Ian Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 9:20 AM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySQL: Alphabetizing title results in library format Petr Tuma wrote: What error message it produce? Ok, table titles is composed of: +--+-++ | titlesid | releaseyear | title | +--+-++ mysql select *,(case when title like 'The %' then substring (title, 5, 255) when title like 'A %' then substring (title, 3, 255) when title like 'An %' then substring (title, 4, 255) else title end) as sort_col from titles order by sort_col; ERROR 1064: You have an error in your SQL syntax near 'when title like 'The %' then substring (title, 5, 255) when title like 'A %' the' at line 1 -- Ian Evans Digital Hit Entertainment - News and Information http://www.digitalhit.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] -- 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] MySQL: Alphabetizing title results in library format
Hi, seems a bit strange. Try this select (use mysql client) mysql select title,if(title like 'The %','yes','no') from titles; if it works try substring functions and if that works try 'order by' clause. It's at least working on server v. 3.23, so in case it will not work find out in the 'list of changes' (for version 3.23) if this feature is not new. In such case tell whoever to upgrade. :( I can't find any error in your query (and at least at 3.23 it's working!) Regards On Thursday 01 November 2001 10:19, you wrote: Petr Tuma wrote: What error message it produce? Ok, table titles is composed of: +--+-++ | titlesid | releaseyear | title | +--+-++ mysql select *,(case when title like 'The %' then substring (title, 5, 255) when title like 'A %' then substring (title, 3, 255) when title like 'An %' then substring (title, 4, 255) else title end) as sort_col from titles order by sort_col; ERROR 1064: You have an error in your SQL syntax near 'when title like 'The %' then substring (title, 5, 255) when title like 'A %' the' at line 1 -- Petr Tuma IT Manager, M.Ward Manufacturing -- 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] MySQL: Alphabetizing title results in library format
Petr Tuma wrote: select *,(case when Title like 'The %' then substring (Title, 5, 255) ETC. Alas, MySQL doesn't like that syntax either. :( -- Ian Evans Digital Hit Entertainment - News and Information http://www.digitalhit.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]
