2

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 :(

Community
  • 1
  • 1
elzaer
  • 729
  • 7
  • 25
  • Not an answer, but please don't use md5 for hashing passwords. It is utterly broken. Look into bcrypt and you may also want to read up on salting passwords. – PeeHaa Aug 15 '12 at 22:54
  • Are you sure there are matching rows? Have you tried `echo`ing the statement and the values and running the query manually? – PeeHaa Aug 15 '12 at 22:57
  • Yep, thats what the printf is for, to output the exact query it is running and it returns rows. I am trying some other things now. I thought it might be to do with some sort of prepare/execute step I am missing – elzaer Aug 15 '12 at 23:00
  • I can't change md5 at the moment without asking everyone to reset their password. I might do that after everything is over on MySQLi format – elzaer Aug 15 '12 at 23:01
  • @elzaer - have you checked that `$stmt->num_rows` is actually returning 0? – andrewsi Aug 15 '12 at 23:03
  • Yeah, that was the first thing I tried, printing it to make sure it wasn't something basic, gives 0. – elzaer Aug 15 '12 at 23:04
  • I think its something to do with the connection to the database. I tried running it unprepared and it doesn't give any results either. Could be something to do with the PHP definitions I have set. – elzaer Aug 15 '12 at 23:09
  • @elzaer - you could also add a few more calls to `$mysqli->error`, too - the only one at the moment is if the prepare fails, so it might be worth adding one after the `execute` and `fetch_assoc` calls, too. – andrewsi Aug 15 '12 at 23:09

3 Answers3

4

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.

SDwarfs
  • 3,189
  • 5
  • 31
  • 53
  • Added store_result() and still get same result. I edited the code and as you can see I run a debug to show the amount of rows returned which is 0 regardless of the query I run. I tried "SELECT * FROM accounts" which also returns 0 row count which makes me think there is a problem perhaps in the DB selection or something like that. It's weird though, I get connection errors when I try to change it to something non existent so am really baffled. – elzaer Aug 15 '12 at 23:37
  • Can you somehow execute the query of your debug output on the server (e.g. via mysql client or phpmyAdmin?). Just to see, weather this gives result rows... note that you of course need to add '...' around your strings. – SDwarfs Aug 15 '12 at 23:44
  • Yeah that is why I added in the printf($query...) line so it was easy for me to run it server side. As I said, it isn't the query. The query returns exactly what I want when I run it on the server. It doesn't matter what query I put in there, it is always 0 num of rows returned – elzaer Aug 15 '12 at 23:46
  • I am quite certain it has to do with user/connection settings on my server. It appears to connect fine when I output $mysqli->host_info but I have no idea about users and what not on Linux – elzaer Aug 15 '12 at 23:51
  • Yeah... that's it! Have a look at my other answer. You need to fix your check, if the connection was established successfully ;-) If that's correct you'll at least get an error message. Your code just skipped the error output, when I tried wrong credentials at my server. – SDwarfs Aug 16 '12 at 00:11
1

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\n", 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...

SDwarfs
  • 3,189
  • 5
  • 31
  • 53
  • That fetch_assoc did need fixing, but at least the result is returning which is easy to adjust and mould from there! – elzaer Aug 16 '12 at 00:17
  • Can I bind an array of 3 columns onto one cariable using the bind_result? So if I select the columns I want in the query, and bind them all to the one varaiable, does it become an associative array? – elzaer Aug 16 '12 at 00:20
  • No, but you can try to bind to multiple subelements... to $data["x"] and $data["y"] and $data["z"] and so on... mysqli isn't very flexible here. Maybe "PDO" is better: see http://de2.php.net/manual/de/pdostatement.fetchobject.php and http://de2.php.net/manual/de/book.pdo.php ; haven't tried that so far... just looked at the documentation. – SDwarfs Aug 16 '12 at 10:47
0

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!

elzaer
  • 729
  • 7
  • 25