1

I'm new to PDO, in fact, this is the first time that I'm using it. All the while I've been using mysql which is depreciated. And so recently, I'm trying to update all my site to use PDO which is better and safer according to many sources that I found from the internet.

However, those tutorials is making me even more curious and full of questions. I've been google for the whole day and I still can't get the best answer or examples.

Let's start by this code below, 

// query
$sql = "SELECT title FROM books ORDER BY title";
$q   = $conn->query($sql);
$q->setFetchMode(PDO::FETCH_ASSOC);

// fetch
while($r = $q->fetch()){
  print_r($r);
}

I do understand that it fetches the data just like mysql_fetch_assoc. But, here's another code that I found from the net.

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

and lastly, this:

$stm = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
$stm->bindParam(1, $limit_from,PDO::PARAM_INT);
$stm->bindParam(2, $per_page,PDO::PARAM_INT);
$stm->execute();
$data = $stm->fetchAll();

Why are there so many different methods to fetch a data ? I somehow found out that with bindParam, you're able to set integer or strings for the variable? But with this below...

$pdo->execute(array(':col2' => $col2, ':col3' => $col3, ':col4' => $col4));

Am I still able to mix integer and strings without declaring if it's integer or strings?

Is it ok if I do like this ? mixing strings and integer in execute array...

$sql = "INSERT INTO books (id,author) VALUES (:id,:author)";
$q = $conn->prepare($sql);
$q->execute(array(':author'=>'string', ':id'=>1));

And also, does all codes above avoid SQL injection ? I somehow prefer the execute array method as it is shorter and I don't want to declare if it's integer or strings every time like using the bindParam method.

nodeffect
  • 1,830
  • 5
  • 25
  • 42
  • 1
    Executing array is the same as binding params, it's just easier to write. It will still bind the params and protect you from sql injection. Some people prefer using `bindParam`/`bindValue` as it's more verbose and to be honest, I can't blame them. – Andrei Jul 03 '15 at 09:37
  • @Andrew, is using PDO::PARAM_INT in bindParam important? Why do I need this if I can just do this -> $q->execute(array(':author'=>'string', ':id'=>1)); – nodeffect Jul 03 '15 at 09:39
  • 2
    When executing an array all the data will be passed in as a string, no matter the datatype you pass in. When doing `bindParam` you pass in the data by reference, so you can change it, like in a loop or whatever, if you need to enforce datatypes then `bindParam` is the way to go. But honestly, I rarely see `bindParam` used, it's more a matter of preference here. Long story short, be consistent, either go with one or the other. For more info see [this](http://stackoverflow.com/questions/12392424/pdo-bindparam-vs-execute?rq=1) it basically says what I just said, only more detailed. – Andrei Jul 03 '15 at 09:43
  • 1
    As for `PDO::FETCH_ASSOC` or fetch whatever, it's just a convenient way to return data, again preference comes to mind here. If you wanna work with arrays, the work with arrays, if you wanna work with objects work with objects. – Andrei Jul 03 '15 at 09:46
  • Thanks Andrew for the good explanation. Once more question. Since you're saying that all datatype will be passed as strings when using execute array. If I'm trying to insert a string into a column which is an INT type. Will it give problems ? Like this code as an example: $q->execute(array(':author'=>'string', ':id'=>1)); – nodeffect Jul 03 '15 at 09:49
  • 1
    Not at all, datatypes are still respected by your database, a string will be a string, an int will be an int, a decimal will remain a decimal and so on. Only PDO will treat them as a string, but the underlying db will work correctly with the datatypes provided. – Andrei Jul 03 '15 at 09:51
  • Thank u so much Andrew, I understand more now... – nodeffect Jul 03 '15 at 09:55
  • 1
    Oh I almost forgot, have a look [here](http://stackoverflow.com/questions/15718148/php-prepared-statements-turn-emulation-off). It's about `PDO::ATTR_EMULATE_PREPARES`. You can leave it as is for the most aprt, most people don't even know it exists, but it's a good idea to be aware of what it does and when you should use it. – Andrei Jul 03 '15 at 09:58

0 Answers0