I'm trying to use mysql stored procedures to define my graphs in my new php app, but it's not quite working as I expect. One of my stored procedures is:
Code:
DELIMITER $$
DROP PROCEDURE IF EXISTS `flightmon`.`sp_graphAvgLowest`$$
CREATE DEFINER=`flightmonadmin`@`IP` PROCEDURE `sp_graphAvgLowest`()
BEGIN
select 'textlin' as 'graphType',
'Average of lowest prices found each day from\n%s to %s' as 'graphTitle',
'depart_date' as 'xCol',
'price' as 'yCol',
'TRUE' as 'bCentAvg',
'TRUE' as 'bOverallAvg';
select depart_date,
avg(price) as price,
query_date
from ( select depart_date,
min(price) as price,
query_date
from flightInfo
group by depart_date,
query_date
order by depart_date) as selection
group by depart_date order by depart_date asc;
END$$
DELIMITER ;
So, as you can see, I'm trying to just use a select statement to retrieve pre-set data to set up my graph inputs, and then have a second result set that contains the actual graph data. I was trying to do it this way so that I don't have to do any code changes to add available graphs to the project.
But, when I call it in my php code, the first result set that should have all my graph config data isn't there:
Code:
// Open db connection:
$con = new mysqli("<db_addr>","<username>","<password>", "<database>");
if (!$con)
{
die('Could not connect: ' . mysqli_error());
}
// Get graph selection code from GET:
$graphProcName = getGraphProcName($con, $_GET);
if ($con->query("call ".$graphProcName))
echo "Call successful...<br>";
else
echo "Problems during call... Error Code: ".$con->errno." Error Message: ".$con->error."<br>";
$graphDetails = null;
$result = null;
if ($graphDetailsResult = $con->store_result())
{
echo "Rows in first result: ".$graphDetailsResult->num_rows."<br>";
if ($graphDetails = mysqli_fetch_assoc($graphDetailsResult))
echo "got rows!";
$graphDetailsResult->close();
}
echo "<br>graphType: ".$graphDetails['graphType'];
echo "<br>graphTitle: ".$graphDetails['graphTitle'];
echo "<br>xCol: ".$graphDetails['xCol'];
echo "<br>yCol: ".$graphDetails['yCol'];
echo "<br>bCentAvg: ".$graphDetails['bCentAvg'];
echo "<br>bOverallAvg: ".$graphDetails['bOverallAvg'];
The call completes successfully (no db errors show up when I check that the call completed), but the first result set has no rows in it.
When I go to the next result set, I can loop through the data, and it's all there (I can display all of the entries for depart_date and price), just the first select of static text doesn't seem to be pulling anything back.
As an aside, this stored procedure pulls back two result sets with exactly what I am expecting when I run it in msyql query browser.