Mutiple selection menu/search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mishal
Forum Newbie
Posts: 12
Joined: Sat Apr 10, 2004 4:23 pm

Mutiple selection menu/search

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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.
mishal
Forum Newbie
Posts: 12
Joined: Sat Apr 10, 2004 4:23 pm

Post 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.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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. :(
mishal
Forum Newbie
Posts: 12
Joined: Sat Apr 10, 2004 4:23 pm

Post 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
mishal
Forum Newbie
Posts: 12
Joined: Sat Apr 10, 2004 4:23 pm

Post by mishal »

if somebody saw such script doing the same then please let me know :)

Thanks for who tried to help me.
Post Reply