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
