I have an issue where I wrote a stored procedure that inserts data into a bunch of tables and then finally returns and id by selecting it. when I execute the stored procedure in PHPMyAdmin, the data is inserted and the ID returned.
我有一个问题,我写了一个存储过程,将数据插入一堆表,然后通过选择它返回和id。当我在PHPMyAdmin中执行存储过程时,将插入数据并返回ID。
When executing the procedure from PHP using MySQLi, the data is inserted into the tables however when I check the results to retrieve the new id, the result seems to be blank. Any help here would be appreciated. I am not sure if the procedure returns empty results before it returns the result containing the row with the new ID. The Blank lines in the Execution results in phpMyAdmin is a bit of a bother to me?
当使用MySQLi从PHP执行过程时,数据被插入到表中,但是当我检查结果以检索新id时,结果似乎是空白的。任何帮助在这里将不胜感激。我不确定该过程在返回包含具有新ID的行的结果之前是否返回空结果。 phpMyAdmin执行结果中的空行对我来说有点麻烦?
Execution results of routine sp_createEvent
例程sp_createEvent的执行结果
eventid 30
The Stored Procedure:
存储过程:
DELIMITER $$
CREATE PROCEDURE `sp_createEvent` (IN varEventName varchar(200), IN varUserID INT, IN varPrintingRequested INT, IN varEventCode BIGINT, IN varActiveFrom DATETIME, IN varActiveTo DATETIME, IN varLimitGuestPhotos BIT)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
DECLARE albumname VARCHAR(50);
DECLARE albumslug VARCHAR(50);
DECLARE usertopalbum INT;
DECLARE eventalbumid INT;
DECLARE fullphotosgalleryid INT;
DECLARE photostripgalleryid INT;
DECLARE eventid INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
START TRANSACTION;
SELECT
CONCAT(u.`display_name`, " - ",varEventName),
REPLACE(LOWER(CONCAT(RTRIM(u.`display_name`), "-",varEventName)), " ", "-"),
uasc.`album_id`
INTO
albumname,
albumslug,
usertopalbum
FROM `wp_users` u
LEFT JOIN `wp_pocketbooth_useralbumshortcode` uasc on uasc.`wp_user_id` = u.ID
WHERE u.`ID` = varUserID and
uasc.`album_id` is not null;
INSERT INTO `wp_bwg_album`(`id`, `name`, `slug`, `description`, `preview_image`, `random_preview_image`, `order`, `author`, `published`)
VALUES (NULL,albumname,albumslug,'smartbooth.co.za Album','','','1','1','1');
SET eventalbumid = LAST_INSERT_ID();
INSERT INTO `wp_bwg_gallery`(`id`, `name`, `slug`, `description`, `page_link`, `preview_image`, `random_preview_image`, `order`, `author`, `published`, `gallery_type`, `gallery_source`, `autogallery_image_number`, `update_flag`)
VALUES (NULL, CONCAT(albumname, ' (Full Photos)'), CONCAT(albumslug, '-full-photos'),'smartbooth.co.za Gallery','','','','','1','1',1,'','12','');
SET fullphotosgalleryid = LAST_INSERT_ID();
INSERT INTO `wp_bwg_gallery`(`id`, `name`, `slug`, `description`, `page_link`, `preview_image`, `random_preview_image`, `order`, `author`, `published`, `gallery_type`, `gallery_source`, `autogallery_image_number`, `update_flag`)
VALUES (NULL, CONCAT(albumname, ' (Photo Strips)'), CONCAT(albumslug, '-photo-strips'),'smartbooth.co.za Gallery','','','','','1','1',1,'','12','');
SET photostripgalleryid = LAST_INSERT_ID();
INSERT INTO `wp_bwg_album_gallery`(`id`, `album_id`, `is_album`, `alb_gal_id`, `order`)
VALUES (NULL,eventalbumid,'0',fullphotosgalleryid,'1');
INSERT INTO `wp_bwg_album_gallery`(`id`, `album_id`, `is_album`, `alb_gal_id`, `order`)
VALUES (NULL,eventalbumid,'0',photostripgalleryid,'1');
INSERT INTO `wp_pocketbooth_events` (`eventid`, `eventname`, `printingrequested`, `printimages`, `eventcode`, `toplevelalbumid`,`activefrom`,`activeto`, `limitguestphotototime`)
VALUES (NULL, varEventName, varPrintingRequested, 0, varEventCode, eventalbumid, varActiveFrom, varActiveTo, varLimitGuestPhotos);
SET eventid = LAST_INSERT_ID();
INSERT INTO `wp_pocketbooth_eventsubscriptions` (`subscriptionid`, `userid`, `eventid`, `isowner`)
VALUES (NULL, varUserID, eventid, 1);
commit;
select eventid;
END $$
DELIMITER ;
The PHP code:
PHP代码:
<?php
include 'connection.php';
$userid = $_POST['userid'];
$eventname = $_POST['eventname'];
$printingrequested = $_POST['printingrequested'];
$eventcode = $_POST['eventcode'];
$activefrom = $_POST['activefrom'];
$activeto = $_POST['activeto'];
$limitphotos = $_POST['limitphotos'];
$sql = "CALL `sp_createEvent` ('$eventname' , '$userid' , '$printingrequested' , '$eventcode' , '$activefrom' ,'$activeto', '$limitphotos');";
$res = $connection->query($sql);
if (!$res)
{
echo "Error: " . $sql . "<br>" . mysqli_error($connection);
}
else
{
$data = array();
while($row = mysql_fetch_array($res)) {
$data[] = $row['eventid'];
}
echo json_encode($data);
}
@mysqli_close($conn);
?>
1 个解决方案
#1
0
instead of SET eventid = LAST_INSERT_ID(); write
而不是SET eventid = LAST_INSERT_ID();写
select LAST_INSERT_ID() into eventid;
#1
0
instead of SET eventid = LAST_INSERT_ID(); write
而不是SET eventid = LAST_INSERT_ID();写
select LAST_INSERT_ID() into eventid;