what's the mysqli prepared statement equivalent to this?

Question

I have this code

$query = mysql_query("SELECT * FROM users ORDER BY exp DESC LIMIT ".$lim.", 10");

and

while($row = mysql_fetch_array($query))

but what's the mysqli equivalent to this (featuring prepared statements). I can't get along.

can you pls explain more about it and what you have tried so far? — Ishan Shah, Dec 24 '15 at 12:23
I don't think there's much more to explain. However, I tried nothing since I only have working code for single statements with prepared statements and results by $someMysqli->bind_result($each, $result). — jan, Dec 24 '15 at 12:25
Take a look at this post: http://stackoverflow.com/questions/1390607/how-could-i-change-this-mysql-to-mysqli — Mirceac21, Dec 24 '15 at 12:30
Have you tried the quick start guide http://php.net/manual/en/mysqli.quickstart.prepared-statements.php? — CodeZombie, Dec 24 '15 at 12:38
take a look at this, try the OOP way which is easier to use. http://www.w3schools.com/php/php_mysql_select.asp — Ikhlak S., Dec 24 '15 at 12:48
@user3284463 He's asking how to translate this deprecated code to `mysqli`. — Barmar, Dec 24 '15 at 12:53
You can't use a placeholder for the `LIMIT` parameters. So the mysqli equivalent is the same query. — Barmar, Dec 24 '15 at 12:54

score 0 · Answer 1 · edited Dec 24 '15 at 12:55

Not tested, but it should be like this. And what is $lim variable? Is it custom way to show what LIMIT is ?

     $query = $mysqli->query("SELECT * FROM users ORDER BY exp DESC LIMIT ".$lim.", 10");

if ($stmt = $mysqli->prepare($query)) {


        $stmt->execute();

        //NOTE: depends on your SQL statement, you bind your columns here, and store into var. I don't know how many columns you need to bind with your select statement, but idea is like this.

        $stmt->bind_result($var1, $var2, var3, *(and so on)*);

        while ($stmt->fetch()) {
            printf ("%s (%s)\n", $name, $code);
        }

        $stmt->close();
    }

The argument to `prepare` is a string, not `$query`. – Barmar Dec 24 '15 at 12:55 — Barmar, Dec 24 '15 at 12:55

score 0 · Accepted Answer · answered Dec 24 '15 at 12:58

Prepared statement placeholders can only be used in places where the SQL allows an expression, but LIMIT requires its parameter to be a literal. Since you can't use a placeholder there, the mysqli equivalent is basically the same as the mysql version, there's no benefit from using a prepared statement. The only difference is that mysqli_query requires the connection object as its first argument.

$query = mysqli_query($conn, "SELECT * FROM users ORDER BY exp DESC LIMIT ".$lim.", 10");
while($row = mysqli_fetch_array($query)) {
    ...
}

score 0 · Answer 3 · answered Dec 24 '15 at 16:29

Here is an example:

$limit = 10;
$user = "john";
$sql="SELECT * FROM users WHERE first_name = ? ORDER BY exp DESC LIMIT ?";
$stmt = $db->stmt_init(); // Initiate prepared statement

if(!$stmt->prepare($sql)){// prepare query, but if it fails get store the error in $error. echo error if you are debugging
   $error = $stmt->error;
 }else{
    $stmt->bind_param("si", $user, $limit);//bind values -- Must be variable --list them by order. First parameter is the data type, and the rest are values to bind
// Data types include "i" - for integer, "d" - for decimal integer, "s" - for string and "b" - for BLOB
    $stmt->execute();//execute the prepared statement
    $result = $stmt->get_result(); //store mysql object so that you can use it later to fetch_array or num_row
  }

//Using the $result mysqli object we stored
$numrows=$result->num_rows;// Get total number of rows
if(!numrows){
    echo "No results found";
    }else{
      while($row = $result->fetch_assoc()){
      echo $row['first_name'];
    }
}

what's the mysqli prepared statement equivalent to this?

3 Answers3