PHP Procedural: error in the syntax using LIKE keyword

Question

I'm experimenting with joining databases in php using prepared statements. I got this error:

Uncaught mysqli_sql_exception: 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 users.class LIKE ? OR users.email LIKE ?' at line 3 in C:\xampp\htdocs\Burza\includes\functions.inc.php:335 Stack trace: #0 #0 C:\xampp\htdocs\Burza\includes\functions.inc.php(335): mysqli_query(Object(mysqli), 'SELECT * FROM p...') #1 C:\xampp\htdocs\Burza\buy.php(20): getProductsBySearch(Object(mysqli), '%summer%') #2 {main} thrown in C:\xampp\htdocs\Burza\includes\functions.inc.php

I think it's because of the LIKE keyword, but I don't know what to do about it. All of the names of the tables and rows are correct

My code looks like this:

function getProductsBySearch($conn, $search){
    $sql = "SELECT * FROM products
        JOIN users  ON products.userid = users.id
        WHERE users.name LIKE ? OR users.surname LIKE ? OR users.class LIKE ? OR users.email LIKE ?;";
    $stmt = mysqli_stmt_init($conn);
    if(!mysqli_stmt_prepare($stmt, $sql)){
        header("location: ../index.php?error=stmtfailed");
        exit();
    }
    $search = "%".$search."%";
    mysqli_stmt_bind_param($stmt, "ssss", $search, $search, $search, $search);// s = string
    mysqli_stmt_execute($stmt);
    mysqli_stmt_store_result($stmt);
    $result = mysqli_query($conn, $sql);
    $products = mysqli_fetch_all($result, MYSQLI_ASSOC);
    mysqli_stmt_close($stmt);
    return $products;
}

Can somebody explain to me why it's happening and how to fix it?

I tried changing the * symbol to more specific part in my database - products.id and it didn't help. And I tried using '%".?."%' and it didn't work as well.

The above code is causing the error or the snippet you put in the last sentence? The placeholder `?` can't be in native PHP, it is for the database or PHP driver to swap. — user3783243, Feb 07 '23 at 21:58
The code that starts with function getProductsBySearch($conn, $search) — Pavee, Feb 07 '23 at 22:00
Is `%".?."%` being attempted? Is that what causes the error or was that your attempt at fixing the error? — user3783243, Feb 07 '23 at 22:01
Are you calling your function like this ( as per error ) `getProductsBySearch(Object(mysqli), '%summer%') ` with the `'%summer%'` ?? You probably want to omit the `%` from that string and just pass in the actual string `summer` — Professor Abronsius, Feb 07 '23 at 22:04
No, I'm inputtng summer and this happens because $search = "%".$search."%"; — Pavee, Feb 07 '23 at 22:08

Bill Karwin · Accepted Answer · 2023-02-07T22:10:28.263

2

This is the problem:

$result = mysqli_query($conn, $sql);

mysqli_query() is used when your query has no query parameters.

If your query has parameters, then use only mysqli_prepare() and mysqli_stmt_execute().

I suggest the following sequence:

try {
  $stmt = $conn->prepare($sql);
  $search = "%$search%";
  $stmt->bind_param("ssss", $search, $search, $search, $search);
  $stmt->execute();
  $result = $stmt->get_result();
  $products = $result->fetch_all(MYSQLI_ASSOC);
catch (mysqli_sql_exception $e) {
  error_log($e);
  header("location: ../index.php?error=stmtfailed");
  exit();
}

edited Feb 07 '23 at 22:10

answered Feb 07 '23 at 22:03

Bill Karwin

538,548
86
673
828

Thank you, I thought it was a problem with LIKE and it was with the prepared statements. Thanks – Pavee Feb 07 '23 at 22:10
The clue is in the error message, and the fact that the error says it was caused by mysqli_query(). The first character it got confused by was `?` because only prepared statements recognize parameter placeholders. – Bill Karwin Feb 07 '23 at 22:15

PHP Procedural: error in the syntax using LIKE keyword

1 Answers1