Jump to content


Photo

PDO Statement to bind value with quotes

php pdo mysql

  • Please log in to reply
4 replies to this topic

#1 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 01 August 2017 - 06:39 PM

Hi Guys,

 

I want to implement a search function on a table column. So I used the below code as reference.

<?php  
 $connect = mysqli_connect("localhost", "root", "", "test_db");  
 if(isset($_POST["submit"]))  
 {  
      if(!empty($_POST["search"]))  
      {  
           $query = str_replace(" ", "+", $_POST["search"]);  
           header("location:advance_search.php?search=" . $query);  
      }  
 }  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | Search multiple words at a time in Mysql php</title>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
      </head>  
      <body>  
           <br /><br />  
           <div class="container" style="width:500px;">  
                <h3 align="center">Search multiple words at a time in Mysql php</h3><br />  
                <form method="post">  
                     <label>Enter Search Text</label>  
                     <input type="text" name="search" class="form-control" value="<?php if(isset($_GET["search"])) echo $_GET["search"]; ?>" />  
                     <br />  
                     <input type="submit" name="submit" class="btn btn-info" value="Search" />  
                </form>  
                <br /><br />  
                <div class="table-responsive">  
                     <table class="table table-bordered">  
                     <?php  
                     if(isset($_GET["search"]))  
                     {  
                          $condition = '';  
                          $query = explode(" ", $_GET["search"]);  
                          foreach($query as $text)  
                          {  
                               $condition .= "video_title LIKE '%".mysqli_real_escape_string($connect, $text)."%' OR ";  
                          }  
                          $condition = substr($condition, 0, -4);  
                          $sql_query = "SELECT * FROM tbl_video WHERE " . $condition;  
                          $result = mysqli_query($connect, $sql_query);  
                          if(mysqli_num_rows($result) > 0)  
                          {  
                               while($row = mysqli_fetch_array($result))  
                               {  
                                    echo '<tr><td>'.$row["video_title"].'</td></tr>';  
                               }  
                          }  
                          else  
                          {  
                               echo '<label>Data not Found</label>';  
                          }  
                     }  
                     ?>  
                     </table>  
                </div>  
           </div>  
      </body>  
 </html> 

I changed it accordingly to my requirement, but I am finding it hard to write it using PDO. Below is my code for PDO. I am able to get the result when i pass the string as %not%, but when i pass the variable $condition no data is displayed. So, could you please let me know what I am doing wrong ? I was thinking the quotes should be the problem but I am not sure about it.

<table class="table table-bordered">  
                     <?php  
                     if(isset($_GET["search"]))  
                     {  
                          $condition = '';  
                          $query = explode(" ", $_GET["search"]);  
                          foreach($query as $text)  
                          {  
                               $condition .= "tabletext LIKE '%".$text."%' ";  
                          }  
                          $condition = substr($condition, 0, -4);  

                          $stmt = $dbconnect->prepare("SELECT * FROM table WHERE :condition");
                          $stmt -> bindValue(':condition', $condition);
                          $stmt -> execute();
                          $row = $stmt -> fetch();
                          echo '<tr><td>'.$row["tabletext "].'</td></tr>'; 
 
                     }  
                     ?>  
</table> 

Thanks in advance !!



#2 requinix

requinix
  • Administrators
  • Maddening Administrator
  • 9,561 posts
  • LocationWA

Posted 01 August 2017 - 07:30 PM

Binding in prepared statements does not work like string replacement. You cannot bind something that is supposed to act like a part of the query - preventing that sort of behavior is one of the main selling points of prepared statements.

1. Make condition be a bunch of "tabletext LIKE ?". Remember that you have to OR all those together. Since they're all the same, fill up an array with that string however-many times you need it, then implode() with an OR.
2. Put that string directly into the query. It's fine.
3. Bind all the $query strings and execute.
The Reimann Zeta Function Trolley Problem | "Summer is when I, the great ice fairy, can show my true power!"

#3 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 01 August 2017 - 08:05 PM

Binding in prepared statements does not work like string replacement. You cannot bind something that is supposed to act like a part of the query - preventing that sort of behavior is one of the main selling points of prepared statements.

1. Make condition be a bunch of "tabletext LIKE ?". Remember that you have to OR all those together. Since they're all the same, fill up an array with that string however-many times you need it, then implode() with an OR.
2. Put that string directly into the query. It's fine.
3. Bind all the $query strings and execute.

 

 

Thanks for your input. I now understood that I have to build the SQL query first then I can call prepare. Below code is working for me.

<table class="table table-bordered">  
                     <?php  
                     if(isset($_GET["search"]))  
                     {  
                           
                          $keywords = explode(" ", $_GET["search"]);  
                          $totalKeywords = count($keywords);
                          $sqlquery = "SELECT * FROM table WHERE tabletext LIKE ? ";
                          
                          for($i=1 ; $i < $totalKeywords; $i++){
                              $sqlquery .= " AND tabletext LIKE ? ";
                            }


                          $stmt=$dbconnect->prepare($sqlquery);
                          foreach($keywords as $key => $keyword){
                              $stmt->bindValue($key+1, '%'.$keyword.'%');
                            }
                          $stmt->execute ();

                           
                         while($row = $stmt -> fetch())  
                         {  
                              echo '<tr><td>'.$row["tabletext"].'</td></tr>';  
                         }  
                          
                     }  
                     ?>  
                     </table> 


#4 ginerjm

ginerjm
  • Members
  • PipPipPip
  • Handball player
  • 4,083 posts
  • LocationVoorheesville NY

Posted 01 August 2017 - 10:25 PM

Your proposed query structure may not work either.   "where alpha like x and alpha like y"  ?


JG
PS - If you're posting here you should be using:
        error_reporting(E_ALL);
        ini_set('display_errors', '1');

at the top of ALL php code while you develop it!

#5 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 01 August 2017 - 11:30 PM

Your proposed query structure may not work either.   "where alpha like x and alpha like y"  ?

 

It is working for me. And yea in my query I use AND because I want to display the texts which contains all the keywords which is entered in the search box. So considering your example I want to display texts which contains both x and y. 






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users