CODEWITHSUNDEEP
phpsqlmysqli
ParisNakitaKejser
ParisNakitaKejser

Reputation: 14859

Why doesn't this prepare statement work in MYSQLI?

I created this code:

$statement = $db->prepare("SELECT * FROM phptech_contact");
$statement->execute();
$result = $statement->result_metadata();
$object = $result->fetch_object();

print_r( $object );

When I run it, it doesn't work. Can anybody tell me why it doesn't work?

I have 20 rows in this table so data should be returned.

Upvotes: 0

Views: 934

Answers (3)

Philippe Gerber
Philippe Gerber

Reputation: 17836

From http://ch.php.net/manual/en/mysqli-stmt.result-metadata.php

Note: The result set returned by mysqli_stmt_result_metadata() contains only metadata. It does not contain any row results. The rows are obtained by using the statement handle with mysqli_stmt_fetch().

As long as you don't need this meta data you don't need to call this method.

$statement = $db->prepare("SELECT fld1, fld2 FROM phptech_contact");
$statement->execute();
$stmt->bind_result($fld1, $fld2);

while ($stmt->fetch()) {
    echo "$fld1 and $fld2<br />";
}

But I really dislike the mysqli extension. PDO is much cooler ... ;-)

$db = new PDO('...');
$stmt = $db->prepare("SELECT fld1, fld2 FROM phptech_contact");
$stmt->execute();

while ($obj = $stmt->fetchObject()) {
    // ...
}

or

$objs = stmt->fetchAll(PDO::FETCH_OBJ);

Upvotes: 5

gapple
gapple

Reputation: 3474

if you're trying to get the rows from the database, the function you need is mysqli_stmt::fetch(), not mysqli_stmt::fetch_metadata()

You're also missing a few steps. When using prepared statements, you must specify the fields you would like to return instead of using the star wildcard, and then use mysqli_stmt::bind_result() to specify which variables the database fields should be placed in.

If you're more familiar with the original MySQL extension, prepared statements have a different process to use. If your select statement has a parameter (eg., "WHERE value=?") prepared statements are definitely recommended, but for your simple query, mysqli:query() would be sufficient, and not very different from the process of mysql_query()

Upvotes: 3

Conspicuous Compiler
Conspicuous Compiler

Reputation: 6469

I believe the problem is that mysqli_stmt::result_metadata() returns a mysqli_result object without any of the actual results — it only holds metadata.

So what you want to do is use $result = $statement->bind_result(...) and then call $result->fetch() repeatedly to get the results.

One of the comments under the bind-result() article shows how to do this for a query like yours, where you don't necessarily know all of the columns being returned.

Upvotes: 1

Related Questions