PHP MySQL wrapper

Description

This is not a full script. This is the MySQL class that I use in all my PHP database projects. I run all my MySQL connections through it because it saves me time and space on my 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

Get the newest version

Terms of Use

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.

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