What is the best way to add data to a database?

Question

$user='root'; try{ $pdo=new PDO('mysql:host=localhost;dbname=test',$user); $pdo->exec('set character set utf8'); } catch(PDOException $e){ echo 'Error: '.$e->getMessage(); } //using bound variables? $stmt=$pdo->prepare('insert into test(name) value(:name)'); $stmt->bindParam(':name',$_POST['name']); $stmt->execute(); //using named parameters $stmt=$pdo->prepare('insert into test(name) value(:name)'); $stmt->execute(array(':name'=>$_POST['name'])); //using placeholders $stmt=$pdo->prepare('insert into test(name) value(?)'); $stmt->execute(array($_POST['name'])); //using bound parameters w/ placeholders $stmt=$pdo->prepare('insert into test(name) value(?)'); $stmt->bindParam($_POST['name']); $stmt->execute(); 

I just want to know what is best used for me as a PHP starter, I'm just confused about what to use, and I want to know what is best and usually used.

Answer 1

I consider the second method the best:

 //using named parameters $stmt=$pdo->prepare('insert into test(name) value(:name)'); $stmt->execute(array(':name'=>$_POST['name'])); 

You called params, so you know what's in the array - I hate looking at five or more ? in the code and try to work if the order is right, and it takes less lines of code to throw into the array and execute all this at the same time, and not through many lines ->bindParam() .

If this parameter were outside the table, I would consider many lines ->bindParam() on top of the rest.

This question comes down to personal preference. Choose what is best for you, what is easy to read and understand (especially if you come back to it in a few weeks or months), and what is easy to read for others who may have to choose a code to debug it.

Edit: nothing works faster as such. Joining a query can take milliseconds longer since the queries run in the database as much time. Faster really is nothing to see.

Answer 2

The answer comes down to one thing: personal preference. They all work as intended and equally give the same result, but “how you write it” depends entirely on what you are more comfortable with, and possibly others who can (ultimately) work with your code.

My personal opinion, I think that the parameters mentioned are the best, as it makes it clear what value you are setting. With one parameter in the query, this may be pointless - but when there are 10+ of them, it can come in handy.

Answer 3

Personally, I prefer the first of four. It’s clear what you are doing, so your code will be easier to maintain. Using arrays is useful when the array already exists and all values ​​will be used in the query. However, when writing code you will encounter errors. And debugging queries that use placeholders ? may be a pain.

When it comes to performance, there is no big difference between any of the four approaches, but there are some differences:

In 2 of 4 examples, you create an array. Creating arrays is cheap, but not free.
When using bindParam you can easily specify the data type (just look at the predefined PDO constants). In most cases this will not matter much, but in cases where a full table scan is performed, it is usually better that MySQL does not do type conversions.

So, performance: don't create new arrays if you don't need it, and set the correct data type if possible.

By code quality: named parameters usually make your life easier, so I would advise you to use them as much as you can