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]
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
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] php-mysql-and the elusive eval statement....
I would think that you can't use '' special tags in the eval
function (at least use only one of it). Try this
?hi
But I think that what will is that it will stop execution on the '?>' sign
(end of PHP code) and print out an error like 'missing closing quote' or such.
On Thursday 01 November 2001 23:05, you wrote:
> I'm having a php/mysql problem using the eval statement. what I'm done is
> created a template that is database driven
> ie. the document is setup so that all of the head, leftside, body, right
> and bottom are all text comming from database query returns.
>
> all database returns are valid tested code. all peices have been tested in
> their own pages and shown to work.
>
> inside the text of the returns supposedly can be php code. using the eval
> statment i can get the html code to return no problem(page works
> correctly). when the code has php code in it it doesn't seem to work.
>
> using the eval statement I should be able to do something like
>
> eval ("?>$string") and it should evaluate the the code within thus when it
> comes to a
> somehow it doesn't.
> here's a small snipit of the code.
>
> // **
> // Header of Document
> // **
> eval ("?>$top");
> ?>
>
>
>
>
>
>
>
>
>
>
>
> the inside code just goes out and grabs a counter value, updates it +1 and
> returns the original counter val.
>
> I get nothing(well I get a space, where somehthing is suppose to be
> returned). the code inside works also as independently in a page by
> itself it'll return valid counter information and does the update.
>
> I'm about ready to pull my hair out, (good thing my wife keeps it about 1/2
> inch long, heh heh). tie all of my computers together with a big rope and
> use them as boat anchors. (I don't even have a boat, so I'll just have
> to throw them in)
>
> so any help avoiding this costly situation would be appreciated thanks
> in advance
>
>
> R
> ---
> Never, Never buy beer! Make it! It tastes better!!
>
> Woke up this mornin' an' I had myself a beer.
> Yea, Ah woke up this mornin' an' I had myself a beer.
> The futures' uncertain and the end is always near.
> -Jim Morrison, "Roadhouse Blues"
>
> Fill with mingled cream and amber,
> I will drain that glass again.
> Such hilarious visions clamber
> Through the chambers of my brain.
> Quaintest thoughts--queerest fancies,
> Come to life and fade away:
> I am drinking ale today.
> --Edgar Allan Poe
--
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]
