SQLSTATE[HY093] : pdo statement during insert into mysql db

Question

Here it is my php code to realize insert into db:

<?php

require_once "includes/db_data_inc.php";

try 
{
    $DBH = new PDO("mysql:host=$db_host;dbname=$db_name",$db_user,$db_pass);

    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $cathy = new patient($_POST['name'],
                         $_POST['surname'],
                         $_POST['address'],
                         $_POST['birth-place'],
                         $_POST['province'],
                         $_POST['dt'],
                         $_POST['gender'],
                         $_POST['select']);

    $STH = $DBH->prepare("INSERT INTO users (name, 
                                            surname, 
                                            address, 
                                            birth_place,
                                            province,
                                            dt,
                                            sex,
                                            case) value (:name
                                                        :surname,
                                                        :address,
                                                        :birth_place,
                                                        :province,
                                                        :dt,
                                                        :sex,
                                                        :case)");

    $STH->execute((array)$cathy);

}
catch (PDOException $pdoe) 
{
    error_log($pdoe->getMessage());
    die("An error was encountered!");
}

?>

Here it is db_data_inc.php where are stored db_info and where I create the object patient

    $db_host = 'localhost';

$db_name = 'main_db';

$db_user = 'root';

$db_pass = 'root';

/* Create an object patient */

class patient
{
    public $name;
    public $surname;
    public $address;
    public $birth_place;
    public $province;
    public $birth_date;
    public $sex;
    public $case;

    function __construct($nm,$sur,$addr,$bp,$pr,$bd,$sx,$cs)
    {
        $this->name = $nm;
        $this->surname = $sur;
        $this->address = $addr;
        $this->birth_place = $bp;
        $this->province = $pr;
        $this->birth_date = $bd;
        $this->sex = $sx;
        $this->case = $cs;
    }

}

I get this error:

[10-Feb-2012 21:14:29] SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

But I didn't realize the reason...why I got this error? someone can help me? Where is the mistake?

@DamienPirsy Here I do the cast $STH->execute((array)$cathy); — eng_mazzy, Feb 10 '12 at 23:22
If you var_dump($catchy), what does it say? is it the correct array? Btw, why build a class just for that, and not a simple array?? — Damien Pirsy, Feb 10 '12 at 23:24
You might be using the wrong name placeholder; see my answer below — Damien Pirsy, Feb 10 '12 at 23:30

score 1 · Answer 1 · answered Feb 10 '12 at 20:38

Not sure if this is the cause of your error, but in your insert statement, CASE is a MySQL reserved word and should be escaped with backticks.

$STH = $DBH->prepare("INSERT INTO users (name, 
                                        surname, 
                                        address, 
                                        birth_place,
                                        province,
                                        dt,
                                        sex,
                                        `case`) value (:name
                                                    :surname,
                                                    :address,
                                                    :birth_place,
                                                    :province,
                                                    :dt,
                                                    :sex,
                                                    :case)");

Damien Pirsy · Accepted Answer · 2012-02-10T23:35:12.237

1

In your query you use :dt as a placeholder, but in the class constructor you use $this->birth_date.

Once casted, this will create an array with index 'birth_date', which doesn't match with the named parameter "dt": choose one or the other.

edited Feb 10 '12 at 23:35

answered Feb 10 '12 at 23:23

Damien Pirsy

25,319
8
70
77

Now unfortunately another error come back: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 – eng_mazzy Feb 10 '12 at 23:41
print_r($catchy) and see if all the array indexes are there and are correct, i.e. they match the placeholders, the column names..and this might also be related to the other answer ('case' being a reserved word both for SQL and php) – Damien Pirsy Feb 10 '12 at 23:44
1

ok ok...I resolved anything...I made a mistake to write.excuse me...THANKS AGAIN SO MUCH!!!!!! – eng_mazzy Feb 10 '12 at 23:47

SQLSTATE[HY093] : pdo statement during insert into mysql db

2 Answers2

Linked