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(); ?>