Here i have provided a simple example for beginners to understand how to insert values into MySQL database using PHP. Here i have used NetBeans IDE for develop my simple application. Project structure looks like below.
In here i have categorized my modules display as below and
1) Data Access Layer used to access the data base
2) Common layer for keep commonly execute methods
3) DB to store connection class and any other related classes
4) JS for keep java script files
I am going to store employee data in a MySQL database and i will use ajax to send data into database without refreshing the page and get response message and display in a label. My user interface looks like below
Here i am going to explain important parts of the source code and you can download the project and view the source later. Here is my employee class.
<?php
/**
-- Author : damith
-- Date :March-16-2013
*/
set_include_path(dirname(__FILE__)."/../");
require 'db/Connection.php';
require_once 'common/Messages.php';
$connection = new createConnection();
$response = array();
class EmployeeDetals
{
/**
*
* @global createConnection $connection
* @param type $firstName
* @param type $lastName
* @param type $age
* @return type
*/
public function addNewEmployee($firstName,$lastName,$age)
{
global $connection;
$connection->connectToDatabase();
$connection->selectDatabase();
try {
$insertQuery = "INSERT INTO emp_personal_details(emp_personal_details.First_Name,emp_personal_details.Last_Name,emp_personal_details.Age) VALUES ('$firstName','$lastName','$age')";
$availableQuery ="SELECT * FROM emp_personal_details e WHERE e.First_Name='$firstName' AND e.Last_Name='$lastName' AND e.Age='$age'";
$availableResult= mysql_query($availableQuery);
if(mysql_num_rows($availableResult)>0){ // checking wether records are already available inthe sysyem
$response[0] = $firstName.' alredy registered in the system';
$response[1]= Message::Warning;
}
else{
if (!mysql_query($insertQuery)){
$response[0]= 'Error occured while inserting data';
$response[1]= Message::Error;
$connection->closeConnection(); // closing the connection
}
else{
$response[0]= 'Successfully added to the database';
$response[1]= Message::Success;
$connection->closeConnection(); // closing the connection
}
}
return $response;
} catch (Exception $exc) {
$response[0]= $exc->getTraceAsString();
$response[1]= Message::Error;
$connection->closeConnection(); // closing the connection
return $response;
}
}
}
?>
And my user interface with JavaScript function looks like below
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script src="js/jquery-1.9.1.min.js" type="text/javascript"></script>
<title></title>
</head>
<body>
<form action="" name="frmAddEmp" id="frmAddEmp">
<table>
<tr>
<td>First Name</td>
<td><input type="text" name="txtFirstName" /> </td>
</tr>
<tr>
<td>Last Name</td>
<td><input type="text" name="txtLastName" /> </td>
</tr>
<tr>
<td>Age</td>
<td><input type="text" name="txtAge" /> </td>
</tr>
<tr>
<td> </td>
<td><input type="button" name="btnSubmit" value="Save" id="btnSubmit" /> </td>
</tr>
</table>
</form>
<div id="response">
</div>
</body>
<script type="text/javascript">
$("#btnSubmit").click(function() {
var url = "addEmployee.php"; // the script where you handle the form input.
$.ajax({
type: "POST",
url: url,
data: $("#frmAddEmp").serialize(), // serializes the form's elements.
success: function(data)
{
$('#response').empty();
$('#response').append(data); // show response from the php script.
}
});
return false; // avoid to execute the actual submit of the form.
});
</script>
</html>
and this is the page which call from the interface using ajax and retrieve the result.
<?php
/**
-- Author : damith
-- Date :March-16-2013
*/
set_include_path(dirname(__FILE__)."/../");
require 'DAL/EmployeeData.php';
$response = array();
$emp = new EmployeeDetals();
if(isset($_REQUEST['txtFirstName']) && isset($_REQUEST['txtLastName']) && isset($_REQUEST['txtAge'])){
$firstName = mysql_real_escape_string($_REQUEST['txtFirstName']);
$lastName = mysql_real_escape_string($_REQUEST['txtLastName']);
$age = mysql_real_escape_string($_REQUEST['txtAge']);
$response = $emp->addNewEmployee($firstName, $lastName,$age);
echo $response[0];
}
?>
If the user enter same values again application will prevent the save duplicate values. Overall process of the application has illustrated as below.
1) User Interface – This file has the user interface code of the application and also java script function which used to post the form data into second layer.
2) Second layer can be used to catch data sent from the User Interface and pass to the third layer.
3) Third layer or the DAL (Data Access Layer) is only the layer which contract with the connection class and the database. All the required queries are wrote in here.
4) Database and the connection class.
This is the basic way to develop application with well managed way. Hope it clear to you. You can download the complete project below with database script.
https://sites.google.com/site/uwudamith/documents/CURD.rar?attredirects=0&d=1
Damith's Blog 