PHP MySQL wrapper - PHP Class to easily call mysql database functions

Description

This is not a full script. This is the MySQL class that I used with PHP4 database projects. I ran all my MySQL connections through it because it saves time and space on code. I don't have to keep typing the database information and other things like that. It also has shortcut functions because I got tired of escaping data, stripping slashes, building UGLY insert and update queries. I'm sharing this class along with some examples for using it.

Download

ZIP with all files (Database.class.php, sample config, sample how to call, copy of license, examples of queries)
Quickview Database.class.php (already included in above zip)

Requirements & Terms of Use

This script is designed to be used with the older PHP4. While it still works great with newer PHP versions, if you know you'll have access to PHP5+ you might want to try the more feature-rich PHP MySQL Wrapper v3.
Version 2.0.0 and onward is released under GNU General Public License. Basically this means you are free to use the script, modify it, and even redistribute versions of your own under the same license.

What's new

2.2.5 [2010-05-08] [credits to Manuel] - query_update() allows increment(5) to create "field = field + 5"
2.2.4 [unreleased.2010-04-18] - fetch_all_array() was passing 2 parameters to fetch_array. cleaned up oops() some more
2.2.3 [unreleased.2009-12-20] [credits to Fatih] - replaced @ repressing $_SEVER notices in oops() with real checking
2.2.2 [2009-12-17] [credits to Jerwin for help] - $this->link_id got added to escape() to help with multi-database handling
2.2.1 [2009-09-14] [credits to Charlie for help] - query_insert got mysql_insert_id($this->link_id); instead of mysql_insert_id();
2.2 [2009-08-25]
1. Cleaned up instruction page with better examples. Example in config all converted to constants.
2. Depreciated table_prefix (still exists for backwards compatibility)
3. fetch_array() no longer saving to unneed/unused $this->record
4. free_result() now checks for $this->query_id!=0
5. Suppressed default mysql_close warnings in close()
2.1.3 [2009-07-06] - query() updated for returning blank link_id and affected_rows with link_id
2.1.2 [2009-04-30] [credits to david for help] - get_magic_quotes_gpc() in escape() to get_magic_quotes_runtime()
2.1.1 [2009-03-13] - was unused var $field_table that I stripped out
2.1 [February 02, 2009] [credits to jeroen for help] - connect() and close() adjusted to fully support multiple connections. (code example added)
HTML error in oops() fixed
2.0.14 [December 05, 2008] - query_insert() was causing an error with mysql_free_result()
2.0.13 [September 18, 2008] - fixed missing else{} for $this->link_id in oops()
2.0.12 [June 29, 2008] - query_insert() and query_update() table name is escaped
2.0.10 - 2.0.11 - changes were made and later reverted (neither version was publicly available)
2.0.9 [March 19, 2008] - error messages same syntax now. after connect() database info reset so can't be dumped by hacker
2.0.8 [March 18, 2008] - query_update() $where now valid if not passing it a value. var $pass not var $pas
2.0.7 [March 16, 2008] - Cleanup minor things/wording while getting ready to publish
2.0.6 [March 10, 2008] - query_insert() returns id of inserted record
2.0.5 [March 04, 2008] - Added fetch_all_array()
2.0.4 [February 25, 2008] - Minor bug corrections. Better error handling
2.0.3 [February 20, 2008] - Added query_update() and query_insert() functions. Went from db_mysql to Database class name
2.0.2 [February 06, 2008] - Added constructor
2.0.1 [January 26, 2008] - Added escape() function, removed functions that were never used (least by me)
2.0.0 - New License
0.1 - 1.9 [December 23, 2003] - Earliest copy i can find of this is dated 2003. Didn't keep real good backups though so no idea when i actually started it. v0.1 to v1.9 was licensed as public domain.

Instructions

Calling the Class, config settings

At the top of your scripts:
require("Database.class.php");
$db = new Database("server_name", "mysql_user", "mysql_pass", "mysql_database");
What each setting means:
- server_name - Name or IP of MySQL server. Usually "localhost" although sometimes it will be like "db1337.perfora.net"
- mysql_user - User Name to log into MySQL database
- mysql_pass - Password to log into MySQL database. If no password, use double quotes with a blank value. Example ""
- mysql_database - Database to select and run the queries on
- table_prefix - Table prefix was removed in version 2.2. It is unnecessarily if you define all your table names with constants (which you should be doing). I will however leave the code in that supports it until v3.0 for backwards compatibility.
What I HIGHLY recommend (optional)
The easiest way to handle this is to create a config.inc.php file. In it put:
//database server
define('DB_SERVER', "localhost");

//database login name
define('DB_USER', "username");
//database login password
define('DB_PASS', "password");

//database name
define('DB_DATABASE', "username_mydata");

//smart to define your table names also
define('TABLE_USERS', "users");
define('TABLE_NEWS', "news");

Then at the top of your scripts, call in the config.inc.php and the Database.class.php files
require("config.inc.php");
require("Database.class.php");
$db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);

The reason why this is a good idea is because then if you change hosts etc, you can edit the single config.inc.php file, change the settings, and it is reflected on all your pages. No need to change the info on each of your pages.

Examples of how to call things

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20


// every page needs to start with these basic things

// I'm using a separate config file. so pull in those values
require("config.inc.php");

// pull in the file with the database class
require("Database.class.php");

// create the $db object
$db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);

// connect to the server
$db->connect();

#####
// your main code would go here
#####

// and when finished, remember to close connection
$db->close();

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21


// update an existing record using query_update()

$data['comments'] = 3536;
$data['title'] = "New Article";

// special cases supported for update: NULL and NOW()
$data['author'] = "NULL";// it knows to convert NULL and NOW() from a string

// also supports increment to the current database value
// will also work with a negative number. eg; INCREMENT(-5)
$data['views'] = "INCREMENT(1)";

// query_update() parameters
//     table name (ideally by calling a constant defined in config file)
//     assoc array with data (does not need escaped)
//     where condition
$db->query_update(TABLE_NEWS, $data, "news_id='46'");

// would create the query:
// UPDATE `news` SET `comments`='3536', `title`='New Article', 
//     `author`=NULL, `views`=`views` + 1 WHERE news_id='46'

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19


// insert a new record using query_insert()

$data['news_id'] = 47;
$data['title'] = "You're Top"; // insert() will auto escape it for us
$data['created'] = "NOW()";// it knows to convert NULL and NOW() from a string

// query_insert() parameters
//     table name (ideally defined as a constant, but did not for this example)
//     assoc array with data (does not need escaped)
// query_insert() returns
//    primary id of the inserted record. you can collect or ignore
$primary_id = $db->query_insert("news", $data);

// then use the returned ID if you want
echo "New record inserted: $primary_id"; 

// would create the query:
// INSERT INTO `news` (`news_id`,`title`,`created`) 
//          VALUES ('47', 'Your\'re Top', NOW())

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19


// escape() query() and fetch_array()

// pullout the first 10 entries where referrer came from google
//     using defined TABLE_USERS table name from config
//     $db->escape() escapes string to make it safe for mysql

$url = "http://www.google.com/";

$sql = "SELECT user_id, nickname FROM `".TABLE_USERS."`
          WHERE referrer LIKE '".$db->escape($url)."%'
          ORDER BY nickname DESC
          LIMIT 0,10";

$rows = $db->query($sql);

while ($record = $db->fetch_array($rows)) {
    echo "<tr><td>$record[user_id]</td>
          <td>$record[nickname]</td></tr>";
}

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22


// using escape() and fetch_all_array()

// pullout the first 10 entries where url came from google
//     using defined TABLE_USERS table name from config
//     $db->escape() escapes string to make it safe for mysql

$url = "http://www.google.com/";

$sql = "SELECT user_id, nickname FROM `".TABLE_USERS."`
          WHERE referer LIKE '".$db->escape($url)."%'
          ORDER BY nickname DESC
          LIMIT 0,10";

// feed it the sql directly. store all returned rows in an array
$rows = $db->fetch_all_array($sql);


// print out array later on when we need the info on the page
foreach($rows as $record){
    echo "<tr><td>$record[user_id]</td>
          <td>$record[nickname]</td></tr>";
}

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16


// using query_first() 

// get user's nickname using their unique ID
//    using defined TABLE_USERS table name from config

$sql = "SELECT nickname FROM `".TABLE_USERS."`
          WHERE user_id=$user_id";

// since user_id is unique, only one record needs returned
//     I use $db->query_first() instead of $db->query() and fetch_array()
//     $db->query_first() will return array with first record found
$record = $db->query_first($sql);

// since it only returns one record, query_first() does the fetching
// I can print off the record directly
echo $record['nickname'];

Code:

1
2
3
4


// delete a specific entry

$sql = "DELETE FROM `".TABLE_USERS."` WHERE user_id=$user_id";
$db->query($sql);

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15


// using $db->affected_rows
//     returns the number of rows in a table affected by your query
//     can be used after UPDATE query (to see how many rows are updated)
//     can be used after SELECT query (to see how many rows will be returned)

$sql = "SELECT nickname FROM `".TABLE_USERS."` WHERE user_id='1'";

$row = $db->query($sql); 

if($db->affected_rows > 0){
    echo "Success! Number of users found: ". $db->affected_rows;
}
else{
    echo "Error: No user found.";
}

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


require("Database.class.php");

// creating and connecting to two database
$db_1 = new Database("localhost", "user", "pass", "deadwood_public");
$db_2 = new Database("localhost", "user", "pass", "deadwood_internal");

// NOTE: this is the main difference
// when I connect using connect(true) it will force open a new connection
// otherwise connect() uses the existing connection if it contains the the same info
$db_1->connect(true);
$db_2->connect(true);

// insert data into both database
$data['member_name']="John Smith";

$db_1->query_insert("members", $data);
$db_2->query_insert("members", $data);

// close the second connection
$db_2->close();


// we can still run a query to the first connection
$rows = $db_1->query("SELECT * FROM members LIMIT 0,5");

while($row = $db_1->fetch_array($rows)){
    echo "<pre>".print_r($row,true)."</pre>";
}

// close the first connection
$db_1->close();

And now because I'm lazy and tend to look here all the time myself when i need a reference.

Data Type	Column Type	Range or Description	Storage
Numeric	TinyInt	Signed values from -128 to 127 Unsigned values from 0 to 255	1 byte
Numeric	SmallInt	Signed values from -32768 to 32767 Unsigned values from 0 to 65535	2 bytes
Numeric	MediumInt	Signed values from -8388608 to 8388607 Unsigned values from 0 to 16777215	3 bytes
Numeric	Int	Signed values from -2147683648 to 2147483647 Unsigned values from 0 to 4294967295	4 bytes
Numeric	BigInt	Signed values from -9223372036854775808 to 9223372036854775807 Unsigned values from 0 to 18446744073709551615	8 bytes
Numeric	Float	Minimum non-zero values: 1.175494351E-38 Maximum non-zero values: 3.402823466E+38	4 bytes
Numeric	Double Float	Minimum non-zero values: 2.2250738585072014E-308 Maximum non-zero values: 1.7976931348623157E+308	8 bytes
Numeric	Decimal	Varies	Maximum width + 2 bytes
String	Char	Range 1-255 characters	Always filled max width
String	VarChar	Range 1-255 characters	Length of string + 1 byte
String	TinyBlob, TinyText	Max length 255 characters	Length of string + 1 byte
String	Blob, Text	Max length 65535 characters (~64KB of text)	Length of string + 2 bytes
String	MediumBlob, MediumText	Max length 16777216 characters (16MB of text)	Length of string + 3 bytes
String	LongBlob, LongText	Max length 4294967295 characters (4GB of text)	Length of string + 4 bytes
String	Enum ('value','value2',...)	String object that can have only one set of allowed values	1 or 2 bytes
String	Set ('value','value2',...)	String object that can have one or many values of a set of allowed values.	1, 2, 3, 4, or 8 bytes
Date/Time	Date	'YY-MM-DD', 'YYYY-MM-DD', 'YYMMDD' Range "1000-01-01" to "9999-12-31"	3 bytes
Date/Time	Time	'HH:MM:SS', 'HHMMSS', 'HHMM', 'HH' Range "-838:59:59" to "838:59:59"	3 bytes
Date/Time	DateTime	'YYYY-MM-DD HH:MM:SS' "0000-01-01 00:00:00" to "9999-12-31 23:59:59"	8 bytes
Date/Time	TimeStamp	19700101000000 to sometime in the year 2037	4 bytes
Date/Time	Year	'YYYY', 'YY' Range "1901" to "2155"	1 byte

Add new comment

Comments

July 21, 2010 - 6:58pm — Anonymous

Question: Joining Tables

I am attempting to get data from a joined table:

$sql = "SELECT * FROM blog, images WHERE images.parent_page = blog.id";
$rows = $db->query($sql);

How would I echo out the results?

<?php
while ($record = $db->fetch_array($rows)) {
    echo $record['blog']['entry']; //nope
    echo $record['blog.entry']; //nope
}
?>

Is this possible?

July 22, 2010 - 11:37pm — ricocheting

I've never joined tables like that so I'm trying hard not to inject my personal syntax preferences (yours is valid, just awkward if you end up doing complex queries). But either way MySQL does not return the table names in the results set (has nothing to do with PHP). So you are just

<?php
while ($record = $db->fetch_array($rows)) {
        echo $record['entry'];//whatever field name from blog table
        echo $record['path'];//or whatever the field name from images table
}
?>

May 12, 2010 - 4:02pm — Anonymous

Error messages

Thanks for the excellent script. It has saved a lot of unnecessary coding. My question is how to suppress a MYSQL error message. I know that in general you would place a @ symbol before a call. My reason is that I have a setup script that is only used once. I thought that if I checked to see if the table already exists and then I would show the form (if not existed) or re-direct the user. Because there is no table, as the script hasn't been setup, then there is an error message.

There is an alternative that I have found but I am not sure that I could use it with this script.

SELECT count(*) 
FROM information_schema.tables 
WHERE table_schema = <schema-or-db-name> 
AND table_name = <table-or-view-name>

Any help would be appreciated

May 13, 2010 - 12:56am — ricocheting

Answer

You could try something with "SHOW TABLES"

<?php
$db->query("SHOW TABLES LIKE 'members'");
if($db->affected_rows > 0){
  echo "table exists";
}
?>

If you're running initial database setup, you can also use MySQL like CREATE TABLE IF NOT EXISTS `members` ... etc

May 6, 2010 - 5:39pm — Manuel J. Rocha

Updating integer fields by incrementing

Hi! I use your script and like it very much! Really nice script!

As you probably agree, it's much easier to update a table using your "query_update" rather than writing the hole update query (or else you wouldn't even write it!).
I'm now facing a little problem with an update, as I'm trying to do something like:
UPDATE table_name SET field_1=345, field_2='example string', field_3=field_3+1 WHERE field_id=4

If I write it like:

<?php
$data['field_1'] = 345;
$data['field_2'] = "example string";
$data['field_3'] = "field_3+1";
$db->query_update(TABLE_NAME, $data, "field_id='4'");
?>

I get this resulting query: UPDATE `table_name` SET `field_1`='345', `field_2`='example string', `field_3`='field_3+1' WHERE field_id=4

And this query puts field_3 value to the integer 0, as field_3 is an integer.

How can I do this? Is it possible with the "query_update" or I'll have to use the "query"?
TIA for a reply

May 8, 2010 - 11:31pm — ricocheting

Answer

I would say your best bet would be to add another elseif statement like the way the "null" and "now()" are handled in query_update(). something like:
elseif(preg_match("/^increment$(\-?\d+)$$/i",$val,$m)) $q.= "`$key` = `$key` + $m[1], ";
and then you can call it like

$data['field_3']="increment(5)";
// it's also case insensitive and will work with a negative number
$data['field_3']="INCREMENT(-1)";

You got me thinking and I actually really like this idea and you might see it in the official version in a future update.

May 5, 2010 - 11:39am — siretom

Great script

Great script! I have used this for two of my major web applications, one a CRM and another a Helpdesk. As far as the mysql connections, queries, and such go, I have had no problem. Other developers were able to view the code and easily make changes as well based on your intuitive method for inserting, updating, etc. data.

I have a quick question/feature request. Have you ever created a User class that would extend this MySQL wrapper class? Currently I am using www.phpuserclass.com and simply passing the mysql link connection to it, but it is a loose integration.

May 6, 2010 - 12:16am — ricocheting

beta version of user management

[edit:] v3 of the MySQL wrapper (PHP5 Singleton Class) is available now.
[edit2:] I went ahead and released a BETA version of my PHP User Login and Management for those interested in playing with it.

May 1, 2010 - 10:31pm — Simon Trigona

Insert ... on duplicate key update

In my application I do a lot of updates/inserts and to reduce the number or queries I've modified your $db->query_insert (Ver: 2.1) to include the 'ON DUPLICATE KEY UPDATE'.

<?php
#-#############################################
# desc: does an insert query with an array.
#    if parameter $update is true, will update record if it already exists
# param: table (no prefix), assoc array with data, bool (UPDATE ON DUPLICATE)
# returns: id of inserted/updated record, false if error
# NOTE: "ON DUPLICATE KEY UPDATE" requires MySQL version 4.1 or higher
function query_insert($table, $data, $update=false) {
    $q="INSERT INTO `".$this->pre.$table."` ";
    $v=''; $n=''; $d='';

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

    $q .= "(". rtrim($n, ', ') .") VALUES (". rtrim($v, ', ') .")";
    if($update) $q .= " ON DUPLICATE KEY UPDATE ".rtrim($d, ', ');
    $q .= ";";

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

May 23, 2010 - 2:27pm — Anonymous

Weird thing...

When ON DUPLICATE KEY UPDATE is used the mysql_insert_id() function return nothing. Any ideas why?

May 26, 2010 - 1:45pm — ricocheting

Answer

I just tested it and the above function returns the primary ID both updating and inserting a new record on MySQL 5.0. Are you sure you have a primary/unique key set for the table you are updating? If the table does not have a primary key, the function won't have any primary ID to return.

April 20, 2010 - 11:47pm — Anonymous

Count Rows

Is it possible to count rows using COUNT?
$sql2 = "SELECT COUNT(*) FROM `MY_TABLE` WHERE `type` LIKE 'fun' "

April 21, 2010 - 12:26pm — ricocheting

Answer

You can use a count() through a normal $db->query($sql2) or easier through query_first() like:

<?php
$sql2 = "SELECT COUNT(*) AS amount FROM `MY_TABLE` WHERE `type` LIKE 'fun'";
$row = $db->query_first($sql2);
echo $row['amount'] ." rows of fun";
?>

PHP Scripts

PHP MySQL wrapper - PHP Class to easily call mysql database functions

Description

Download

Requirements & Terms of Use

What's new

Instructions

Calling the Class, config settings

Examples of how to call things

Comments

Question: Joining Tables

Answer

Error messages

Answer

Updating integer fields by incrementing

Answer

Great script

beta version of user management

Insert ... on duplicate key update

Weird thing...

Answer

Count Rows

Answer

Comment viewing options