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

Question

I have read everything I can think of to get an explanation but nothing seems to help. If someone might be able to point out the obvious or give me a slight idea of what is wrong. I have read through php.net and the mysqli tag and can't seem to figure this out. Everything I read says you can't send two queries but I am only trying one. Any help would be much appreciated.

This->https://stackoverflow.com/a/9649149/1626329 - States that maybe I have multiple result sets but I am not sure that makes much sense or what I can do to get more detail on the inner workings of prepared statements.

My Code:

class mydb {

    public function __construct() {
        // Connect to Database
        $this->mydb = new mysqli('****', '***', '***', '***');
        if ($this->mydb->connect_errno) { // Error on connection failure
            echo "Failed to connect to MySQL in Construct: (" . $this->mydb->connect_errno . ") " . $this->mydb->connect_error;
        }
    }

    public function choose ($select, $from, $config = 0, $options = NULL) {
        if ($config === 0) { /** Configure statement for prepare depending on options */
            $stmt = 'SELECT ' . $select . ' FROM ' . $from;
        } elseif ($config === 1) {
            $stmt = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $options['where_comp'] . ' LIKE ?';
        } elseif ($config === 2) {
            $stmt = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $options['where_comp'] . ' = ?';
        } /** End if/elseif Prepare statemenet */
        $mydb = $this->mydb->prepare($stmt);
        if ($config === 1 || $config === 2) {
            $mydb->bind_param("s",$options['where_value']);
        }

        if ($mydb->execute()) { /** If execute is good then get results */
            $result = $mydb->get_result();
            $payload = array();
            while ($row = $result->fetch_array(MYSQLI_NUM)) {
                $payload[] = $row;
            }
            return $payload;
        } /** End if results */
    } /** End choose class method */
} /** End mydb Class */

$myDB = new mydb();

$agentArray = $myDB->choose('*','`agent`');

Used the php.net example and modified it to show a better example:

$mysqli = new mysqli('host', 'database', 'user', 'pass');
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!($stmt = $mysqli->prepare("SELECT ? FROM ?"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!($res = $stmt->get_result())) {
    echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;
}

for ($row_no = ($res->num_rows - 1); $row_no >= 0; $row_no--) {
    $res->data_seek($row_no);
    var_dump($res->fetch_assoc());
}
$res->close();

You cannot use table name or column name as parameter, that's the first thing I noticed. — vee, Aug 09 '13 at 02:55
just found one error: `$payload[] .= $row;` should be `$payload[]= $row;` (the dot `.` removed) — , Aug 09 '13 at 02:56
Arg I wish that was more apparent in the documentation. Maybe i just missed the whole purpose of prepared statements or I am sanitizing things that are not user input. Time to rethink this whole class LOL - THanks Guys! — sin0cide, Aug 09 '13 at 03:02

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

The very first result from the "Related" section on this page (Means it was offered to you while you were in struggle writing your question) offers a solution.

As a general rule, it is quite easy to find an answer to a question based on the error message. Only you need is not to stop at the very first search result but proceed a bit more.

However, on this function choose() of yours. I find it quite impractical, unsafe, and useless:

impractical because it doesn't let you to use SQL, but a very limited subset of it.
- and also it makes your code extremely hard to understand.
unsafe because it does offer no protection for all the dynamical parts but where value only
useless because it can save you not that much to worth a mess.

Look, you think you saved yourself 2 words - SELECT and FROM.

 $agentArray = $myDB->choose('*','`agent`',1,
               array('where_comp' => 'name', 'where_value' -> "%bob%"));

yet you made this code hard to understand, hard to maintain and unable to run ever simplest JOIN. Why not to make it. Not to mention that actual code become longer than conventional SQL query:

$sql = 'SELECT * FROM `agent` WHERE name LIKE ?';
$agentArray = $myDB->query($sql, "%bob%");

which one is easier to read?

Viewing the scope of it right now looks very useless but I assure you in the long run it will make more sense. I am not a fan of joins and my system is designed to avoid that but that is a very valid point that I will keep in mind. The idea of a class is in essence to write "unreasonable or limited code that works" and then easily be able to change the format later and not recode the entire site. Another reason I posted this code is to see other angles that people that have been coding for a while normally use instead. Thank you for the insight! — sin0cide, Aug 09 '13 at 21:44

score -1 · Accepted Answer · answered Aug 09 '13 at 22:09

-1

Adding an if statement to show the error correctly actually gives a mysql error response that can be used:

if (!($stmt = $mysqli->prepare("SELECT ? FROM ?"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

Error response: Prepare failed: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

-- You can't pass identifiers through prepared-statements and you should only use it for values passed from user input.

answered Aug 09 '13 at 22:09

sin0cide

65
10

I was supplying the correct answer for anyone else to view since your suggestion of the answer was incorrect. This is the way I could have seen an issue before even doing the execute that returned the result I showed above. I was under the impression that if my issue did not apply to all the examples that I should show examples of what caused my problem at a later step when the error did not show in debugging. Its very disappointing to see me losing rep for actually explaining the issue in detail. I was not trying to steal the credit for it. – sin0cide Aug 09 '13 at 22:35

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

2 Answers2