i have issue wrote stored procedure inserts data bunch of tables , returns , id selecting it. when execute stored procedure in phpmyadmin, data inserted , id returned.
when executing procedure php using mysqli, data inserted tables when check results retrieve new id, result seems blank. here appreciated. not sure if procedure returns empty results before returns result containing row new id. blank lines in execution results in phpmyadmin bit of bother me?
execution results of routine 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 not found begin end; start transaction; select concat(u.`display_name`, " - ",vareventname), replace(lower(concat(rtrim(u.`display_name`), "-",vareventname)), " ", "-"), uasc.`album_id` albumname, albumslug, usertopalbum `wp_users` u left join `wp_pocketbooth_useralbumshortcode` uasc on uasc.`wp_user_id` = u.id u.`id` = varuserid , uasc.`album_id` not null; insert `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 `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 `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 `wp_bwg_album_gallery`(`id`, `album_id`, `is_album`, `alb_gal_id`, `order`) values (null,eventalbumid,'0',fullphotosgalleryid,'1'); insert `wp_bwg_album_gallery`(`id`, `album_id`, `is_album`, `alb_gal_id`, `order`) values (null,eventalbumid,'0',photostripgalleryid,'1'); insert `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 `wp_pocketbooth_eventsubscriptions` (`subscriptionid`, `userid`, `eventid`, `isowner`) values (null, varuserid, eventid, 1); commit; select eventid; end $$ delimiter ; the 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); ?>
Comments
Post a Comment