Empty result set from mysql stored procedure

talz13 · #1 (**permalink**) December 18th, 2008, 09:48 AM

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.

Old Pedant · #2 (**permalink**) December 19th, 2008, 10:33 PM

I'm sure I already know the answer, but...

What happens if you change the SP to only return the first result set? Does it THEN come back okay??

talz13 · #3 (**permalink**) December 23rd, 2008, 11:21 AM

I had a whole response typed up but my browser crashed... (repeatable crash for me, ctrl+z in firefox on ubuntu 8.10 causes crash for me)

I changed my SP to just return the first result set, and it retrieved nothing:

Code:

procString: call sp_graphAvgLowest
Call successful...
No result set returned!

graphType: 
graphTitle: 
xCol: 
yCol: 
bCentAvg: 
bOverallAvg:

The call completed successfully, but no result was returned.

Here is the updated SP with only the first result set:

Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `flightmon`.`sp_graphAvgLowest`$$
CREATE DEFINER=`<name goes here>` 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 ;

Old Pedant · #4 (**permalink**) December 28th, 2008, 05:58 PM

Sorry...missed this post of yours.

I dunno what to tell you. I tried this (the single SELECT, not the two of them) with ASP (instead of PHP) and it worked fine.

Need to download code?

Navigation