note 103557 deleted from class.mysqli by danbrown
| From: | danbrown@php.net | Date: | Wed, 20 Apr 2011 14:32:34 +0000 |
| Subject: | note 103557 deleted from class.mysqli by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-178998@lists.php.net to get a copy of this message | ||
Note Submitter: codrakon
----
here is code for a generic Data Access Layer that can be used for insert, update, and select
queries. It uses the MySQLi library to connect to the db and handle MySQL queries. Note depandancies
on the mnconfig.php file.
Usage:
function promoemail($membershipnumber, $emailaddress, $name, $contactnumber) {
/* your code here for example
if ($membershipnumber != null) {....
.....}
*/
$dbfunctions = new mnDAL();
$result = $dbfunctions->getnewsletterusers();
if($this->commitdb($membershipnumber, $emailaddress, $name, $contactnumber, $vouchernumber,
$discountnumber, $validdate)) {
return $body;
}
else {
return false;
}
foreach ($result as $row) {
echo $row['membershipnumber'].'<br/>';
}
}
function commitdb ($membershipnumber, $emailaddress, $name, $contactnumber, $vouchernumber,
$discountnumber, $validdate) {
$dbfunctions = new mnDAL();
echo $membershipnumber . ' ' . $emailaddress . ' ' . $name . '
' . $contactnumber . ' ' . $validdate;
$dbfunctions->insertnewsletteruser($membershipnumber, $emailaddress, $name, $contactnumber,
$vouchernumber, $discountnumber, $validdate);
return true;
}
}
Note that $dbfunctions = new mnDAL(); creates a new instance of the object, which has a public
method
$dbfunctions->insertnewsletteruser($membershipnumber, $emailaddress, $name, $contactnumber,
$vouchernumber, $discountnumber, $validdate);
END FRONTEND EXAMPLE
BEGIN Generic Data Access Layer Code
<?php
/*Author: Chris Morton/codrakon;
Dependancies: mnconfig.php (contains $dbhost, $dbusername, $dbpassword, $dbname)
Version: 1.0 Last updated: 17-01-11
*/
include_once('mnconfig.php');
class mnDAL {
/*
INSERT PUBLIC FUNCTIONS HERE
*/
private function dbconnect() {
global $dbhost, $dbusername, $dbpassword, $dbname;
$con = mysqli_connect($dbhost, $dbusername, $dbpassword, $dbname);
if (mysqli_connect_errno()) {
printf('<br/>Connection to Database failed: %s<br/>',
mysqli_connect_error());
exit();
}
return $con;
}
private function doinsertquery($params,$query) {
if ($con = $this->dbconnect()) {
if ($stmt = mysqli_prepare($con, $query)){
if ($params != null) {
$types = $params[0];
$dynphp = 'mysqli_stmt_bind_param($stmt, $types, ';
for ($x = 1; $x <= count($params)-1; $x++) {
$dynphp .= '$params[' . $x . '],';
}
$dynphp = substr($dynphp, 0, strlen($dynphp)-1);
$dynphp .= ');';
eval($dynphp);
}
}
mysqli_stmt_execute($stmt);
$affectedrows = mysqli_stmt_affected_rows($stmt);
mysqli_stmt_close($stmt);
return $affectedrows;
}
}
private function doselectquery($params, $query) {
/*code ommitted due to space on php.net, contact me for code*/
}
private function doupdatequery($params, $query) {
/*contact codrakon for complete code*/
}
private function getvars($fields) {
$outputstr = '';
foreach ($fields as $field) {
$outputstr .= '$col[\''. strtolower($field->name) .'\'],';
}
$outputstr = substr($outputstr, 0, strlen($outputstr)-1);
return $outputstr;
}
}
?>
As one can see there is
/*
INSERT PUBLIC FUNCTIONS HERE
*/
In here you would insert such a function as this:
function insertnewsletteruser ($membershipnumber, $emailaddress, $name, $contactnumber,
$vouchernumber, $discountnumber, $validdate) {
$params = array('sssssss',$name, $emailaddress, $contactnumber, $membershipnumber,
$vouchernumber, $discountnumber, $validdate);
$query = 'Insert into mnnewsletters (Name, Email, ContactNumber, MembershipNumber,
VoucherNumber, DiscountNumber, ValidUntil)
Values (?,?,?,?,?,?,?)';
return $this->doinsertquery($params,$query);
}
which is used by the front end code.
END GENERIC DATA ACCESS LAYER CODE