8

I have converted my register script from mysql to mysqli. I worked fine as mysql however it now gives me the error 

Commands out of sync; you can't run this command now

This is the function I use to register the user

function register_user($register_data) { 
global $myConnection;
array_walk($register_data, 'array_sanitize'); 
//Make the array readable and seperate the fields from data 
$fields = '`' . implode('`, `', array_keys($register_data)) . '`'; 
$data = '\'' . implode('\', \'', $register_data) . '\''; 
//Insert the data and email an activation email to the user 
mysqli_query($myConnection, "INSERT INTO `members` ($fields) VALUES ($data)") or die(mysqli_error($myConnection)); 
email($register_data['mem_email'], 'Activate your account', "Hello " . $register_data['mem_first_name'] . ",\n\nThank you for creating an account with H Fencing. Please use the link below to activate your account so we can confirm you identity:\n\nhttp://blah.blah.co.uk/activate.php?mem_email=" . $register_data['mem_email'] . "&email_code=" . $register_data['email_code'] . "\n\n - David & Jay "); 
}

The email sends fine with the correct data from my array. However no data is inserted into the database and I get the error mentioned above. I have never come across this error before.

jhetheringt7
  • 191
  • 1
  • 1
  • 7
  • 4
    1. Print out the query before you run it and look at it. 2. Parameterize your query. 3. For the love of god, parameterize your query. – Sammitch Apr 03 '13 at 21:54

1 Answers1

10

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

From here: http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html

Update

If you make the a variable for the query and paste the variable directly into something like MySQL Workbench you can check the syntax prior to execution.

<?php
            function myConnection(){
              $myConnection = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
              return $myConnection;
            }   


    function register_user($register_data) { 
        array_walk($register_data, 'array_sanitize'); 
        //Make the array readable and seperate the fields from data 
        $fields = '`' . implode('`, `', array_keys($register_data)) . '`'; 
        $data = "'" . implode("', '", $register_data) . "'"; 
        //Insert the data and email an activation email to the user 
        $query = "INSERT INTO `members` ($fields) VALUES ($data)";
                    $myNewConnection = myConnection();          

                    if($result = mysqli_query($myNewConnection, $query)){ 
        email($register_data['mem_email'], 'Activate your account', "Hello " . $register_data['mem_first_name'] . ",\n\nThank you for creating an account with H Fencing. Please use the link below to activate your account so we can confirm you identity:\n\nhttp://blah.blah.co.uk/activate.php?mem_email=" . $register_data['mem_email'] . "&email_code=" . $register_data['email_code'] . "\n\n - David & Jay "); 
        mysqli_free_result($result);
         return ("Success");
        } else {
            echo $query;
            die(mysqli_error($myNewConnection));
        } 

    }

?>
AbsoluteƵERØ
  • 7,816
  • 2
  • 24
  • 35
  • I pasted the query directly into PHPmyadmin and it inserted fine, meaning the actual query works – jhetheringt7 Apr 03 '13 at 22:30
  • Yes, however what it does is run the else statement and print the query rather than run the if statement – jhetheringt7 Apr 03 '13 at 23:39
  • It's running the if, the problem is that the query fails. Is your connect statement in this order? $myConnection = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db'); (with the proper settings of course). – AbsoluteƵERØ Apr 03 '13 at 23:55
  • yes the connection looks the same and works fine as I use it for functions on the website. Weird thing is that it echoes the query however no errors appear after – jhetheringt7 Apr 04 '13 at 00:01
  • Scrap that I tried again and the error is showing after the query – jhetheringt7 Apr 04 '13 at 00:07
  • And if I manually put this query into PHpmyAdmin it inserts fine – jhetheringt7 Apr 04 '13 at 00:13
  • I've pulled the mysqli_connect script into its own function. This way you can trigger it inside of the function and save it to a var when you call the function to make it create a new connection... just in case that's why it's dying. – AbsoluteƵERØ Apr 04 '13 at 09:41
  • It worked! So is it best to keep this connection as a function? And all the functions in this file to use the same method? – jhetheringt7 Apr 04 '13 at 09:56
  • I would say yes. That way you can call them and have them open right before you run the function (so the link still exists). – AbsoluteƵERØ Apr 04 '13 at 10:03