Page 1 of 1

Mutiple selection menu/search

Posted: Sat Apr 10, 2004 5:39 pm
by mishal
I'm storing values into database table in this way :

ROW1: user_id1 | 23,34,55,25
ROW2: user_id2 | 54,34,65
ROW3: user_id3 | 12

where these numbers are country codes stored in country field for certain members (user_id's) from a mutiple selection menu.

Now when i tried to search for members having those codes i used MySQL REGEXP for query, e.g.:

Code: Select all

while(...){
...
$pattern.="REGEXP('^".$country_codeї$i].",|,".$country_codeї$i].",|,."$country_codeї$i]."$|^."$country_codeї$]."$')";
..
}

$query="SELECT .. FROM .. WHERE ".$pattern;
Actually this will slow down the process and will produce a huge query specially when i have lot of values need to be searched on.
So.. how usually multiple selection menu values should be stored into a database and the best way to search for those stored values?.

Regards..

Mishal

Posted: Sat Apr 10, 2004 5:46 pm
by markl999
You could use MySQL's FIND_IN_SET function, eg
SELECT .. FROM .. WHERE FIND_IN_SET(ccodes, $country_code[$i])

(presumes ccodes is the name of your column holding the country codes)

If you look at http://dev.mysql.com/doc/mysql/en/String_functions.html it gives some examples to make it a bit more clearer than my example ;). Please post back if you're unsure and need an example.

Posted: Sat Apr 10, 2004 6:32 pm
by mishal
Oh ! great i didn't use it before :).. but in this case we always assume the same order, e.g. when we have 20,30,40 stored in a database then '20,25,30' or '20' or '20,40' will never match.
we can't gaurantee getting the same sequence of values from multiple selection menu with that stored in the field.

Posted: Sat Apr 10, 2004 6:58 pm
by markl999
Ah, you're quite right, i missed the fact that you might be searching for multiple country codes in a set. Your regex might be the right way to go, so if no-one else posts a solution before my brain unfluffs itself i'll take another look. :(

Posted: Tue Apr 13, 2004 7:09 am
by mishal
Hi again ,,,
Just want to refresh this issue and make it more clear
the following is a sample portion taken from my code :

Code: Select all

<?php
.
...
....
.....
## Construct country query
//.................................................................

        if(is_array($c_code))// $c_code is an array that holds country codes e.g. : 25,44,85,47
        {
        	$array_size=sizeof($c_code);
            if($array_size>5);// die("Maximum criteria reached");
	        for($i = 0; $i < $array_size - 1 ; $i++){
            $RES.=" ccode_field REGEXP '^".$c_code[$i].",|,".$c_code[$i].",|^".$c_code[$i]."$|,".$c_code[$i]."$' or";
	        }
        $RES.=" ccode_field REGEXP '^".$c_code[$array_size-1].",|,".$c_code[$array_size-1].",|^".$c_reside[$array_size-1]."$|,".$c_code[$array_size-1]."$'";


        }else{
        $RES.=" ccode_field REGEXP '^".$c_code.",|,".$c_code.",|^".$c_code."$|,".$c_code."$'";
        }
//.................................................................

## Construct country query
$Query="SELECT DISTINCT(user_id) WHERE ".$RES;
$Results=mysql_query($Query);
//.................................................................
?>
Note :
$Query value will looks like "SELECT DISTINCT(user_id) WHERE ccode_field REGEXP '^22,|,22,|^22$|,22$' AND ccode_field REGEXP '^35,|,35,|^35$|,35$' AND ccode_field REGEXP '^44,|,44,|^44$|,44$' ...."

as we can see, $Query will produce a long query terms that might slow down searching process.

So again, is there such a way to speed up my searching process and if there are any alternatives for doing the same gool in effecient way.

Regards ..

Mishal

Posted: Tue Apr 13, 2004 5:12 pm
by mishal
if somebody saw such script doing the same then please let me know :)

Thanks for who tried to help me.