SQL query

[rich w]

Yep, i'm back again with yet another query problem:


SELECT `hikeid` FROM `hiking_daydetails` WHERE `dayid`='1' AND `legid`='1' AND `currentgridreference` LIKE 'ST%' || 'SP%' || 'TQ%' || 'NY%' || 'TL%' || 'TR%' || 'SJ%' || 'SU%' || 'SK%' || 'NZ%' || 'SX%' || 'SO%' || '%TA%' || 'SD%' || 'SE%' || 'TG%' || 'SZ%' || 'SW%'


It doesn't return any results, however, there are over 50 records which should match the above criteria..what am I doing wrong?! I guess its something to do with the LIKE and ORs...

Btw I can't use fulltext as its searching less than 3 characters..

Can anyone help?

[Christopher Lee]

Just "Blue Skying" an idea, but you could run REGEXP on it instead of the ORs.

[Christopher Lee]

Another way: since you are simply comparing the first two letters in the field, you could SUBSTRING those out and then compare those against a set you create.


SELECT `hikeid`
FROM `hiking_daydetails`
WHERE `dayid`='1'
AND `legid`='1'
AND SUBSTRING(`currentgridreference` FROM 1 FOR 2) IN
('ST', 'SP', 'TQ', 'NY', 'TL', 'TR', 'SJ', 'SU', 'SK', 'NZ', 'SX', 'SO', 'TA', 'SD', 'SE', 'TG', 'SZ', 'SW')

[rich w]

That second one worked spot on first time :D Thanks!

Chris - I'm trying to stay clear of regexp as much as I can! :lol:

Right next question...this one is a bit more logical and can just be the structure or in psuedo code, as I can convert it to PHP..

I've got 5 arrays:
$countryresults
$cityresults
$noofdaysresults
$distanceresults
$ageresults

Each of these contains a bunch of ids. E.g.

Array ( [0] => 26 [1] => 80 [2] => 38 [3] => 69 [4] => 65 [5] => 73 [6] => 103 [7] => 113 [8] => 114 [9] => 115 [10] => 116 )

Some of these arrays may not be set, so for example, only one array could have data in it. Other times all 5 arrays could contain data.

This is how the data is sorted in each array:
$countryresults, $cityresults and $ageresults contain IDs which meet the search criteria exactly, and are ordered by ID
$noofdaysresults contains results which may or may not have met the exact search criteria and are ordered by closest value top to bottom
$distanceresults contains results which may or may not have met the exact search criteria and are be ordered by closest value top to bottom.Now basically what I want to do is to return an array '$finalresults' which contains IDs based on the following rules:
Any array may or may not be set
The 30 best match results should be stored in the array '$finalresults' and should be sorted by order of relevance.I really hope someone can help about the structure of how to calculate this final array!

Cheers

[Christopher Lee]

I think that you don't have enough information to calculate the final array. All you are storing in these arrays are IDs. So let's look at the arrays that may or may not match the exact search criteria. Let's say for the first elements of each of the arrays look something like this:

$noofdaysresults[0] = 45
$distanceresults[0] = 67

Out of that discreet information given, there is no way of knowing how to order it, because there is no sort of relevancy associated with that data. Does $noofdaysresults[0] match the search criteria 100%, while $distanceresults[0] only matches 37.85%? Or does $noofdaysresults[0] only match 5% while $distanceresults[0] matches 100% or 85%, or any other percentage.

In the vague arrays, you would really need to capture the relevance somehow to be able to sort on it. Edit: I started to work on something, but that might drill to deep in implementation, so I erased it. Let's see if you get where I'm coming from before we proceed further.

[rich w]

Based on your ideas, i've been programming madly for the last 45 minutes, and I think i've actually sussed it :)

I assigned each criteria a certain number of points. The more relevant the criteria was, the more points it was assigned. This also solved the problem of relevancy for the criteria which may or may not be met..

Anyway I took these points, converted it to %, and then reordered the final array based on that value. Just gotta build the return list now...

Thanks for your time and ideas! I'll post a link as soon as this is up and running :)

[Christopher Lee]

I'm glad you nailed it. Let us know how it progresses. :)