I have been converting a lot of my old MySQL stuff to MySQLi in PHP and am getting a problem on the following code:

### FETCH INCLUDES ###
$player=$_POST['player'];
$password=md5($_POST['password']);
#### DB CONNECTION ####
if(!$mysqli=new mysqli(DBHOST,DBUSER,DBPWD,DBNAME)) {$err=$mysqli->error; print($err); }
$sql="SELECT * FROM accounts WHERE name='?' AND passkey='?'";
if($stmt=$mysqli->prepare($sql)) {
    //$stmt->bind_param('ss',$player,$password);
    $stmt->execute();
    $stmt->store_result();
    if($stmt->num_rows==1) {
        $account=$stmt->fetch_assoc();
        // purely for debugging
        print_r($account);
        if($_SESSION['account']=$account) $account=true;
    } else {
        echo "Failed. Row count: ";
        print($stmt->num_rows);
        echo "<br />";
        $query=str_replace('?','%s',$sql);
        printf($query,$player,$password);
        $account=false;
    }
    $stmt->close();
} else {
    $err=$mysqli->error;
    print($err);
}

I have narrowed down the fault to the query itself. I am getting 0 rows returned, no errors, so I thought I would output the query (the str_replace thing I have going there) and I can use the query to return a row from the database using the same query from PHPMyAdmin

Where am I going wrong?

EDIT

I tried changing the query to a basic one without binding params - "SELECT * FROM table" still get no rows returned. So it is isn't the query itself, it would be something in my order/format of the prepare,execute situation

second edit: I have added the $stmt->store_result() to the code and still returns 0 row count.

Third Edit: I investigated the connection and user settings which seem fine. I can connect via console to the database using the same user and password, and the database name is the same. I am really stumped on this :(

Add a $stmt->store_result(); after $stmt->execute();, as it seem's it must be called once before $stmt->num_rows... At least they do this in the examples (see http://php.net/manual/en/mysqli-stmt.store-result.php). And they meantion a dependency in the documentation of "num_rows".

Other ideas: You check for if($stmt->num_rows==1) {, are you sure num_rows is 0? I don't know your database structure for the table "accounts". Is "name" the primary key (or at least a unique index)? If not, there could be multiple columns that match. That's just a quick idea what could be wrong, and cause you looking hours for the problem in your source code. While the problem is somewhere else.

I got it working, I ended up needing the $stmt->store_result();

But I also noticed I had added single quotes around the ? in the prepare statement which was causing problems.

After taking those out and leaving in the store_result method it works!

Ok, I did check your code. First you should fix your error handling when connecting. Don't check for "$mysqli" is true but check mysqli_connect_errno() like this:

$mysqli=new mysqli(DBHOST,DBUSER,DBPWD,DBNAME);
if (mysqli_connect_errno()) {
  printf("Connect failed: %s
", mysqli_connect_error());
  exit();
}

On my server the your code worked with correct credentials (e.g. DBHOST,DBUSER,DBPWD,DBNAME needed to be replaced).

And the statement $account=$stmt->fetch_assoc(); doesnt work. There is no fetch_assoc() function in the $stmt object. The fetch_assoc() is for mysqli::result objects obtained by using normal queries (not prepared statements). You need to use $stmt->bind_result(); and then $stmt->fetch(); Also you should to put a list of all column names in the query instead of "*", this defines a field order...