Search using php . comparing keyward to multiple columns in same table

Question

                                                                      <?php
//get data
session_start();

$button = $_GET['submit'];
$search = $_GET['search'];
if (!$button)
    echo "YOU DIDNT SUBMIT A KEYWORD";
else
{
   if (strlen($search)<=2)
       echo "SEARCH TERM TOO SHORT";
   else
   {
       echo"You searched for <b>$search</b><hr size='1'>";


   //connect to database
   include("connect.php");



    // a bit of filtering
 $search = strtoupper($search);
 $search= strip_tags($search);
 $search = trim ($search);

$search_exploded = explode(" ",$search);

foreach ($search_exploded as $search_each)

       {
          //construct query
           $x++;
           if ($x==1)
             $construct .= "CONCAT(role_category1, role_category2, role_category3, role_category4) LIKE '%$search_each%'";
           else
               $construct .= " OR CONCAT(role_category1, role_category2, role_category3, role_category4) LIKE '%$search_each%'";

       }
   //echo out construct
$construct = "SELECT * FROM  jobdescription WHERE $construct";
$run = mysql_query($construct);

$foundnum = mysql_num_rows($run);

if ($foundnum==0)
    echo "No results found.";
else
    {
     echo "$foundnum results found!<p>";

     while ($runrows = mysql_fetch_assoc($run))
         {

         $jobdescriptionid = $runrows['jobdescriptionid'];

         $companyid = $runrows['companyid'];
         $companyid = $runrows['companyid'];
         $role_1= $runrows['role_1'];
         $role_location_1 = $runrows['role_location_1'];
         $role_category_1 = $runrows['role_category_1'];
         $role_term_1 = $runrows['role_term_1'];
         $role_description_1 = $runrows['role_description_1'];
          $role_2= $runrows['role_2'];
         $role_location_2 = $runrows['role_location_2'];
         $role_category_2 = $runrows['role_category_2'];
         $role_term_2 = $runrows['role_term_2'];
         $role_description_2 = $runrows['role_description_2'];

         echo "
         <b>$companyid
          <b>$jobseekerfirstname $jobseekerlastname </b><br>
         $role_1<br>
         $role_location_1<br>
         $role_category_1<br>

          $role_2<br>
         $role_location_2<br>
         $role_category_2<br>";




 }
        }}}

?> am using the php script but its still now identifying what am searching for. Am trying to search for fields in the same table but different columns (heads of these columns are role_category1,role_category2.....) any suggestions

Marc B · Accepted Answer · 2011-02-26 02:49:24Z

1

$construct .= "'role_category_1' || 'role_category_2' || 'role_category_3' || 'role_category_4' OR preferedlocation LIKE '%$search_each%'";

in MySQL, concatenation is done via the CONCAT() function - you're doing bitwise OR operations on strings, so your construct is treated as:

string || string || string || string LIKE '%blahblahblah%'

which becomes

0 || 0 || 0 || 0 LIKE '%blahblah%';
0 || (0 LIKE '%blahblah%')
0

As well, since you put single quotes around the field names, MySQL will treat them as strings, not as field names, so you were in effect trying to match your input text string against a fixed string that looks like

role1_category1role_category2role_category3role_category4

You want

$construct .= "CONCAT(role_category1, role_category2, role_category3, role_category4) LIKE '%blahblah%'";

instead. Note the lack of quotes around the field names.

answered Feb 26, 2011 at 2:49

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Devin Ceartas Over a year ago

postgres and other databases use || for concatenation, so it's not a crazy error :-)

Marc B Over a year ago

Yah. Too bad the various DB vendors can't get their respective crania out of their own posteriors and agree on a standard that's universally supported.

ope Over a year ago

am using the php script but its still now identifying what am searching for. Am trying to search for fields in the same table but different columns (heads of these columns are role_category1,role_category2.....) any suggestions

Marc B Over a year ago

What's the query look like when you finish building it? Does it actually work when you run it manually?

ope Over a year ago

@Marc B that didnt work. am trying to search for all four columns for the keyword entered. help please its wrecking my head

|

Collectives™ on Stack Overflow

Search using php . comparing keyward to multiple columns in same table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related