Home>

I would like to create a process to display the search results when I send a search word ($_GET ['search']) to the model below.
$articles->execute () does not become true and you cannot proceed to the next process.
I want to make this true.

* Articles can be obtained when $_GET ['search'] is in the state of''.

$articles->execute () becomes false.

Corresponding source code
{
    if (isset ($_GET ['search'])&&! empty ($_GET ['search'])) {
      $word = preg_replace ('/ (\ s |) +/',',', $_GET ['search']);
      if (strpos ($word,',') === 0) {
        $word = substr ($word, 1, strlen ($word) ―― 1);
      }
      $searchWord = explode (',', $word);
      $keywordCondition = [];
      for ($i = 0;$i<count ($searchWord);$i ++) {
        $keywordCondition [] = "title LIKE: title {$i}";
      }
      $title = implode ('AND', $keywordCondition);
    } else {
      $title ='';
    }
    if ($_GET ['category']! =='') {
      $category ='category =: category';
    } else {
      $category ='';
    }
    $top = filter_input (INPUT_GET,'top');
    switch ($top) {
      case'all':
        $top ='';
        break;
      case '0':
        $top = "top = 0";
        break;
      case '1':
      case '2':
        $top ='(top =: top OR top = 3)';
        break;
      case '3':
        $top = "top = 3";
        break;
    }
    if ($title ===''&&$top ===''&&$category ==='') {
      $where ='';
    } else {
      $where ='WHERE';
    }
    $and1 ='';
    $and2 ='';
    $and3 ='';
    if ($title! ==''&&$category! ==''&&$top! =='') {
      $and1 ='AND';
      $and2 ='AND';
    } else if ($title! ==''&&$category! ==''&&$top ==='') {
      $and1 ='AND';
    } else if ($title ===''&&$category! ==''&&$top! =='') {
      $and2 ='AND';
    } else if ($title! ==''&&$category ===''&&$top! =='') {
      $and3 ='AND';
    }
    $sql = "SELECT * FROM articles {$where} {$title} {$and1} {$and3} {$category} {$and2} {$top} ORDER BY created DESC";
    $articles = $this->db->prepare ($sql);
    $i = 0;
    if (isset ($searchWord)) {
      foreach ($searchWord as $search) {
        $articles->bindValue (": title {$i}",'"%'. $Search.'%"');
        $i ++;
      }
    }
    if ($_GET ['category']! =='') {
      $articles->bindValue (': category', $_GET ['category']);
    }
    if ($_GET ['top']! =='') {
      $articles->bindValue (': top', $_GET ['top']);
    }
    if ($articles) {
      if ($articles->execute ()) {
        while ($row = $articles->fetch ()) {
          $rows [] = $row;
        }
        if (! isset ($rows)) {
          $errors = '0 was';
          return $errors;
        }
      }
      $this->db = null;
      return $rows;
    }
  }
What I tried

When I checked if the value was bound in the bindValue part below, it became true.
Postscript, 1 was returned instead of true.

if (isset ($searchWord)) {
      foreach ($searchWord as $search) {
        $articles->bindValue (": title {$i}",'"%'. $Search.'%"');
        $i ++;
      }
 }
Supplementary information (FW/tool version, etc.)

heroku/7.42.13
PHP 7.1.33
macOS Mojave 10.14.6

  • Answer # 1

    It's pretty difficult for others to debug in this state, so
    First of all

    Refer to the summary when connecting to the database with PHP and catch and check if there is an SQL error by throwing an exception

    PDOStatement :: debugDumpParams
    Check the SQL prepared command,

    Variables in various placesvar_dump ()As soon asxdebugSet a breakpoint at and check if it is as expected.

    Try replacing suspicious variables with fixed values ​​and testing

    I think that you can get a more appropriate answer if you debug around and organize the results and add them to the question. (Often resolved while organizing)


    If you're debugging (assuming you're throwing exceptions)

    $sql = "SELECT * FROM articles {$where} {$title} {$and1} {$and3} {$category} {$and2} {$top} ORDER BY created DESC";


    Is as expected

    $sql = "SELECT * FROM articles {$where} {$title} {$and1} {$and3} {$category} {$and2} {$top} ORDER BY created DESC";
    var_dump ($sql);
    die ();

    I think that it will start from checking whether it is as expected by such as.
    If this is as expected
    Write a simple test script using the output string and check if the assumption is correct.

  • Answer # 2

    Wrong way of writing named placeholders in $sql.
    Also, $where is not always set, so it seems that you are getting a SQL Syntax error.
    PDOStatement :: bindValue