Im seeking some advice on how to go about this.

Im trying to figure out, how in the new mysqli php5 extension one would fetch a result set, using prepared statements.

In the old mysql extension with php4, this was easy


/* Assume we already have a connection setup */

// send a query to mysql
$sql = mysql_query('SELECT names FROM users LIMIT 0,100');

// check for result rows and process result set
if(mysql_num_rows($sql)) {

while($row = mysql_fetch_assoc($sql)) {

$set[] = $row;

}

// free memory
mysql_free_result($sql);

}

Now, in PHP5 with mysql 4.1.19 (what im running) using prepared statements and the mysqli OOP class, we go about it like this.


/* Assume the mysqli class has been setup, and we have a connection */

if($stmt = $mysqli->prepare('SELECT names FROM users LIMIT 0,100')) {

// execute the statement and send query
$stmt->execute();

// bind results to variable
$stmt->bind_result($set);

while($row = $stmt->fetch()) {

$set[] = $row;

}

// Close statement and free memory
$stmt->close();

}

Now, of course the OOP code for mysqli above will not properly return the result set, b/c i've assigned the variable $set to bind results, then tried to use[] for writing strings, which will throw a warning in php5.

My question for you is, How can I get the same functionality from the prepared statement setup above, as I used to from php4 / mysql extension in example 1?

Many people have this question, so let's start the discussion.

    Ok, I have figured out the solution after a couple hours of reading the php manual and playing around.

    	$stmt = $mysqli->prepare('SELECT names FROM users LIMIT 0,100');

$stmt->execute();

$stmt->store_result();

$stmt->bind_result($data);

while($stmt->fetch()) {

	$set[] = $data;

}

$stmt->close();

print_r($set);

    This will successfully store the 100 names from our query, into $set.

    I hope this helps someone else.

    Cheers.

      Write a Reply...