Using wildcards in prepared statement - MySQLi

Viewed 82 times

I'm trying to run the following query, and I'm having trouble with the wildcard.

   function getStudents() {
        global $db;
        $users = array();
        $query = $db->prepare("SELECT id, adminRights FROM users WHERE classes LIKE ? && adminRights='student'");
        $query->bind_param('s', '%' . $this->className . '%');
        $query->execute();
        $query->bind_result($uid, $adminRights);
        while ($query->fetch()) {
            if (isset($adminRights[$this->className]) && $adminRights[$this->className] == 'student')
                $users[] = $uid;
        }
        $query->close();
        return $users;
    }

I'm getting an error that states: Cannot pass parameter 2 by reference. The reason I need to use the wildcard is because the column's data contains serialized arrays. I guess, if there's an easier way to handle this, what could I do?

Thanks in advance!

Answers

You have to pass parameters to bind_param() by reference, which means you have to pass a single variable (not a concatenated string). There's no reason you can't construct such a variable specifically to pass in, though:

$className = '%' . $this->className . '%';
$query->bind_param('s', $className);

Thursday, October 20, 2022

sabid.m-b623ac80f6a6

Yes, you would have to bind it twice. If you are opposed to that for some reason, you could rephrase the query as:

SELECT *
FROM `login` l cross join
      (select ? as thename) const
WHERE l.`username` = thename OR `emailAddress` = thename;

This is using a subquery to name the parameter so it can be referred to multiple times in the query.

Wednesday, November 2, 2022

unutbu

you want the following:

$start = 1; $postsPerPage = 1;
$sql = "SELECT id, title, author, LEFT(description, 40) AS excerpt, 
               image_small, image_med, date 
        FROM posts 
        ORDER BY id DESC 
        LIMIT ?, ?";

$stmt = $connect->prepare($sql) or die ('error');
$stmt->bind_param('ii', $start, $postsPerPage);
$stmt->execute();
$stmt->bind_result($id, $title, $author, $excerpt, $image_small, $image_med, $date);

while($stmt->fetch()) {
  printf('<h1>%s</h1><p>%s <small> by %s on %s</small></p>',
    htmlspecialchars($title),
    htmlspecialchars($excerpt),
    htmlspecialchars($author),
    htmlspecialchars($date)
  );
}

this binds both question marks to integer (i) values of $start and $postsPerPage. do NOT use variables directly in prepared statements, because that would defeat the whole purpose of prepared statements (apart from eliminating parsing time)

Friday, December 23, 2022

yanik

If that really is your code, it may be that either $_POST["name"] or $_POST["password"] is an array, so that bind_param binds more than just one value.

Check:

var_dump($_POST["name"]);
var_dump($_POST["password"]);

Tuesday, September 27, 2022

user2297081

If you need to perform a selection of all of the columns:

SELECT * FROM `table`

You would use PHP's get_result() rather than bind_result().

bind_result() is better when you're specifying each column that you're retrieving where get_result() will allow you to work with a more generic return of data from your tables.

Saturday, September 10, 2022

israel

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysqli

prepared-statement

wildcard

Using wildcards in prepared statement - MySQLi

Answers

php,mysqli,prepared-statement,wildcard