PHP - Accessing Database Using Class

Well I've designed this database class extending from mysqli, it has been working out nicely until I try to run a while loop to retrieve more than one row of data:

Class file:
Code: [Select]
<?php

class Database extends mysqli{
  protected $mysqli, $result, $table, $column, $where, $value, $limit, $order, $query;

  public function __construct($host, $user, $pass, $db){
    $this->mysqli = new mysqli($host, $user, $pass, $db) 
   or die("Error connecting to the database {$db}");

  }

  public function __destruct(){
$this->mysqli->close();
  }

  protected function prepareQuery(){
if ($prepare = $this->mysqli->prepare($this->query)) {
  trigger_error("Problem preparing query ($this->query) ".$this->mysqli->error, E_USER_ERROR);
}
return $prepare;
  }
  
  protected function reset(){
unset($this->table);
unset($this->column);
unset($this->where);
unset($this->value);
unset($this->limit);
unset($this->order);
    unset($this->result);
unset($this->query);
  }
  
  public function table($table){
    $this->table = $table;
return $this;
  }

  public function addcolumn($column, $key = "false"){ 
    $i = 0;  
    foreach($column as $col => $type){
  $this->column .= "{$col} {$type}";
  $this->column .= ($i == 0 and $key == "true")?" NOT NULL AUTO_INCREMENT PRIMARY KEY ":"";
  $this->column .= ($i < count($column) - 1)?", ":"";
  $i++;
}
return $this;
  }
  
  public function managecolumn($column, $type="", $action = ""){
    if($action == "add") $this->column = "ADD column {$column} {$type}";
if($action == "unique") $this->column = "ADD UNIQUE ({$column})";
if($action == "drop") $this->column = "DROP column {$column}";
if($action == "modify") $this->column = "MODIFY {$column} {$type}";
if($action == "action" and is_array($column)) $this->column = "CHANGE {$column[0]} {$column[1]} {$type}";
return $this;
  }
  
  public function fromcolumn($column){  
    $this->column = (is_array($column))?implode(",", $column):$column;    
return $this;
  }

  public function setcolumn($column){  
    $i = 0;
    foreach($column as $col => $val){
      $this->column .= "{$col} = '{$val}'";
      $this->column .= ($i < count($column) - 1)?", ":"";
      $i++; 
    }  
return $this;
  }

  public function intocolumn($column){  
    $this->column = (is_array($column))?implode(", ", $column):$column;
    $this->column = "({$this->column})";    
return $this;
  }

  public function value($value){
    $i = 0;  
    foreach($value as $val){
      $valuestring .= "'{$val}'";
  $valuestring .= ($i < (count($value)-1))?", ":" "; 
      $i++;
    }
    $this->value = $valuestring;  
return $this;
  }
  
  public function where($where, $comparison = "", $logic = ""){
    $i = 0;
    $comparison = (empty($comparison))?"=":$comparison;
    $logic = (empty($logic))?array_fill(0,count($where)," AND"):$logic;
    foreach ($where as $col => $val){
  $wherestring .= (is_array($comparison))?" {$col} {$comparison[$i]} '{$val}'" : " {$col} {$comparison} '{$val}'";
  $wherestring .= ($i < (count($where)-1))?" {$logic[$i]}" :" ";
  $i++;
}
$this->where = $wherestring;
return $this;
  }
  
  public function limit($limit){
    $this->limit = $limit;
return $this;
  }
  
  public function order($order){
    $this->order = $order;
return $this;
  }

  public function fetch($action = ""){
    if($action == "row") $result = $this->result->fetch_row(); 
    if($action == "assoc") $result = $this->result->fetch_assoc();
    if($action == "array") $result = $this->result->fetch_array();
if($action == "field") $result = $this->result->fetch_field(); 
if($action == "fields") $result = $this->result->fetch_fields(); 
    if($action == "object")  $result = $this->result->fetch_object();
if($action == "all")  $result = $this->result->fetch_all();
    if($action == "num") $result = $this->result->num_rows();
    $this->reset();
    return $result;    
  }

  public function create(){
    $query = "CREATE TABLE {$this->table} ({$this->column})";
$this->mysqli->query($query) or die("Mysql Error, cannot create table {$this->table}");
    $this->reset();
  }
  
  public function alter(){
    $query = "ALTER TABLE {$this->table} {$this->column}";
$this->mysqli->query($query) or die("Mysql Error, cannot alter table {$this->table}");
    $this->reset();
  }
  
  public function drop(){
    $query = "DROP TABLE {$this->table}";
$this->mysqli->query($query) or die("Mysql Error, cannot drop table {$this->table}");
    $this->reset();
  }
  
  public function select(){
    if(empty($this->column)) $this->column = "*";
    $query = "SELECT {$this->column} FROM {$this->table}";
$query .= (!empty($this->where))?" WHERE {$this->where}":"";
$query .= (!empty($this->order))?" ORDER BY {$this->order}":"";
$query .= (!empty($this->limit))?" LIMIT {$this->limit}":"";
    $this->result = $this->mysqli->query($query);
return $this;
  }

  public function update(){
    $query = "UPDATE {$this->table} SET {$this->column} WHERE {$this->where}";
    $this->mysqli->query($query) or die("Mysql Error, cannot update table {$this->table}");
    $this->reset();
  }

  public function insert(){
    $query = "INSERT INTO {$this->table} {$this->column} VALUES ({$this->value})";
    $this->mysqli->query($query) or die("Mysql Error, cannot insert into table {$this->table}");
    $this->reset();
  }

  public function delete(){
    $query = "DELETE FROM {$this->table} WHERE {$this->where}";
    $this->mysqli->query($query) or die("Mysql Error, cannot delete from table {$this->table}");
    $this->reset();
  }

}
?>

Example File:
Code: [Select]
include("classes/class_database.php");
$db = new Database($dbhost, $dbuser, $dbpass, $dbname);
while($row = $db->table($prefix."adoptables")->fromcolumn("*")->order("id")->limit(2)->select()->fetch("array")){
  echo $row['type'];
  echo "<br>";
}

So what I am trying to accomplish is to write one single line of codes to accomplish what people normally have to do in three lines:

Code: [Select]
$query = "SELECT * FROM {$prefix}adoptables ORDER by id LIMIT 2";
$result = mysql_query($result);
while($row = mysql_fetch_array($result))

to:

Code: [Select]
while($row = $db->table($prefix."adoptables")->fromcolumn("*")->order("id")->limit(2)->select()->fetch("array"))

It appears to trigger an infinite loop, and I cant fix it myself. Can anyone of you please lemme know how I can make my database class codes to work with while loop? Thanks.

Would this be a decent class to use, or is there anything I should add,edit, or remove?


<?php
/**
 * MySQL Database Class
 *
 * @author: Dillion DeWitt
 * @copyright: Dillion DeWitt (c), 2012.
 */
 
class DB {

var $link;
var $host;
var $user;
var $password;
var $database;
var $query;
var $result;
var $debug=false;

function connect($host,$user,$pass,$database=false,$persistent=false){
    //Assign values to class variables.
$this->host = $host;
$this->user = $user;
$this->password = $pass;

if( $persistent ){
$this->link = mysql_pconnect($host,$user,$pass); 
} else {
$this->link = mysql_connect($host,$user,$pass);
}

if( $database ){
$this->database = $database;
mysql_select_db($database,$this->link);
}

}

function isConnected(){

if(mysql_ping($this-link) === true){
$msg = 'Connected.';
} else {
$msg = 'Disconnected.';
}

return $msg;

}

function query($sql){

$this->query = $sql;
$this->result = mysql_query($sql, $this->link) or $this->Error();

}

function num_rows($result){
$result = $this->result;
return mysql_num_rows($result);
}

function get_single($result){
$result = $this->result;
return mysql_fetch_row($result);
}

function get_rows($result){
$result = $this->result;
return mysql_fetch_assoc($result);
}

function Error(){

if($this->debug){
echo mysql_errno().': '.mysql_error();
exit;
} else {
echo 'An error has occured.';
exit;
}

}

}

?>

It is designed so you can dump it in a PHP file and open in your browser with no need for actual db access.

Some people say to only use 2 classes, not 3 deep. What do you think?

Code: [Select]
<?php

//simulate $_POST
$_POST['name'] = "Dave Smith extra long name here";
$_POST['username'] = "smith123";

$_POST['searchname'] = "Dave";
$_POST['searchUserID'] = "555";


class Database {

//plus lots of other stuff (basically everything else not already shown below)

protected function escape($val) {

//mysql_real_escape_string
return $val;

}

protected function query($sql) {

//mysql_query
return $sql;

}

}

class DatabaseControl extends Database {

public $connection;
public $errors = array();

public function __construct($db) {
// grab db connection variable
$this->connection = $db;
}

//logs errors when preparing to add/update record
public function addError($num, $text) {

$this->errors[$num] = $text;

}

public function go($class, $method) {

call_user_func(array($class, $method), $this);

}

public function checkMaxCharacters($num, $text, $val) {

if(strlen($val)>20) {

$this->addError($num, $text);

} else {

return $val;

}

}

}

class User extends DatabaseControl {

public static function findUser() {

$name = parent::escape($_POST['searchname']);
$userID = parent::escape($_POST['searchUserID']);
$sql = "SELECT Name FROM users WHERE `Name` = '{$name}' && UserID = '{$userID}'";
echo "THIS WAS COMPLETED: " . parent::query($sql);

}

public static function addUser($object) {

//check input
$nameEntered = $object->checkMaxCharacters(1, "Name has too many characters", $_POST['name']);
$usernameRequested = $object->checkMaxCharacters(2, "Username has too many characters", $_POST['username']);

$errors = $object->errors;

if(count($errors)>0) {

//errors so don't add
foreach($errors as $v) {

echo $v . "<br />";

}

} else {

//good to go
$nameEntered = parent::escape($nameEntered);
$usernameRequested = parent::escape($usernameRequested);
$sql = "INSERT INTO users (`name`, `username`) VALUES ('{$nameEntered}', '{$usernameRequested}')";
echo "THIS WAS COMPLETED: " . parent::query($sql);

}

}

}

$db = new Database();

$getUser = new DatabaseControl($db);
$getUser -> go("User", "findUser");

echo "<br /><br />";

$addUser = new DatabaseControl($db);
$addUser -> go("User", "addUser");

?>

Anyone please provide me good examples of php database classes. with usage examples.

Thanks

need help. am learning php oop.
Logic below breaks when processing second parent. $db was defined a second time?
Suggestions? Do i need to define new database for child???


$db = new Database;
$Query = ('Select parentId from PARENT');
$db->query($Query);

while ($db->nextRecord()){
         $PARENT_ID = $db->Record['PARENT_ID'];
         $PARENT_NAME = $db->Record['PARENT_NAME'];
         
         $Query = ('Select * from CHILD where CHILD_PARENT_ID = $PARENT_ID');
         $db->query($Query);
         while ($db->nextRecord()){
               print  "child name: $db->Record['CHILD_NAME'];
         }
}

I have spent a little time lately developing a database class (not finished yet) that automatically will escape the data that is sent to it if used properly. I would like to get some input on it and see what some of you guys think of it. Mainly I would like to know if it is easy to use, if there is any potential issues so far, and if there are any suggestions on better ways to do things in the class. I have attached a copy of the class to this post. Here is an example of how to use it:

Code: [Select]
//create object
$db = new db();

//perform query
$users = $db->table('users')->select('*')->where("name='%s'")->vars("somename")->getResults();

//another way to perform the query above
$db->table('users')->select('*')->where("name='%s'")->vars(array("somename"));
$users = $db->getResults();

You will have to change the db connection properties at the top of the db.class.php file for your own connections. Please let me know of any suggestions, questions or issues that you have.

I got some help from this forum previously and am having some more issues.  I created a database class that works and returns a PDO object.  I am having trouble figuring out how to use the object in another class I want to use to access some CRUD functions.  I get to the point where I  start using the pdo object I create in the database class and my code fails.  I am obviously calling the pdo object property incorrectly.  I guess I don't understand the proper syntax.  I've included the code for the database class and the CRUD class.  The problem starts at the point where I try to run a prepared statement.  I have include a comment "Problem stars here" to indicate that point.  There is a lot of debug stuff still in the code.

Thanks,

--Kenoli

<?php
	
	class Db {
	
	public $pdo = '';
	public $message = 'A message from db!<br><br>';

function __construct() {
	
	$servername = "localhost";
	$username = "root";
	$password = "";
	$dbname = "tio-local";
	
	$db_options = array(
	PDO::ATTR_EMULATE_PREPARES => false, 
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, 
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
	);
	
	try {
		
	  $this->pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password, $db_options);
	  // set the PDO error mode to exception
	  $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	}  // End Try
	
	catch(PDOException $e) {
		  echo "Error: " . $e->getMessage();
		}
		
	} // End __construct
	
} // End class definition DB.php

$db = new Db;

$pdo = $db->pdo;

print_r ($pdo);

?>

<?php // Db_functions.php

include_once ('Db.php'); // $db instantiated in class file

$pdo = $db->pdo;

class Db_functions {
	
	public $pdo = '';
	
	function __construct($pdo) { 
		
		$this->pdo = $pdo;
		
	} // Close __construct
  
  public function insert($table_name, $white_list, $insert_array) {
		
			if ($white_list == '' && $table_name == 'Sites') {
		
			$white_list = array('gone'=>'','site_name' =>'', 'site_address' =>'', 'grommets' =>'', 'tape' =>'', 'site_image' =>'',  'description' =>'', 'surface' =>'', 'tio_contact' =>'', 'site_contact' =>'','owner' =>'', 'lessee' =>'',  'contact_phone' =>'', 'contact_email' =>'', 'contact_date' =>'', 'comments' =>'');
			} elseif  ($white_list == '' && $table_name == 'Persons') {
				
				$white_list = array('gone'=>'', 'fname'=>'', 'lname'=>'', 'tio'=>'', 'volunteer'=>'', 'general'=>'', 'artist_pic'=>'', 'email'=>'', 'website'=>'', 'telephone'=>'', 'address'=>'', 'city'=>'', 'state'=>'', 'zip'=>'', 'statement'=>'');
			}
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
			
			echo '<p>The following is the $white_list:<br>';
			
			echo '<pre>';
			print_r ($white_list);
			echo '</pre>';
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
			
			/** Test and remove any unpermitted columns **/
			
			$insert_array = array_intersect_key($insert_array, $white_list);
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
		
		/** Generate variables to create prepared statements **/
		
			foreach($insert_array as $key => $value) {  
				$col .= $key . ', ';  
				$val .= ':' .$key . ', ';
			}
			
			echo '$col = ' . $col . '<p>';
			echo '$val = ' . $val . '<p>';
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
		
		/** Remove ', ' at end of arrays and create prepared statement */
		
			$col = substr_replace($col ,"",-2);
			$val = substr_replace($val ,"",-2);
			$sql = "INSERT INTO Sites ($col) VALUES ($val)";
			
			echo "<p>SQL = $sql<br><br>"; /** Debug **/
		
		echo '<h3>More</h3<br>';	
			
		/** Register prepared statement */
		
		/****** PROBLEM STARTS HERE *****/
		
			$stmt = $this->pdo->prepare($sql);
			
		echo '<h3>More2</h3>';	
		
		/** Create [:field, $value] pairs. */
		
			foreach($insert_array as $key => $value) {
				$param = ':' . $key;
				$stmt->bindParam($param, $$value);
				
			//}
		
		/** Create [field => value] array */
		
			foreach($insert_array as $key => $value) {
				$insert[$key] =  $value;
			}
			
		
		/** Execute statement using $insert array. **/
		
			$stmt->execute($insert);
	
	} // End insert function
	
	
} // Close class definition

$db_functions = new Db_functions($pdo);

$insert_array = array('fname' => 'John', 'lname' => 'Hancock');

$db_functions->insert('Persons', '', $insert_array);

echo '<pre>';
print_r ($db_functions);
echo '</pre>';

?>

 

Edited February 6 by kenoli

here's the class im using
Code: [Select]
<?php
# Name: Database.singleton.php
# File Description: MySQL Singleton Class to allow easy and clean access to common mysql commands
# Author: ricocheting
# Web: http://www.ricocheting.com/
# Update: 2010-07-19
# Version: 3.1.4
# Copyright 2003 ricocheting.com


/*
    This program is free softwa  you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/


//require("config.inc.php");
//$db = Database::obtain(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);

//$db = Database::obtain();


###################################################################################################
###################################################################################################
###################################################################################################
class Database{

// debug flag for showing error messages
public $debug = true;

// Store the single instance of Database
private static $instance;

private $server   = ""; //database server
private $user     = ""; //database login name
private $pass     = ""; //database login password
private $database = ""; //database name

private $error = "";

#######################
//number of rows affected by SQL query
public $affected_rows = 0;

private $link_id = 0;
private $query_id = 0;


#-#############################################
# desc: constructor
private function __construct($server=null, $user=null, $pass=null, $database=null){
// error catching if not passed in
if($server==null || $user==null || $database==null){
$this->oops("Database information must be passed in when the object is first created.");
}

$this->server=$server;
$this->user=$user;
$this->pass=$pass;
$this->database=$database;
}#-#constructor()


#-#############################################
# desc: singleton declaration
public static function obtain($server=null, $user=null, $pass=null, $database=null){
if (!self::$instance){ 
self::$instance = new Database($server, $user, $pass, $database); 
} 

return self::$instance; 
}#-#obtain()


#-#############################################
# desc: connect and select database using vars above
# Param: $new_link can force connect() to open a new link, even if mysql_connect() was called before with the same parameters
public function connect($new_link=false){
$this->link_id=@mysql_connect($this->server,$this->user,$this->pass,$new_link);

if (!$this->link_id){//open failed
$this->oops("Could not connect to server: <b>$this->server</b>.");
}

if(!@mysql_select_db($this->database, $this->link_id)){//no database
$this->oops("Could not open database: <b>$this->database</b>.");
}

// unset the data so it can't be dumped
$this->server='';
$this->user='';
$this->pass='';
$this->database='';
}#-#connect()



#-#############################################
# desc: close the connection
public function close(){
if(!@mysql_close($this->link_id)){
$this->oops("Connection close failed.");
}
}#-#close()


#-#############################################
# Desc: escapes characters to be mysql ready
# Param: string
# returns: string
public function escape($string){
if(get_magic_quotes_runtime()) $string = stripslashes($string);
return @mysql_real_escape_string($string,$this->link_id);
}#-#escape()


#-#############################################
# Desc: executes SQL query to an open connection
# Param: (MySQL query) to execute
# returns: (query_id) for fetching results etc
public function query($sql){
// do query
$this->query_id = @mysql_query($sql, $this->link_id);

if (!$this->query_id){
$this->oops("<b>MySQL Query fail:</b> $sql");
return 0;
}

$this->affected_rows = @mysql_affected_rows($this->link_id);

return $this->query_id;
}#-#query()


#-#############################################
# desc: does a query, fetches the first row only, frees resultset
# param: (MySQL query) the query to run on server
# returns: array of fetched results
public function query_first($query_string){
$query_id = $this->query($query_string);
$out = $this->fetch($query_id);
$this->free_result($query_id);
return $out;
}#-#query_first()


#-#############################################
# desc: fetches and returns results one line at a time
# param: query_id for mysql run. if none specified, last used
# return: (array) fetched record(s)
public function fetch($query_id=-1){
// retrieve row
if ($query_id!=-1){
$this->query_id=$query_id;
}

if (isset($this->query_id)){
$record = @mysql_fetch_assoc($this->query_id);
}else{
$this->oops("Invalid query_id: <b>$this->query_id</b>. Records could not be fetched.");
}

return $record;
}#-#fetch()


#-#############################################
# desc: returns all the results (not one row)
# param: (MySQL query) the query to run on server
# returns: assoc array of ALL fetched results
public function fetch_array($sql){
$query_id = $this->query($sql);
$out = array();

while ($row = $this->fetch($query_id)){
$out[] = $row;
}

$this->free_result($query_id);
return $out;
}#-#fetch_array()


#-#############################################
# desc: does an update query with an array
# param: table, assoc array with data (not escaped), where condition (optional. if none given, all records updated)
# returns: (query_id) for fetching results etc
public function update($table, $data, $where='1'){
$q="UPDATE `$table` SET ";

foreach($data as $key=>$val){
if(strtolower($val)=='null') $q.= "`$key` = NULL, ";
elseif(strtolower($val)=='now()') $q.= "`$key` = NOW(), ";
        elseif(preg_match("/^increment\((\-?\d+)\)$/i",$val,$m)) $q.= "`$key` = `$key` + $m[1], "; 
else $q.= "`$key`='".$this->escape($val)."', ";
}

$q = rtrim($q, ', ') . ' WHERE '.$where.';';

return $this->query($q);
}#-#update()


#-#############################################
# desc: does an insert query with an array
# param: table, assoc array with data (not escaped)
# returns: id of inserted record, false if error
public function insert($table, $data){
$q="INSERT INTO `$table` ";
$v=''; $n='';

foreach($data as $key=>$val){
$n.="`$key`, ";
if(strtolower($val)=='null') $v.="NULL, ";
elseif(strtolower($val)=='now()') $v.="NOW(), ";
else $v.= "'".$this->escape($val)."', ";
}

$q .= "(". rtrim($n, ', ') .") VALUES (". rtrim($v, ', ') .");";

if($this->query($q)){
return mysql_insert_id($this->link_id);
}
else return false;

}#-#insert()


#-#############################################
# desc: frees the resultset
# param: query_id for mysql run. if none specified, last used
private function free_result($query_id=-1){
if ($query_id!=-1){
$this->query_id=$query_id;
}
if($this->query_id!=0 && !@mysql_free_result($this->query_id)){
$this->oops("Result ID: <b>$this->query_id</b> could not be freed.");
}
}#-#free_result()


#-#############################################
# desc: throw an error message
# param: [optional] any custom error to display
private function oops($msg=''){
if(!empty($this->link_id)){
$this->error = mysql_error($this->link_id);
}
else{
$this->error = mysql_error();
$msg="<b>WARNING:</b> No link_id found. Likely not be connected to database.<br />$msg";
}

// if no debug, done here
if(!$this->debug) return;
?>
<table align="center" border="1" cellspacing="0" style="background:white;color:black;width:80%;">
<tr><th colspan=2>Database Error</th></tr>
<tr><td align="right" valign="top">Message:</td><td><?php echo $msg; ?></td></tr>
<?php if(!empty($this->error)) echo '<tr><td align="right" valign="top" nowrap>MySQL Error:</td><td>'.$this->error.'</td></tr>'; ?>
<tr><td align="right">Date:</td><td><?php echo date("l, F j, Y \a\\t g:i:s A"); ?></td></tr>
<?php if(!empty($_SERVER['REQUEST_URI'])) echo '<tr><td align="right">Script:</td><td><a href="'.$_SERVER['REQUEST_URI'].'">'.$_SERVER['REQUEST_URI'].'</a></td></tr>'; ?>
<?php if(!empty($_SERVER['HTTP_REFERER'])) echo '<tr><td align="right">Referer:</td><td><a href="'.$_SERVER['HTTP_REFERER'].'">'.$_SERVER['HTTP_REFERER'].'</a></td></tr>'; ?>
</table>
<?php
}#-#oops()


}//CLASS Database
###################################################################################################

?>
here's what im trying to do.
Code: [Select]
$sql="SELECT * FROM rpg_announcements ORDER BY id desc";
$result = $db->query($sql);
$row = $db->fetch_array($result);
echo $row[1];
here's my error:

Database Error
Message:   MySQL Query fail: Resource id #7

 Notice: Undefined offset: 1

What am i doing wrong?

Hi

I have created a Database class that uses the singleton method.

I have a query method within this class so i would call this to return an array of results.

What i need to know is what would be the best way to use this returned array

Database Class:
Code: [Select]
<?php

class Database
{
private static $dbInstance;
private $hostname;
private $username;
private $password;
private $database;

private function __construct()
{
$this->hostname = 'localhost';
$this->username = 'user';
$this->password = 'pass';
$this->database = 'test';

mysql_connect($this->hostname, $this->username, $this->password);
mysql_select_db($this->database);
}

public static function getDBInstance()
{
if (!self::$dbInstance) 
     { 
         self::$dbInstance = new Database(); 
      } 

      return self::$dbInstance; 
}


public function query($q)
{
return mysql_query($q);
}


}

?>

Person Class:
Code: [Select]
<?php
require_once('Database.php');

class person
{
public function getName($id)
{
$con = Database::getDBInstance();
$query = $con->query("select name from data where id = ".$id);
$result = mysql_fetch_assoc($query);
echo $result['name'];

}


}

?>

Index page:
Code: [Select]
<?php

require_once('person.php');

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<?php

$person = new person();
$person->getName(1);
echo "<br />";
$person->getName(2);


?>

</body>
</html>

I created a database class to connect to a database.  The code is below.  I'm not sure how to call this connection in other classes.  Do I use:

$db->pdo = $conn->prepare($sql);

or what?  Note that the db object is instantiated at the end of the class file.  Here is the class:

 

class DB {
	
	public $pdo = '';
	//public $message = 'A message from db'; // Debug

function __construct() {
	
	// Database info located elsewhere
	$servername = "localhost";
	$username = "root";
	$password = "";
	$dbname = "dbname";
	
	try {
		
	  $this->pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	  // set the PDO error mode to exception
	  $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	}  // End Try
	
	catch(PDOException $e) {
		  echo "Error: " . $e->getMessage();
		}
		
		//echo '<h3>Everything wnet OK.</h3>';  // Debug

	} // End __construct
	
} // End class definition DB.php

$db = new DB;

Thanks,

 

--Kenoli

Hey guys,

So im building a Content Management System for my A2 project in Computing, and i have a dataBase class. Now, i can return one thing, or do things like inserts however im having a problem returning a list of things such as:

dataBase.class.php

Code: [Select]

          public static function getNYCPost($table, $limit)
{
dataBase::_dbConnect();
if($limit == 0)
{
//debug: echo 'in as 0';
$data = mysql_query("SELECT id, linklabel FROM ".$table." WHERE showing='1' AND id >= 2");
}

if($limit == 1)
{
//debug: echo 'in';
$data = mysql_query("SELECT id, linklabel FROM ". $table ." WHERE showing='1' AND id >= 2 ORDER BY id DESC LIMIT 5");
return $data;
}
}

When i try to do this in the main code:

index.php

Code: [Select]
<?php
  $list = dataBase::getNYCPost($pages,1);
//  echo $list;
  while ($row = mysql_fetch_array($list)) 
{ 
 $pageId = $row["id"];
 $linklabel = $row["linklabel"];
 $menuDisplay .= '<a href="index.php?pid=' . $pid . '">' . $linklabel . '</a><br />';
}
echo $menuDisplay;
  ?>
$list doesnt return anything, the previous method i used in the class was to make a list and an array and put the contents of the query in an array like:

Code: [Select]
list($list[] = $row);
or something i cant quite remember i saw a youtube video tutorial and it worked for them, but it wasnt for me.


If anyone knows how i can return various rows from a database it would be appreciated

Thanks.

Well, the title says it all, I do have a class named Database and one Users with multiple methods (code below). When i do try to use one user method (find_all) it returns 2 identical results from the database (and there is only one record in the DB). I tried to spot some mistake in the code but i couldn't find it, if you find something please tell...

Database Class:
Code: [Select]
<?php

/**
 * Class Name: MySQLDatabase
 * 
 * Most of the methods in this class are 
 * database-neutral methods for code reusability.
 * 
 * Author: hisk
 */

require_once("config.php");

class MySQLDatabase {
private $connection;
private $last_query;
private $magic_quotes_active;
private $real_escape_string;

function __construct() {
$this->open_connection();
$this->magic_quotes_active = get_magic_quotes_gpc();
$this->real_escape_string = function_exists("mysql_real_escape_string"); 
}

public function open_connection() {
$this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS);
if(!$this->connection) {
die("Database connection failed: " . mysql_error());
} else {
$db_select = mysql_select_db(DB_NAME, $this->connection);
if (!$db_select) {
die("Database selection failed: " . mysql_error());
}
}
}

public function close_connection() {
if(isset($this->connection)) {
mysql_close($this->connection);
unset($this->connection);
}
}

public function query($sql) {
$this->last_query = $sql;
$result = mysql_query($sql, $this->connection);
$this->confirm_query($result);
return $result;
}

private function confirm_query($result) { // usable only in query() function (private)
if(!$result) {
echo "DB Query Failed: " . mysql_error();
// The last query that might got us the problem
echo "Last Query Was: " . $this->last_query;
}
}

// agnostic
public function num_rows($result) {
return mysql_num_rows($result);
}

public function affected_rows($result)
{
return mysql_affected_rows($result);
}

public function insert_id($result) {
return mysql_insert_id($result);
}

public function fetch_array($result) {
return mysql_fetch_array($result);
}


public function escape_value($value) {
if($this->real_escape_string) {
// undo any magic quote effect so mysql_real_esc_str can work
if($this->magic_quotes_active) {  $value = stripslashes($value);}
$value = mysql_real_escape_string($value);
} else {
if (!$this->magic_quotes_active) { $value = addslashes($value); }
}
return $value;
}
}

$database = new MySQLDatabase();

?>

User Class:
Code: [Select]
<?php
// smart to include the database class
require_once("database.php");

class User {
public static function find_by_id($id=0) {
global $database;
$result_set = self::find_by_sql("SELECT * FROM users WHERE id = {$id}");
$result = $database->fetch_array($result_set);
return $result;
}

public static function find_all() {
global $database;
$result_set = self::find_by_sql("SELECT * FROM users");
return $result_set;
}

public static function find_by_sql($sql="") {
global $database;
$result_set = $database->query($sql);
return $result_set;
}

}
?>

TEST FILE:
Code: [Select]
<?php
       require_once("../includes/database.php");
   require_once("../includes/user.php");
   
   $users = User::find_all();
   $result = $database->fetch_array($users);
   foreach($result as $user) {
    echo "<pre>" . $user . "</pre>";
   }

        ?>

Can I not pass references into class properities from outside the object? 

class db_manipulation { 

private $connection = NULL;

function __construct(&$database_connection) {
$this->connection = $database_connection;
if (!$this->connection) die('couldnt connection to database');
}

function __destruct() {
mysqli_close($this->connection);
}
}

Results in: Warning: mysqli_close() [function.mysqli-close]: Couldn't fetch mysqli in

I need to set up a threaded comments system in a PHP project and I got this script shown below from http://www.jongales....#comment-436261

class Threaded_comments
{
    
    public $parents  = array();
    public $children = array();

    /**
     * @param array $comments 
     */
    function __construct($comments)
    {
        foreach ($comments as $comment)
        {
            if ($comment['parent_id'] === NULL)
            {
                $this->parents[$comment['id']][] = $comment;
            }
            else
            {
                $this->children[$comment['parent_id']][] = $comment;
            }
        }        
    }
   
    /**
     * @param array $comment
     * @param int $depth 
     */
    private function format_comment($comment, $depth)
    {   
        for ($depth; $depth > 0; $depth--)
        {
            echo "\t";
        }
        
        echo $comment['text'];
        echo "\n";
    }
    
    /**
     * @param array $comment
     * @param int $depth 
     */ 
    private function print_parent($comment, $depth = 0)
    {   
        foreach ($comment as $c)
        {
            $this->format_comment($c, $depth);

            if (isset($this->children[$c['id']]))
            {
                $this->print_parent($this->children[$c['id']], $depth + 1);
            }
        }
    }

    public function print_comments()
    {
        foreach ($this->parents as $c)
        {
            $this->print_parent($c);
        }
    }
  
}

Here’s the example usage with the data provided as an array:

$comments = array(  array('id'=>1, 'parent_id'=>NULL,   'text'=>'Parent'),
                    array('id'=>2, 'parent_id'=>1,      'text'=>'Child'),
                    array('id'=>3, 'parent_id'=>2,      'text'=>'Child Third level'),
                    array('id'=>4, 'parent_id'=>NULL,   'text'=>'Second Parent'),
                    array('id'=>5, 'parent_id'=>4,   'text'=>'Second Child')
                );

$threaded_comments = new Threaded_comments($comments);

$threaded_comments->print_comments();

I have a sample select query that pulls data from a database and stores the result in the $comments array as shown below. The $comments array is then passed as an argument to the $threaded_comments object:

$sql = 'SELECT * FROM test_comments';
// submit the query and capture the result
$result = $conn->query($sql);
$comments = array();
while ($row = $result->fetch_assoc()) {
    $comments[] = $row;
}

The challenge is that nothing is printed to the screen when I run the script. Inspection of the comments array with the var_dump function is shown below:

array (size=4)
  0 => 
    array (size=3)
      'id' => string '1' (length=1)
      'parent_id' => string '0' (length=1)
      'text' => string 'comment' (length=7)
  1 => 
    array (size=3)
      'id' => string '2' (length=1)
      'parent_id' => string '0' (length=1)
      'text' => string 'comment' (length=7)
  2 => 
    array (size=3)
      'id' => string '3' (length=1)
      'parent_id' => string '1' (length=1)
      'text' => string 'comment ' (length=8)
  3 => 
    array (size=3)
      'id' => string '4' (length=1)
      'parent_id' => string '3' (length=1)
      'text' => string 'comment ' (length=8)

I was wondering if the array format from my select query is the issue? Could anyone provide a clue as to how to fix this?   Thanks.
Edited by terungwa, 11 January 2015 - 05:07 PM.