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
No Record Found to display";` - are you serious? – Your Common Sense Dec 16 '20 at 07:47