CODEWITHSUNDEEP
phpsqldatabasepostgresqlpdo
MasterOdin
MasterOdin

Reputation: 7886

Error with postgres/pdo query binding values

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).

Upvotes: 1

Views: 808

Answers (2)

Adrian Cid Almaguer
Adrian Cid Almaguer

Reputation: 7791

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();

Upvotes: 1

dev1234
dev1234

Reputation: 5716

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.

Upvotes: 2

Related Questions