Why empty result when passing argument via execute?

Question

I expected moving WHERE argument value string from the query to execute() would be a simple adjustment but it fails. I've read related documentation and cannot understand why I'm getting an empty result in the latter case. How can I pass the school_year in the execute method?

Returns expected results:

$query =   "SELECT faculty_id, school_year, term, department_name, course_name, section_id
                    FROM all_school_classfeedback
                    WHERE school_year = '2012-2013'";
        if ($stmt = self::$dbCon->prepare($query)) 
        {

            $stmt->execute();

Returns empty:

$query =   "SELECT faculty_id, school_year, term, department_name, course_name, section_id
                    FROM all_school_classfeedback
                    WHERE school_year = :school_year";
        if ($stmt = self::$dbCon->prepare($query)) 
        {

            $stmt->execute(array(
                ':school_year' => '2012-2013'
            ));

Datatype of field: school_year varchar(10) utf8_general_ci

Related links and posts:

I get boners from people down voting without telling me why. — bassxzero, Sep 16 '15 at 21:55
@Dhirendra datatype `school_year varchar(10) utf8_general_ci` — Geoffrey Hale, Sep 16 '15 at 23:29
It should work. You are using `PDO::ATTR_EMULATE_PREPARES` as `true` which is the default setting for the PDO connection? Are you displaying errors? Also, have you set the `PDO` connection to throw execptions on error? i.e. `setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)` — Ryan Vincent, Sep 17 '15 at 00:34

score 1 · Answer 1 · answered Sep 16 '15 at 00:55

1

I think the parm is failing to bind because its data type is taken from php. Basically MySQL is looking for one data type (date?) and PHP says that the parm is of a different type (string).

Try

$year = '2012-2013';

$query =   "SELECT faculty_id, school_year, term, department_name, course_name, section_id
                    FROM all_school_classfeedback
                    WHERE school_year = :school_year";
        if ($stmt = self::$dbCon->prepare($query)) 
        {
             $stmt->bindParam(':school_year', $year, PDO::PARAM_STR);
             $stmt->execute(); 
       }

answered Sep 16 '15 at 00:55

bassxzero

4,838
22
34

datatype `school_year varchar(10) utf8_general_ci` – Geoffrey Hale Sep 16 '15 at 23:29
1

[PHP Manual](http://php.net/manual/en/pdostatement.execute.php): `public bool PDOStatement::execute ([ array $input_parameters ] )` -- "input_parameters is an array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR." – Geoffrey Hale Sep 16 '15 at 23:50

Why empty result when passing argument via execute?

1 Answers1