Dynamic bind_result on PHP

Question

Possible Duplicate:
Dynamically bind mysqli_stmt parameters and then bind result (PHP)

can any one help me on how could I create a dynamic bind_result on PHP. My query field doesn't know how many fields are there since it was created dynamically (e.g creating year fields base on the date range). below is my script and highlighted on where is the problem.

public function getMarketingReports($datefrom,$dateto)
    {
        $yearfrom = date("Y", strtotime($datefrom));
        $yearto = date("Y", strtotime($dateto));

        //create year fields
        $concatYear = "";
        for($year=$yearfrom;$year<=$yearto;$year++){
            $concatYear .= "SUM(IF(c.datecreated='".$year."',IF(LOWER(c.fullTimeEployeeType)='basic hour rate', c.fullTimeEployeeTypeAmount*2080 , c.fullTimeEployeeTypeAmount),0)) ".$year.",";
        }


        $reportdata = array();
        $db = Connection::Open();
        $stmt = $db->stmt_init();
        if($stmt->prepare("SELECT p.Code `PositionCode`,
                                  p.name `PositionName`,
                                  l.value `Location`,
                                  ".$concatYear."
                                  SUM(b.field205) `TotalEmployees`
                             FROM c1 c
                            INNER JOIN b1 b
                               ON c.registrationid=b.id
                            INNER JOIN positions p
                               ON c.positionid=p.id
                            INNER JOIN lookupvalues l
                               ON c.location=l.id
                            WHERE c.`status`!=2
                              AND c.datecreated BETWEEN ? AND ?
                            GROUP BY c.positionid,c.location,YEAR(c.datecreated)")){

            $datefrom = $datefrom." 00:00:00";
            $dateto = $dateto." 23:59:59";
            $stmt->bind_param("ss",$datefrom,$dateto);
            $stmt->execute();
            $stmt->bind_result
            (
                $positionCode,
                $positionName,
                $location,

                **//getting bind result data here for year fields**

                $totalEmployees
            );
            while($stmt->fetch())
            {
                $surveydata = array();
                $surveydata['positionCode'] = $positionCode;
                $surveydata['positionName'] = $positionName;
                $surveydata['location'] = $location;

                **//storing of data here for year fields**

                $surveydata['totalEmployees'] = $totalEmployees;
                array_push($reportdata,$surveydata);
            }
        }
        Connection::Close();
        return $reportdata;

    }

Is it possible? Can anyone help me on how could I solve this problem

score 10 · Accepted Answer · edited May 23 '17 at 12:08

To start, your code that generates the year fields has a nice syntax error:

"SUM(IF(c.datecreated='".$year."',IF(LOWER(c.fullTimeEployeeType)='basic hour rate', c.fullTimeEployeeTypeAmount*2080 , c.fullTimeEployeeTypeAmount),0) ".$year.","

The SUM( opening statement is missing a closing parentheses. On top of that, you have a floating ".$year." that isn't part of any of the enclosed methods (though, it could be used as an alias; I'm not used to seeing it without a preceeding AS though - so this could be my mistake). To take a guess, I would say to replace the ".$year." with a ) and that should fix that part:

$concatYear .= "SUM(IF(c.datecreated='".$year."',IF(LOWER(c.fullTimeEployeeType)='basic hour rate', c.fullTimeEployeeTypeAmount*2080 , c.fullTimeEployeeTypeAmount),0)),";

If the $year addition is in fact to be an alias, you could add the closing parentheses immediately before it to close the SUM() function.

Regarding dynamically binding the variables, my ideal solution actually comes from a similar question/answer on SO (this is a direct copy/paste and I take no credit for it, but I do like it =P):

    // Get metadata for field names
    $meta = $stmt->result_metadata();

    // This is the tricky bit dynamically creating an array of variables to use
    // to bind the results
    while ($field = $meta->fetch_field()) { 
        $var = $field->name; 
        $$var = null; 
        $fields[$var] = &$$var;
    }

    // Bind Results
    call_user_func_array(array($stmt,'bind_result'),$fields);

    // Fetch Results
    $i = 0;
    while ($stmt->fetch()) {
        $results[$i] = array();
        foreach($fields as $k => $v)
            $results[$i][$k] = $v;
        $i++;
    }

score 1 · Answer 2 · answered Sep 28 '12 at 02:34

You have to call_user_func_array:

call_user_func_array(array($stmt, "bind_result"), $argArray);

And you fill $argArray this way:

$years = array();

$concatYear = "";
for($year=$yearfrom;$year<=$yearto;$year++){
    $concatYear .= "SUM(IF(c.datecreated='".$year."',IF(LOWER(c.fullTimeEployeeType)='basic hour rate', c.fullTimeEployeeTypeAmount*2080 , c.fullTimeEployeeTypeAmount),0) ".$year.",";

    // track the years you need
    $years[] = $year;

}

...

//you prepare all the vars here
$argArray = array(
    $positionCode,
    $positionName,
    $location
);

//loop the years
foreach($years as $y)
{

    $argArray[] = ${$y};

}

$argArray[] = $annualSalary;
$argArray[] = $totalEmployees;
$argArray[] = $yearOfData;

And you do stuff with years later this way:

...

foreach($years as $y)
{

$surveydata[$y] = ${$y};

}

how do I bind the parameters given on the above variables of $argArray[]..?? I'm going to loop on the bind_param parameters? — Bryan, Sep 28 '12 at 03:33

Dynamic bind_result on PHP

2 Answers2