September 9, 2013

How to Get Parameters from Database in PHP with PDO

M4g4bu’s Question:

I’m developing a setup class to manage some parameters stored in the database and I am trying to make a class effective and shorter so, I did this:

First, I add a db.php file where the database is configured and connected, after that I added the parameters as private attributes. To process them in a better way all are included into an Array, so I build the query in the variable ‘consulta’ processing the information and retrieve one by one the values from the db

  require 'db.php';

  class setup {
private $lenguaje;
private $charset;
private $sitio_titulo;
private $sitio_descripcion;
private $kewords;
private $autor;
private $path_css_frontend;
private $path_css_backend;
private $path_modernizr;
private $path_jquery;
private $logo_url;
private $copyright;
private $dbconn;
private $site_version;

//edit – code separated only for visibility, part of same class

    public function __construct() {
    $this->dbconn = new database ();
private function fillData() {
    $valores = array (

//edit – code separated only for visibility, part of same class

public function getData($columnName) {


        $consulta = 'SELECT $columnName from config LIMIT 1';

        $this->dbconn->query ( $consulta );

        $this->dbconn->execute ();

        $r = $this->dbconn->fetch (); //

        '$this->'.$columnName = $r;




did I something wrong?

First quote the values of your array or they will be considered as constants.

$valores = array (

Next, the way you are using while loop is wrong. Combine the array values and send one query.

public function getData($columnName) {

    $columnName = implode(",", $columnName);
    $consulta = 'SELECT $columnName from config LIMIT 1';

    // Query Now

July 10, 2013

How to write mysql query into PDO

Latitude Brace’s Question:

Appreciate if you could help me to write below mysql code to PDO statement.

     $sql  = "SELECT * FROM node WHERE node_name='$nodename'";

     $result = mysql_query($sql);

when I read on PDO::query Manual i found this code

      $connection = new pdo("sqlite:file.sq3");
      $query="SELECT * FROM table";
      $result = $connection->query($query);
      $row = $result->fetch(PDO::FETCH_ASSOC);

what is the function for “sqlite:file.sq3” and “(PDO::FETCH_ASSOC)”

you connect to the database like this :

try {
$db = new PDO("sqlite:file.sq3");
    } catch (PDOException $e) {
       echo 'Error : <br>' . $e->getMessage();

PS: You dont need the try and the catch, but we used to get the error and handle it in a nice way as we want to

and next we query like this :

 $db->query(SELECT * FROM node WHERE node_name='$nodename'");

and we fetch it like this :

 $query = $db->query(SELECT * FROM node WHERE node_name='$nodename'");
 $row   = $query->fetch(PDO::FETCH_OBJ);

and now you use $row->name for example

here is more about PDO::FETCH

  • PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

  • PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set

  • PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were
    bound with the PDOStatement::bindColumn() method

  • PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the
    class. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g.
    PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class
    is determined from a value of the first column.

  • PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in
    the class

  • PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed

  • PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0

  • PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set

That line points to your sql file, there is no function for it. It opens a PDO connection and you can query further.

$connection = new pdo("sqlite:/path/to/sql/file.sq3");

PDO::FETCH_ASSOC tells the function fetch() to pull associative array from table.

June 17, 2013

Insert value to MySQL table safely using PDO

FriedBitz’s Question:

Would this protect my INSERT from SQL Injections? and can I somehow shorten this code to make it look neater?

$db = new PDO('mysql:host=XXXXXXXXXX;dbname=XXXXXXXXX', 'XXXXXXXXX', 'XXXXXXXXXX');

// query MySQL to verify login
$query = $db->prepare("INSERT INTO login (username,password,name,email_add,age,country) VALUES (:username,:password,:name,:email_add,:age,:country)");
':username' => $username, 
':password' => $password,
':name' => $name,
':email_add' => $email,
':age' => $age,
':country' => $country));

YES, PDO Extension already removes all the injection vulnerabilities like that. Since you are binding params afters ward, you are doing it correctly too.

However, just to make the query at it safest state, specify the data type of the variable while binding them.

$query = $db->prepare('INSERT INTO login (username,password,name,email_add,age,country) VALUES (:username,:password,:name,:email_add,:age,:country)');

$query->bindParam(':username', $username, PDO::PARAM_STR, 20);
// and so on
$query -> execute();

More Details

May 30, 2013

PDO – get two select values into an array

NojoRu’s Question:

I have this PDO:

$id = 1;
$title = 'resourceName';
$url = 'resourceURL';
$result = array($title => $url);


$pdo = new PDO("mysql:host=$db_host;dbname=$db_name;", $db_user, $db_password);
$stmt = $pdo->prepare("SELECT resourceName, resourceURL FROM Resources WHERE categoryID = :id");
$stmt->bindParam(':id', $id);
$row = $stmt->fetchAll();

I am just getting this error:
Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

How can make the PDO result be an array where resourceName is the key and resourceURL is the value?

You are binding unneeded parameters to the query.

$stmt->bindParam(':id', $id);
$stmt->execute(array_values($result)); //On this line. These parameters are 
                                         not needed

Let me explain

$stmt->bindParam(':id', $id);

Binds the value of $id to the SQL Parameter :id and Again


You are binding another parameters without any indexes.

Thus your query wants 1 parameter and you are sending two paramters.

Solution: Use one of them


$stmt->bindParam(':id', $id);

Or , directly like this

$stmt->execute(array(":id" => $id));

After, that get columns from the rows and convert them into a new array in your required format

$row = $stmt->fetchAll();
//Now assuming only one was returned from the database this might work
$new = array($row[0] -> resourceName => $row[0] -> resourceURL);
March 9, 2013

Insert statement not working using execute(array()) of PDO Extension

Question by shnisaka

 $stmt = $conn->prepare("INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',:cover,:dateofbirthYear:dateofbirthMonth:dateofbirthDay,NOW(),:sex,:country)");
  ':username'   => $username,
  ':password' => $password,
  ':email'   => $email,
  ':cover' => $cover,
  ':dateofbirthYear'   => $dateofbirthYear,
  ':dateofbirthMonth' => $dateofbirthMonth,
  ':dateofbirthDay'   => $dateofbirthDay,
  ':sex' => $sex,
  ':country'   => $country 

For some reason this insert statement is not working. I am very new in PDO so I do not know much about it. What am I doing wrong?

this statment gives me this error :

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens’ in /home/manga/public_html/new/register.php:80 Stack trace:
#0 /home/manga/public_html/new/register.php(80): PDOStatement->execute(Array)
#1 {main} thrown in /home/manga/public_html/new/register.php on line 80

Answer by Starx

You have prepared your query in the wrong way

INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',
     // ^ These need to either single or separated

For what you are trying, you can do it this way

//Prepare the date of birth earlier
$dob = $dateofbirthYear.$dateofbirthMonth.$dateofbirthDay;

//Then pass it as a single $variable

$stmt = $conn->prepare("INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',:cover,:dob,NOW(),:sex,:country)");
  ':username'   => $username,
  ':password' => $password,
  ':email'   => $email,
  ':cover' => $cover,
  ':dob'   => $dob, // <-- Problem solved
  ':sex' => $sex,
  ':country'   => $country 
 // Then it will execute
February 25, 2013

PHP PDO data into Json_Encode + but only first line appears

Question by Adam

                       $sql='SELECT word,language,acceptable FROM profanity;';
        foreach($pds as $row) {
            $profanityText = json_encode(array('word' => $row['word'],
                                       'language' => $row['language'],
                                       'acceptable' => $row['acceptable']));

I have the above code. The catch is it works but only the first line from the database goes into the json array. I’ve run the query directly against the DB and it pulls all the data.

I’m guessing my foreach loop has an issue or many the way I’m doing the PDO call.

any ideas?

Answer by Starx

The problem is that the variable holding the json encoded values $profanityText gets overidden every time in the loop.

Use this

    $rows = array()
    foreach($pds as $row) {

        $rows[] = array('word' => $row['word'],
            'language' => $row['language'],
            'acceptable' => $row['acceptable']);
    $profanityText = json_encode($rows);

Or, if you are not manipulating your data in anyway, you can just directly call (as mentioned by deceze)

$profanityTest = json_encode($pds->fetchAll(PDO::FETCH_ASSOC));
September 7, 2012

PHP changing old mysql_query to PDO

Question by neeko

I have some old mysql_query queries in my code which i want to convert in to PDO but am struggling to get to work.

my original code was:

mysql_query("UPDATE people SET price='$price', contact='$contact', fname='$fname', lname='$lname' WHERE id='$id' AND username='$username' ")
or die(mysql_error()); 

now i am trying:

$sql = "UPDATE people SET price='$price', contact='$contact', fname='$fname', lname='$lname' WHERE id='$id' AND username='$username'";
$q   = $conn->query($sql) or die("failed!");

but can’t seem to get it to work, any ideas?


$conn = new PDO("mysql:host=$host;dbname=$db",$user,$pass);

 // check if the form has been submitted. If it has, process the form and save it to the   database
 if (isset($_POST['submit']))
 // confirm that the 'id' value is a valid integer before getting the form data
 if (is_numeric($_POST['id']))
 // get form data, making sure it is valid
 $id = $_POST['id'];
 $fname = mysql_real_escape_string(htmlspecialchars($_POST['fname']));
 $lname = mysql_real_escape_string(htmlspecialchars($_POST['lname']));
 $contact = mysql_real_escape_string(htmlspecialchars($_POST['contact']));
 $price = mysql_real_escape_string(htmlspecialchars($_POST['price']));

 // check that firstname/lastname fields are both filled in
 if ($fname == '' || $lname == '' || $contact == '' || $price == '' )
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';

 //error, display form
 renderForm($id, $fname, $lname, $contact, $price, $error);
 // save the data to the database
 $username = $_SESSION['username'];

 $query = "UPDATE people 
         SET price=?, 
          WHERE id=? AND 
$stmt = $db->prepare($query);
$stmt->bindParam(1, $price);
$stmt->bindParam(2, $contact);
$stmt->bindParam(3, $fname);
$stmt->bindParam(4, $lname);
$stmt->bindParam(5, $id);
$stmt->bindParam(6, $username);    

 // once saved, redirect back to the view page
header("Location: view.php"); 

Answer by John Woo

For more information visit this link: PHP PDO

based on your example,


    $query = "UPDATE people 
             SET price=?, 
              WHERE id=? AND 
    $stmt = $dbh->prepare($query);
    $stmt->bindParam(1, $price);
    $stmt->bindParam(2, $contact);
    $stmt->bindParam(3, $fname);
    $stmt->bindParam(4, $lname);
    $stmt->bindParam(5, $id);
    $stmt->bindParam(6, $username);    


PDO Prepared statements and stored procedures

enter image description here

Answer by Starx

Few things you have to be clear while using PDO extension is that there are multiple ways to get things done.

The way you are currently using being one of them including few more. However it is always a good idea to bind parameters separately, because this prevents many problems like SQL Injection and many more.

Other important things to look at are statement, prepare and execute.

$conn = new PDO("...."); //Creating the handler

//Create the statement
$stmt = $conn -> prepare("UPDATE people SET price = :price, contact = :contact, fname = :fname, lname = :lname WHERE id= :id AND username = :username");

// Bind the params
$stml -> bindParam(":contact", $contact, PDO::PARAM_STR); //This way you can also define the DATATYPE of the parameter

$stmt -> execute(array(
   ":price" => $price, //another way of binding the params
   ":fname" => $fname, 
   ":lname" => $lname,
   ":id" => $id, 
   ":username" => $username));
April 18, 2012

PDO MySQL: Insert multiple rows in one query

Question by Adam Ramadhan

hello im making a class for doing multiple insert in pdo.

something like this

INSERT INTO $table (key1,key2,key3,etc) VALUE (value1,value2,value3,etc), (value1,value2,value3,etc), (value1,value2,value3,etc)

so after searching i found out that i have to build something like

INSERT INTO $table (key1,key2,key3,etc) VALUE (:key1,:key2,:key3,etc), (:key1,:key2,:key3,etc), (:key1,:key2,:key3,etc)

then execute with this $this->execute($data);
where $data is

 0 => 
      'key1' => 'value1'
      'key2' => 'value2'
      'key3' => 'value3'
 1 => 
      'key1' => 'value1'
      'key2' => 'value2'
      'key3' => 'value3'


the problem is i still get an error Array to string conversion on $insert->execute($data); how can i fix that?

heres a snippet of what im making.

public function multipleInsert($table, $data = array()) 

    # INSERT (name) VALUE (value),(value)
    if (count($data) > 1) 
        $fieldnames = array_keys($data[0]);
        $count_inserts = count(array_values($data));
        $count_values = count(array_values($data[0]));

        # array(????) untill x from first data
        for($i = 0; $i < $count_values; $i++)
            $placeholder[] = '?';

        # array((????),(????),(????)) for query
        for ($i=0; $i < $count_inserts; $i++) 
            $placeholders[] = '('. implode(',',$placeholder) . ')';

        $query  = 'INSERT INTO '. $table;
        $query .= '(`'. implode('`, `', $fieldnames) .'`)';
        $query .= ' VALUES '. implode(', ', $placeholders);

        $insert = $this->start->prepare($query);

        $i = 1;
        foreach($data as $item) 
            foreach ($item as $key => $value) 
               $insert->bindParam($i++, $item[$key]);

        echo $query;

        $return['status'] = true;
        $return['lastid'] = $this->start->lastInsertId();

        return $return;
        die('$data is less then two array, use single insert instead.');

Answer by Starx

An easy way for this avoiding the complications would be something like this

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
    $stmt->bindValue(':a', $item[0]);
    $stmt->bindValue(':b', $item[1]);
    $stmt->bindValue(':c', $item[2]);

However, this executes the statement multiple times. So, it is better if we create a long single query in order to do this.

Here is an example of how we can do this.

$query = "INSERT INTO foo (key1, key2) VALUES "; //Prequery
$qPart = array_fill(0, count($data), "(?, ?)");
$query .=  implode(",",$qPart);
$stmt = $dbh -> prepare($query); 
$i = 1;
foreach($data as $item) { //bind the values one by one
   $stmt -> bindParam($i++, $item['key1']);
   $stmt -> bindParam($i++, $item['key2']);
$stmt -> execute(); //execute
April 15, 2012

SQL Statement Giving me an error?

Question by PHPLOVER

My code below (must be something to do with sql statement (the UPDATE query statement), basically when i go in browser and visit script with a key that i know exists in the database i get the following error:

[15/04/2012 18:33:57] - exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'user_activation_key'' in C:wampwwwuser-verify.php:53
Stack trace:
#0 C:wampwwwuser-verify.php(53): PDOStatement->execute(Array)
#1 {main}

Here is my code:, not sure what it saying about duplicate entry, as the user_activation_key column is unique and yes i am using InnoDB and foreign keys for data interigty.

// check if key is set and alphanumeric and equals 40 chars long
// we use sha1 so it will always be 40 chars long.
if(isset($_GET['key']) && ctype_alnum($_GET['key']) && strlen($_GET['key']) == 40){
    $key = trim($_GET['key']);

// if key isset

try {
        // connect to database
        $dbh = sql_con();

        // if key is of valid length and type we need to update the `user_activation_key` in the `users_status` table to NULL
        // and update the `user_status`in the `users` table to 1 (tinyint)(active) based on the condition that the 
        // activation key can be found in the users_status.user_activation_key column and user_uid match in both users_status and users table
        $stmt = $dbh->prepare("
                                users_status.user_activation_key = ?
                                users.user_status = 1,
                                users_status.user_activation_key = NULL
                                users_status.user_uid = users.user_uid");

        // execute query

        if ( $stmt->rowCount() > 0 ) {

            echo 'account now activated';

        } else {
            echo 'could not activate account at this time';

    // close database connection
    $dbh = null;

} // if any errors found log them and display friendly message
catch (PDOException $e) {

} else {

// else key not valid or set
echo '<h1>Invalid Activation Link</h1>';

$SiteErrorMessages =
"Oops! Your account could not be activated. Please recheck the link in your email.
The activation link appears to be invalid.<br /><br />
If the problem persists please request a new one <a href='/member/resend-activation-email'>here</a>.";




Not sure why i am getting that error, any know what it means exactly ?

It won’t perform the update even thou the key exists in the users_status table. if i enter an invalid key it says could not activate account at this time which is what it should do but when the key is valid it should update but it’s outputting the error above.



Thanks for the quick replies as always!

Here is the the database design for those two tables.

CREATE TABLE `users` (
  `user_uid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'users unique id',
  `user_status` tinyint(1) unsigned NOT NULL COMMENT '0 = verify | 1 = active |  2 =  suspended | 3 = delete | 4 = spam |',
  `user_login` varchar(15) NOT NULL COMMENT 'users login username',
  `user_pass` char(152) NOT NULL,
  `user_email` varchar(255) NOT NULL COMMENT 'users email',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'user registration date',
  `user_display_name` varchar(60) NOT NULL COMMENT 'users display name (first & last name)',
  `user_failed_logins` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'failed login attempts',
  PRIMARY KEY (`user_uid`),
  UNIQUE KEY `user_login` (`user_login`),
  UNIQUE KEY `user_email` (`user_email`),
  KEY `user_pass` (`user_pass`)

CREATE TABLE `users_status` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto generated id',
  `user_uid` int(10) unsigned NOT NULL,
  `user_activation_key` char(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_uid` (`user_uid`),
  UNIQUE KEY `user_activation_key` (`user_activation_key`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='user status table, when a user registers they must first activate there account';

ALTER TABLE `users_status`

Answer by Starx

On your query you are setting users_status.user_activation_key as NULL, and I am pretty sure, it has a UNIQUE index and value NULL must be already available in the table.

That’s why you are receiving that error.

April 14, 2012

How do I get PDO to work on WAMP with PHP 5.4?

Question by JREAM

How do I get PDO to work on WAMP with PHP 5.4?
I have installed PHP 5.4 inside of WAMP (2.2d 32-bit). My PHP.ini file has it enabled (Inside of the 5.4 phpForApache.ini file). I downloaded the latest PHP 5.4 Thread Safe for windows.

But I can’t get the PDO extension to work. Here is my php info file:

Configure Command   cscript /nologo configure.js "--enable-snapshot-build" 
"--disable-isapi" "--enable-debug-pack" "--disable-nsapi" "--without-mssql" 
"--without-pdo-mssql" "--without-pi3web" 
"--with-pdo-oci=C:php-    sdkoracleinstantclient10sdk,shared" 
"--with-oci8=C:php-sdkoracleinstantclient10sdk,shared" "--with-oci8-11g=C:php-        sdkoracleinstantclient11sdk,shared" 
"--enable-object-out-dir=../obj/" "--enable-com-dotnet" 
"--with-mcrypt=static" "--disable-static-analyze" "--with-pgo"

PDO drivers no value

Answer by Starx


Though you’ll now have support for 5.4, you’ll be missing some fancy extensions and features that you may have gotten used to on other
versions of PHP. These may include:

PDO drivers

I will suggest installing Apache 2, Mysql, PHP separately. And its working just fine.


Please fill the form - I will response as fast as I can!