prepare($sql); $stmt->bindValue(':SavedPlanGUID', $savedPlanGUID, PDO::PARAM_INT); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); $jsonopts = array(); // rebuild json like it existed in older method using child SavedPlanOptions records foreach($rows AS &$row) { $savedOptions = explode(',',$row['SavedPlanOptions']); foreach($savedOptions AS $k => $v) $jsonopts[]['optionid'] = $v; $row['SavedPlanOptions'] = json_encode($jsonopts); } //echo "rows=

" . print_r($rows, TRUE) . "

"; echo(json_encode($rows)); } catch (Exception $ex) { die('Error! Error getting saved plan.'); } exit(0); */ try { $sql = "SELECT SavedPlan.SavedPlanID ,SavedPlanGUID AS SavedPlanGUID ,'' AS SavedPlanEmail ,'' AS SavedPlanPhone ,'' AS SavedPlanFirstName ,'' AS SavedPlanLastName ,SavedPlan.SavedPlanName AS SavedPlanName ,SavedPlan.SavedPlanFlippedH AS SavedPlanFlippedH ,SavedPlan.SavedPlanFlippedV AS SavedPlanFlippedV ,CONCAT('[', GROUP_CONCAT('{\"optionid\":\"', COALESCE(newopt.OptionID,origopt.OptionID),'\"}'),']') AS `SavedPlanOptions` ,'' AS SavedPlanComponents ,SavedPlan.SavedPlanIfpVersion AS SavedPlanIfpVersion ,SavedPlan.SavedPlanSkinPath AS SavedPlanSkinPath ,SavedPlan.SavedPlanComponentGroupIDs AS SavedPlanComponentGroupIDs FROM SavedPlan LEFT JOIN SavedPlanOption ON SavedPlanOption.SavedPlanID=SavedPlan.SavedPlanID LEFT JOIN PlanOption origopt ON origopt.OptionID=SavedPlanOption.PlanOptionID LEFT JOIN PlanOptionGroup origoptgrp ON origoptgrp.OptionGroupID=origopt.OptionGroupID INNER JOIN Plan orig ON SavedPlan.PlanID=orig.PlanID LEFT JOIN Plan new ON new.OriginalPlanID=orig.OriginalPlanID AND (new.Status='Approved' OR new.IsActive=1) LEFT JOIN PlanOptionGroup newoptgrp ON newoptgrp.PlanID=new.PlanID LEFT JOIN PlanOption newopt ON newopt.OptionGroupID=newoptgrp.OptionGroupID AND newopt.OptionData1=origopt.OptionData1 LEFT JOIN `User` on `SavedPlan`.`UserIDWeb` = `User`.`UserID` WHERE (origopt.OptionID IS NULL OR newopt.OptionID IS NOT NULL) AND SavedPlanGUID=:SavedPlanGUID GROUP BY SavedPlanGUID"; //$sql = "SELECT * FROM vw_SavedPlanJSON WHERE SavedPlanGUID=:SavedPlanGUID LIMIT 1"; --- THE OLD WAY --- $stmt = $db->prepare($sql); $stmt->bindValue(':SavedPlanGUID', $savedPlanGUID, PDO::PARAM_INT); $stmt->execute(); $SavedPlan = $stmt->fetchAll(PDO::FETCH_ASSOC); $savedplan_id = $SavedPlan[0]['SavedPlanID']; // $jsonopts = array(); } catch (Exception $ex) { die('Error! Error getting saved plan.'); } /* try { $sql = "SELECT SavedPlanID, CONCAT('[', GROUP_CONCAT('\"{\"optionid\":\"', COALESCE(newopt.OptionID,origopt.OptionID),'\"}'),']') AS `SavedPlanOptions` FROM SavedPlanOption INNER JOIN PlanOption origopt ON origopt.OptionID=SavedPlanOption.PlanOptionID INNER JOIN PlanOptionGroup origoptgrp ON origoptgrp.OptionGroupID=origopt.OptionGroupID INNER JOIN Plan orig ON origoptgrp.PlanID=orig.PlanID LEFT JOIN Plan new ON new.OriginalPlanID=orig.OriginalPlanID AND new.Status='Approved' AND new.IsActive=1 LEFT JOIN PlanOptionGroup newoptgrp ON newoptgrp.PlanID=new.PlanID LEFT JOIN PlanOption newopt ON newopt.OptionGroupID=newoptgrp.OptionGroupID AND newopt.OptionData1=origopt.OptionData1 WHERE SavedPlanOption.SavedPlanID=" . (int)$savedplan_id . " GROUP BY SavedPlanOption.SavedPlanID"; $stmt = $db->prepare($sql); $stmt->execute(); $SelectedOptions = $stmt->fetchAll(PDO::FETCH_ASSOC); // rebuild json like it existed in older method, now using child SavedPlanOptions records $savedOptions = explode(',',$SelectedOptions[0]['SavedPlanOptions']); foreach($savedOptions AS $k => $v) $jsonopts[]['optionid'] = $v; $SavedPlan[0]['SavedPlanOptions'] = json_encode(stringify_numbers($jsonopts)); $SavedPlan[0]['SavedPlanOptions'] = $SelectedOptions[0]['SavedPlanOptions']; } catch (Exception $ex) { die('Error! Error getting saved plan options.'); } */ try { $sql = "SELECT COALESCE(newview.ViewID,origview.ViewID) AS viewid , ComponentID AS componentid , DropID AS dropid , Type AS type , IF(Position<>'',Position,NULL) AS position , IF(Data<>'{}',Data,NULL) AS data , IF(X>0,X,NULL) AS x , IF(Y>0,Y,NULL) AS y , IF(Rotation<>0,Rotation,NULL) AS rotation , IF(FlippedH=1,'true','false') AS flippedh , IF(FlippedV=1,'true','false') AS flippedv , IF(ForceTop=1,'true','false') AS forcetop , IF(ForceBottom=1,'true','false') AS forcebottom , IF(DimensionWidth>0,DimensionWidth,NULL) AS dimensionwidth , IF(DimensionHeight>0,DimensionHeight,NULL) AS dimensionheight , IF(DimensionFixed=1,'true','false') AS dimensionfixed , IF(DimensionNoSize=1,'true','false') AS dimensionnosize , IF(ScaleX>0,ScaleX,NULL) AS scalex , IF(ScaleY>0,ScaleY,NULL) AS scaley FROM SavedPlanComponent INNER JOIN PlanView origview ON origview.ViewID=SavedPlanComponent.ViewID INNER JOIN Plan orig ON origview.PlanID=orig.PlanID LEFT JOIN Plan new ON new.OriginalPlanID=orig.OriginalPlanID AND new.Status='Approved' AND new.IsActive=1 LEFT JOIN PlanView newview ON newview.PlanID=new.PlanID AND newview.ViewSVGID=origview.ViewSVGID WHERE SavedPlanID=" . (int)$savedplan_id ; $stmt = $db->prepare($sql); $stmt->execute(); $SelectedComponents = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($SelectedComponents AS &$component) { foreach ($component AS $k=>$v) { if (is_null($v)) unset($component[$k]); // remove NULL data for json } } $SavedPlan[0]['SavedPlanComponents'] = json_encode(stringify_numbers($SelectedComponents)); } catch (Exception $ex) { die('Error! Error getting saved plan components.'); } echo(json_encode($SavedPlan)); // helper function stringify_numbers($obj) { foreach($obj as &$item) { if(is_object($item) || is_array($item)) $item = stringify_numbers($item); // recurse! if(is_numeric($item)) $item = (string)$item; } return $obj; }