MySQLi equivalent of mysql_result()?

Question 1

I'm porting some old PHP code from mysql to MySQLi, and I've ran into a minor snag.

Is there no equivalent to the old mysql_result() function?

I know mysql_result() is slower than the other functions when you're working with more than 1 row, but a lot of the time I have only 1 result and 1 field. Using it lets me condense 4 lines into 1.

Old code:

if ($r && mysql_num_rows($r))  
    $blarg = mysql_result($r, 0, 'blah');

Desired code:

if ($r && $r->num_rows)  
    $blarg = $r->result(0, 'blah');

But there is no such thing. :(

Is there something I'm missing? Or am I going to have to suck it up and make everything:

if ($r && $r->num_rows)  
{  
    $row = $r->fetch_assoc();  
    $blarg = $row['blah'];  
}

Question 2

PHP 5.4 now supports function array dereferencing, which means you can do this:

if ($r && $r->num_rows)  
{  
    $row = $r->fetch_assoc()['blah'];  
}

Question 3

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Question 4

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Question 5

Well, in an OO sense, it would go from:

if(mysql_num_rows(mysql_query("SELECT userid FROM users WHERE userid = '$userid'"))){
    //code to be exectued if user exists
}

To (assuming numeric userid):

$result = $mysqli->query("SELECT userid FROM users WHERE userid = ".(int) $userid);
if ($result->num_rows) {
    //code
}

To (assuming string userid) :

$result = $mysqli->query("SELECT userid FROM users WHERE userid = '". $db->real_escape_string($userid) . "');
if ($result->num_rows) {
    //code
}

To (assuming prepared statements) :

$stmt = $mysqli->prepare("SELECT userid FROM users WHERE userid = ?");
$stmt->bind_param('s', $userid);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows) {
    //code
}

Now, that's assuming you're using the OOP version of MySQLi (which you should be, IMHO, since it makes life easier in a lot of ways).

Question 6

To be sure you see all PHP errors, add this code on top of your script:

error_reporting(E_ALL);
ini_set('display_errors', 1);

You must correct your calls to mysqli_real_escape_string. According to the documentation, there must be two parameters, and the first parameter must be a MySQL link. In your case that link would be $mysqli.

Also, replace:

if($row==1){

with:

if($result->num_row==1){

You are misunderstanding what $result->num_rows is: it contains the TOTAL number of rows returned by the query whose result is stored in $result. So, it is useless to check the value of $result->num_rows inside the loop where you retrieve all records returned by the query.

I removed the constant MYSQLI_USE_RESULT from your query() because the documentation for mysqli_query says:
If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result().

New code:

<?php
    $mysqli = new mysqli('localhost', 'root', 'password', 'aiesec');

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %sn", mysqli_connect_error());
        exit();
    }

    // cleanup POST variables
    $myusername = mysqli_real_escape_string($mysqli, stripslashes(trim($_POST['myusername'])));
    $mypassword = mysqli_real_escape_string($mysqli, stripslashes(trim($_POST['mypassword'])));

    // If result matched $myusername and $mypassword, table row must be 1 row
    $sql = "SELECT * FROM members WHERE username='$myusername' and password='$mypassword'"; 
    $result = mysqli->query($sql);
     if($mysqli->errno<>0)
        die("Errormessage: %sn", $mysqli->error);
    echo $result->num_rows;
    if($result->num_rows==1){
        echo "correct username and pass";
        // Register $myusername, $mypassword and redirect to file "login_success.php"
       // session_register("myusername");
        //session_register("mypassword");
        //header("location:login_success.php");
    }
    else {
        echo "Wrong Username or Password";
    }
    mysqli_close();     
?>

MySQLi equivalent of mysql_result()?

Answers

php,mysqli