CODEWITHSUNDEEP
php
user3286022
user3286022

Reputation: 13

array in prepared statement not working

I want to create a prepared statement with an array called $params. When I run my script I get the following error:

Fatal error: Call to a member function bind_param() on a non-object in /home/morea/directory-new.php on line 110

line 110 is $stmt->bind_param('i', $place_holders);

$params = array(5, 6, 7, 9, 10, 11, 12, 3, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 4, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42);
/* Create a string for the parameter placeholders filled to the number of params */
$mysqli = new MySQLi("localhost", "erpa", "48p", "le3");
$mysqli->set_charset('utf8');
$place_holders = implode(',', array_fill(0, count($params), '?'));
$sth = ("SELECT u.url, COUNT(u.url) AS total, u.SiteTypeID, p.SiteType FROM sites AS u LEFT JOIN sitetypes AS p USING (SiteTypeID) WHERE SiteTypeID=($place_holders)");
$stmt = $mysqli->prepare($sth);
$stmt->bind_param('i', $place_holders);
$stmt->execute($params);
$query5 = mysqli_query($dbc, $sth);

while($row2 = mysqli_fetch_array($sql3, MYSQLI_ASSOC) && $row4 = mysqli_fetch_array($query5, MYSQLI_ASSOC)){
if ($row4['SiteType'] == 'selected'){
    $list .= $row4['total'];
    $list .= '<option value="'.$row4['SiteTypeID'].'"';
     $list .= "selected='selected'>";
    $list .= $row4['SiteType'];
    $list .= "</option>";
}
}

Upvotes: 1

Views: 81

Answers (2)

gen_Eric
gen_Eric

Reputation: 227220

You need to send bind_param a value for each ? you have in your query. It also needs the values to be references. Also, ->execute() doesn't take any parameters. Finally, mysqli_query() is incorrect here.

$stmt = $mysqli->prepare($sth);

$boundParams = array(str_repeat('i', count($params)));
foreach($params as &$val){
    $boundParams[] =& $val;
}
call_user_func_array(array($stmt, 'bind_param'), $boundParams);

$stmt->execute();    
$query5 = $stmt->get_result();

// then you can run mysqli_fetch_array on $query5

NOTE: get_result() requires you have the mysqlnd driver installed.

P.S. WHERE SiteTypeID=($place_holders) should be WHERE SiteTypeID IN ($place_holders).

Upvotes: 0

RobP
RobP

Reputation: 9522

You should check $stmt after calling prepare(). It is probably false because your query should use WHERE SiteTypeID IN ($place_holders) not WHERE SiteTypeID=($place_holders);

EDIT: (should now cover all issues and be an acceptable answer) Additionally (thanks Rocket!) bind_params() expects a parameter list, not an array, and execute() needs no params at all. Double check the php docs for these function at http://us.php.net/manual/en/mysqli-stmt.bind-param.php. If you want to bind to an array, consider using PDO which I find much nicer to code than mysqli anyhow.

Upvotes: 1

Related Questions