获取存储过程 Laravel 的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42737433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Result of Stored Procedure Laravel
提问by Thibaud Lacan
I am using Laravel 5.4 and MySql 5.7, PHP 5.6. I am trying to call a stored procedure (that does a SELECT query, joining multiple tables), and to get the result returned to my PHP.
我使用的是 Laravel 5.4 和 MySql 5.7、PHP 5.6。我正在尝试调用一个存储过程（执行 SELECT 查询，加入多个表），并将结果返回给我的 PHP。
Here are the things I've unsuccessfully tried:
以下是我尝试失败的事情：
PHP 1:
PHP 1：
$result = DB::select('CALL rentalsAvailables_get(?, ?, ?, ?)',
array(
'p0' => Carbon::now(),
'p1' => Carbon::now()->addDays(7),
'p2' => 100,
'p3' => 2
)
);
return var_dump($result);
Result 1:
结果 1：
{
"error": {
"message": "SQLSTATE[HY000]: General error: 2031 (SQL: CALL rentalsAvailables_get(2017-03-11 16:00:42, 2017-03-18 16:00:42, 100, 2))",
"code": "HY000",
"status_code": 500
}
}
PHP 2:
PHP 2：
$result = DB::select('CALL rentalsAvailables_get(:p0, :p1, :p2, :p3)',
array(
'p0' => Carbon::now(),
'p1' => Carbon::now()->addDays(7),
'p2' => 100,
'p3' => 2
)
);
return var_dump($result);
Result 2:
结果 2：
array(0) {
}
** Stored Procedure: **
** 存储过程：**
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `rentalsAvailables_get`(IN `startDate` DATETIME, IN `endDate` DATETIME, IN `maxPrice` INT(11) ZEROFILL, IN `capacity` INT(2))
NO SQL
SELECT a.rentalId, a.rentalName, a.rentalBathroom, a.rentalBedroom, a.rentalCapacity, a.rentalCover, a.rentalLat, a.rentalLong, a.rentalLocation, a.rentalStatus, a.rentalSummary, a.rentalText, a.rentalOwnerId,
b.rentalTypeId, b.rentalTypeName,
c.id as 'userId', c.name,
d.rentalPriceId, d.rentalPrice
FROM rentals as a
INNER JOIN rentalTypes as b
ON a.rentalTypeId = b.rentalTypeId
INNER JOIN users as c
ON a.rentalOwnerId = c.id
INNER JOIN rentalprice as d
ON a.rentalId = d.rentalId
WHERE a.rentalCapacity > @p3
AND a.rentalStatus = true
AND d.rentalPrice < @p2
AND d.rentalPriceStartDate <= @p0
AND d.rentalPriceEndDate >= @p1
AND a.rentalId NOT IN (
SELECT eSub.rentalId FROM unavailabilities as eSub WHERE eSub.unavailabilityStartDate >= @p0 AND eSub.unavailabilityEndDate <= @p1
)
AND a.rentalId NOT IN (
SELECT fSub.rentalId FROM rent as fSub WHERE fSub.rentStartDate >= @p0 AND fSub.rentEndDate <= @p1
)$$
DELIMITER ;
Note:
笔记：
I have of course checked and i am getting results from my query when calling it like that:
我当然已经检查过，并且在像这样调用它时我从我的查询中得到了结果：
SET @p0='2017-03-11 04:26:09.000000';
SET @p1='2017-03-17 04:26:09.000000';
SET @p2='1000';
SET @p3='2';
CALL `rentalsAvailables_get`(@p0, @p1, @p2, @p3);
Did anyone already got this issue? Thanks for reading
有没有人已经得到这个问题？谢谢阅读
采纳答案by Khalil Laleh
You should pass CALL your_procedurevia DB::rawto DB::SELECT.
你应该CALL your_procedure通过DB::raw到DB::SELECT.
So this will work and it returns result set:
所以这将起作用并返回结果集：
$p0 = Carbon::now();
$p1 = Carbon::now()->addDays(7);
$p2 = 100;
$p3 = 2;
DB::select(DB::raw("CALL rentalsAvailables_get($p0, $p1, $p2, $p3)"));
Also you can convert the result set to Eloquent modelusing hydratemethod.
您也可以使用方法将结果集转换为Eloquent 模型hydrate。
Foo::hydrate($result_from_db_select);
Hope this help you after 6 months :))
希望这能在 6 个月后对您有所帮助:))
回答by ARIF MAHMUD RANA
You see this two values
你看到这两个值
2017-03-11 16:00:42, 2017-03-18 16:00:42//they are not escaped that's your problem
Solution -
解决方案 -
$result = \DB::statement('CALL rentalsAvailables_get(\'' . Carbon::now() . '\', \'' . Carbon::now()->addDays(7) . '\', 100, 2)')->fetchAll();
回答by thinhnk
$result = DB::select('CALL rentalsAvailables_get(?, ?, ?, ?)',
array(
'p0' => Carbon::now(),
'p1' => Carbon::now()->addDays(7),
'p2' => 100,
'p3' => 2
)
);
above is bind param by index => only need like below is enough
上面是通过索引绑定参数 => 只需要像下面这样就足够了
$result = DB::select('CALL rentalsAvailables_get(?, ?, ?, ?)',
array(
Carbon::now(),
Carbon::now()->addDays(7),
100,
2
)
);
Pls read more about PHP PDO
请阅读有关 PHP PDO 的更多信息
