Full text search with PDO

Beginners - Learning PHP
#1

Hello,

Performing full text or tolerant searches with the following code when using MySQLi
Text only to search with tolerances: word
To search for full text, use double quotes to search: "word"

$search_keyword = $_POST['query'];
    $pattern = '/^".+"$/i';
    if(preg_match($pattern, $search_keyword)){
      $search_keyword = str_replace('"','',$search_keyword);
      $search_keyword = " '$search_keyword' ";
    }else{
      $search_keyword = " '%$search_keyword%' ";
    }

Now I switched to PDO
I want to include the above code in the query code below
Could you help

if(isset($_POST['per'])){
    $limit = $_POST['per'];
  }
  else
  {
    $limit = '10';
  }
  
  $search_keyword = '';
  if(!empty($_POST['query'])) {
    $search_keyword = $_POST['query'];
  }
  
  
  $sql = 'SELECT 
    teklifler.id,
    teklifler.teklif_no, 
    teklifler.teklif_tarihi, 
    teklifler.members_id, 
    teklifler.members_group, 
    teklifler.firma_yetkili_adi_soyadi, 
    teklifler.pdf_dosya_adi, 
    teklifler.secilen_parabirimi_id, 
    teklifler.musteri_bilgileri_json,
    teklifler.firma_bilgileri_json, 
    teklifler.siparis_durumu,
    members.members_group, 
    members.firma_adi, 
    members.firma_yetkili_adi_soyadi, 
    members.firma_emaili
    FROM teklifler 
    INNER JOIN members ON teklifler.members_id = members.members_id
    WHERE 
    teklifler.teklif_no LIKE :keyword OR 
    teklifler.teklif_tarihi LIKE :keyword OR 
    teklifler.musteri_bilgileri_json LIKE :keyword OR 
    teklifler.members_id LIKE :keyword OR 
    teklifler.siparis_durumu LIKE :keyword OR 
    members.firma_adi LIKE :keyword OR 
    members.firma_yetkili_adi_soyadi LIKE :keyword OR 
    members.firma_emaili LIKE :keyword
    ORDER BY teklifler.id DESC ';
    
    $page = 1;
    $start = 0;
    if(!empty($_POST["page"])) {
      $page = $_POST["page"];
      $start = ($page-1) * $limit;
    }
    $limit_code = " LIMIT " . $start . "," . $limit;
    $pagination_statement = $PDOdatabase->prepare($sql);
    $pagination_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
    $pagination_statement->execute();
  
    $total_page = $pagination_statement->rowCount();
  
    $query = $sql.$limit_code;
    $pdo_statement = $PDOdatabase->prepare($query);
    $pdo_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
    $pdo_statement->execute();
    $result = $pdo_statement->fetchAll();
#2

Actually, that’s not what you are trying to do. You are doing either an exact match (the data is exactly and only the entered search term) or a wild-card match (the data contains the entered search term.) A full text search is entirely different from this, uses syntax different than a LIKE match, and would try to find the literal phrase (any number of words) between the “…” anywhere within the data. This would still be a wild-card match, since anything could exist before/after the literal phrase in the data being searched for.

Assuming that what you are trying to do is what you actually want, regardless of its name, you need either the (trimmed) $_POST['query'] value (for an exact match) or that value with leading/trailing % characters added to it (for a wild-card match), without the extra space characters and without the single-quotes as part of the value.

For the posted query/code to ‘work’, you are using an emulated prepared query. For a true prepared query, you cannot use a named place-holder more than once in a query. For an emulated prepared query, you can use a named place-holder more than once in the query (the emulator is not accurate since it operates differently from a true prepared query.) The problem with using an emulated prepared query is that it relies on the character set that php is using in order to protect against sql special characters in the values from breaking the sql query syntax. If the character set that php is using is not the same as your database table(s), sql special characters in the data can break the sql query syntax, which is how sql injection is accomplished. Short-answer: use a true prepared query (it’s a setting when you make the database connection), and you will either need to use uniquely named place-holders or simply use positional ? place-holders.

As to the pagination logic you have. Don’t query for all the matching rows in order to find out the total number of rows, use a SELECT COUNT(*) … query instead and since the $limit value can be from external data, use a prepared query place-holder for it in the sql query statement.

Btw - a search operation should use a get method form/links, not a post method form.

#3

Full text search may be wrong phrase

When I search the search field in double quotes: “word” => sql => 'word’
When I search by entering regular text: sql => '%word%'

I didn’t write the database query code from scratch, I customized a code I found on the Internet

About the total number of rows,
Suppose the total is row 10
Suppose there are 2 lines in the search
SELECT COUNT (*) … always showing 10 lines

Can you write the query code as it should be

Thank you from now

Merkezi_Uydu_Anten_TV_Sistemi_Fiyat_%C3%87%C4%B1karma_Web_Sitesimysqli
Merkezi_Uydu_Anten_TV_Sistemi_Fiyat_%C3%87%C4%B1karma_Web_Sitesimysqli1509×498 37.7 KB

Merkezi_Uydu_Anten_TV_Sistemi_Fiyat_%C3%87%C4%B1karma_Web_SitesiPDO
Merkezi_Uydu_Anten_TV_Sistemi_Fiyat_%C3%87%C4%B1karma_Web_SitesiPDO1545×467 43.8 KB

#4

The search keyword code should have never added the extra spaces or single-quotes to the variable. These are part of the sql syntax and belong in the sql query statement, not in the variable holding the search keyword value.

The search keyword code should (have) look(ed) like this -

<?php
$search_keyword = $_POST['query'];
// starts and ends with a "
$pattern = '/^".+"$/i';
if(preg_match($pattern, $search_keyword))
{
	$search_keyword = str_replace('"','',$search_keyword);
	// $search_keyword is already the needed value
}else{
	// add the % wild-card characters only
	$search_keyword = "%$search_keyword%";
}

The usage in your existing query should then look like - teklifler.teklif_no LIKE '$search_keyword' OR ...

To convert this to a prepared query, you would replace each php variable and any single-quotes around the variable with a prepared query place-holder. That would look like - teklifler.teklif_no LIKE ? OR ...

You would then build an array of values for the place-holders and supply this array when you call the ->execute(…) method.

$n = 8; // number of search parameters/place-holders in the query
$search_params = array_fill(0, $n, $search_keyword);

If there are other prepared query place-holders in the query, you would use array_merge() to combine the above $search_params array with the other input values before supplying the resultant array to the ->execute() method call.

#5

Thank you,

I used double quotes because it is necessary to search for an exact match with two characters, such as 8, 12, 16, 20, 24, 32, 5, 9, 10, 13, 14, 17, but the important thing here is the exact match. is to create a search option.

I ran the query as below
Sample:
Tried searching in double quotes no problem "12"
Normal search worked no problem 12

Gives an error when there is no search

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OR teklifler.teklif_tarihi LIKE OR teklifler.musteri_bilgileri_json LIKE O' at line 20 in *********\test.php on line 90
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OR teklifler.teklif_tarihi LIKE OR teklifler.musteri_bilgileri_json LIKE O' at line 20 in *********\test.php on line 90

if(isset($_POST['per'])){
    $limit = $_POST['per'];
  }
  else
  {
    $limit = '10';
  }
  
  $search_keyword = '';

  if(!empty($_REQUEST['query'])) {
    $search_keyword = $_REQUEST['query'];
  
  // starts and ends with a "
  $pattern = '/^".+"$/i';
  if(preg_match($pattern, $search_keyword))
  {
  $searchkeyword = str_replace('"','',$search_keyword);
  $search_keyword = " '$searchkeyword' ";
  // $search_keyword is already the needed value
  }else{
  // add the % wild-card characters only
  $search_keyword = " '%$search_keyword%' ";
  }
  }
  
  $n = 10; // number of search parameters/place-holders in the query
  $search_params = array_fill(0, $n, $search_keyword);

  $sql = 'SELECT 
    teklifler.id,
    teklifler.teklif_no, 
    teklifler.teklif_tarihi, 
    teklifler.members_id, 
    teklifler.members_group, 
    teklifler.firma_yetkili_adi_soyadi, 
    teklifler.pdf_dosya_adi, 
    teklifler.secilen_parabirimi_id, 
    teklifler.musteri_bilgileri_json,
    teklifler.firma_bilgileri_json, 
    teklifler.siparis_durumu,
    members.members_group, 
    members.firma_adi, 
    members.firma_yetkili_adi_soyadi, 
    members.firma_emaili
    FROM teklifler 
    INNER JOIN members ON teklifler.members_id = members.members_id
    WHERE 
    teklifler.teklif_no LIKE '.$search_keyword.' OR 
    teklifler.teklif_tarihi LIKE '.$search_keyword.' OR 
    teklifler.musteri_bilgileri_json LIKE '.$search_keyword.' OR 
    teklifler.members_id LIKE '.$search_keyword.' OR 
    teklifler.siparis_durumu LIKE '.$search_keyword.' OR 
    members.firma_adi LIKE '.$search_keyword.' OR 
    members.firma_yetkili_adi_soyadi LIKE '.$search_keyword.' OR 
    members.firma_emaili LIKE '.$search_keyword.'
    ORDER BY teklifler.id DESC ';
    
    $page = 1;
    $start = 0;
    if(!empty($_POST["page"])) {
      $page = $_POST["page"];
      $start = ($page-1) * $limit;
    }
    $limit_code = " LIMIT " . $start . "," . $limit;
    $pagination_statement = $PDOdatabase->prepare($sql);
    //$pagination_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
    $pagination_statement->execute($search_params); // test.php on line 90
  
    $total_page = $pagination_statement->rowCount();
  
    $query = $sql.$limit_code;
    $pdo_statement = $PDOdatabase->prepare($query);
    //$pdo_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
    $pdo_statement->execute($search_params);
    $result = $pdo_statement->fetchAll();
#6

This way the rows were listed even when there was no search

$search_keyword = " '%%' ";

Now I apply it to all other queries

I applied it to all and it works fine for now

Should I use plus instead of double quotes?
"word" => +word+ ?

Thank you

#7

You’re not using a prepared query properly, to prevent sql special characters in the data from breaking the sql query syntax, which is how sql injection is accomplished. Don’t put external data values directly into the sql query statement.

#8

I’m using filter_input () for all POST and GET
Thank you

#9

Any filter that you are using that allows a double-quote ", won’t do anything to stop sql injection. You have to use the right tool for the job. A true prepared query is the only fool proof way of preventing sql special characters in data from breaking the sql syntax, for all possible data types and all possible character encoding settings for database tables.

#10

Double quotes “” only in search option

Can you suggest other characters instead of double quotes?
To search for an exact match

#11

Hello again,
I tried PHP 8 locally, but the code I used above gave an error

PHP Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in

Stack trace:
#0***********.php(2851): PDOStatement->execute(Array)

what to fix for php 8

Thank you

#12

The last code you posted is not using a prepared query correctly, as was previously stated. While it may have ‘worked’, it always contained a different number of parameters compared to the number of bound variables. What likely changed was that the logic for this error condition was either ‘fixed’ or changed from a notice/warning to a fatal error.

You would need to post the current php code and query to get specific help with it.

1 Like
Sponsor our Newsletter | Privacy Policy | Terms of Service