PHP - Prepared Statement Query doesn't work the second time

Question

So, I'm working with PHP and prepared statements sent to a MySQL database. I've ran into a problem that I can't quite debug. Here is my code:

        // Check if the input username is in the database
  $stmtQuery = "SELECT * FROM updatedplayers WHERE Player=?;";
  $preparedStmt = $dbc->prepare($stmtQuery);
  $preparedStmt->bind_param("s", $setUsername);

  $preparedStmt->execute();
  $preparedStmt->bind_result($resultUUID, $resultUsername);
  $preparedStmt->fetch();

  // If it's not, kill the page.
  if ($resultUUID == null) {

   incorrect();
  }
  
  $stmtQuery = "SELECT Password, Salt FROM logins WHERE UUID=?;";
  echo 'flag1 ';
  $preparedStmt = $dbc->prepare($stmtQuery);
  echo 'flag2 ';
  $preparedStmt->bind_param("s", $resultUUID);
  echo 'flag3 ';

The fist prepared statement works fine, it's at the line $preparedStmt->bind_param("s", $resultUUID);. There are also a couple other prepared statements before these, so I know I'm doing this correctly, but I'm not too sure about the last statement.

The code just seems to stop running after echo 'flag2 ';, which I put there to find the specific line. I don't get any error messages, it just doesn't print out flag3.
I've tried replacing $resultUUID with a static string, yet I get the same outcome. Also, I know my SQL statement is correctly formatted, I've tested within the console manually.

That's pretty much it, I'd love to hear some criticism, as I am new to PHP. Also, is there any way to get a better idea about the errors I get, instead of trying to pinpoint the error myself? Thanks!

The `mysqli` driver has an error reporting function, http://php.net/manual/en/mysqli.error.php. Try to use that on each DB interaction. Might also want to look at http://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments for more mysqli error reporting options. — chris85, Sep 23 '16 at 01:57
@chris85 I don't have time to fill that out currently. I'll have time the day after tomorrow, and then I'll get back to you. :) — Kaelinator, Sep 23 '16 at 02:29

score 2 · Accepted Answer · edited May 23 '17 at 12:15

So, adding ini_set('display_error', 1);, suggested by @user2182349, gave me a little more insight, I got "Fatal error: Call to a member function bind_param() on boolean".

After some research, I tried adding mysqli_report(MYSQLI_REPORT_ALL);, which ended up throwing "No index used in query/prepared statement".

I did some research on that to realize that it wasn't a problem, just MySQLI reporting unnecessary errors (which is what I asked it to do lol). In order to get a better, more insightful stack trace, I used mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);.

This threw "Commands out of sync; you can't run this command now". Again, more research taught me to use $preparedStmt->store_result();, in order to allow for another prepared statement to run.

Big thanks to all y'alls' help, hope this can help someone too.

score 0 · Answer 2 · answered Sep 23 '16 at 02:37

You should be able to use a single select statement similar to this:

SELECT u.UUID, u.Username, l.Password, l.Salt 
    FROM updatedplayers AS u 
    JOIN logins AS l ON (u.UUID = l.UUID) 
    WHERE u.Player = ?

Check the case of the field names to be sure they match the database.

At the top of the file, add ini_set('display_errors',1);. If you have any PHP errors, they will be displayed. Also check the return values from the database calls and use the error display functions.

Thank you, I will try this once I get the chance and I'll get back with you. — Kaelinator, Sep 23 '16 at 02:47

Emz · Answer 3 · 2016-09-23T02:09:34.350

-1

I would suggest you should start using PDO... I have issues encountered with mysqli prepared statement years ago. Since then, PDO gives me no headaches when it comes to multiple queries at a time.

You should try PDO.. :-) it's more efficient.

http://php.net/manual/en/intro.pdo.php http://php.net/manual/en/class.pdostatement.php

Or you can do the following "if you want alternative solution"..

//Close connection
$preparedStmt->close();

//AND OPEN YOUR CONNECTION AGAIN TO PREPARE NEW QUERIES..

$stmtQuery = "SELECT Password, Salt FROM logins WHERE UUID=?;";
echo 'flag1 ';
$preparedStmt = $dbc->prepare($stmtQuery);
echo 'flag2 ';
$preparedStmt->bind_param("s", $resultUUID);
echo 'flag3 ';

edited Sep 23 '16 at 02:09

answered Sep 23 '16 at 01:59

Emz

502
1
3
11

1

This doesn't answer the question, it is an alternative approach which may or may not work. – chris85 Sep 23 '16 at 02:00
I have edited my Answer here... for "alternative solution" – Emz Sep 23 '16 at 02:10
But for me.. the answer is to use PDO... I have patched "alternative solutions" before but did not work well... – Emz Sep 23 '16 at 02:12

M4tini · Answer 4 · 2016-09-23T02:08:31.857

-1

I think you need to close the prepared statement before you use the variable for another query:

$preparedStmt->close();

Or use another variable name like $preparedStmt2 for the second query.

edited Sep 23 '16 at 02:08

answered Sep 23 '16 at 02:02

M4tini

447
1
6
11

1

I know that this is not the case because I have made multiple prepared statement without having to close it after each one. :/ – Kaelinator Sep 23 '16 at 02:08
Per the manual, http://php.net/manual/en/mysqli.quickstart.prepared-statements.php, see example #3 `executed multiple times`, with no closure. – chris85 Sep 23 '16 at 02:14
@chris85..INSERT is different FROM SELECT ... I had no problem with INSERT multiple times.. But I had troubles SELECTING MULTIPLE TIMES.... that's why I shifted to using PDO.. – Emz Sep 23 '16 at 02:17
@victor Look at the functions being used. It is the same DB interactions. – chris85 Sep 23 '16 at 02:18
@chris85.. yes it is BUT different TABLEs... I had this problems way back when I was using MySQLi.. and been searching for answers but got no LUCK... so I had to re do my libraries to be using PDO.. and PDO is SWEET.. it's been 4 years I have been using PDO... – Emz Sep 23 '16 at 02:22
@chris85.. the only thing I did in here was Close the connection every now and then when SELECTING multiple times at a time.. – Emz Sep 23 '16 at 02:24
@victor Yes, I prefer PDO as well. However saying to just change something because you can't find the cause isn't an answer. Things are happening for a reason and it should be determinable. Mysqli can have multiple selects on a connection (OP even states that part is working for him/her). – chris85 Sep 23 '16 at 02:26
@chris85.. so do you have any ideas to share? on how this problem be solved? – Emz Sep 23 '16 at 02:27
@victor I posted my suggestion 30 minutes ago. Use the error reporting function that is meant to tell you what specifically is failing. – chris85 Sep 23 '16 at 02:28
@chris85... I see, I did that too the error was non-sense because I couldn't make it work ... and I can't spend more time in debugging on mysqli .. any way I'll check on this to find out what will be the solutions and to find what I have missed before... – Emz Sep 23 '16 at 02:39

PHP - Prepared Statement Query doesn't work the second time

4 Answers4