ordering by name, possible?

[jake66]

I am not sure how to word this properly.. but here's what I am trying to do:

- organize database output based upon words combined

like so:

Sparky Blue Widget
Black n Blue Widget
Green Widget with Wings
Purple Widget that Smells
Black Widget with Smells

anything that has the same/similar last word, should be grouped.

so instead of the current output as placed above, it would be:
Black n Blue Widget
Sparky Blue Widget

Black Widget with Smells
Purple Widget that Smells

Green Widget with Wings

is this possible with php / mysql?

[justageek]

You can do it but it not always easy. If you use something like:

select * from db order by substring_index(`db`.`column`,' ',-1)

That will give you what you want.

The problem though is that it may take a long time if you have a lot of records. If we could use the substring_index to create an index it would be fast but we can't. So, if you have a lot of records then use the substring_index to populate another field at insert time that holds the last word and then index that field and do the sort it should be fast and give you what you want.

JAG

[sabai]

Or you could do it in PHP... assuming you have selected what you want from the DB. Write a function that compares two strings and use the "usort" php function. This will do what you want... look up usort in the PHP manual to see how to go about doing this.

[jake66]

thank you both for the suggestions.

i've tried usort, but it was a bit above my head.
i took a stab at justageek's suggestion and got it to work :)

dealing with the same question.. now that i have my ouput sorted the way i wanted it, is it possible to insert a break or:

<br>

between each different sort term (the word that defines what's being sorted)?

for instance.. presently my list looks like this:
Black n Blue Widget
Sparky Blue Widget
Black Widget with Smells
Purple Widget that Smells
Green Widget with Wings

can i make it output like:
Black n Blue Widget
Sparky Blue Widget
--break here--
Black Widget with Smells
Purple Widget that Smells
--break here--
Green Widget with Wings

[justageek]

If you have the last word in its own column then just add a condition in your loop. Something like:

$current_word = false;
$data_output = "";
while($row = mysql_fetch_assoc($rs)){
if($current_word and ($row['word'] <> $current_word)){
$data_output .= "<br>";
$current_word = $row['word'];
}
do the rest of what you have in the loop now
}

JAG

[jake66]

thank you everybody! :)

now i would like to do the same thing, but order the results AFTER a -
(dash)

i don't think it's feasible to do a count by characters, like the script mentioned in this topic, because all of my results have varying character counts, but the majority that i want to sort have similar titles, after the -

is this possible?

[jake66]

is it possible to combine this and an explode() function? if so, how could i structure the command?