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

Comments

August 19, 2011 - 11:04pm — Anonymous

@fetch_array_object()

Hey!, it's an amazing script for mysql using php5+oop.
but, I wish to get an object or an objects from the database.
Using the Database.class.php class I can use:
- $db->fetch_array($sql)
- $db->fetch_all_array($sql)

I think is important to have:
- fetch_array_object($sql);
- fetch_all_array_object($sql);
using @mysql_fetch_object

only that.
Thanks! :)

August 22, 2011 - 1:50pm — ricocheting

Answer

You are more than welcome to create both functions and share them here. I personally don't ever use the fetched database info in object format, but if there is enough interest from other users it might go into a future version (with credits of course).

January 5, 2011 - 4:38am — Anonymous

Japanese UTF-8

Great class ! Thank you

I have a problem with Japanese characters appearing as question marks in the results. I fixed this problem by adding mysql_query("SET NAMES utf8;"); on line 81 in the Database.class.php file. Is this going to create performance issues ?

Stephen

January 5, 2011 - 3:10pm — ricocheting

Answer: Specifying a character set

No, there shouldn't be any performance issues setting the character-set right after it connects as long as all your queries using the object need UTF-8. One minor suggestion, for PHP4 instead of calling mysql_query() yourself use:

// set character set in PHP4
$this->query("SET NAMES utf8;");

that way the class handles the query, the link_id, and any errors. For anyone with PHP5, instead you should use:

// set character set in PHP5
mysql_set_charset('utf8', $this->link_id);

If you need to set the charset every time you connect, you can modify the function connect() to automatically set every time. bit more info on this comment on the v2 page.

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

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.

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

@fetch_array_object()

Answer

Japanese UTF-8

Answer: Specifying a character set

Updating integer fields by incrementing

Answer

Great script

beta version of user management

Comment viewing options