Error with postgres/pdo query binding values

Question

I'm running the following query in postgres:

SELECT (SUM(g.grade_days_late) - SUM(s.ex_late_days)) as used_late_days 
FROM grades as g 
    LEFT JOIN ( SELECT * FROM late_day_exceptions ) 
        as s on s.ex_rubric_id = g.rubric_id and s.ex_student_rcs=g.student_rcs 
WHERE g.student_rcs='?' AND g.status=1 AND g.rubric_id < ?

with the following commands:

$statement = Database::$link->prepare($query);
$statement->execute($parameters);

$parameters is equal to:

array (size=2)
  0 => string 'test' (length=7)
  1 => int 6

and I get the following error:

PDOException: SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $2

I run the query directly against the database replacing the two ? with the stuff in $parameters and it works fine. If I add ' around the second parameter, the query returns nothing (yet still returns the proper answer if run directly against it with the values inserted).

I really have no idea what's going on here or why I'm getting an error. (Database is an open PDO connection, no issues there).

Why do you use a sub select? LEFT JOIN late_day_exceptions should do the same. — Frank Heikens
– Frank Heikens, Commented Apr 8, 2015 at 5:58

dev1234 · Accepted Answer · 2015-04-08 04:36:25Z

2

The length of the array should match the query output. in your case used_late_days

function execute() expects a single argument that is an array of parameters. Each element of that array is treated as a query parameter. So right now PDO "thinks" that you pass two parameters while your query contains only one.

answered Apr 8, 2015 at 4:36

dev1234

5,75617 gold badges62 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adrian Cid Almaguer · Accepted Answer · 2015-04-08 04:40:09Z

1

Should be WHERE g.student_rcs= ? in your query:

SELECT (SUM(g.grade_days_late) - SUM(s.ex_late_days)) as used_late_days 
FROM grades as g 
    LEFT JOIN ( SELECT * FROM late_day_exceptions ) 
        as s on s.ex_rubric_id = g.rubric_id and s.ex_student_rcs=g.student_rcs 
WHERE g.student_rcs= ? AND g.status=1 AND g.rubric_id < ?

And bind the values like this:

$statement = Database::$link->prepare($query);

$statement->bindValue(1, $parameters[0], PDO::PARAM_STR);
$statement->bindValue(2, $parameters[1], PDO::PARAM_INT);

$statement->execute();

edited Apr 8, 2015 at 4:40

answered Apr 8, 2015 at 4:36

Adrian Cid Almaguer

7,79113 gold badges44 silver badges66 bronze badges

2 Comments

MasterOdin Over a year ago

As soon as I looked at your solution, I realized that '?' was being interpreted as a literal and not as something to be replaced. Ugh, the little things.

MasterOdin Over a year ago

yup, just had to wait for SO to actually let me accept it ;)

Collectives™ on Stack Overflow

Error with postgres/pdo query binding values

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related