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

4

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
3

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);
    $stmt->execute($values);

}
Wednesday, December 14, 2022
4

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
 
1

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

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

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

// 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);

Remark:

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

Friday, November 18, 2022
 
gunan
 
2

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 :
 
Share

Browse Other Code Languages