PHP Classes

PHP MySQL Database: Connect and query a MySQL database

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
StarStar 35%Total: 604 This week: 1All time: 5,172 This week: 34Up
Version License PHP version Categories
php-simple-db-class 1.0.0GNU General Publi...5PHP 5, Databases
Description 

Author

Waqar Ahmed

This class can connect and query a MySQL database.

It parses a INI file to extract the database server host, user, password and database name to connect.

The class can perform arbitrary SQL queries and extract the query results into arrays.

The database connection is closed when the object is destroyed.

Picture of Waqar Ahmed
Name: Waqar Ahmed is available for providing paid consulting. Contact Waqar Ahmed .
Classes: 3 packages by
Country: United Kingdom
Age: ???
All time rank: 2612119 in United Kingdom
Week rank: 44 Up3 in United Kingdom Up
Innovation award
Innovation award
Nominee: 1x

Recommendations

mysql database
script for update mysql database

Example

<?php
require_once('database.class.php');

/**
* database table used in this example
*
CREATE TABLE `guestbook` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `message` varchar(400) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

*
* some random data inserted
*
insert into guestbook (user, message, DATE) values ('me', 'anything', now());
insert into guestbook (user, message, DATE) values ('user', 'a message', now());
insert into guestbook (user, message, DATE) values ('user1', 'new message', now());


**/


//run select query and display results
$db = new MySQLDb;
$query = "select * from guestbook";
$result = $db->query($query);

//check total number of rows returned
// var_dump($result); to see details of returned object
echo "number of rows in result set = ".$result->num_rows;

//fetch those rows into $rows
$rows = $db->fetch();
var_dump($rows);

//insert into table query
$query = "insert into guestbook (user, message, DATE) values ('user', 'a message', now())";
$result = $db->query($query);

//$result will return false if query was unsucsessful
echo ($result) ? "<br> Successfully inserted" : "<br> Insert query was not successfull";

//update a row in table
$query = "update guestbook set user = 'user1' where id = 3";
$result = $db->query($query);
echo ($result) ? "<br> Successfully update" : "<br> Update query was not successfull";

//delete a row in table
$query = "delete from guestbook id = 2";
$result = $db->query($query);
echo ($result) ? "<br> Successfully deleted" : "<br> Delete query was not successfull";

/* SQL Injection Prevention
Use prepareed statement and bind variables
Most common attacks can be prevented by binding variables
Use PHP sanitize filters on user inputs
e.g: FILTER_SANITIZE_NUMBER_INT will remove all non-int from integer inputs
more info:
http://us3.php.net/manual/en/filter.filters.sanitize.php
http://php.net/manual/en/pdo.prepared-statements.php
*/

// sanitize user id input, remove all non digit characters
    $id = filter_var($_GET['id'], FILTER_SANITIZE_NUMBER_INT);

// Insert query with prepare and bind
    $values = array(0 => 'userx', 1 => 'guest message', 2 => date("Y-m-d H:i:s"));
    $stmt = $db->connection->prepare("INSERT INTO guestbook (fid, user, message, DATE) VALUES (?, ?, ?, ?)");
    if($stmt){
// bind variables here "sss" declares 1st, 2nd & 3rd values are string, use i for integer
    $stmt->bind_param("sss", $values[0], $values[1], $values[2]);
    $stmt->execute();
    }

?>

Details

PHP-Database-Class

OOP Database class for PHP MySQL connection and insert, update, delete and select query

CONFIG File

All database configurations are stored in seperate config.ini.php file, which can be kept off document root.

DATABASE CONNECTION AND QUERIES

Database class loads configuration file and establishes a MySQLi connect in constructor so just creating an instance of class connects you with database.

e.g:

$db = new MySQLDb; // would connect to database

To run any kind of query (select, insert, update, delete), pass SQL query to query method of class

e.g:

$query = "select * from table_name";

$result = $db->query($query); //$result object returns true on successful execution

To select rows after executing query, use fetch() method and store result in an array

e.g:

$rows = $db->fetch();

Examples of Use:

examples.php has a create table SQL to create a test table and load some data in it

Examples includes quering database, checking result object and examples of select, insert, update and delete queries.


  Files folder image Files (5)  
File Role Description
Accessible without login Plain text file config.ini.php Conf. Configuration script
Plain text file database.class.php Class Class source
Accessible without login Plain text file example.php Example Example script
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file README.md Doc. Documentation

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
Downloadphp-simple-db-class-2017-06-03.zip 15KB
Downloadphp-simple-db-class-2017-06-03.tar.gz 14KB
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:604
This week:1
All time:5,172
This week:34Up
User Ratings User Comments (5)
 All time
Utility:41%StarStarStar
Consistency:41%StarStarStar
Documentation:50%StarStarStar
Examples:58%StarStarStar
Tests:-
Videos:-
Overall:35%StarStar
Rank:4092
  

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.