Prctical PHP Class with MySQL DB connection Included Fuctionalities Such as connecting a DB , DB , Insert Data , Delete , Update , Search …
I have included a practicle php class as follows. You can use the functions when you needed using OOP php concepts .
PHP class (connection.php)
<?php</pre>
//create a class for connecting a database
Class connect
{
//Use your DB connection details in here i have used a MySQL connection
Var $HostName ="";
Var $Schema = "";
Var $username ="";
Var $Password = "";
Var $con2;
//create the connect function
function connectDB()
{
$con = mysql_connect($this->HostName,$this->username,$this->Password);
$this->con2 =$con;
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
else
{
//echo "You have successfully connected to the database !! ";
}
}
//create function for select database
function selectDB()
{
mysql_select_db($this->Schema);
//echo "</br> successfully selected the DB!!";
if (mysql_error())
{
print "Database ERROR: " . mysql_error();
}
}
//functon insert
function insert2($appID,$cusID , $date ,$hour, $min , $ampm, $venue, $description ,$progress , $deal , $plan)
{
$sql = " INSERT INTO appoinments";
$sql .= " (appID , cusID , date ,hour, min , ampm, venue, description , progress , deal ,plan)VALUES";
$sql .= " ('','$cusID','$date','$hour','$min','$ampm','$venue','$description','$progress','','$plan') ";
#execute SQL statement
$result = mysql_query($sql,$this->con2);
//echo "</br>$sql </br>" ;
mysql_error();
}
//function for search customer IDS
function searchID($cusName)
{
$rowcount=0;
$sql = "Select * from customer where name='".$cusName ."'";
$result = mysql_query($sql,$this->con2);
// echo "<hr/>";
echo "<center><table width='800' cellpadding='2' border =0>";
echo "<tr bgcolor='#02a7ec' > <td><b>CusID</b></td><td><b>Title</b> </td><td><b>Initial </b></td><td><b> Name</b></td> <td><b> Desig</b></td> <td><b> NIC</b></td> <td><b>B'day </b></td><td><b>Address1 </b></td><td><b>Address2 </b></td><td><b>Mobile1 </b></td><td><b>Mobile2 </b></td><td><b>Work Ph. </b></td><td><b>Home Ph. </b></td><td><b>Mail1 </b></td><td><b>Mail2 </b></td> <td><b>FB </b></td><td><b>Google </b></td><td><b>Skype </b></td><td><b>Description </b></td><td><b>Joined Date </b></td></tr>";
while($row = mysql_fetch_array( $result ))
{
echo "<form name='form". $rowcount ."' action='deleteEditmakeApp.php' method='post'>";
if ($rowcount%2==1)
{
$rowcol ="#c7ebfa";
}
else if($rowcount%2==0)
{
$rowcol ="#86d7f9";
}
echo "<tr bgcolor='".$rowcol."'>";
echo "<td>";
echo "<input type='text' name='cusID' size='3px' value='".$row['CusID']."'>";
echo "</td>";
echo "<td>";
echo $row['title'];
echo "</td>";
echo "<td>";
echo $row['initial'];
echo "</td>";
echo "<td>";
echo $row['name'];
echo "</td>";
echo "<td>";
echo $row['desig'];
echo "</td>";
echo "<td>";
echo $row['NIC'];
echo "</td>";
echo "<td>";
echo $row['date']."/".$row['month']."/".$row['year'];
echo "</td>";
echo "<td>";
echo $row['address1'];
echo "</td>";
echo "<td>";
echo $row['address2'];
echo "</td>";
echo "<td>";
echo $row['mobil1'];
echo "</td>";
echo "<td>";
echo $row['mobile2'];
echo "</td>";
echo "<td>";
echo $row['work'];
echo "</td>";
echo "<td>";
echo $row['home'];
echo "</td>";
echo "<td>";
echo $row['email1'];
echo "</td>";
echo "<td>";
echo $row['email2'];
echo "</td>";
echo "<td>";
echo $row['fb'];
echo "</td>";
echo "<td>";
echo $row['gt'];
echo "</td>";
echo "<td>";
echo $row['skp'];
echo "</td>";
echo "<td>";
echo $row['other'];
echo "</td>";
echo "<td>";
echo $row['currDate'];
echo "</td>";
echo "</tr></form>";
$rowcount = $rowcount +1;
}
echo "</table ></center>";
//echo "<hr/>";
}
function selectAllcusByID($q)
{
$sql = "Select * from customer where cusID='".$q ."'";
$result = mysql_query($sql,$this->con2);
$count = 0;
while($row = mysql_fetch_array($result))
{
$count = $count +1;
}
if($count>0)
{
echo "<img src='validating/true.png'> <font color='green'>Ok there is a customer in this ID!! </font>";
}
else
{
echo "<img src='validating/false.png'> <font color='red'>Sory there is no customer found in this ID </font>";
}
}
function filterAppDate($date1 ,$date2)
{
$rowcount =0;
$formcount=0;
$sql = "Select * from appoinments WHERE `date` BETWEEN '".$date1."' AND '".$date2."' " ;
$result = mysql_query($sql,$this->con2);
echo "<center><table width='800' cellpadding='5' border =0> ";
echo "<tr bgcolor='#02a7ec' > <td><b>appID</b></td><td><b>Date</b> </td><td><b>Time </b></td><td><b> Venue</b></td> <td><b>Description </b></td><td><b>Progress </b></td><td><b>Deal </b></td><td><b>Delete </b></td><td><b>Edit </b></td></tr>";
while($row = mysql_fetch_array( $result ))
{
echo "<form name='form". $rowcount ."' action='deleteapp.php' method='post'>";
if ($rowcount%2==1)
{
$rowcol ="#c7ebfa";
}
else if($rowcount%2==0)
{
$rowcol ="#86d7f9";
}
echo "<tr bgcolor='".$rowcol."'>";
echo "<td>";
echo "<input type='text' size='2' name='appID' value='".$row['appID'] ."'> ";
echo "</td>";
echo "<td>";
echo $row['date'];
echo "</td>";
echo "<td>";
echo $row['hour'].":". $row['min']. " ". $row['ampm'];
echo "</td>";
echo "<td>";
echo $row['description'];
echo "</td>";
echo "<td>";
echo $row['venue'];
echo "</td>";
echo "<td>";
echo $row['progress'];
echo "</td>";
echo "<td>";
echo $row['deal'];
echo "</td>";
echo "<td>";
echo "<input type='submit' name='delete' value='delete'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' name='edit' value='edit'>";
echo "</td>";
echo "</tr> </form>";
$rowcount = $rowcount +1;
}
echo "</table > </center>";
}
function selectcuSThisMonth($lastMonth,$today)
{
$rowcount =0;
$sql = "Select * from customer WHERE `currDate` BETWEEN '".$lastMonth."' AND '".$today."' " ;
$result = mysql_query($sql,$this->con2);
// echo "<hr/>";
echo "<center><table width='800' cellpadding='5' border =0>";
echo "<tr bgcolor='#02a7ec' > <td><b>CusID</b></td><td><b>Title</b> </td><td><b>Initial </b></td><td><b> Name</b></td> <td><b> Desig</b></td> <td><b> NIC</b></td><td><b>B'day </b></td><td><b>Address1 </b></td><td><b>Address2 </b></td><td><b>Mobile1 </b></td><td><b>Mobile2 </b></td><td><b>Work Ph. </b></td><td><b>Home Ph. </b></td><td><b>Mail1 </b></td><td><b>Mail2 </b></td> <td><b>FB </b></td><td><b>Google </b></td><td><b>Skype </b></td><td><b>Description </b></td><td><b>Joinned Date </b></td><td><b>Delete </b></td><td><b>Edit </b></td><td><b>Make.App. </b></td></tr>";
while($row = mysql_fetch_array( $result ))
{
echo "<form name='form". $rowcount ."' action='deleteEditmakeApp.php' method='post'>";
if ($rowcount%2==1)
{
$rowcol ="#A9F5D0";
}
else if($rowcount%2==0)
{
$rowcol ="#86d7f9";
}
echo "<tr bgcolor='".$rowcol."'>";
echo "<td>";
echo "<input type='text' name='cusID' size='3px' value='".$row['CusID']."'>";
echo "</td>";
echo "<td>";
echo $row['title'];
echo "</td>";
echo "<td>";
echo $row['initial'];
echo "</td>";
echo "<td>";
echo $row['name'];
echo "</td>";
echo "<td>";
echo $row['desig'];
echo "</td>";
echo "<td>";
echo $row['NIC'];
echo "</td>";
echo "<td>";
echo $row['date']."/".$row['month']."/".$row['year'];
echo "</td>";
echo "<td>";
echo $row['address1'];
echo "</td>";
echo "<td>";
echo $row['address2'];
echo "</td>";
echo "<td>";
echo $row['mobil1'];
echo "</td>";
echo "<td>";
echo $row['mobile2'];
echo "</td>";
echo "<td>";
echo $row['work'];
echo "</td>";
echo "<td>";
echo $row['home'];
echo "</td>";
echo "<td>";
echo $row['email1'];
echo "</td>";
echo "<td>";
echo $row['email2'];
echo "</td>";
echo "<td>";
echo $row['fb'];
echo "</td>";
echo "<td>";
echo $row['gt'];
echo "</td>";
echo "<td>";
echo $row['skp'];
echo "</td>";
echo "<td>";
echo $row['other'];
echo "</td>";
echo "<td>";
echo $row['currDate'];
echo "</td>";
echo "<td>";
echo "<input type='submit' value='delete' name='delete'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' value='edit' name='edit'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' value='Make.app.' name='makeapp'>";
echo "</td>";
echo "</tr></form>";
$rowcount = $rowcount +1;
}
echo "</table ></center>";
//echo "<hr/>";
}
function selectbetweenDates($date1,$date2)
{
$rowcount =0;
$sql = "Select * from customer WHERE `currDate` BETWEEN '".$date1."' AND '".$date2."' " ;
$result = mysql_query($sql,$this->con2);
// echo "<hr/>";
echo "<center><table width='800' cellpadding='5' border =0>";
echo "<tr bgcolor='#02a7ec' > <td><b>CusID</b></td><td><b>Title</b> </td><td><b>Initial </b></td><td><b> Name</b></td> <td><b> Desig</b></td> <td><b> NIC</b></td><td><b>B'day </b></td><td><b>Address1 </b></td><td><b>Address2 </b></td><td><b>Mobile1 </b></td><td><b>Mobile2 </b></td><td><b>Work Ph. </b></td><td><b>Home Ph. </b></td><td><b>Mail1 </b></td><td><b>Mail2 </b></td> <td><b>FB </b></td><td><b>Google </b></td><td><b>Skype </b></td><td><b>Description </b></td><td><b>Joined Date </b></td><td><b>Delete </b></td><td><b>Edit </b></td><td><b>Make.App. </b></td></tr>";
while($row = mysql_fetch_array( $result ))
{
echo "<form name='form". $rowcount ."' action='deleteEditmakeApp.php' method='post'>";
if ($rowcount%2==1)
{
$rowcol ="#c7ebfa";
}
else if($rowcount%2==0)
{
$rowcol ="#86d7f9";
}
echo "<tr bgcolor='".$rowcol."'>";
echo "<td>";
echo "<input type='text' name='cusID' size='3px' value='".$row['CusID']."'>";
echo "</td>";
echo "<td>";
echo $row['title'];
echo "</td>";
echo "<td>";
echo $row['initial'];
echo "</td>";
echo "<td>";
echo $row['name'];
echo "</td>";
echo "<td>";
echo $row['desig'];
echo "</td>";
echo "<td>";
echo $row['NIC'];
echo "</td>";
echo "<td>";
echo $row['date']."/".$row['month']."/".$row['year'];
echo "</td>";
echo "<td>";
echo $row['address1'];
echo "</td>";
echo "<td>";
echo $row['address2'];
echo "</td>";
echo "<td>";
echo $row['mobil1'];
echo "</td>";
echo "<td>";
echo $row['mobile2'];
echo "</td>";
echo "<td>";
echo $row['work'];
echo "</td>";
echo "<td>";
echo $row['home'];
echo "</td>";
echo "<td>";
echo $row['email1'];
echo "</td>";
echo "<td>";
echo $row['email2'];
echo "</td>";
echo "<td>";
echo $row['fb'];
echo "</td>";
echo "<td>";
echo $row['gt'];
echo "</td>";
echo "<td>";
echo $row['skp'];
echo "</td>";
echo "<td>";
echo $row['other'];
echo "</td>";
echo "<td>";
echo $row['currDate'];
echo "</td>";
echo "<td>";
echo "<input type='submit' value='delete' name='delete'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' value='edit' name='edit'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' value='Make.app.' name='makeapp'>";
echo "</td>";
echo "</tr></form>";
$rowcount = $rowcount +1;
}
echo "</table ></center>";
//echo "<hr/>";
}
//function select all
function GetAllData()
{
$rowcount =0;
$sql = "Select * from customer " ;
$result = mysql_query($sql,$this->con2);
// echo "<hr/>";
echo "<center><table width='800' cellpadding='5' border =0>";
echo "<tr bgcolor='#02a7ec' > <td><b>CusID</b></td><td><b>Title</b> </td><td><b>Initial </b></td><td><b> Name</b></td><td><b> Desig</b></td> <td><b> NIC</b></td> <td><b>B'day </b></td><td><b>Address1 </b></td><td><b>Address2 </b></td><td><b>Mobile1 </b></td><td><b>Mobile2 </b></td><td><b>Work Ph. </b></td><td><b>Home Ph. </b></td><td><b>Mail1 </b></td><td><b>Mail2 </b></td> <td><b>FB </b></td><td><b>Google </b></td><td><b>Skype </b></td><td><b>Description </b></td><td><b>Joined Date </b></td><td><b>Delete </b></td><td><b>Edit </b></td><td><b>Make.App. </b></td></tr>";
while($row = mysql_fetch_array( $result ))
{
echo "<form name='form". $rowcount ."' action='deleteEditmakeApp.php' method='post'>";
if ($rowcount%2==1)
{
$rowcol ="#c7ebfa";
}
else if($rowcount%2==0)
{
$rowcol ="#86d7f9";
}
echo "<tr bgcolor='".$rowcol."'>";
echo "<td>";
echo "<input type='text' name='cusID' size='3px' value='".$row['CusID']."'>";
echo "</td>";
echo "<td>";
echo $row['title'];
echo "</td>";
echo "<td>";
echo $row['initial'];
echo "</td>";
echo "<td>";
echo $row['name'];
echo "</td>";
echo "<td>";
echo $row['desig'];
echo "</td>";
echo "<td>";
echo $row['NIC'];
echo "</td>";
echo "<td>";
echo $row['date']."/".$row['month']."/".$row['year'];
echo "</td>";
echo "<td>";
echo $row['address1'];
echo "</td>";
echo "<td>";
echo $row['address2'];
echo "</td>";
echo "<td>";
echo $row['mobil1'];
echo "</td>";
echo "<td>";
echo $row['mobile2'];
echo "</td>";
echo "<td>";
echo $row['work'];
echo "</td>";
echo "<td>";
echo $row['home'];
echo "</td>";
echo "<td>";
echo $row['email1'];
echo "</td>";
echo "<td>";
echo $row['email2'];
echo "</td>";
echo "<td>";
echo $row['fb'];
echo "</td>";
echo "<td>";
echo $row['gt'];
echo "</td>";
echo "<td>";
echo $row['skp'];
echo "</td>";
echo "<td>";
echo $row['other'];
echo "</td>";
echo "<td>";
echo $row['currDate'];
echo "</td>";
echo "<td>";
echo "<input type='submit' value='delete' name='delete'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' value='edit' name='edit'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' value='Make.app.' name='makeapp'>";
echo "</td>";
echo "</tr></form>";
$rowcount = $rowcount +1;
}
echo "</table ></center>";
//echo "<hr/>";
}
function GetAllNotAppointed()
{
$rowcount =0;
$sql = "SELECT * FROM customer LEFT JOIN appoinments ON customer.cusID=appoinments.CusID where plan IS NULL;";
$result = mysql_query($sql,$this->con2);
// echo "<hr/>";
echo "<center><table width='800' cellpadding='5' border =0>";
echo "<tr bgcolor='#02a7ec' > <td><b>CusID</b></td><td><b>Title</b> </td><td><b>Initial </b></td><td><b> Name</b></td><td><b> Desig</b></td> <td><b> NIC</b></td> <td><b>B'day </b></td><td><b>Address1 </b></td><td><b>Address2 </b></td><td><b>Mobile1 </b></td><td><b>Mobile2 </b></td><td><b>Work Ph. </b></td><td><b>Home Ph. </b></td><td><b>Mail1 </b></td><td><b>Mail2 </b></td> <td><b>FB </b></td><td><b>Google </b></td><td><b>Skype </b></td><td><b>Description </b></td><td><b>Joined Date </b></td><td><b>Delete </b></td><td><b>Edit </b></td><td><b>Make.App. </b></td></tr>";
while($row = mysql_fetch_array( $result ))
{
echo "<form name='form". $rowcount ."' action='deleteEditmakeApp.php' method='post'>";
if ($rowcount%2==1)
{
$rowcol ="#c7ebfa";
}
else if($rowcount%2==0)
{
$rowcol ="#86d7f9";
}
echo "<tr bgcolor='".$rowcol."'>";
echo "<td>";
echo "<input type='text' name='cusID' size='3px' value='".$row['CusID']."'>";
echo "</td>";
echo "<td>";
echo $row['title'];
echo "</td>";
echo "<td>";
echo $row['initial'];
echo "</td>";
echo "<td>";
echo $row['name'];
echo "</td>";
echo "<td>";
echo $row['desig'];
echo "</td>";
echo "<td>";
echo $row['NIC'];
echo "</td>";
echo "<td>";
echo $row['date']."/".$row['month']."/".$row['year'];
echo "</td>";
echo "<td>";
echo $row['address1'];
echo "</td>";
echo "<td>";
echo $row['address2'];
echo "</td>";
echo "<td>";
echo $row['mobil1'];
echo "</td>";
echo "<td>";
echo $row['mobile2'];
echo "</td>";
echo "<td>";
echo $row['work'];
echo "</td>";
echo "<td>";
echo $row['home'];
echo "</td>";
echo "<td>";
echo $row['email1'];
echo "</td>";
echo "<td>";
echo $row['email2'];
echo "</td>";
echo "<td>";
echo $row['fb'];
echo "</td>";
echo "<td>";
echo $row['gt'];
echo "</td>";
echo "<td>";
echo $row['skp'];
echo "</td>";
echo "<td>";
echo $row['other'];
echo "</td>";
echo "<td>";
echo $row['currDate'];
echo "</td>";
echo "<td>";
echo "<input type='submit' value='delete' name='delete'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' value='edit' name='edit'>";
echo "</td>";
echo "<td>";
echo "<input type='submit' value='Make.app.' name='makeapp'>";
echo "</td>";
echo "</tr></form>";
$rowcount = $rowcount +1;
}
echo "</table ></center>";
//echo "<hr/>";
//function for update
function EditCustomer($CusID,$title,$NIC,$name,$initial,$date,$month,$year,$mobil1,$mobile2,$work,$home,$email1,$email2,$fb,$gt,$skp,$other,$address1,$address2)
{
$sql = "Update customer set title='".$title."' , NIC='".$NIC."' , name='".$name."' , initial='".$initial."' , date='".$date."' , month='".$month."', address1='".$address1."', address2='".$address2."' , year='".$year."' , mobil1='".$mobil1."' , mobile2='".$mobile2."' , work='".$work."' , email1='".$email1."' , email2='".$email2."', home='".$home."', fb='".$fb."' , gt='".$gt."' , skp='".$skp."' , other='".$other."' where CusID='".$CusID ."'";
mysql_query($sql,$this->con2);
}
//select with a condition
function DeleteCustomer($cusID)
{
$sql = "Delete from customer where cusID=".$cusID ."";
mysql_query($sql,$this->con2);
//echo "SID = ".$Sid." was deleted ";
}
function DeleteAppoinment($appID)
{
$sql = "Delete from appoinments where appID=".$appID ."";
mysql_query($sql,$this->con2);
//echo "SID = ".$Sid." was deleted ";
}
//function for delete all data from a given table
function TRUNCATE($Tname)
{
$sql="TRUNCATE TABLE ".$Tname;
mysql_query($sql);
}
//function close the connection
function closeDB()
{
mysql_close($this->con2);
}
//customer report
function customerReport($date1,$date2)
{
$rowcount =0;
$cusCount =0;
$sql = "Select * from customer WHERE `currDate` BETWEEN '".$date1."' AND '".$date2."' " ;
$result = mysql_query($sql,$this->con2);
// echo "<hr/>";
echo "<center><table width='800' cellpadding='5' border =0>";
echo "<tr bgcolor='#02a7ec' > <td><b>CusID</b></td><td><b> Name</b></td> <td><b>Address1 </b></td><td><b>Mobile1 </b></td><td><b>Mail1 </b></td><td><b>Joined Date </b></td></tr>";
while($row = mysql_fetch_array( $result ))
{
echo "<form name='form". $rowcount ."' action='deleteEditmakeApp.php' method='post'>";
if ($rowcount%2==1)
{
$rowcol ="#c7ebfa";
}
else if($rowcount%2==0)
{
$rowcol ="#86d7f9";
}
echo "<tr bgcolor='".$rowcol."'>";
echo "<td>";
echo "<input type='text' name='cusID' size='3px' value='".$row['CusID']."'>";
echo "</td>";
echo "<td>";
echo $row['name'];
echo "</td>";
echo "<td>";
echo $row['address1'];
echo "</td>";
echo "<td>";
echo $row['mobil1'];
echo "</td>";
echo "<td>";
echo $row['email1'];
echo "</td>";
echo "<td>";
echo $row['currDate'];
echo "</td>";
echo "</tr></form>";
$rowcount = $rowcount +1;
$cusCount = $cusCount +1;
}
echo "</table > </center>";
echo "<hr/>";
echo "<table width='400px' celpadding='10px ' style='font-size:20px; '>";
echo " <tr><td>Customer Count : </td> <td>" . $cusCount . "</td> </tr>";
echo "</table> <hr/>";
}
}
?>
You can Use your functions when you needed in other class as follows
<?php
include('connection.php');
$db1 = new connect();
//call the object
$db1->connectDB();
$db1->selectDB();
$db1->closeDB();
?>
Solution for the “Denied access” problem when using Wamp server 2 on Windows 8
Object oriented concepts with PHP
About Author
Rangana Dissanayake
Working as a lead consultant(software) at well reputed IT company in Sri Lanka, I have been involved in UID, UXD, graphic design, website design & development, eCommerce and WordPress for over 10 years. I'm proficient with React JS, Angular, Node JS, Java Script, HTML5, CSS3, Responsive Web Design, Adobe CC and PHP. I also specialize in all forms of digital marketing including SEO, AdSense, Affiliate marketing and blogging.