Asked  1 Year ago    Answers:  5   Viewed   13 times

I've seen this question before but all the solutions do not work for me. The main solution is to store the result which I already do. Most people say I am running 2 simultaneous queries which I don't understand. I may have used this function below more than once but I always free and close the statement so not sure why this is happening.

You can most likely ignore the top half of the function which just generates a string to represent the types. This is the full DB class I made:

(I edited it since I originally posted it and replaced self::$connection with self::$db)

class DB {
    private static $dbhost = "localhost";
    private static $dbuser = "some_user"; // placeholder
    private static $dbpass = "some_assword"; // placeholder
    private static $dbname = "database_name"; // placeholder
    private static $db;

    public static function connect() {          
        self::$db = new mysqli(self::$dbhost,  self::$dbuser, self::$dbpass, self::$dbname);        
        if (self::$db->connect_errno) {
            die("Database mysqli failed: " .
                self::$db->connect_error . " (" . 
                self::$db->connect_errno . ")"      
            );
        }
    }

    // IGNORE THIS! It just formats the parameters for the 
    // call_user_func_array function to work correctly.
    private static function getBindings(&$params) {
        $types = "";        
        $bindings = array();
        foreach ($params as $value) {
            switch (gettype($value)) {
                case "integer":
                    $types .= "i";
                    break;
                case "double":
                    $types .= "d";
                    break;
                case "string":
                    $types .= "s";
                    break;
                default: 
                    $types .= "s";
                    break;
            }
        }       
        foreach($params as $key => $value)
            $bindings[$key] = &$params[$key]; // assign to references (because bind_param requires references)      
        // to add a string of variable types to the start of the $bindings array (such as 'sss')
        array_unshift($bindings, $types);
        return $bindings;
    }

    public static function query($query, $params) {
        if (!isset(self::$db)) { self::connect(); }

        if (empty($params)) {
            // prepared statement not needed
            return self::$db->query($query);
        }

        $successful = false;
        $bindings = self::getBindings($params);

        // MySQL prepared statement execution:
        $statement = self::$db->prepare($query);
        call_user_func_array(array($statement, 'bind_param'), $bindings);       
        $statement->execute();
        $statement->store_result(); 

        if ($statement->num_rows > 0) {
            // for select queries
            $successful = $statement->get_result(); // does not work! (returns boolean)

            echo self::$db->errno; // 2014          
            echo "<br />";
            echo self::$db->error; // Commands out of sync; you can't run this command now
            echo "<br />";      

            // this method works fine (but I need to return the result set!):
            /*$name = false; $link = false;
            $statement->bind_result($name, $link);

           while ($statement->fetch()) {
                echo 'name: '.$name.'<br>';
                echo 'link: '.$link.'<br>';
           }*/
        } else if ($statement->affected_rows > 0) {
            // for insert queries
            $successful = true;
        }       

        $statement->free_result();
        $statement->close();
        return $successful;
    }

    public static function close() {
        if (isset(self::$db)) self::$db->close();
    }
}

EDIT: This is what one of my requests looks like (I have queried 2 requests on this same page using my DB class and DB::query(...) function):

$result = DB::query("SELECT * FROM table_name;");
if ($result) {
    while ($row = $result->fetch_assoc()) {
        // do stuff                             
    }
    $result->close();
}

 Answers

1

For the love of what is sane, change your driver to PDO and make all this code into

public static function query($query, $params = NULL)
{
    if (!$params) {
        return self::$connection->query($query);
    }

    $statement = self::$connection->prepare($query);
    $statement->execute($params);
    return $statement;
}

to be used like this

$result = DB::query("SELECT * FROM table_name");
foreach($result as  $row) {
    // do stuff                             
}
Thursday, April 1, 2021
1

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>
Thursday, April 1, 2021
 
THEK
 
4

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'] . ",nnThank you for creating an account with H Fencing. Please use the link below to activate your account so we can confirm you identity:nnhttp://blah.blah.co.uk/activate.php?mem_email=" . $register_data['mem_email'] . "&email_code=" . $register_data['email_code'] . "nn - David & Jay "); 
        mysqli_free_result($result);
         return ("Success");
        } else {
            echo $query;
            die(mysqli_error($myNewConnection));
        } 

    }

?>  
Thursday, April 1, 2021
 
Fanda
 
1

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Thursday, April 1, 2021
 
NewPHP
 
4

Got the Answer! It seems like codeigniter's mysql driver has bugs handling stored procedures.

I changed the drivers from mysql to mysqli in the config/database file by changing

$db['default']['dbdriver'] = 'mysql';

to

$db['default']['dbdriver'] = 'mysqli';

Post that i modified the system/database/drivers/mysqli/mysqli_result.php file and added the below function

function next_result()
{
  if (is_object($this->conn_id))
  {
      return mysqli_next_result($this->conn_id);
  }
}

and modified the model as below

$db = $this->load->database('mailbox',TRUE);
$qry_res = $db->query('Call circle_pending_p()');

echo $db->_error_message();
$res = $qry_res->result_array();

$qry_res->next_result();
$qry_res->free_result();

if (count($res) > 0) {
      return $res;
} else {
      return 0;
}

This solved the problem!

Thursday, April 1, 2021
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :