Problem with MySql => PDO

[sevsar]

Hello,

> Sorry for my English I use a translator frensh => english:)

I have a problem for convertire my current code MySql in PDO, I want to keep the same structure.

This is my MySql structure:

<?php
class Dbase{

	private $_host = "localhost";
	private $_user = "root";
	private $_password = "";
	private $_name = "dbase";

	private $_conndb = FALSE;
	public $_last_query = NULL;
	public $_affected_rows = 0;

	public $_insert_keys = array();
	public $_insert_values = array();
	public $_update_sets = array();

	public $_id;

	public function __construct(){
		$this->connect();
	}

	private function connect(){
		$this->_conndb = mysql_connect($this->_host,$this->_user,$this->_password);
		if(!$this->_conndb){
			die("Database connection failed:<br />".mysql_error());
		}else{
			$_select = mysql_select_db($this->_name,$this->_conndb);
			if(!$_select){
				die("Database selection failed:<br />".mysql_error());
			}
		}
		mysql_set_charset("UTF-8", $this->_conndb);
	}

	public function close(){
		if(!mysql_close($this->_conndb)){
			die("Closing connection failed.");
		}
	}

	public function escape($value){
		if(function_exists("mysql_real_escape_string")){
			if(get_magic_quotes_gpc()){
				$value = stripslashes($value);
			}
			$value = mysql_real_escape_string($value);
		}else{
			if(!get_magic_quotes_gpc()){
				$value = addslashes($value);
			}
		}
		return $value;
	}

	public function query($sql){
		$this->_last_query = $sql;
		$result = mysql_query($sql, $this->_conndb);
		$this->displayQuery($result);
		return $result;
	}

	public function displayQuery($result){
		if(!$result){
			$output  = "Database query failed: ".mysql_error()."<br />";
			$output .= "Last SQL query was: ".$this->_last_query;
			die($output);
		}else{
			$this->_affected_rows = mysql_affected_rows($this->_conndb);
		}
	}

	public function fetchAll($sql){
		$result = $this->query($sql);
		$out = array();
		while($row = mysql_fetch_assoc($result)){
			$out[] = $row;
		}
		mysql_free_result($result);
		return $out;
	}

	public function fetchOne($sql){
		$out = $this->fetchAll($sql);
		return array_shift($out);
	}

	public function lastId(){
		return mysql_insert_id($this->_conndb);
	}

}

I am trying to self resolve but and don’t kan resolve, i try this but is not work:

<?php
class Dbase{
	private $_pdo_host_dbname = "mysql:host=127.0.0.1;dbname=dbase";
	
	private $_user = "root";
	private $_password = "";

	private $_conndb = FALSE;
	public $_last_query = NULL;
	public $_affected_rows = 0;

	public $_insert_keys = array();
	public $_insert_values = array();
	public $_update_sets = array();

	public $_id;

	public function __construct(){
		$this->connect();
	}

	private function connect(){
		try{
			$this->_conndb = new PDO($this->_pdo_host_dbname,$this->_user,$this->_password);
		} catch {
			echo 'Connection failed';
		}
	}
	
	public function escape($value){
		if(get_magic_quotes_gpc()){
			$value = stripslashes($value);
		}
		$value = $this->_conndb->quote($value);
		return $value;
	}

	public function query($sql){
		$this->_last_query = $sql;
		$result = $this->_conndb->query($sql);
		$this->displayQuery($result);
		return $result;
	}

	public function displayQuery($result){
		if(!$result){
			$output  = "Database query failed: ".$this->_conndb->errorInfo()."<br />";
			$output .= "Last SQL query was: ".$this->_last_query;
			die($output);
		}else{
			$this->_affected_rows = $this->_conndb->rowCount($this->_conndb);
		}
	}

	public function fetchAll($sql){
		$result = $this->query($sql);
		$out = array();
		while($row = $this->_conndb->fetch(PDO::FETCH_ASSOC)){
			$out[] = $row;
		}
		return $out;
		
	}

	public function fetchOne($sql){
		$out = $this->fetchAll($sql);
		return array_shift($out);
	}

	public function lastId(){
		return $this->_conndb->lastInsertId($this->_conndb);
	}
}

Please help me

[ahundiak]

I’ll assume that your connection is being made. PDO does not have a fetch method. So your fetchAll should look more like:

    public function fetchAll($sql){ 
        $result = $this->_conndb->query($sql); 
        if ($result) return $result->fetchAll(PDO::FETCH_ASSOC);
    } 

Get this one method working, understand how to use PDOStatement and then refactor the rest of your code.

[sevsar]

THANK YOU, you are resolve my problem, this work:

<?php
class Dbase{
    private $_pdo_host_dbname = "mysql:host=127.0.0.1;dbname=dbase";
    private $_user = "root";
    private $_password = "";

    private $_conndb = FALSE;
    public $_last_query = NULL;
    public $_affected_rows = 0;

    public $_insert_keys = array();
    public $_insert_values = array();
    public $_update_sets = array();

    public $_id;

    public function __construct(){
        $this->connect();
    }

    private function connect(){
        try{
            $this->_conndb = new PDO($this->_pdo_host_dbname,$this->_user,$this->_password);
        } catch (Exception $e) {
            echo 'Connection failed: '.$e->getMessage();
        }
    }

    public function query($sql){
        $this->_last_query = $sql;
        $result = $this->_conndb->query($sql);
        $this->displayQuery($result);
        return $result;
    }

    public function displayQuery($result){
        if(!$result){
            $output  = "Database query failed: ".$this->_conndb->errorInfo()."<br />";
            $output .= "Last SQL query was: ".$this->_last_query;
            die($output);
        }else{
            $this->_affected_rows = $this->_conndb->rowCount($this->_conndb);
        }
    }

    public function fetchAll($sql){
        $result = $this->_conndb->query($sql);
        if ($result) return $result->fetchAll(PDO::FETCH_ASSOC);
    }

    public function fetchOne($sql){
        $out = $this->fetchAll($sql);
        return array_shift($out);
    }

    public function lastId(){
        return $this->_conndb->lastInsertId($this->_conndb);
    }
}

[SpacePhoenix]

@ahundiak

I’ll assume that your connection is being made. PDO does not have a fetch method. So your fetchAll should look more like:

    public function fetchAll($sql){ 
        $result = $this->_conndb->query($sql); 
        if ($result) return $result->fetchAll(PDO::FETCH_ASSOC);
    } 

Get this one method working, understand how to use PDOStatement and then refactor the rest of your code.

??? PDO does have a fetch method.

[cpradio]

@SpacePhoenix

??? PDO does have a fetch method.

Ultimately, what fixed it was calling fetchAll or fetch on $result and not on $this->_conndb, as that is where those methods would exist.

[sevsar]

I go explain how a need to be this structure:

Dbase class:

<?php
class Dbase{
    private $_pdo_host_dbname = "mysql:host=127.0.0.1;dbname=dbase";
    private $_user = "root";
    private $_password = "";

    private $_conndb = FALSE;
    public $_last_query = NULL;
    public $_affected_rows = 0;

    public $_insert_keys = array();
    public $_insert_values = array();
    public $_update_sets = array();

    public $_id;

    public function __construct(){
        $this->connect();
    }

    private function connect(){
        try{
            $this->_conndb = new PDO($this->_pdo_host_dbname,$this->_user,$this->_password);
        } catch (Exception $e) {
            echo 'Connection failed: DBase offline';
        }
    }

    public function query($sql){
        $this->_last_query = $sql;
        $result = $this->_conndb->query($sql);
        $this->displayQuery($result);
        return $result;
    }

    public function displayQuery($result){
        if(!$result){
            $output  = "Database query failed: ".$this->_conndb->errorInfo()."<br />";
            $output .= "Last SQL query was: ".$this->_last_query;
            die($output);
        }else{
            $this->_affected_rows = $this->_conndb->rowCount($this->_conndb);
        }
    }

    public function fetchAll($sql){
        $result = $this->_conndb->query($sql);
        if ($result) return $result->fetchAll(PDO::FETCH_ASSOC);
    }

    public function fetchOne($sql){
        $out = $this->fetchAll($sql);
        return array_shift($out);
    }

    public function lastId(){
        return $this->_conndb->lastInsertId($this->_conndb);
    }
}

Application Class:

<?php
class Application {
	
	public $db;
	
	public function __construct(){
		$this->db = new Dbase();
	}

}

User Class:

<?php
class Users extends Application{

    private $_table = 'users';

    public function getOneUser($id){
        $sql = "SELECT * FROM`{$this->_table}` WHERE `id` = '" . ($id) . "'";
        return $this->db->fetchOne($sql);
    }

    public function getAllUsers(){
        $sql = "SELECT * FROM`{$this->_table}` ORDER BY `id";
        return $this->db->fetchAll($sql);
    }

}

index.php

<?php
    $user_detail = new Users();
    $user = $user_detail->getOneUser($id);
    $users = $user_detail->getAllUsers();

    /** One user detail */
    echo ;
    echo "<p>Password: </p>" . $user['password'] . "<br />";
    echo "<p>Email: </p>" . $user['email'] . "<br />";
    echo "<p>Group: </p>" . $user['group'] . "<br />";

    /** All user detail */
    foreach($users as $user){
        echo "<p>Name: " . $user['name'] . " " . "Password: " . $user['password'] . " " . "Email: " . $user['email'] . "Group: " . $user['group'] . "</p><br />";
    }
?>

THANK YOU ahundiak, and thank you sitepoint