Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
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);
                Other people have said this, but passing the table name as a parameter works fine? Is it bad practice to do so?
– user1253538
                Mar 3, 2009 at 12:32
                Passing table name as a parameter won't work, MySQL won't be able too prepare such a statement. You cannot have a prepared statement with a table name as a parameter.
– Quassnoi
                Mar 3, 2009 at 13:07
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);
                I have no idea where to issue that..I have freed everything as far as I can see, and still get that error.
– user1253538
                Mar 4, 2009 at 18:24
                This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.
– mike
                Mar 5, 2009 at 17:03
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);
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.