How to write a prepared insert statement?

Question

I'm trying to learn how to write a PDO insert statement, so apologise if the code is not so good, It's coming up with error:

SQLSTATE[42000]: Syntax error or access violation: 1064 at line 3

But I can't see why it's not working.

Note: I'm trying to pass html input data to a database, and in prepared statement online it says something like this:

$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";

$variable is relating to the database field name, but how do you reference the name in the html input field?

code can be found here and below: http://pastebin.com/fjAy1Fvn

<?php
include_once 'dbconnect.php';  
   if(isset($_POST["update_vacancies"])){       

    try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // begin the transaction
    $conn->beginTransaction();
    // our SQL statements
   $conn->exec("INSERT INTO vacancies (vac_id, vac_post_date, vac_job_title, vac_comp_name, vac_ess_one, vac_ess_two, vac_ess_three, vac_ess_four, vac_ess_five, vac_ess_six, vac_ess_seven, vac_ess_eight, vac_ess_nine, vac_ess_ten,  vac_des_one, vac_des_two, vac_des_three, vac_des_four, vac_des_five, vac_des_six, vac_des_seven, vac_des_eight, vac_des_nine, vac_des_ten, add_info) 

   VALUES ('vacData', 'postaDate', 'jobTitle', 'companyNanme', 'vac_ess_one', 'vac_ess_two', 'vac_ess_three', 'vac_ess_four', 'vac_ess_five', 'vac_ess_six', 'vac_ess_seven', 'vac_ess_eight', 'vac_ess_nine', 'vac_ess_ten', ,'vac_des_one' ,'vac_des_two' ,'vac_des_three' ,'vac_des_four' ,'vac_des_five' ,'vac_des_six' ,'vac_des_seven' ,'vac_des_eight' ,'vac_des_nine' ,'vac_des_ten' ,'add_info'  )");


// commit the transaction
$conn->commit();
echo "New vacancy created successfully";
}
catch(PDOException $e)
{
// roll back the transaction if something failed
$conn->rollback();
echo "Error: " . $e->getMessage();
}
}

$conn = null;
?>

Any help is genuinely appreciated.

Are you able to execute that query manually on the database? — David, Feb 10 '16 at 12:15
What you are doing has nothing to do with prepared statements. You should read tutorials about it — Jens, Feb 10 '16 at 12:16
`'vac_ess_ten', ,'vac_des_one'` : you have two commas instead of one. change in: `'vac_ess_ten', 'vac_des_one'` — fusion3k, Feb 10 '16 at 12:17
Are you aware that you are inserting 'vac_ess_ten' (etc...) **word**, not a variable value? — fusion3k, Feb 10 '16 at 12:18
I'm not getting an error any more, but it still won't pass the data to the database. — Kez, Feb 10 '16 at 12:22
@Kez you have to insert variables prepended by `$` (you know the variable names). Please also take a look at [Prepared Statements](http://www.w3schools.com/php/php_mysql_prepared_statements.asp) — fusion3k, Feb 10 '16 at 12:28
so the html input the name="variable" and in the php '$variable'? Thanks for the link! :) — Kez, Feb 10 '16 at 12:42
Why do you include `dbconnect.php` but then you also connect to the database in the actual code? — Tom, Feb 10 '16 at 12:53

score 1 · Answer 1 · answered Feb 10 '16 at 12:52

1

You can write a prepared insert statement like this:

<?php
$queryInsert = $conn->query('INSERT tableName (`example`, `exampleInt`) VALUES  (:example, :exampleInt)');

try{
    $queryInsert->execute([
         ':example'    => $example,
         ':exampleInt' => (int)$exampleInt
    ]);
}
catch(PDOException $e){
    echo $e->getMessage(); //Remove this when done testing and put your own error message here.
}
?>

answered Feb 10 '16 at 12:52

Tom

606
7
28

So this will take html input data via name and pass it to the database? – Kez Feb 10 '16 at 12:57
If you define `$example` and `$exampleInt` above the `try` then it will pass that information to where you see `:example` and `:exampleInt`, in this code example `$exampleInt` must be an integer or it will return `null` – Tom Feb 10 '16 at 12:59
[Neither `echo $e->getMessage();` nor any own error message have to be here, as well as try and catch at all.](https://phpdelusions.net/pdo#errors). Just leave try and catch alone. – Your Common Sense Feb 10 '16 at 13:00
Also, it is essentially pointless to cast a value you send in execute. – Your Common Sense Feb 10 '16 at 13:01
@YourCommonSense True, but it will make your code safer if an exception happens. – Tom Feb 10 '16 at 13:02
@YourCommonSense It might be that you are right, although I personally try to do this whenever user input comes into place, so that I know what the error will be if something went wrong. – Tom Feb 10 '16 at 13:04
so like this? https://jsfiddle.net/wc9v75Lh/ I just put the php in the javascript area just to show it to you. – Kez Feb 10 '16 at 13:04
Yes, although you need to of course use the correct values for your insert query. – Tom Feb 10 '16 at 13:07
Yes, of course. and this is relating to the database ('INSERT tableName (`db_fieldname`, `db_fieldnameint`) VALUES (:db_fieldname, :db_fieldnameint)') Correct? – Kez Feb 10 '16 at 13:09
@Kez It is related to `tableName` and the things in `VALUES` as well. – Tom Feb 10 '16 at 13:10
Yeah the field names within the table? and the values of what? – Kez Feb 10 '16 at 13:12
`VALUES (:example, :exampleInt, :etc, :etcInt)` – Tom Feb 10 '16 at 13:14
sorry, but what is :example, :exampleInt, :etc relating to? – Kez Feb 10 '16 at 13:17
Look at this please: [link](http://php.net/manual/en/pdo.prepared-statements.php) – Tom Feb 10 '16 at 13:22

How to write a prepared insert statement?

1 Answers1