#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    2

    Help with multiple Search using PHP and Mysql


    Hi,

    I found a script here by someone called "Suraj Thapaliya" for doing a multiple search using PHP and MySQL. I am trying to adopt it to my own use for one application am working on.

    In my application, if I populate only one of the text boxes or combo boxes the search is executed fine. But the problem is that when I use two or more criterias I am getting an error like the one below:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cmaterial = 'oil' Limit 0, 16' at line 1
    My code:

    PHP Code:
    <? include("includes/conn.php"); $ctitle=$_POST["ctitle"]; $csubject=$_POST["csubject"]; $creference=$_POST["creference"]; $cyear=$_POST["cyear"]; $cobjecttype=$_POST["cobjecttype"]; $cmaterial=$_POST["cmaterial"]; $ctechnic=$_POST["ctechnic"]; $cwidth=$_POST["cwidth"]; $cheight=$_POST["cheight"]; $cperiod=$_POST["cperiod"]; $cmarkings=$_POST["cmarkings"]; $o=$_POST["o"];                  $rest="";                  $text="Search Keywords : ";                                        if($ctitle!="")                      {                          if($rest=="")                             {                                 $rest.="where ctitle Like '$ctitle%' ";                                 $text.="Item Title = $ctitle";                             }                             else                             {                             $rest.=$o ctitle = '$ctitle' ";                             $text.=", Item Title = $ctitle";                             }                      }                                             if($csubject!="")                      {                          if($rest=="")                             {                                 $rest.="where csubject = '$csubject' ";                                 $text.="Subject = $csubject ";                             }                             else                             {                             $rest.=$o  csubject = '$csubject' ";                             $text.=", Subject = $csubject ";                             }                      }                                            if($creference!="")                      {                          if($rest=="")                             {                                 $rest.="where creference = '$creference' ";                                 $text.="Reference No. = $creference";                             }                             else                             {                             $rest.=$o  creference = '$creference' ";                             $text.=", Reference No. = $creference";                             }                      }                                                                                       if($cyear!="")                      {                          if($rest=="")                             {                                 $rest.="where cyear Like '$cyear%' ";                                 $text.="Year = $cyear";                             }                             else                             {                             $rest.=$o  cyear = '$cyear' ";                             $text.=", Year = $cyear";                             }                      }                                            if($cobjecttype!="")                      {                          if($rest=="")                             {                                 $rest.="where cobjecttype Like '$cobjecttype%' ";                                 $text.="Object = $cobjecttype";                             }                             else                             {                             $rest.=$o  cobjecttype = '$cobjecttype' ";                             $text.=", Object = $cobjecttype";                             }                      }                                            if($cmaterial!="")                      {                          if($rest=="")                             {                                 $rest.="where cmaterial Like '$cmaterial%' ";                                 $text.="Material = $cmaterial";                             }                             else                             {                             $rest.=$o  cmaterial = '$cmaterial' ";                             $text.=", Material = $cmaterial";                             }                      }                                            if($ctechnic!="")                      {                          if($rest=="")                             {                                 $rest.="where ctechnic Like '$ctechnic%' ";                                 $text.="Technic = $ctechnic";                             }                             else                             {                             $rest.=$o  ctechnic = '$ctechnic' ";                             $text.=", Technic = $ctechnic";                             }                      }                                            if($cwidth!="")                      {                          if($rest=="")                             {                                 $rest.="where cwidth Like '$cwidth%' ";                                 $text.="Width = $cwidth";                             }                             else                             {                             $rest.=$o  cwidth = '$cwidth' ";                             $text.=", Width = $cwidth";                             }                      }                                            if($cheight!="")                      {                          if($rest=="")                             {                                 $rest.="where cheight Like '$cheight%' ";                                 $text.="Height = $cheight";                             }                             else                             {                             $rest.=$o  cheight = '$cheight' ";                             $text.=", Height = $cheight";                             }                      }                                           if($cperiod!="")                      {                          if($rest=="")                             {                                 $rest.="where cperiod Like '$cperiod%' ";                                 $text.="Period = $cperiod";                             }                             else                             {                             $rest.=$o  cperiod = '$cperiod' ";                             $text.=", Period = $cperiod";                             }                      }                                            if($cmarkings!="")                      {                          if($rest=="")                             {                                 $rest.="where cmarkings Like '$cmarkings%' ";                                 $text.="Markings = $cmarkings";                             }                             else                             {                             $rest.=$o  cmarkings = '$cmarkings' ";                             $text.=", Markings = $cmarkings";                             }                      }                                                                                                           if($rest!="")                     {                     $rest=$rest;                     }                     else                     {                         //die("Enter Search Parameter<br><br><br><br><br><br><br><br><br><br><br>");                     }  //Number of items to display per page  $perpage = 16;     if(isset($_GET["page"]))     {     $page = intval($_GET["page"]);     }     else     {     $page = 1;     }     $calc = $perpage * $page;     $start = $calc - $perpage;  //////////////////                       $sql="select * from $tbl_name $rest Limit $start, $perpage"; $result=mysql_query($sql) or die(mysql_error());             $num=mysql_num_rows($result); ?>
    I will appreciate any help.

    joseph
  2. #2
  3. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,115
    Rep Power
    1990
    What is the actual SQL string that's being produced? After looking at your code, I'll bet that it's not adding the WHERE clause the right way. All you need is

    PHP Code:
    echo "<p>".$sql."</p>"
    after the $sql="... line and you'll be able to see exactly what the query is and why it's going wrong.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    2
    Originally Posted by Catacaustic
    What is the actual SQL string that's being produced? After looking at your code, I'll bet that it's not adding the WHERE clause the right way. All you need is

    PHP Code:
    echo "<p>".$sql."</p>"
    after the $sql="... line and you'll be able to see exactly what the query is and why it's going wrong.
    Hi,

    After adding that line:

    PHP Code:
    $sql="select * from $tbl_name $rest Limit $start$perpage";$result=mysql_query($sql) or die(mysql_error());$num=mysql_num_rows($result);?>

    echo "<p>".$sql."</p>"; 
    I immediately got the following error printed even before I could provide the query criteria:

    select * from collections where ctitle like "monalisa" cobjecttype="painting" Limit 0, 16
    My only guess now is that the AND operator is missing somewhere but how to include it in the code i do not know. I mean, it did not appear in the original code, which seemed to work properly.

    Please, advise.

    Joseph
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    there's indeed no "AND" between the conditions.

    Actually, I'd throw away this whole script and write my own. It's just horrible with regard to security, because he just dumps all user input into the query strings and the HTML without any escaping or filtering whatsoever. Also note that this stuff is 7 years old and uses the obsolete mysql_ functions.

    So you're really better off writing your own scripts rather than copying stuff somewhere from the internet. This example again proves that most of it is actually dangerous and not only bad.

    Before you start, you might wanna check the 6 worst sins of security to avoid typical mistakes.


    // Out of curiosity, I've checked some other scripts. Please stay away from that site, seriously.
    Last edited by Jacques1; February 5th, 2013 at 02:44 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    2
    I have been searching the web for an up to date script that can do a multiple query, such as the one refered to above but I did not find any.

    Could you have or know somewhere I can look for a similar script>

    joseph
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Like I said: You should write your own script. Think about what you need to do and then simply start with it. Just try it. If you got concrete questions or problems, I'm sure we can help you.

    I understand that you might be unsure about how to do it, but copying and pasting will get you nowhere. And good code is indeed hard to find.

    So the best way is to really do it yourself. This "multiple query" isn't rocket science, it's a normal query. The only difference is that you build it depending on the user input.

    Comments on this post

    • Aurum84 agrees
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    56
    Rep Power
    2

    Thumbs up


    Originally Posted by Jacques1
    Like I said: You should write your own script. Think about what you need to do and then simply start with it. Just try it. If you got concrete questions or problems, I'm sure we can help you.

    I understand that you might be unsure about how to do it, but copying and pasting will get you nowhere. And good code is indeed hard to find.

    So the best way is to really do it yourself. This "multiple query" isn't rocket science, it's a normal query. The only difference is that you build it depending on the user input.
    Thanx.

    I will do just that.

    Stay well.

IMN logo majestic logo threadwatch logo seochat tools logo