pdo | Tech Tips & Tricks

While this is not really an error, extra data that is not used should not be included in any output as it will only add to the extra bytes.

When you look at your JSON output, you realized the column names and values also have extra index column numbers and the associated values included.

To remove this, simply add an option in the PDO when you instantiate a database connection in PHP like this:

<?php
$db = new PDO("mysql:host=localhost; dbname=mydbname", "myusername", "mypassword");
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
?>

<?php

$db = new PDO("mysql:host=localhost; dbname=mydbname", "myusername", "mypassword");

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

That should fix the problem. When you use json_encode(), there will be no more duplicate values with the corresponding column index number as key.

I am not sure if this is a matter of one’s preference but I believe from my experience PDO provides more coding flexibility that MySQLi.

Here is an example so you will better understand what I mean.

$query = 'select from tablename where id = ?';
$params = array($id);
if ($name != '') {
    $query .= ' and name = ?';
    array_push($params, $name);
}
$stmt = $db->prepare($query);
$stmt->execute($params);

$query = 'select from tablename where id = ?';

$params = array($id);

if ($name != '') {

$query .= ' and name = ?';

array_push($params, $name);

}

$stmt = $db->prepare($query);

$stmt->execute($params);

Now, if that were to be MySQLi, I would be doing it like this:

$query = 'select from tablename where id = ?';
if ($name != '') {
    $query .= ' and name = ?';
}
$stmt = $db->prepare($query);
$stmt->bind_param('s', $id);
if ($name != '') {
   $stmt->bind_param('s', $name);
}
$stmt->execute();

$query = 'select from tablename where id = ?';

if ($name != '') {

$query .= ' and name = ?';

}

$stmt = $db->prepare($query);

$stmt->bind_param('s', $id);

if ($name != '') {

$stmt->bind_param('s', $name);

}

$stmt->execute();

Notice the difference? I had to call if conditions twice. If my query is very long, then my code would also be long.

Plus, there is also the issue of mysqli_escape_string(). With PDO, you do not have to call one since it calls that function automatically when you bind values to the parameters.

When I started using prepared statements in MySQL because of the SQL injection issue, this is the main reason why I opted to use PDO rather than MySQLi.

Tech Tips & Tricks

tech tips gadgets programming software

Tag: pdo

PHP MySQL Result json_encode Includes Value And Its Column Index As Key

Which Is Better In PHP: PDO Or MySQLi?