too long

Question

I'm having a little bit of an issue with PDO binding Parameters.

My setup is as follows.

Ubuntu Desktop 16.04 Netbeans 8.1 (php and html only version) php cli 7.0.4 (Running internal web server) Postgres SQL 9.5 Slim Framework 3

I have opted to use PDO to access my database. This is my learning the system for a future project.

I can grab all records from a table, I can get the argument issued in the uri to echo on screen.

But using the GET method to locate a specific entry throws the following error at me.

{"error":{"text":SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 0 parameters, but prepared statement "pdo_stmt_00000001" requires 1}}

The following is my code.

db.php

<?php
function getDB() {
    $dbtype="pgsql";
    $dbhost="localhost";
    $dbuser="postgres";
    $dbpass="SomeSecurePassword";
    $dbname="bms";
    $dbConnection = new PDO("$dbtype:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $dbConnection;
}
?>

index.php

<?php

use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;

require 'vendor/autoload.php';
require 'db.php';

$app = new \Slim\App;

$app->get('/','getRoot');
$app->get('/contacts', 'getContacts');
$app->get('/contacts/{contact_id}', 'getContact');

$app->run();

function getRoot() {
echo 'This is the Root URI';
}

function getContacts() {
$sql = "SELECT last_name,first_name FROM contacts ORDER BY last_name DESC";
try {
    $db = getDB();
    $stmt = $db->query($sql);
    $contacts = $stmt->fetchAll(PDO::FETCH_OBJ);
    $db = null;
    echo '{"Contacts": ' . json_encode($contacts) . '}';
} catch(PDOException $e) {
    echo '{"error":{"text":'. $e->getMessage() .'}}';
}
}

function getContact(Request $request, Response $response, $args) {
$contact_id = (int)$args['contact_id'];
$sql = "SELECT * FROM contacts WHERE contact_id = :contact_id";
try {
    $db = getDB();
    $stmt = $db->query($sql);
    $stmt->bindParam(':contact_id', $contact_id, PDO::PARAM_INT);
    $stmt->execute();
    $stmt->debugDumpParams();
    $db = null;
    echo '{"Contact": ' . json_encode($contact) . '}';
} catch(PDOException $e) {
    echo '{"error":{"text":'. $e->getMessage() .'}}';
}
}

I'm lost as to where I could be going wrong. Any guidance would be appreciated.

Thanks in advanced.

Answer 1

You need to use prepared statements.

$stmt = $db->query($sql); //Executes a query and returns a statement

What you want is...

$stmt = $db->prepare($sql);
$stmt->bindParam(':contact_id', $contact_id, PDO::PARAM_INT);
$stmt->execute();