Asked  2 Years ago    Answers:  5   Viewed   80 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->bind_result($uid, $adminRights);
        while ($query->fetch()) {
            if (isset($adminRights[$this->className]) && $adminRights[$this->className] == 'student')
                $users[] = $uid;
        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!



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

Yes, bindParam binds a parameter to a variable name (reference), not a value, as the manual says.

However, there's a simpler syntax for your situation. PDOStatement::execute can take an array of values.

public function insert($table, $cols, $values){

    $placeholder = array();
    for ($i = 0; i < count($values); $i++)
      $placeholder[] = '?';

    $sql = 'INSERT INTO '. $table . ' (`' . implode("`, `", $cols) . '`) ';
    $sql.= 'VALUES (' . implode(", ", $placeholder) . ')';

    $stmt = $this->dbh->prepare($sql);

Wednesday, December 14, 2022

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->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>',

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

The way stored procedures work with prepared statements is a bit more complicated. PHP manual states that you've got to use session variables (MySQL sessions, not PHP)

INOUT/OUT parameter

The values of INOUT/OUT parameters are accessed using session variables.

So you could do it with

// bind the first parameter to the session variable @uid
$stmt = $connect->prepare('SET @uid := ?');
$stmt->bind_param('s', $uid);

// bind the second parameter to the session variable @userCount
$stmt = $connect->prepare('SET @userCount := ?');
$stmt->bind_param('i', $userCount);

// execute the stored Procedure
$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter
$r = $connect->query('SELECT @userCount as userCount');
$row = $r->fetch_assoc();               

$toRet = ($row['userCount'] != 0);


I recommend to rewrite this procedure as a function with one IN parameter that returns INT.

Friday, November 18, 2022

Your question-mark place holder is inside single quotes, so it's being seen as a literal character - and not a place holder at all. When the statement is parsed the '%?%' is just a string and no bind variable is seen, so no bind variable can be set - there are no variables, so no variable with index 1.

You can use concatenation to fix this:

    PreparedStatement searchKeyword = 
    connection.prepareStatement("SELECT title, registered FROM paper "
    + "WHERE title LIKE '%' || ? || '%'");
Sunday, October 9, 2022
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 :

Browse Other Code Languages