Execute query inside a while loop.. MySQli

Question

I'm trying to update my current database from plain text passwords to hashes passwords using crypt().. I'm trying to do this without users having to change their passwords (this is an instable approach) My code is like so:

$Query = $Database->prepare("SELECT ID,Username,Password FROM userlist");
$Query->execute();
$Query->bind_result($ID,$Username,$Password);
   while ($Query->fetch()){
      $Hashed = $FrameWork->Hash_Password($Password);
       $Secondary_Query = $Database->prepare("UPDATE userlist SET Password=?, Salt=? WHERE ID=?");
       $Secondary_Query->bind_param('ssi', $Hashed['Password'],$Hashed['Salt'],$ID);
       $Secondary_Query->execute();
       $Secondary_Query->close();
   }
$Query->close();

I'm getting the error:

Fatal error: Call to a member function bind_param() on a non-object in C:\inetpub\www\AdminChangeTextPass.php on line 24

Now. I know my column names are 100% match aswell as my database names. I also know my variables are correctly set.

Debugging

Debugging:

$Query = $Database->prepare("SELECT ID,Username,Password FROM userlist");
$Query->execute();
$Query->bind_result($ID,$Username,$Password);
   while ($Query->fetch()){
      echo $Password."<br>";
   }
$Query->close();
// Returns: 
//test
//test

Then: 
$Query = $Database->prepare("SELECT ID,Username,Password FROM userlist");
$Query->execute();
$Query->bind_result($ID,$Username,$Password);
   while ($Query->fetch()){
      print_r($FrameWork->Hash_Password($Password));
   }
$Query->close();

/*
Returns: 
Array ( [Salt] => ÛûÂÒs8Q-h¸Ý>c"ÿò [Password] => Ûûj1QnM/Ui/16 )

Array ( [Salt] => ÛûÂÒs8Q-h¸Ý>c"ÿò [Password] => Ûûj1QnM/Ui/16 ) 

*/

Database Schema

CREATE TABLE IF NOT EXISTS `userlist` (
  `ID` int(255) NOT NULL AUTO_INCREMENT,
  `Username` varchar(255) NOT NULL,
  `Password` varchar(255) NOT NULL,
  `Salt` text NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `userlist`
--

INSERT INTO `userlist` (`ID`, `Username`, `Password`, `Salt`) VALUES
(1, 'test', 'test', ''),
INSERT INTO `userlist` (`ID`, `Username`, `Password`, `Salt`) VALUES
(2, 'test', 'test', '');

Having my code look like this:

$Secondary_Query = $Database->prepare("UPDATE userlist SET Password=? WHERE ID=?");
$Query = $Database->prepare("SELECT ID,Username,Password FROM userlist LIMIT 1");
    var_dump($Secondary_Query);
#$Query->execute();
#$Query->bind_result($ID,$Username,$Password);
#   while ($Query->fetch()){
#       $Hashed = $FrameWork->Hash_Password($Password);
#       $Secondary_Query = $Database->prepare("UPDATE userlist SET Password=? WHERE ID=?");
#             $Secondary_Query->bind_param('ssi', $Hashed['Password'],$Hashed['Salt'],$ID);
#       $Secondary_Query->execute();
      # $Secondary_Query->close();
#   }
#$Query->close();

The var_dump($Secondary_Query); returns:

object(mysqli_stmt)#3 (10) { ["affected_rows"]=> int(-1) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2)
["field_count"]=> int(0) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(1) }

And var_dump($Query); returns:

object(mysqli_stmt)#4 (10) { ["affected_rows"]=> int(-1) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(0) ["field_count"]=> int(3) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(2) }

As I cannot submit an answer as of yet.. My working code is as followed:

$Query = $Database->prepare("SELECT ID,Username,Password FROM userlist");
$Query->execute();
$Query->bind_result($ID,$Username,$Password);
$Query->store_result();
   while ($Query->fetch()){
       $Hashed = $FrameWork->Hash_Password($Password);
       $Secondary_Query = $Database->prepare("UPDATE userlist SET Password=?, Salt=? WHERE ID=?");
       $Secondary_Query->bind_param('ssi', $Hashed['Password'],$Hashed['Salt'],$ID);
       $Secondary_Query->execute();
       $Secondary_Query->close();
   }
$Query->close();

You are debugging the wrong thing. The issue is that `$Secondary_Query` doesn't appear to be an object. What does `print_r($Secondary_Query);` return after `$Secondary_Query = $Database->prepare("UPDATE userlist SET Password=?, Salt=? WHERE ID=?");`? — rtcherry, May 19 '13 at 00:22
@rtcherry Print_r($Secondary_Query); returns blank.. `Var_dump($Secondary_Query);` returns: `bool(false)` — Sophie Mackeral, May 19 '13 at 00:24
@rtcherry Changed it. I manually written the insert query due to the inserts not being exported — Sophie Mackeral, May 19 '13 at 00:25
Try enabling more error reporting by adding `mysqli_report(MYSQLI_REPORT_ALL)` to see if that helps narrow the problem (suggested [here](http://stackoverflow.com/a/1220197/2355083)). — rtcherry, May 19 '13 at 00:31
@rtcherry Warning: mysqli::prepare(): (HY000/2014): Commands out of sync; you can't run this command now — Sophie Mackeral, May 19 '13 at 00:33
Hm, take a look at [this](http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now) question. I think the answer for that one will apply here. — rtcherry, May 19 '13 at 00:35
@rtcherry Submit the mysqli_report comment as an answer. I'll mark it, that really helped me solve my problem. — Sophie Mackeral, May 19 '13 at 00:37

score 1 · Accepted Answer · edited May 23 '17 at 10:24

1

Edit: Enabling more verbose error reporting was key in helping to debug the problem: mysqli_report(MYSQLI_REPORT_ALL).

The following answer is from another SO question posted here.

You can't have two simultaneous queries because mysqli uses unbuffered queries by default (for prepared statements; it's the opposite for vanilla mysql_query). You can either fetch the first one into an array and loop through that, or tell mysqli to buffer the queries (using $Query->store_result()).

See here for details.

edited May 23 '17 at 10:24

Community

1
1

answered May 19 '13 at 00:37

rtcherry

4,840
22
27

This has helped me find the solution to my problem. I was not aware of the mysqli_report function. After adding this to my script, I was presented with a searchable error message. Hence finding the solution. This is included within my question. – Sophie Mackeral May 19 '13 at 00:39
I was having a similar problem, I was trying to run a query inside another query off a `while` statement. Adding `$query->store-result();` after the first `$query->execute();` fixed it by allowing it to run the 2nd query by storing the 1st results. At least that's what I think is happening, maybe somebody can verify that? – Ghost Echo Apr 03 '15 at 12:37

Execute query inside a while loop.. MySQli

1 Answers1