FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

SQL Problem Help

   Frihost Forum Index -> Scripting -> Php and MySQL
 


AOP Web Development
Hi, i would like to ask how to search the query on a 1 line string in that if found any word in the field will be display the result. for ex.
Code:


$String = "The quick brown fox jumped over the lazy cat! lolz";

#Then let say i have data in the fields Reaction

#Reaction Field Data
# 1. dog
# 2. zebra
# 3. monkey
# 4. cat
# 5. goat



so this is what i want to do, if my i query a on the reaction table checking whether any values on the reaction field match on the string will return the field value.

if i query this $sql ="SELECT * FROM REACTTABLE WHERE reactact field like ='$string'"
something like that but i want to show ouput the return field match which is cat

Is there anyone can help me thanks a lot Smile
alalex
well the easiest way (not the fastest though, but the time difference is little) is the following:

    1- Take out any characters that are not alfanumeric
    2- Explode the string into each of the words
    3- Use a loop to query each of the words in the sentence


An alternative way to do this, and maybe faster for small tables would be to create an array containing all values in the database and then loop through the words array checking if they are also in the query array...

I'll put the code below (check it for errors, cause im doing it here without any checking,,,)

-Using one query per word:
Code:
$search = preg_replace('/\s+/', ' ', $string); //replace weird characters with white space
$keywords = explode(" ", $search); //create array
$keywords = array_diff($keywords, array("")); //remove white space
for ($i=0; $i<count($keywords); $i++) {
     $query = 'SELECT * FROM `table` WHERE `field` LIKE \'%'.$keywords[$i].'%\' LIMIT 0,30';
     $result = mysql_query($query);
     $num_rows = mysql_numrows($result);
     if($num_rows > 0){
           echo $keywords[$i].' matched the query';
     }
}


And now the other method:
Code:
$search = preg_replace('/\s+/', ' ', $string); //replace weird characters with white space
$keywords = explode(" ", $search); //create array
$keywords = array_diff($keywords, array("")); //remove white space
$query = 'SELECT * FROM `table`';
$result = mysql_query($query);
if(mysql_numrows($result)>0){
     $reaction = array();
     for($i=0; $i<$num_rows; $i++){ //loop through results
           array_push($reaction,mysql_result($result,$i,'field');
     }
}else{
     echo 'No reactions';
}
for($i=0; $i<count($keywords); $i++){
     if(in_array($keywords[$i],$reaction)){
          echo $keywords[$i].' is in reactions.';
     }
}


Im not sure which one is best, that you can decide, but by the look of it i think is faster the first method, but wait for a php expert to tell you the fastest way if you are concerned by that Wink
Luck!
AOP Web Development
thanks for the effort with it, anyway, i'm not sure if that would be compatilbe with my projects or, i hiope that it will...

Anyway what i'm doing now is the look every UserAgent gets into the Reactions words in my table and any match word from reaction word into the user agent will return true or false if nothing.....


#So let say i have a this this symbol
Code:

# user agent string
$str ='Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.12) Gecko/20080201 Firefox/2.0.0.12';

and then i have this data in reaction table
1. Gecko
2. testbot
3. test test
4. testreaction
5. reactiontest


So the main thing here is In any Word from reaction table match in the $str User AGent will return true otherwise false of course..

What i want to know if it is possible compare in a single query.
alalex
well you see the problem is that there is no "in text" select option in mysql. The only way I can think of in php and mysql to use only one query is the second one I showed you, get all the results in an array and then look up all words in the string with those in the userAgent array you just got from mysql. That way you only query the db once, and you check for it...

If you need it I can rewrite the php code to do that, but I think you just need to adapt a little the one I wrote in my first reply

Luck Wink
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.