SQL LIKE query failing - fatal error in prepared statement

Question

I have the following code:

$countQuery = "SELECT ARTICLE_NO FROM ? WHERE upper(ARTICLE_NAME) LIKE '% ? %'";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("ss", $table, $brand);
    $numRecords->execute();
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = mysql_num_rows($data);
    $rows = getRowsByArticleSearch($query, $table, $max);
    $last = ceil($rowcount/$page_rows);
}

Which should work fine. However I receive the error that :

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 '? WHERE upper(ARTICLE_NAME) LIKE '%?%'' at line 1

If I put

SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE '% o %';

The query works fine. $table is defined above, and query is received from GET, and both are correct valid values. Why is this failing?

edit: changing to:

$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE '% ? %'";

if ($numRecords = $con->prepare($countQuery)) {

    $numRecords->bind_param("s", $query);

results in the error:

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in C:\Program Files\EasyPHP 3.0\www\prog\get_records.php on line 38

Commands out of sync; you can't run this command now

where as

$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ?";

if ($numRecords = $con->prepare($countQuery)) {

    $numRecords->bind_param("s", "%".$query."%");

results in

Fatal error: Cannot pass parameter 2 by reference in C:\Program Files\EasyPHP 3.0\www\prog\get_records.php on line 38

and lastly

$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ? ";

if ($numRecords = $con->prepare($countQuery)) {

    $numRecords->bind_param("s", $query);

will give only:

Commands out of sync; you can't run this command now

Is it impossible to use a paramter for a LIKE statament?

Answer 1

Try the following instead:

$countQuery = "SELECT ARTICLE_NO FROM ? WHERE upper(ARTICLE_NAME) LIKE ?";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("ss", $table, "%$brand%");
    $numRecords->execute();
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = mysql_num_rows($data);
    $rows = getRowsByArticleSearch($query, $table, $max);
    $last = ceil($rowcount/$page_rows);
}

Answer 2

Afaik you can't use placeholders for identifiers with mysqli and prepare statements. So you'd have to manually interpolate the tablename into the query.

Answer 3

For LIKE clause, use this:

SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE CONCAT('%', ?, '%')

As for the table name, it's an extremely bad practice to have table names as parameters.

If for some reason you still need to do it, you'll need to embed it into the query text before preparing the query:

$countQuery = "SELECT ARTICLE_NO FROM $table_name WHERE upper(ARTICLE_NAME) LIKE CONCAT('%', ? ,'%')";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s", $brand);
    $numRecords->execute();
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = mysql_num_rows($data);
    $rows = getRowsByArticleSearch($query, $table, $max);
    $last = ceil($rowcount/$page_rows);
}

Answer 4

Have you issued

mysqli_free_result($result);

after the last query? That's the command out of sync error.

This should work however

$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ?";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s", "%".$query."%");

Wondering what is in the $query variable. Try doing this instead

$query = '%'.$query.'%';
$numRecords->bind_param("s", $query);