'Invalid parameter number' error using bindParam to create an mySQL query

Question

I have written a function that takes four arguments, and passes them to my MySQL database using a PDO.

I am trying to use bindParam so that I can use the variables to construct the query. I have followed the examples in the PHP documentation, but I am getting the following error:

ERROR: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

What am I doing wrong?

function saveAddress($addressLine1, $addressLine2, $town, $county_id) {

    try {
        $conn = new PDO('mysql:host=localhost;dbname=' . DB_DATABASE, DB_USER, DB_PASSWORD);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $conn->prepare('INSERT INTO address (`address_line_1`, `address_line_2`, `town`, `county_id`)
            VALUES (:addressLine1, :addressLine2, :town, :county_id);');
        $stmt->bindParam(':addressLine1', $addressLine1, PDO::PARAM_STR);
        $stmt->bindParam(':addressLine2', $addressLine2, PDO::PARAM_STR);
        $stmt->bindParam(':town', $town, PDO::PARAM_STR);
        $stmt->bindParam(':county_id', $county_id, PDO::PARAM_INT);     
        $success = $stmt->execute(array());
    }
    catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
}

keelerm keelerm · Accepted Answer · 2013-12-28T21:45:55

$stmt->execute takes an optional array parameter which represents the values to bind in your prepared statment.

Since you are explicitly calling bindParam, you don't need to pass in the array param to execute.

Just as a side note, if in the future you choose to use the array option instead of calling bindParam, be aware that each of the values in that array will be bound using PDO::PARAM_STR.

'Invalid parameter number' error using bindParam to create an mySQL query

1 Answers