How to Get Parameters from Database in PHP with PDO

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

<?php
  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 (
            lenguaje,
            charset,
            sitio_titulo,
            sitio_descripcion,
            kewords,
            autor,
            path_css_frontend,
            path_css_backend,
            path_modernizr,
            path_jquery,
            logo_url,
            copyright,
            dbconn,
            site_version
    );
    $this->getData($valores);
}

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

public function getData($columnName) {

    while($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?

Why don't you at least run your code and fix numerous syntax errors? — Your Common Sense
– Your Common Sense, Commented Sep 9, 2013 at 3:11

Your Common Sense · Accepted Answer · 2013-09-09 03:13:29Z

2

Sure.
You just managed to make it long and ineffective.
Here is an improved version:

<?php
require 'db.php';
$dbconn = new database();

class setup
{
    public function __construct($dbconn)
    {
        $this->dbconn = $dbconn;
        $this->fillData();
    }

    private function fillData()
    {
        $data = $this->getData();
        foreach ($data as $key => $value)
        {
            $this->$key = $value;
        }
    }
    private function getData()
    {
        $sql = 'SELECT * FROM config';
        return $this->dbconn->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
}

edited Sep 9, 2013 at 3:13

answered Sep 9, 2013 at 3:06

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phil Over a year ago

I'm surprised you're letting the database class slide. Seems to support the same method set as PDO and I know how much you love PDO wrappers

Your Common Sense Over a year ago

Honestly, I am no wonder it didn't. If you have as much errors in your other files as have in this one.

Starx · Accepted Answer · 2013-09-09 03:21:47Z

0

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

$valores = array (
            'lenguaje',
            'charset',
            'sitio_titulo',
            'sitio_descripcion',
            'kewords',
            'autor',
            'path_css_frontend',
            'path_css_backend',
            'path_modernizr',
            'path_jquery',
            'logo_url',
            'copyright',
            'dbconn',
            'site_version'
    );

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

}

edited Sep 9, 2013 at 3:21

answered Sep 9, 2013 at 3:04

Starx

79.3k50 gold badges187 silver badges266 bronze badges

1 Comment

Phil Over a year ago

You don't typically quote MySQL identifiers (column names)

m4g4bu · Accepted Answer · 2013-09-10 03:32:08Z

-1

Finally I've changed the architecture of the table and I made more easier the class to get the params

<?php
    require 'db.php';

    class setup {

      public function __construct() {

    $this->dbconn = new database ();

      }

      public function getParameter($p) {

    $sql = 'SELECT param_value from parameters where param_name = :par';

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

    $this->dbconn->bind(':par', $p);

    $r = $this->dbconn->getSingleRecord();

    return $r['param_value'];
}

?>

The following functions are involved in the solution from the file db.php

public function query($query) {

    if ($this->isConnected == False) {

        $this->Connect ();

    } else {

        $this->q = $this->pdo->prepare ( $query );

    }
}


public function bind($param,$value,$type = NULL){

    if (is_null($type)) {

        switch (true) {
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
        }
    }

    $this->q->bindValue($param,$value,$type);

}

  public function getSingleRecord(){

    $this->execute();

    return $this->q->fetch(PDO::FETCH_ASSOC);

}

answered Sep 10, 2013 at 3:32

m4g4bu

4552 gold badges7 silver badges19 bronze badges

1 Comment

Phil Over a year ago

This is terribly designed. Your bind method depends on the successful execution of the query method yet it does no sanity checks. I really don't see what you're adding to the suite of PDO classes other than useless complexity

Collectives™ on Stack Overflow

How to Get Parameters from Database in PHP with PDO

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related