|
|
Posted by muldoonaz on 08/24/05 21:26
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.
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?
Navigation:
[Reply to this message]
|