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
On Thu, Nov 01, 2001 at 04:43:00AM -0500, Ian Evans wrote: > 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 > I would do this in the php layer. -- 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
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
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
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, there was an typing error 'from Title' should be 'from Titles'. Anyway it's working for me. What error message it produce? On Thursday 01 November 2001 05:09, you wrote: > Petr Tuma wrote: > > select *,(case when Title like 'The %' then substring (Title, 5, 255) > > > > ETC. > > Alas, MySQL doesn't like that syntax either. :( -- 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]
Re: [PHP-DB] MySQL: Alphabetizing title results in library format
Hi, I would assume that MySQL doesn't support functions in the 'order by' clause. Try this: 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 Title order by sort_col (I'm not sure about the "case" function syntax :( On Wednesday 31 October 2001 23:42, you wrote: > Back when I was working with MSSQL I need to alphabetize movie titles in > the proper library format where 'A' 'An" and 'The" are ignored. > > For MSSQL I was told to use: > select * > from Titles > order by 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 > > That worked like a charm, yet MySQL doesn't seem to accept that. Any > solutions or advice? -- 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]
