MySQL procedure returns empty result in PHP

Question

I have a problem when I wrote a stored procedure that inserts data into a bunch of tables, and then finally returns id, selecting it. when I execute the stored procedure in PHPMyAdmin, the data is inserted and the identifier is returned.

When executing a procedure from PHP using MySQLi, the data is inserted into the tables, however, when I check the results to get a new identifier, the result seems empty. Any help here would be greatly appreciated. I am not sure if the procedure returns empty results before it returns a result containing a string with a new identifier. Empty lines in phpMyAdmin runtime results bother me a bit?

Subroutine Results sp_createEvent

EventID 30

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 ;

PHP code:

<?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);

?>

Answer 1

SET eventid = LAST_INSERT_ID();

select LAST_INSERT_ID() into eventid;