php PDO insert error

Question

Hello guys i have a small isue.. I'm new to PDO and i want to implement it in my website, i have the following code:

foreach($liga1 as $item) {
$data = $item->get_date('j M Y, g:i a');
$titlu = $item->get_title();
$link = $item->get_permalink();
$text = $item->get_description();
$cat = "Liga 1";

$dbhost     = "localhost";
$dbname     = "site";
$dbuser     = "root";
$dbpass     = "root";

// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

// query
$sql = "INSERT INTO stiri ('id','data', 'continut', 'link', 'titlu', 'categorie' ) VALUES (:data, :text, :link, :titlu, :categorie)";
$q = $conn->prepare($sql);
$q->execute(array(':data', $data,
                ':continut', $text,
                ':link', $link,
                ':titlu', $titlu,
                ':categorie', $cat));
}

When i run this code i get the following error:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampp\htdocs\ice\sql\sport2.php on line 50

As you can see i want to insert in the database a specific category wich i input, also i have in the mysql table the "id" wich i don't know how to insert it using PDO. I will also want ot use classes here because i need to put the same code on over 20 pages.

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

The problem is uou have define 5 columns but you have only supplied four values. If the column name ID is set as AUTO_INCREMENTed column, you can omit it from the column list, eg.

INSERT INTO stiri (data, continut, link, titlu, categorie ) VALUES (...)

Another reason that an error will soon arise is you are wrapping the column names with single quotes. It converts the column name into string that's why the query failed. In order to work out, remove the single quotes around them.

INSERT INTO stiri (id,data, continut, link, titlu, categorie ) VALUES (...)

MySQL - when to use single quotes, double quotes, and backticks?

UPDATE 1

$sql = "INSERT INTO stiri (data, continut, link, titlu, categorie) VALUES (?, ?, ?, ?, ?)";
$q = $conn->prepare($sql);
$q->bindParam(1, $data);
$q->bindParam(2, $text);
$q->bindParam(3, $link);
$q->bindParam(4, $titlu);
$q->bindParam(5, $cat);
$q->execute();

edited May 23 '17 at 11:49

Community

1
1

answered Mar 01 '13 at 00:18

John Woo

258,903
69
498
492

Changed the code but the error is still present. – Cosmin Stan Mar 01 '13 at 00:25
also, unless that's an alternative syntax, the execute array should contain key-value pairs in the form of `array('data'=>$data, 'content'=>$text),...)` – mpen Mar 01 '13 at 00:26
Ok, edited but i get this error now : Invalid parameter number: parameter was not defined changed array(1=>$data, 2=>$text),...) and in the insert i put ? i am going the right way ? – Cosmin Stan Mar 01 '13 at 00:35
how about the updated answer? – John Woo Mar 01 '13 at 00:42

score 1 · Accepted Answer · answered Mar 01 '13 at 00:37

1

you're missing :text . You need to add another param for it. you named it contuit and told the pdo to expect :text

answered Mar 01 '13 at 00:37

Zak

24,947
11
38
68

Yes indeed i make a mistake a i assumed that the code was written badly, thak you for the remark – Cosmin Stan Mar 01 '13 at 00:43

php PDO insert error

2 Answers2