CODEWITHSUNDEEP
phpsqlmysqli
PinheadLarry
PinheadLarry

Reputation: 117

mysqli prepared statement with fetch_assoc

my goal here is to be able to get a variable (with php) and use it in a prepared statement (with mysqli), and then fetch_assoc. For some reason this code will not work (no errors). I've rtm and I haven't found anything combining fetch_assoc with prepared statements, so I'm not sure if it's even possible. Any help to get this working is appreciated, here's my code currently.

$where = $_GET['section'];
            $mysqli = mysqli_connect("localhost", "root", "","test");

            if($stmt = mysqli_prepare($mysqli,"SELECT title, img, active, price FROM ? ORDER by ID limit 5 ")){
                mysqli_stmt_bind_param($stmt, 's', $where);
                mysqli_stmt_execute($mysqli);
                mysqli_stmt_fetch($mysqli);
                 while($row = mysqli_fetch_assoc($stmt)){
                    if($row['active']=="yes"){
                        echo 'the rest of my stuff goes here';

Upvotes: 5

Views: 6282

Answers (2)

LinhTran
LinhTran

Reputation: 1

Another way style, we can write it below:

$mysqli=new mysqli("host","user","pass","db");
$stmt = $mysqli->prepare($query);
$stmt->bind_param('s', $variable);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()){
....
}

Upvotes: -2

Gareth
Gareth

Reputation: 5719

From the PHP website page for mysqli->prepare (with emphasis added to the most relevant part):

Note:

The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.

However, they are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement), or to specify both operands of a binary operator such as the = equal sign. The latter restriction is necessary because it would be impossible to determine the parameter type. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

Assuming you can get past that problem, your use of mysqli is a little confused. You correctly bind your parameters and execute, but you've mixed up two different ways of getting at your results. Either

  1. Use mysqli_stmt_get_result to fetch the result set and then use mysqli_fetch_assoc on that, or
  2. Bind your results with mysqli_stmt_bind_result, and then use mysqli_stmt_fetch to fetch the next set of results into your bound variables. (Usually you'd iterate over the results using something like while(mysqli_stmt_fetch($stmt)){ //do stuff here }

Upvotes: 5

Related Questions