Why it not working?

i just want to create testing page by using pdo by separated class connection, Select, Insert, update, delete difference classes here my select class
<?php
class PDO_DBConnect {
static $db ;
private $dbh ;
private function PDO_DBConnect () {
$db_type = ‘mysql’;
$db_name = ‘testdb’;
$user = ‘root’ ;
$password = ‘’ ;
$host = ‘localhost’ ;
try {
$dsn = “$db_type:host=$host;dbname=$db_name”;
$this->dbh = new PDO ( $dsn, $user, $password);
$this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch ( PDOException $e ) {
print "Error!: " . $e->getMessage () . "
" ;
die () ;
}
}

public static function getInstance ( ) {
if (! isset ( PDO_DBConnect::$db )) {
PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
}
return PDO_DBConnect::$db->dbh;
}
}
class ClassSelect
{
public function Select($field,$table)
{
$con = PDO_DBConnect::getInstance();
$sql = "SELECT “.$field.” FROM ".$table;
$pre = $con->prepare($sql);
$pre->execute();
$pre->closeCursor();
$row = $pre->fetch(PDO::FETCH_ASSOC);
return $row;
}
}
$a= new ClassSelect();
$a->Select(“field1”,“tbltest”);
$row = $a->fetch(PDO::FETCH_ASSOC);
echo $row[‘field1’];

why it getting error it a few day ago i have no solution or i did it wrong? can any body expert with php give me some idea about it pls

[SIZE=2]Hi,

Please see the modifications I made to your class. These are the changes I needed to make to get it working:[/SIZE][LIST=1]
[*]In class ClassSelect

  • created a protected property to store your connection object in the event that you need to reference it in other methods that this class might have; also because originally you where trying to do this $row = $a->fetch(PDO::FETCH_ASSOC); that was not​ referencing the connection object; I had originally set this property to public so $row = $a->con->fetch(PDO::FETCH_ASSOC); would work but then realized that this method call was not required as you already do it in the Select method
  • removed the $pre->closeCursor(); as you had not opened a cursor and it was causing the script to croak.

[/LIST][COLOR=#000000][FONT=Helvetica Neue][SIZE=2] [FONT=verdana]Here are the revised classes:


class PDO_DBConnect {
   static $db ;
   private $dbh ;
   private function PDO_DBConnect(){
     $db_type = 'mysql'; 
     $db_name = 'testdb';
     $user = 'root' ;    $password = '' ;
     $host = 'localhost' ; 
    try {
        $dsn = "$db_type:host=$host;dbname=$db_name"; 
        $this->dbh = new PDO ( $dsn, $user, $password); 
        $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     } catch ( PDOException $e ) {
        print "Error!: " . $e->getMessage () . "\
" ;      die () ; 
     }  
   }
   public static function getInstance ( ) {
     if (! isset ( PDO_DBConnect::$db )) {
        PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
     }
     return PDO_DBConnect::$db->dbh;
  }
}

class ClassSelect {
  protected $con;
  public function __construct(){
    $this->con = PDO_DBConnect::getInstance();
  }
 public function Select($field,$table){
    $sql = "SELECT ".$field." FROM ".$table;
    $pre = $this->con->prepare($sql);
    $pre->execute();
    $row = $pre->fetch(PDO::FETCH_ASSOC);
    return $row; 
  }
}
$a= new ClassSelect();
$row = $a->Select("field1","tbltest");
echo $row['field1'];

Regards,
Steve
[/FONT][/SIZE]
[/FONT][/COLOR]

I’m stuck trying to figure out what it is you are even trying to do – the first class looks like it would be better handled as an “extends”, and the last class doesn’t look like it has any way to actually interface the first class… unless you’re defining it as a global someplace I’m not seeing… and whatever that ‘getinstance’ thing is trying to do… looks like gibberish to me.

It LOOKS like something I’d probably be handling by extending the PDO object…


class PDO_autoConnect extends PDO {

	public static function exceptionHandler($exception) {
		die('Uncaught Exception:'.$exception->getMessage());
	}
	
	public function __construct() {
		$username='root' ;
		$password='' ;
		$dsn='mysql:host=localhost;dbname=testdb';
		set_exception_handler(array(__CLASS__,'exceptionHandler'));
		parent::__construct($dsn,$username,$password);
		$this->setAttribute(PDO::ATTR_PERSISTENT,true);
		$this->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
		restore_exception_handler();
	}

	public function select($field,$table) {
		$handler=$this->prepare('SELECT :field FROM :table');
		$handler->closeCursor
		return $handler->execute(array(
			':field' => $field,
			':table' => $table
		));
	}
}

$db=new PDO_autoConnect();
$statement=$db->select('field1','tbltest');
$row=$statment->fetch(PDO::FETCH_ASSOC);
echo $row['field1'];

Notice I took the time to use prepare/exec PROPERLY so it actually sanitizes $field and $table.

I did not understand either what you were trying to do, I simply got what you had working. I think you are best to rethink what you are trying to do as it looks like you are trying to create a cross between a Registry and CRUD. I did not know why you had a Cursor close in the class as there never appeared for a cursor to be open.

If you wonder, to prepare sql you need to set you need to bind parameter in place of your variables for the prepared statement to cleans them. see http://www.php.net/manual/en/pdo.prepared-statements.php

Like DeathShadow60 suggested you might want to extend the PDO object.

If you don’t like the idea of extending then you can create a database instance as a parameter into your CRUD objects; currently you just have a limited Select out of the CRUD arsenal.

If you want to have one instance then you can use a factory/registry object used to create then store your instance. In essence you have to pass the registry object around anyway so I don’t favour this idea. Instead, use a factory to create instances of your db when it is needed.

You could use either a Dependency Injection container to store this factory and then register and implement the container and the db class(es) when needed or simply when you need a db instance use a factory to create one, and then pass it as a parameter to the constructor of the classes that need the db.

Make sure you close the db connection before the Object is no longer used in the script as PHP’s garbage collection is not great. For that matter make sure your objects are disposed of in this way as well.

Steve

Dear all i really happy to participate this forum :lol::lol: all of you guy really really professional i try to joint many forum no 1 fixed my problem now you did it, you’re i’m a starter of OOP for connection i search in google about Singleton, the ClassSelect i did it by myself anyway i less understand about PDO that why it have problem thank all of you to take time sold my problem.
big thank to deathshadow60, ServerStorm,

Dear ServerStorm
i tried your replied it work perfectly but still have 1 problem
$a= new ClassSelect();
$row = $a->Select(“field1”,“tbltest”);
echo $row[‘field1’];
it echoed out:: SELECT fieldname FROM tablename usertest
so why it happened or because i echo $row[‘field1’]; ??

Hi hengwebdeveloper,

What do you get when you do this


$a= new ClassSelect();
var_dump($a)
/* Should Output */
object(ClassSelect)#1 (1) { ["con:protected"]=> object(PDO)#3 (0) { } }

Then in this line:

$row = $a->Select("field_name","table");

you need to make sure that your field name is the first parameter and the table name is the second parameter of your Select() method.

What do you get wen you do this?:


$row = $a->Select("permission_id","permissions"); 
var_dump($row);

Steve

dear ServerStorm
i get it wrong myself :slight_smile: i print_r $sql in the class that why it return now i understand it thank so much dude

Glad You found the problem.

I would recommend that you use what DeathShadow60 showed, so replace your current Select() method with his:


public function select($field,$table) {
        $handler=$this->prepare('SELECT :field FROM :table');
        $handler->closeCursor
        return $handler->execute(array(
            ':field' => $field,
            ':table' => $table
        ));    
}

This code does make ‘proper use’ of escaping the data. If you don’t bind parameters as DeathShadow60 shows in his code then the prepare doesn’t escape anything, so you need to bind parameters like he showed.

Regards,
Steve

thank so much i’ll do as your advise :slight_smile:

Dear ServerStorm
i used this class


   static $db ;
   private $dbh ;
   private function PDO_DBConnect(){
     $db_type = 'mysql'; 
     $db_name = 'testdb';
     $user = 'root' ;    $password = '' ;
     $host = 'localhost' ; 
    try {
        $dsn = "$db_type:host=$host;dbname=$db_name"; 
        $this->dbh = new PDO ( $dsn, $user, $password); 
        $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     } catch ( PDOException $e ) {
        print "Error!: " . $e->getMessage () . "\
" ;      die () ; 
     }  
   }
   public static function getInstance ( ) {
     if (! isset ( PDO_DBConnect::$db )) {
        PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
     }
     return PDO_DBConnect::$db->dbh;
  }
}

class ClassSelect {
  protected $con;
  public function __construct(){
    $this->con = PDO_DBConnect::getInstance();
  }
 public function Select($field,$table){
    $sql = "SELECT ".$field." FROM ".$table;
    $pre = $this->con->prepare($sql);
    $pre->execute();
    $row = $pre->fetch(PDO::FETCH_ASSOC);
    return $row; 
  }
}

why the class it not loop when i var_dump it show only 1 record


$a= new ClassSelect();
$row = $a->Select("field1","tbltest");
var_dump($row);

it replied


array
  'id' => string '2' (length=1)
  'fName' => string 'first name' (length=9)
  'lName' => string 'last name' (length=8)

if i


while($row)
{
   echo $row['id'];
echo $row['fName'];
echo $row['lName'];
}

it not working.

about deathshadow60


class PDO_autoConnect extends PDO {

	public static function exceptionHandler($exception) {
		die('Uncaught Exception:'.$exception->getMessage());
	}
	
	public function __construct() {
		$username='root' ;
		$password='' ;
		$dsn='mysql:host=localhost;dbname=testdb';
		set_exception_handler(array(__CLASS__,'exceptionHandler'));
		parent::__construct($dsn,$username,$password);
		$this->setAttribute(PDO::ATTR_PERSISTENT,true);
		$this->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
		restore_exception_handler();
	}
 
	public function select($field,$table) {
		$handler=$this->prepare('SELECT :field FROM :table');
		$handler->closeCursor;
		return $handler->execute(array(
			':field' => $field,
			':table' => $table
		));
	}
}

$db=new PDO_autoConnect();
$statement=$db->select('field1','tbltest');
$row=$statment->fetch(PDO::FETCH_ASSOC);
echo $row['field1'];
var_dump($row);

the msg display like that:
Notice: Undefined property: PDOStatement::$closeCursor in C:\wamp\www\ est.php on line 21

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘tbltest’’ at line 1’ in C:\wamp\www\ est.php on line 25

@hengwebdeveloper; please put your code between code tags like these

[noparse]


[/noparse]

It makes it so much easier to read.

I’ve corrected it in the previous post for you.

this code while loop it not working it work only first record what is actually happened?
<?php
class PDO_DBConnect {
static $db ;
private $dbh ;
private function PDO_DBConnect(){
$db_type = ‘mysql’;
$db_name = ‘testdb’;
$user = ‘root’ ; $password = ‘’ ;
$host = ‘localhost’ ;
try {
$dsn = “$db_type:host=$host;dbname=$db_name”;
$this->dbh = new PDO ( $dsn, $user, $password);
$this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch ( PDOException $e ) {
print "Error!: " . $e->getMessage () . "
" ; die () ;
}
}
public static function getInstance ( ) {
if (! isset ( PDO_DBConnect::$db )) {
PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
}
return PDO_DBConnect::$db->dbh;
}
}

class ClassSelect {
protected $con;
public function __construct(){
$this->con = PDO_DBConnect::getInstance();
}
public function Select($field,$table){
$sql = "SELECT “.$field.” FROM ".$table;
$pre = $this->con->prepare($sql);
$pre->execute();
$row = $pre->fetch(PDO::FETCH_ASSOC);
return $row;
}
}
$a= new ClassSelect();
$row = $a->Select(“field1”,“tbltest”);
echo $row[‘field1’];
?>

Let me repeat myself, in case you missed it.

Uhm… how does your second class even SEE the first one? You’re not doing a ‘new’, so you’re just bypassing it’s constructor?!?

I’ve seen two or three people using code that looked like this, and I can’t for the life of me figure out how it’s supposed to even work; calling classes without their constructor firing?

PDO_DBConnect::$db = new PDO_DBConnect ( ) ;

Your class isn’t asssigned to a varaible – you can’t assign a classes properties until it’s assigned… Classes are like a template; until you give that template an instance, you can’t perform operations on it. Now if you were referring to $this->db, then maybe that would make sense. but this entire method:


   public static function getInstance ( ) {
     if (! isset ( PDO_DBConnect::$db )) {
        PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
     }
     return PDO_DBConnect::$db->dbh;
  }

Unless I’m unaware of some really weird/oddball behavior of objects in PHP, is flat out nonsensical gibberish. You’re acting like the class declaration is a variable, when it’s not – it’s a TYPE. (though coming from REAL object implementations in other languages, I could be missing something odd in PHP)

NOT that many PHP developers know what types or typecasting even is; part of why objects are semi-crippled in PHP – no types, no complex types, no pointers… making objects an unnatural fit at best.

I mean this:


public function __construct(){
    $this->con = PDO_DBConnect::getInstance();
  }

Should fail just because PDO_DBConnect is a class, not a variable or instance of that class… Until you assign that class to a variable with NEW, you can’t call it’s methods.

Unless i REALLY missed some oddball PHP behavior coming from other programming languages like C++, Object Pascal or Smalltalk.

Oh, I get it, you’re trying to use the static instance - unfortunately static does not call the constructor, it just lets you set values for the NEXT instance; something that to be frank, I always thought was a really BAD idea.

It doesn’t ‘work’ that way.

Though to be frank, using static in that manner even if it did work, you might as well just use a global var for all the ‘improvement’ it offers… since basically it just turns a class into a global var.

Hi deathshadow60,

Yes there has been much debate over using this. I originally pulled this out of a Singleton implementation with factory of the DB class. In my class I wanted the implementation of singleton Db object to satisfy a single instance and global access principles. To this end it required a mechanism to access the singleton class member without creating a class object and a mechanism to persist the value of class members among class objects. My Db Singleton is implemented with a method that creates a new instance of the class if one does not exist. If an instance already exists, it simply returns a reference to that object. To make sure that the object cannot be instantiated any other way, the constructor is made private.

It was my bad as I used part of it quickly to help hengwebdeveloper and test their code and did not remove it when posting.

I do have a good understanding of the up/down sides of singletons, for one of the projects that I am working on there is heavy use of the Db and connections were beginning to be a problem, so I implemented the singleton Db as a step before connection pooling which would be a bigger deal to implement.

This works differently than a global

 
class DB {
    static $dbh ;
    public function conn(){
         $db_type = 'mysql'; 
          $db_name = 'db_name';
         $user = 'root' ;
          $password = 'secret' ;
          $host = 'localhost' ;
          try {
                  $dsn = "$db_type:host=$host;dbname=$db_name";
                  $this->dbh = new PDO ( $dsn, $user, $password);
                  $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
                  $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
           } catch ( PDOException $e ) {
                  print "Error!: " . $e->getMessage () . "\
" ;
                  die () ;
           }
           return $dbh;
    }
} 
class Something {
    private $_db;
    public function __construct(){
       $this->_db = DB::conn;
    }
    protected function doSomeDb()
       $this->_db->fetch();
    }
}

Once class Something instantiates and instance of the Db only class Something can use it within its’ own context. If class Something does not implement a method to change $_db then know one else can mess with it.

This is unlike a global were anything in the script can mess with, type cast or nullify it.

However, to your point it does act like shared or global variable which is the main reason why so many debates have warned about using this approach.

I rarely use this as I prefer other options, but it is something that can be used with caution.

Regards,
Steve


    public function select($field,$table) {
        $handler=$this->prepare('SELECT :field FROM :table'); // this can't be done
        $handler->closeCursor;
        return $handler->execute(array(
            ':field' => $field,
            ':table' => $table
        ));
    }

You can’t define table names nor columns after the query has been prepared. They must be part of the actual SQL string. Only data can be sent individually from the query using prepared statements. What was suggested is gibberish. The initial way with string concentration was the proper method of doing it.

Dear ServerStorm
i just wondering why i do while loop it not looping of you idea before because now i’m urgently of 1 project about deathshadow60 idea is good too but it’s not working at the moment i’ll think about bindParam later and will update class later because i have not time to fix this. about new class DB it test it but the connection it not worked the error msg: Fatal error: Using $this when not in object context, now could you help me why you class
<?php
class PDO_DBConnect {
static $db ;
private $dbh ;
private function PDO_DBConnect(){
$db_type = ‘mysql’;
$db_name = ‘testdb’;
$user = ‘root’ ; $password = ‘’ ;
$host = ‘localhost’ ;
try {
$dsn = “$db_type:host=$host;dbname=$db_name”;
$this->dbh = new PDO ( $dsn, $user, $password);
$this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch ( PDOException $e ) {
print "Error!: " . $e->getMessage () . "
" ; die () ;
}
}
public static function getInstance ( ) {
if (! isset ( PDO_DBConnect::$db )) {
PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
}
return PDO_DBConnect::$db->dbh;
}
}

class ClassSelect {
protected $con;
public function __construct(){
$this->con = PDO_DBConnect::getInstance();
}
public function Select($field,$table){
$sql = "SELECT “.$field.” FROM ".$table;
$pre = $this->con->prepare($sql);
$pre->execute();
$row = $pre->fetch(PDO::FETCH_ASSOC);
return $row;
}
}
$a= new ClassSelect();
$row = $a->Select(“field1”,“tbltest”);
echo $row[‘field1’];
?>

while loop it not working when i var_dump it show only first record. big thank to you guy all for this :slight_smile:
Now i’m really really urgent !! help me please