|
PHP - Database Class Review
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. Similar TutorialsAnyone please provide me good examples of php database classes. with usage examples. Thanks 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. 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"); ?> 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; } } } ?> 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 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']; } } 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? Can somebody help me what i'm doing wrong? The value from database is not visible when using: <?php $artikel = new Artikel(); $content= $artikel->printArtikel(); echo $content; ?> <?php class Artikel { // db connection protected $mydb; // database value protected $land; public function __construct($artikelnummer = false) { // databaseconnection: $this->mydb = new MyDB(); if($artikelnummer) { $this->load($artikelnummer); } else // load everything from database. { $sql = "SELECT * FROM artikel"; $this->mydb->doQuery($sql); $this->mydb->close(); } } public function load($artikelnummer) { $sql = "SELECT * FROM artikel"; $this->mydb->doQuery($sql); if($artikel = $this->mydb->fetch()) { // i got a feeling there is something missing here, like query from database?? } $this->mydb->close(); } public function printArtikel() { // return database value as table. $html = "<table border='1'>"; $html .= "<tr> <td>".$this->land."</td> </tr>"; $html .= "</table>"; return $html; } } ?> Other class are accessing extern. For example database connection with class MyDB. please, can somebody correct my script?? 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. 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 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> 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. Heya folks, I came here in hope someone could shed some light on the situation I'm in, it's probably looking me straight in the face and I cant see it.. So basically, I've got my page sorted, I'll theme it for my website later, but it echo's out entry's from a SQL database (images to be reviewed) then it has the name, id and accept and reject buttons. Currently I have the page echoing perfectly, the reject button removes from SQL but does not delete the actual image (that's where I need help) The accept button currently has no function as I've tried INSERT INTO but I cant get it working, so here's some code I hope someone knows what's missing Page that echo's from SQL; Code: [Select] <?php $dbHost = "xxxxxxx"; $dbUser = "xxxxxxx"; $dbPass = "xxxxxxx"; $dbName = "xxxxxxxx"; $db = mysql_connect($dbHost,$dbUser,$dbPass); mysql_select_db($dbName,$db); $image_name='fullsize' ?> <?php $result = mysql_query("SELECT id, fullsize FROM tblimages"); $filename['fullsize'] ?> <html> <table> <tr> <td>Image Uploads</td> </tr> <?php while($row = mysql_fetch_array($result)) : $image = $row['fullsize'] ?> <tr> <td><?php echo $row['id']; ?></td> <td><?php echo $row['fullsize']; ?></td> <td><?php echo '<img src= "uploads/'.$row['fullsize'].'" width="180" height="180"/> '; ?> </td> <!-- and so on --> <td> <form action="delete.php" method="post"> <input type="hidden" name="delete_id" value="<?php echo $row['id']; ?>" /> <input type="submit" value="Reject" /> </form> <form action="insert.php" method="post"> <input type="hidden" name="insert_id" value="<?php echo $row['filename']; ?>" /> <input type="submit" value="Accept" /> </form> </td> </tr> <?php endwhile; ?> </table> </html> The delete.php's code is; Code: [Select] <?php if(isset($_POST['delete_id']) && !empty ($_POST['delete_id'])) { $delete_id = mysql_real_escape_string($_POST['delete_id']); mysql_query("DELETE FROM tblimages WHERE `id`=".$delete_id); header('Location: test.php'); } ?> And my insert.php is currently faulty code, so but I used virtually the same as the delete.php but where 'delete_id' is I replaced with 'insert_id' My database is laid out like this, tblimages (this is where images that need to be reviewed are stored) [id, fullsize] <- columns stored inside images (this is where images that have been accepted should be moved to) [filename] <-where images should be inserted to. Hope someone can understand and help with this, if so, thank you very much ^_^ I've develped this small script to display user reviews stored in a my databases review table. The problem with the script though is that it seems to be looping ad nauseum. The wierd thing though, is that I've used a very similair script to display another list of items on this site, and it worked correctly without any issue. Could someone take a look at this for me and diagnose the error with the script. The SQL table: Code: [Select] CREATE TABLE IF NOT EXISTS `rev` ( `id` int(11) NOT NULL AUTO_INCREMENT, `rev_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `usr_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `text` varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; The PHP script: Code: [Select] User reviews for <? echo $member;?> <br> <table border="0"> <?php for($count = 1; $count <= $revrows; $count++) { $sqlrev = "SELECT * FROM rev WHERE rev_name = '$member' ORDER BY id DESC"; $revresult = mysql_query($sqlrev) or die(mysql_error()); $revrows = mysql_fetch_row($revresult); $revname = mysql_fetch_array($revresult); ?> <tr> <?php print "Review by: " . $revname['usr_name'] . "<br>". $revname['text']; ?> </tr> <?php } ?> </table> why is it that when i try to submit it tells me add a picture but i did added Code: [Select] <?php //decarling some variables $msg = ""; //begin if if($_POST['submitbtn']){ $author = mysql_real_escape_string($_POST['author']); $date = mysql_real_escape_string($_POST['date']); $picture = $_FILES['picture']['name']; $ext = strtolower(substr($name,strpos($name,'.')+1)); $size = $_FILES['picture']['size']; $maxsize = 200000; $type = $_FILES['picture']['type']; $tmp = $_FILES['picture']['tmp_name']; $review = mysql_real_escape_string($_POST['review']); $move = "uploads/"; if(isset($author) && !empty($author)){ if(isset($date) && !empty($date)){ if(isset($picture) && !empty($picture)){ if($size <= $maxsize){ if($ext == 'jpg' || $ext == 'jpeg' || $ext == 'png'){ if(move_uploaded_file($tmp,$move.$name)){ if(isset($review) && !empty($review)){ $query = mysql_query(" INSERT INTO reviews ('',author,date,picture,review) VALUES ('',$author,$date,$picture,$review)"); }else $msg = "Please write a review"; }else $msg = "Error has happen try again later"; }else $msg = "Image must be a jpg, jpeg, or png"; }else $msg = "You must select a smaller image size"; }else $msg = "Select a picture"; }else $msg = "Please enter a date"; }else $msg = "Please fill in the Authors name"; } //ending the if here ?> hello dear community i try to find a way to use file_get_contents: a download of set of pages: Can any body review my approach .. and as i thought i can find all 790 resultpages within a certain range between Id= 0 and Id= 100000 i thought, that i can go the way with a loop: http://www.foundationfinder.ch/ShowDetails.php?Id=11233&InterfaceLanguage=&Type=Html http://www.foundationfinder.ch/ShowDetails.php?Id=927&InterfaceLanguage=1&Type=Html http://www.foundationfinder.ch/ShowDetails.php?Id=949&InterfaceLanguage=1&Type=Html http://www.foundationfinder.ch/ShowDetails.php?Id=20011&InterfaceLanguage=1&Type=Html http://www.foundationfinder.ch/ShowDetails.php?Id=10579&InterfaceLanguage=1&Type=Html How to mechanize with a loop from 0 to 10000 and throw out 404 responses once you reach the page we then could use beautifulsoup to get the content (in our case the image file address) but we also could just loop trough the images directely with simple webrequests. Well - how to proceed: like this: <?php // creating a stream! $opts = array( 'http'=>array( 'method'=>"GET", 'header'=>"Accept-language: en\r\n" . "Cookie: foo=bar\r\n" ) ); // opens a file $file = file_get_contents('http://www.example.com/', false, $context); ?> a typical page is http://www.foundationfinder.ch/ShowDetails.php?Id=134&InterfaceLanguage=&Type=Html and the related image is at http://www.foundationfinder.ch/ShowDetails.php?Id=134&InterfaceLanguage=&Type=Image after downloading the images we will need to OCR them to extract any useful info, so at some stage we need to look at OCR libs. I think google opensourced one, and since its google it has a good chance it has a python API can anybody review the approach - look forward to hear from you Hello all. I'm a newbie to this site and PHP and trying to build a basic contact/info form for an insurance company. I've been testing what I've done in FF, IE, and Google Chrome, and it seems to be generating consistent results ... but I thought I'd run it past the EXPERTS here, so you can tell me all that I've done wrong. : ) Attached is the PHP page that is triggered by a basic HTML page with check boxes, etc. And below is what a resulting email looks like, which is fine by me (of course this is empty). Thanks for any advice! -RP ------------------------------------------------- Name: Tel: Company: State: Website: Employees: ===== Type of Business ===== Manufacturer: Retailer: Jobber/Restyler: Distributor: Motorsports: Professional Services: ===== Insurance Needs ===== General Liability: Garage/Keepers: Property Building: Property Equipment: Loss of Income: Worker's Comp.: Internet Liability: Life & Disability: Product Liability: Legal Liability: Property Contents: Cargo/Mobile Property: Employee Dishonesty: 401K & Retirement: Health: Other: Personal Comments: ========================= I've been maintaining my old site http://www.tomsfreelance.com even though it's kind of just a business card at this point. So I decided to implement some javascript stuff I had been working on for other reasons. So far it seems to I have some work to do in Firefox, but I think it's working pretty well otherwise.
This is a bit of an oddball of a site and just about 99% javascript.
Feedback is much appreciated!
|
|||||||