Home > mysql, php, sqlite > Easier And Better Mysql/Sqlite in PHP

Easier And Better Mysql/Sqlite in PHP

September 3rd, 2009
Goto comments Leave a comment

Update: fixed Mysql Class

Most websites that use php mostly use MySql, People use it because it’s dynamic, fast(in most cases) and popular.
But most of you don’t know that there’s also something called “Sqlite” it’s also an Sql database, but it stores the database in a file.

Problem

There are two main problems with using mysql and sqlite these days:

  1. To switch between mysql and sqlite you have to go and replace mysql_query with sqlite_query in every file that uses mysql.
  2. If your’e obfuscating your code then you can’t obfuscate functions.
  3. If you want to process some code or an sql query before each time a mysql or an sqlite function is run like counting the number of queries in a page, and a lot of other uses.

There are a lot of inconvenience when using mysql or sqlite functions, everyone with their own problems, and this should help you solve most of them.

The code

MySql(Don’t forget to change all the variables to your own server, host, user, pass):

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45

<?php
class MySql
{
	var $con;
	var $e;
 
	var $host = 'localhost';
	var $user = 'db_user';
	var $pass = 'db_pass';
 
	function MySql($db){
		$this->con = mysql_connect($this->host, $this->user, $this->pass);
		if(!$this->con){
			$this->e = mysql_error();
		}
		$dbcon = mysql_select_db($db, $this->con);
		if(!$dbcon){
			$this->e = mysql_error();
		}
	}
	function q($q){
		$r=mysql_query($q, $this->con);
		$qe = mysql_error();
 
		$this->e='';
		if(!empty($qe)){
			$this->e=$qe;
			return false;
		}
		return $r;
	}
	function a($r){
		return mysql_fetch_array($r, MYSQL_ASSOC);
	}
	function fa($r){
		return mysql_fetch_all($r, MYSQL_ASSOC);
	}
	function n($r){
		return mysql_num_rows($r);
	}
	function lr(){
		return mysql_insert_id($this->con);
	}
}
?>

And For Sqlite:

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

<?php
class SQLite
{
	var $con;
	var $e;
	function SQLite($db){
		$this->con = sqlite_open($db);
	}
	function q($q){
		$r = sqlite_query($this->con, $q, SQLITE_BOTH, $qe);
		if(!empty($qe)){
			$this->e = $qe;
			return false;
		}
		return $r;
	}
	function a($r){
		return sqlite_fetch_array($r,SQLITE_ASSOC);
	}
	function fa($r){
		return sqlite_fetch_all($r,SQLITE_ASSOC);
	}
	function n($r){
		return sqlite_num_rows($r);
	}
	function lr(){
		return sqlite_last_insert_rowid($this->con);
	}
}
?>



Just put the one you need in a file called sql.php, or if you use both put them both in the same file(if you want you can also put them into different files, doesn’t matters…).


To use MySql in your php add this before you use mysql(only once):

1
2
3
4

<?php
include 'sql.php'; // Include The Sql Class
$sql = new MySql('my_database'); // Open The database called 'my_database'
?>



And this before you use Sqlite(only once):

1
2
3
4

<?php
include 'sql.php'; // Include The Sql Class
$sql = new Sqlite('database.db'); // Open The database in the database.db file
?>

If you are using both just rename each variable to their own name(e.g. for mysql call it $mysql).

How do I use it?

Here’s an example of how to use the classes instead of php functions.

1
2
3
4
5
6
7
8
9
10

<?php
$q = $sql->q('SELECT name FROM peoples'); // Get a Query from the database
 
// Now you can use more advanced stuff
 
// For example a while loop to list all peoples
while($a = $sql->q($q)){
	echo $a['name'],'<br/>';
}
?>


Using two databases at the same time

For MySql:

1
2
3
4
5
6
7
8

<?php
include 'sql.php';
$sql1 = new MySql('database1'); // $sql1 now holds databse1
$sql2 = new MySql('database2'); // $sql2 now holds databse2
 
$sql1->q('SELECT * FROM table'); // This will work on database 1
$sql2->q('SELECT * FROM table'); // This will work on database 2
?>


For Sqlite:

1
2
3
4
5
6
7
8

<?php
include 'sql.php';
$sql1 = new Sqlite('database1.db'); // $sql1 now holds databse1.db database file
$sql2 = new Sqlite('database2.db'); // $sql2 now holds databse2
 
$sql1->q('SELECT * FROM table'); // This will work on database 1
$sql2->q('SELECT * FROM table'); // This will work on database 2
?>

So as you can see it will allow you to use two databases or more easily.

Which functions are which

MySql:

mysql_query() – $sql->q()
mysql_fetch_array() – $sql->a()
mysql_fetch_all() – $sql->fa()
mysql_num_rows() – $sql->n()
mysql_insert_id() – $sql->lr()

Sqlite:

sqlite_query() – $sql->q()
sqlite_fetch_array() – $sql->a()
sqlite_fetch_all() – $sql->fa()
sqlite_num_rows() – $sql->n()
sqlite_last_insert_rowid() – $sql->lr()



There are a lot of uses for this kind of things, just giving the idea.
And that’s it, saves time and file size, better, smaller and easier.

admin mysql, php, sqlite , ,

  1. T-Bone
    November 17th, 2009 at 09:02 | #1
    Reply | Quote

    Great post! I’m going to use this because I know I’ll have to switch from sqlite to mysql in the future, and this will save me plenty of time when I make the switch.

  1. No trackbacks yet.