I am trying to execute a query through parameters using mysqli. It works normally for other procedures, but for this specific procedure, it keeps returning a null.
I cross-referenced every single detail I could find, but for some strange reason, it does not like what it receives.
I tested the query using Workbench and it seems to work without an issue, but it does not execute via php for this brain scratcher.
The Stored Procedure that does the insert and it receives 10 parameters.
PROCEDURE `usp_Advert`(IN `advertID` VARCHAR(50),
IN `sellerID` VARCHAR(100),
IN `make` VARCHAR(30),
IN `model` VARCHAR(30),
IN `description` varchar(100),
IN `mileage` int(11),
IN `color` VARCHAR(100),
IN `price` int(8),
IN `cityID` int(11),
IN `year` int(4))
BEGIN
Declare errorNo int;
Declare Exit Handler for sqlexception
Begin
Get current diagnostics condition 1 errorNo = Mysql_errno;
select errorNo as MYSQL_ERROR;
Rollback;
end;
start transaction;
set autocommit=0;
set @now := curdate();
if((select count(a.advertID) from `vas`.advert as a where a.advertID=`advertID`)>=1)then
select true as `tryagain`;
else
insert into vas.advert
(`advertID`,
`sellerID`,
`vehicleMake`,
`vehicleModel`,
`advertDescription`,
`vehicleMileage`,
`vehicleColor`,
`sellingPrice`,
`cityID`,
`advertDate`,
`advertExpireDate`,
`vehicleYear`)
VALUES
(`advertID`,
`sellerID`,
`make`,
`model`,
`description`,
`mileage`,
`color`,
`price`,
`cityID`,
@now,
null,
`year`);
END IF;
IF (row_count()>0) then
select true;
end if;
COMMIT WORK;
end
The Database connection
namespace DAL;
class DB
{
// Local setup
private static $dbHost = '127.0.0.1';
private static $dbUser = 'username';
private static $dbPass = 'password';
private static $dbName = 'db';
protected static function Connect()
{
return mysqli_connect(self::$dbHost, self::$dbUser, self::$dbPass, self::$dbName);
}
}
The Helper that does a selection with parameters.
<?php
namespace DAL;
require_once '../DAL/DBHelper.php';
class DBHandler
{
public static function SelectParam($query, array $param)
{
//Variables
$arrayType = null;
//Format Calculation Loop
foreach ($param as $element) {
if (is_string($element)) {
$arrayType .= 's';
} else if (is_int($element)) {
$arrayType .= 'i';
} else if (is_double($element)) {
$arrayType .= 'd';
}
else if(is_bool($element))
{
$arrayType .='b';
}
}
//Open Connection
$conn = DB::Connect();
//Prepare Query
//Somewhere here it breaks, but I cannot find the answer.
$call = mysqli_prepare($conn, $query);
//Bind Query
call_user_func_array(array($call, 'bind_param'), array_merge(array($arrayType),$param));
//Execute Query
mysqli_stmt_execute($call);
//Fetch Result
$result = mysqli_stmt_get_result($call);
//Fill Row
$row = mysqli_fetch_all($result, MYSQLI_ASSOC);
//Close Connection
mysqli_close($conn);
//Return Result
return $row;
}
}
The Handler that parses the query and parameters to the Helper.
<?php
namespace DAL;
require_once '../DAL/DBHelper.php';
class DBHandler
{
public static function Advertise($advertID,$sellerID,$vehicleMake,$vehicleModel,$description,$price,$mileage,$color,$cityID, $vehicleYear)
{
$sp='CALL usp_Advert(?,?,?,?,?,?,?,?,?,?)';
$param = array(&$advertID,&$sellerID,&$vehicleMake,&$vehicleModel,&$description,&$price,&$mileage,&$color,&$cityID,&$vehicleYear);
return DBHelper::SelectParam($sp,$param);
}
}
The Business logic layer that actually sends the values through...
<?php
require_once '../BLL/CORS_Headers.php';
require_once '../DAL/DBHandler.php';
use DAL\DBHandler;
//$json=json_decode(file_get_contents('php://input'));
$json_encode(DBHandler::Advertise('dsknfdsnnfd','email@address.com','Toyota','Regius','it is cool',10000,10000,'Blue',1,2006));
?>
The values does make their way to the DBHelper, but it keeps returning a null when it is trying to bind the values to the parameters.
I tried altering tiny bits of code, but the result is still the same. All the other calls work, but this one decides not to.
