June 14, 2016 Design 0

In this article I’ll explain how to connect to a MySQL database via Object Oriented PHP (OOP) techniques. There are many reasons you might want to use OOP PHP to connect to a database – your application will be safer, more structured, organised and re-usable.  In this article I’ll only write about how to connect to the database and retrieve information from the tables via MySQL SELECT – the update and delete functions will be shown and discussed in future articles.

So here is what we’ll do with our code:

We’ll create a class first, with the below structure:

1
2
3
4
5
6
class Database
{
public function connect() { }
public function select() { }

}

 

Here is the 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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
class Database {
/////////////////////////////////////////////////////// private, so no one can access it outside the Database class (they can't steel your credentials)
private $db_host = 'your_host_here_localhost';
private $db_user = 'database_user_here';
private $db_pass = 'your_password_here';
private $db_name = 'your_database_name';
/////////////////////////////////////////////////connect
public function connect()
{
if(!$this->con)
{
$myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);
if($myconn)
{
$seldb = @mysql_select_db($this->db_name,$myconn);
if($seldb)
{
$this->con = true;
return true;
} else
{
return false;
}
} else
{
return false;
}
} else
{
return true;
}
}
private $result = array();
private function tableExists($table)
{
$tablesInDb = @mysql_query('SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
if($tablesInDb)
{
if(mysql_num_rows($tablesInDb)==1)
{
return true;
}
else
{
return false;
}
}
}
public function select($table, $rows = '*', $where = null, $order = null)
{
$q = 'SELECT '.$rows.' FROM '.$table;
if($where != null)
$q .= ' WHERE '.$where;
if($order != null)
$q .= ' ORDER BY '.$order;
if($this->tableExists($table))
{
$query = @mysql_query($q);
if($query)
{
$this->numResults = mysql_num_rows($query);
for($i = 0; $i < $this->numResults; $i++)
{
$r = mysql_fetch_array($query);
$key = array_keys($r);
for($x = 0; $x < count($key); $x++) { // Sanitizes keys so only alphavalues are allowed if(!is_int($key[$x])) { if(mysql_num_rows($query) > 1)
$this->result[$i][$key[$x]] = $r[$key[$x]];
else if(mysql_num_rows($query) < 1) $this->result = null;
else
$this->result[$key[$x]] = $r[$key[$x]];
}
}
}
return true;
}
else
{
return false;
}
}
else
return false;
}
/////////////////////////////////////////////////////////////////
public function GetResult(){
return $this->result;
}
}
////////////////////////////// create a new instance of the class Database
$db = new Database();
/////////////////////////////// connect
$db->;connect();
///////////////////////////////////////// specify the table to select
$db->select('your_table_to_select');
$res = $db->getResult();
foreach ($res as $resulty) {
////////////////////////////////////////////////////////////// loop through the results and retrieve the desired column
echo $resulty['table_column'].'
'
;
}

Just as I mentioned, this OOP PHP code will do 2 main things: it will connect to your MySQL database, and retrieve the results. The next functions of UPDATE and DELETE will be discussed in the next article .

Please let me know in your comments if you have any questions, and feel free to play around with the code above. Don’t forget to change the user name, host name, database name and password (at the top of the code.