Re: Natural order of things.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 27/04/06, tedd <tedd@xxxxxxxxxxxx> wrote:
> >On Wed, 2006-04-26 at 16:57, Robert Cummings wrote:
> >>  On Wed, 2006-04-26 at 16:51, tedd wrote:
> >>  > Hi gang:
> >>  >
> >>  > I posted the following question to the MySQL list, but the only
> >>  > answer I received thus far was a php solution (it didn't work for
> >>  > what I wanted).
> >>  >
> >>  > As such, maybe if I post a MySQL question to the PHP group, then I'll
> >>  > receive a MySQL answer -- so here goes:
> >>  >
> >>  > I'm using the following query, and it works.
> >>  >
> >>  > SELECT id, title, url_image_small
> >>  > FROM $dbtable
> >>  > WHERE type="type_title"
> >>  > ORDER BY title
> >>  > LIMIT $offset, $rowsPerPage"
> >  > >
> >-snip-
> >  > You need ot perform a type conversion from string to integer.
> >
> >Sorry, just realized... "Basel Square" is part of the entry in the table
> >:/ Do you have entries that aren't prefixed with Basel Square?
> >
> >Cheers,
> >Rob.
>
> Rob:
>
> Yes, there are other titles, such as:
>
> Celtic Deco 1
> Lucerne 1
> Dutch Hearth 1
>

Yeah, two items of information in the same column's not really a good
idea, which is why you're finding it so tricky.

So what you can do is this:

SELECT title,
   LEFT(title, LENGTH(title) - LENGTH(SUBSTRING_INDEX(title, ' ', 
-1))) AS name,
   CAST(SUBSTRING_INDEX(title, ' ',  -1) AS UNSIGNED) AS number
FROM $dbtable
ORDER BY name, number;

So that's finding the position of the last space and putting
everything up to that in `name`. Then it's taking everything after
that, turning it into an integer and putting it in `number`.

Then the ORDER BY works as you'd like because it's sorting on an
actual number rather than a string representation of a number.

If you'd had the name and the number in separate columns in the first
place it would be so much simpler.

  -robin

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux