0

I am trying to execute following query using mysqli (PHP).

        public function brandAvailabilities($start_date, $return_date)
        {
            $query ="
            SELECT brand_id, label, MIN(free) 
                FROM
                (
                    SELECT B1.brand_id, CONCAT(B1.brand,' ',B1.model) as label, B1.stock - COUNT(*) as free
                        FROM 
                            VIEW_VEHICLE_BOOKINGS B1
                            JOIN
                            VIEW_VEHICLE_BOOKINGS B2
                            ON  B2.brand_id = B1.brand_id AND
                                B2.start_date <= B1.start_date AND
                                B2.return_date > B1.start_date
                        WHERE B1.start_date <= ? and B1.return_date >= ?
                        GROUP BY B1.brand_id, B1.start_date
                    UNION
                    SELECT DISTINCT B3.id, CONCAT(B3.brand,' ',B3.model) as label, B3.stock as free
                        FROM VEHICLE_BRANDS B3
                ) T GROUP BY brand_id;";

            $paramType = "ss";
            $paramArray = array($return_date, $start_date);
            return $this->db->select($query, $paramType, $paramArray);
        }

VIEW_VEHICLE_BOOKINGS is view created as (not using above query, It is done already before)..

CREATE OR REPLACE VIEW VIEW_VEHICLE_BOOKINGS
AS
SELECT B.id, B.start_date, B.return_date, C.brand, C.model, C.id as brand_id, C.stock
FROM 
    BOOKING_ASSIGNMENTS A
    JOIN
    VEHICLE_BOOKINGS B
    ON A.booking = B.id
    JOIN
    VEHICLE_BRANDS C
    ON A.vehicle = C.id

db->Select function is defined as

        public function select($query, $paramType="", $paramArray=array())
        {
            if(!($stmt = $this->conn->prepare($query)))
            {
                echo "<br/>ERROR : Prepare failed : (" . $this->conn->errno . ") " . $this->conn->error;
                echo "<br/>ERROR for QUERY '".$query."' PARAMS '".$paramType."'";
            }

            if(!empty($paramType) && !empty($paramArray))
            {
                $this->bindQueryParams($stmt, $paramType, $paramArray);
            }
            
            $stmt->execute();
            $result = $stmt->get_result();
            if($result)
            {
                if ($result->num_rows > 0) 
                {
                    while ($row = $result->fetch_assoc()) 
                    {
                        $resultset[] = $row;
                    }
                }
                else 
                {
                    echo "<br> No Record Found to display";
                }
            }
            else 
            {
                echo "<br> Database error ** ".mysqli_error($this->conn)." **";
            }

            if (! empty($resultset)) 
            {
                return $resultset;
            }
        }
    

Saying error

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

I am have no idea who to fix it. Help to fix it. Thanks in advance

PHP 7.0
MySQL 5.6
Jai
  • 1,292
  • 4
  • 21
  • 41
  • 1
    the problem is in the previous query not this one. as the error message says – Your Common Sense Dec 16 '20 at 07:41
  • I don't get it, It mean whatever I ran earlier was not ran properly ? – Jai Dec 16 '20 at 07:43
  • that's quite a smart assumption – Your Common Sense Dec 16 '20 at 07:43
  • https://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now – Giacomo M Dec 16 '20 at 07:44
  • I commented out earlier execution, but still same error. Now this query is running alone on page load. – Jai Dec 16 '20 at 07:45
  • Good gracious, the select function is all wrong. Please see [how to write this function properly](https://phpdelusions.net/mysqli/simple) and why there shouldn't be [not a single echo statement](https://phpdelusions.net/articles/error_reporting) – Your Common Sense Dec 16 '20 at 07:45
  • `echo "
    No Record Found to display";` - are you serious?
    – Your Common Sense Dec 16 '20 at 07:47
  • Sorry, I just added it for debug – Jai Dec 16 '20 at 07:47
  • 1
    To make it clear: this is NOT the only query on this page – Your Common Sense Dec 16 '20 at 08:07
  • @YourCommonSense I am really confuse, if it is possible can you write a sample select function so that I can compare what I am doing wrong in my select function – Jai Dec 16 '20 at 10:15
  • Sorry I don't understand what select query I should write and why – Your Common Sense Dec 16 '20 at 10:15
  • No, YourCommonSense is saying that the error is because there must be another query running before this one, where you haven't closed the first one properly. You're saying there isn't, but there **must** be, otherwise you wouldn't have this error. Check your code again more carefully. – ADyson Dec 16 '20 at 10:21
  • So you are saying I need to call $this->conn->close() at end of select() ? – Jai Dec 16 '20 at 10:32
  • It's not the connection you need to close really, but the statement or the result set. Did you read the link that Giacomo provided above? It should have already shown you the possible solutions. – ADyson Dec 16 '20 at 10:34
  • Yes, I read. $stmt is local variable. Should not it close automatically on scope out ? because In one function call only one query is executing. I am new to PHP. So let me know If I assuming in wrong way – Jai Dec 16 '20 at 10:39
  • Yes you are assuming in the wrong way. Otherwise you wouldn't have an error, would you? Just because the variable goes out of scope doesn't mean it isn't still holding an open result set in the database connection. Follow the instructions in the link. – ADyson Dec 16 '20 at 10:44

0 Answers0