Connect MySQL Database using PHP in OOP

Share

Let me assume that you have some background of PHP and MySQL based development . Now, you want to take advantage of object-oriented PHP to communicate with the database. Although, PHP provides, PDO class to provide some data abstraction, but we create our own, for some understanding about the language.
You simply create a file called “database.php” in your web-root directory, and using any of your favorite plain-text editor, copy and paste the following code. Then, execute the file: http://localhost:port/project_folder/database.php in your favorite browser, needless to say!

 &lt;?php<br />
//database.php<br />
class Database{ //properties private $host; private $user; private $pwd; private $db; ///////////////////////////////////<br />
public $conxn;<br />
public $sql;<br />
public $res;<br />
public $affRows;<br />
 public $numRows;<br />
 public $data = array();<br />
public $error;<br />
public $row;<br />
 /////////////////////////////////////////////////////<br />
//constructor<br />
///////////////////////////////////////////////////<br />
/*public function Database(){ } */<br />
public function __construct($host = 'localhost',<br />
                               $user = 'root',<br />
                             $pwd = '',<br />
                              $db = 'test'){<br />
//connect to the server<br />
 $this-&gt;setHost($host);<br />
$this-&gt;setUser($user);<br />
$this-&gt;setPwd($pwd);<br />
$this-&gt;setDb($db);<br />
$this-&gt;conxn = mysqli_connect($host, $user, $pwd, $db)<br />
		or trigger_error(mysqli_error($this-&gt;conxn));</p>
<p>//echo &quot; The connection has been established&quot; . $this-&gt;conxn-&gt;thread_id;</p>
<p>}<br />
//setters<br />
public function setHost($host){<br />
$this-&gt;host = $host;<br />
}<br />
public function setUser($user){<br />
$this-&gt;user = $user;<br />
}<br />
public function setPwd($pwd){<br />
$this-&gt;pwd = $pwd;<br />
}<br />
public function setDb($db){<br />
$this-&gt;db = $db;<br />
}<br />
//getters<br />
public function getHost(){<br />
return $this-&gt;host;<br />
}<br />
public function getUser(){<br />
return $this-&gt;user;<br />
}<br />
public function getPwd(){<br />
return $this-&gt;pwd;<br />
}<br />
public function getDb(){<br />
return $this-&gt;db;<br />
}<br />
//////////////////////////////////////////////////////<br />
//destructor: it gets called automatically when an object of the class<br />
//is no further required, it does not need any args<br />
////////////////////////////////////////<br />
public function __destruct(){<br />
	//close the connection</p>
<p>	//echo &quot; The connection has been closed&quot; . $this-&gt;conxn-&gt;thread_id;<br />
	@mysqli_close($this-&gt;conxn); // @ sign is used to suppress any error or warnings if any<br />
}</p>
<p>//execute the selectQuery<br />
public function selectQuery(){<br />
//execute the sql<br />
$this-&gt;res=mysqli_query($this-&gt;conxn, $this-&gt;sql) or<br />
error_log($this-&gt;error=mysqli_error($this-&gt;conxn).$_SERVER['SCRIPT_NAME']);<br />
	//decisive parameter<br />
	$this-&gt;numRows = mysqli_num_rows($this-&gt;res);</p>
<p>	if($this-&gt;numRows &gt; 0){<br />
	//there are records<br />
	while($this-&gt;row = mysqli_fetch_assoc($this-&gt;res)){<br />
		//put everything at the bottom : queue implementation<br />
		array_push($this-&gt;data, $this-&gt;row);<br />
		//$this-&gt;data[] = $this-&gt;row; //is same as above</p>
<p>	}//loop ends<br />
	}//if ends<br />
	//$this-&gt;data contains some information or completely blank<br />
	return $this-&gt;data;<br />
}<br />
//method ends</p>
<p>////////////////////////////////////////////////////////<br />
//nonSelectQuery<br />
//////////////////////////////////////////////////////<br />
public function nonSelectQuery(){<br />
	//execute the sql<br />
	$this-&gt;res = mysqli_query($this-&gt;conxn, $this-&gt;sql) or<br />
			error_log($this-&gt;error = mysqli_error($this-&gt;conxn));<br />
//decisive parameter<br />
$this-&gt;affRows = mysqli_affected_rows($this-&gt;conxn); // integer<br />
if($this-&gt;affRows &gt; 0){<br />
//success<br />
return TRUE;<br />
}else{<br />
//failure<br />
return FALSE;<br />
}<br />
}//method ends<br />
}//class ends</p>
<p>//lets instantiate: create an object<br />
$objDatabase = new Database();</p>
<p>//lets view the status og the object<br />
echo '<br />
&lt;pre&gt;';<br />
print_r($objDatabase);<br />
echo '&lt;/pre&gt;</p>
<p>';</p>
<p>

The code explanation will follow shortly. In case, you have any question, please provide them in the comment box below.

About Dhiraj 44 Articles
Blogger on development, securities, and efficiency on web technologies.

1 Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.