ordering results in php

[sticks]

Hi. I'm pretty new to all of this so please bear with me

We have a database at http://www.familytravel.co.nz whereby holidays are displayed as per region. The code for displaying such holidays is:

<a href="availableproduct.php?region=fiji"> 

Region is one of the fields in our database. Now here comes my problem. We have been asked to order the holidays for one region - i.e. Fiji, so that when they are displayed anything to do with Mana Island comes up first. Mana Island is not a region on its own and therefore is not a field. It will only be text in the title and/or description fields. Is this at all possible or no because it is not a region.

Hope this makes sense.

Sheryll - php real newbie.

[dswimboy]

well, with some php, and mostly just mySQL expiernce i see two options.

1) create a new field in your database that ranks the importance to Fiji. Mana islands would be 1, and other islands would decrease. then structure your query to sort the importance field ascending (i think)

this may not scale well, if you are planning to rate every region, as you would have to create a new field for every region

2) you could also split your query into seperate parts. the first one would only extract rows that contain 'mana islands' in the title. out put these results. make another query that extracts rows that doesn't contain 'mana islands' and out put those results.

these may not be the best ways to fix your problem. but i currently like perl better than php (learned it first) and am stubburnly learning new ways. Maybe someone else has a better idea. for in perl, the number 1 rule is "there is more than 1 way to do things"

[Spookster]

You really only need SQL to accomplish this. If you want only certain data then select only that data and then order it how you want it. Something like so.

SELECT * FROM tablename WHERE region = "fiji" AND otherfield LIKE "mana" ORDER BY region, otherfield

This is more of a MySQL question so I will move this to the MySQL forum.

[fj_sailor]

A major question having to do with the nature of the data is: How will you determine what rows have to do with Mana Island?

If indeed the answer is that the string Mana is always found in one field, using a like statement on that field in your where clause may be advisable. If that field is one on which you can use the "like" reserved word. If you can use the "like", it would be wise to pad it with the multi-character wild-card '%' on both sides.

Ordering the result with that column as the second sort column would not work, because the order by statement will order text starting at the first character of the string, not by where the word Mana exist.

Also assuming that you can use a where clause to determine that Mana is in your row, it would probably be necessary to write an sql statement which is a union of those rows which do have Mana with those which do not have Mana. In the result set for these you can hard code another column and then include that value in your order by statement. You will find pseudo code below.

select a, b, c, d , 1 as fabricated from basetable
where c like "%Mana" and whatever
union
select a, b, c, d , 2 as fabricated from basetable
where c not like "%Mana" and whatever
order by a, fabricated

This approach should work, but depending on the size of the table, it could be a real performance problem.

[raf]

fj_sailor is right about the where-clause, wildcard and orderingproblem.

But i don't think that the proposed solution (using a UNION) is the best option. You should use an if() function in your fieldlist, and as a condition inthere, search for the 'mana 'or whatever (check if it is a substring of the variable you want to search against, i.e. titlevariable)

Like

SELECT IF(LOCATE('mana',titlevariable),1,0) AS isincluded, a,b,c,d FROM table ORDER BY isincluded DESC

Of course, you could add in a where clause to only get the matched records or do filtering on other records + it would be wise to include a LIMIT clause if you expect a lott of matches.

Or you could concider a fulltext search --> not search if the fields contain this value, but do a 'natural language' search

[fj_sailor]

Mana sql problem

My apologies to the forum. My like clause should have Mana enclosed in % thusly: '%Mana%', the way I originally had it would have only found rows where Mana was the last four characters in the column.

I would like to know more about the if() function. In the statement where it was used, I saw no way to order the Mana information where it could be put at the top of the list. (Please, help me see it if it is there.)

By using the union, we are able to create a new column "fabricated" in the result set which can be sorted so that the Mana rows can be displayed together.

It seems that the statement using the if() would only retrieve the rows having to do with Mana, and another statement would have to be written to retrieve the other fiji rows. This would add unnecessary logic to the program. Using the union would allow one cursor to be returned in the order needed instead of returning two cursors and having to program to merge the information in the two cursors into one result set.

[raf]

I would like to know more about the if() function. In the statement where it was used, I saw no way to order the Mana information where it could be put at the top of the list. (Please, help me see it if it is there.)

It is there. Try it out
Just replace the tablename and titlevariable by tablename and columnname of any of your table. Then replace the 'mana' by whatever valeu ('a' for instance, if it's a string-variable) Like

SELECT IF(LOCATE('a',yourcolumn),1,0) AS isincluded, anothercolumn FROM table ORDER BY isincluded DESC

(replace the italics an make sure the 'a' or whateve value is a substring of one of the values)

It seems that the statement using the if() would only retrieve the rows having to do with Mana, and another statement would have to be written to retrieve the other fiji rows. This would add unnecessary logic to the program.

Absolutely not.
The statement returns ALL records. The
IF(LOCATE('a',yourcolumn),1,0) AS isincluded
also creates an extra variable (names 'isincluded' here). Records, where yourcolumn contains the 'a' will have value 1 for isincluded. The others will have '0' inside isincluded. So by ordering on 'isincluded' in descending order, the records that contain the substing ('a') are shown at the top.
This is due to the IF-function

http://www.mysql.com/doc/en/Control_flow_functions.html

The LOCATE('a',yourcolumn)

http://www.mysql.com/doc/en/String_functions.html

is the condition that is evaluated inside the IF --> if this condition returns True, then the if-function returns '1'. If the condition doesn't return True, then the IF functin returns 0
The Locate searched if the substring (first argument) occurs in the string 'second argument). So it returns true on exaclty the same records as "... WHERE yourcolumn LIKE '%a%' "
The biggest diferences compaired to the where-clause are
- that you now get all rows
- that you have an extra column which indicates if the substring is part of the field

By using the union, we are able to create a new column "fabricated" in the result set which can be sorted so that the Mana rows can be displayed together.

I do exactly the same, and i can return identicalyy the same recordset as you can.

This [the if(), raf]] would add unnecessary logic to the program. Using the union would allow one cursor to be returned in the order needed instead of returning two cursors and having to program to merge the information in the two cursors into one result set.

I don't need server side logic. I don't need to merge. Returning two cursors

My method is simple better, because;
- the RDBM only needs to loop once through the table, so all records only need to be evaluated once. With the union, the table is sequentialy searched twice. So my sollution is more performant
- a union on recorsets isn't realy fast and memory eating. a union from the same table is probably NEVER necessary. And certainly not here, as i've shown

Post back if you need more info, and keep up the good work here.

[fj_sailor]

Thank you. Your reply was quite enlightening. While I do have extensive experience in sql most of it is from my days working with DB2 in version 6 and earlier. The if() is total new to me. I can see if MySql forces two passes of the database how this would be a performance problem. Earlier forms of DB2 also had to make two passes.

So much I know already!!!! maybe .0000000001% of what there is to know.

Thanks