Posted by Stefan Rybacki on 08/24/05 22:03

muldoonaz wrote:
> Stefan Rybacki wrote:
>
>> muldoonaz wrote:
>>
>>> For those of you who braved looking at this thread, i commend thee.
>>> out of all the PHP and MySQL work I've done, REGEXP is purely chinese
>>> to me. Thus I seek all of you out there with knowledge about REGEXP
>>> that is far greater than mine.
>>>
>>> I have a database with comma separated values (ie. 2,3,4,5 [note, no
>>> leading or trailing commas]) that cross-reference to another table's
>>> ID's. I need a way to send a SQL query to the db to search within the
>>> comma separated values and find whichever entries match the search.
>>>
>>> Mind you, LIKE doesn't work (at least I haven't been able to get it to
>>> work). For instance, LIKE %4% will pull up entries with 4, but also
>>> with 44, 444, 4444, 14, 24, you get the idea. Thus REGEXP seems the
>>> way to go.
>>
>>
>>
>> Nope, the way to go is to repair you database design. Are you able to
>> change it?
>>
>> Stefan
>>
>>
>>> If anyone out there can help me by providing me information on building
>>> a REGEXP string to do what I need it to, I would be forever grateful!
>>>
>
> Well, I wrote a snippet of php that used the LIKE clause in its SQL
> queries to pull 4, 44, 14, 24, etc. Then as the results came back it
> verified that the ID that was queried (in this case, 4) was actually the
> result being returned... otherwise it dumped the result. I might put
> this back into place, but that'll require me to use LIKE again.. which
> isn't very friendly when you're searching for 10-15 variables within a
> comma separated string.
>
> Maybe this should be the better route. I really don't know how else I
> would design the database and this is the first time someone has
> requested search functionality at a level this is at.
>
> The verification php code goes a little something like this:
>
> function validateResults($queried,$results) {
> if (eregi(",", $results)) {
> $idexploded = explode(",", $results);
> if (eregi(",", $queried)) {
> $queriedexploded = explode(",", $queried);
> foreach($queriedexploded as $qe) {
> if (in_array($qe, $idexploded)) $return = 1;
> else $return = 0;
> }
> }
> else {
> if (in_array($queried,$idexploded)) $return = 1;
> else $return = 0;
> }
> }
> else {
> if (eregi(",", $queried)) {
> $queriedexploded = explode(",", $queried);
> if (in_array($results, $queriedexploded)) { $return = 1; }
> else { $return = 0; }
> }
> else {
> if ($results == $queried) $return = 1;
> else $return = 0;
> }
> }
> return $return;
> }
>
>
> basically you feed the function the variables you're looking for (can be
> comma separated) for $queried, and then feed the id's from the
> results... it'll compare the two and return a 1 if it matches, or a 0 if
> it doesn't. 0 will prevent the result from being displayed on the page,
> and 1 will parse it out onto the page.

What does match mean? Do you want to match all $idexploded to match all in
$queriedexploded? Or just one of $queriedexploded have to match $idexploded?

Shorter Version:

case 1:

function validateResults($queried,$results) {
//maybe array_unique is not necessary because you already have distinct ids from
//the database
$idexploded=array_unique(explode(",",$results));
$queriedexploded = array_unique(explode(",", $queried));

$array=array_intersect($idexploded,$queriedexploded);

return (count($array)==count($queriedexploded))?1:0
}

case 2:

function validateResults($queried,$results) {
//maybe array_unique is not necessary because you already have distinct ids from
//the database
$idexploded=array_unique(explode(",",$results));
$queriedexploded = array_unique(explode(",", $queried));

$array=array_intersect($idexploded,$queriedexploded);

return (count($array)>0)?1:0
}

Nevertheless I still think your database design is broken and you should repair it very soon.


Stefan

>
> pretty simple workaround, I was just trying to avoid using it since it
> tags an extra 1/10th of a second to the script. I'm sure there are ways
> to clean up the code a bit but im pressed for time right now.
>
> Any comments?

[Back to original message]