How to insert data into MySQL using PHP (Ajax)

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.

project_structure

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

main_ui

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>&nbsp;</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.

process

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 

How to insert data to MySQL using PHP

Hi friends.. Today i am going to tell you about how to insert data available in HTML from using PHP in to MySQL database. Here i will use post method for transfer data using HTML form to my PHP script. Lets see here how to create form without having table. I mean you can create a HTML form and you want to align it in proper manner. In here you can see inside my css code i have aligned it without using table .OK  here is my code for create HTML form

Below you can seemy css code for displaying the proper out put like this

<!--
To change this template, choose Tools | Templates
and open the template in the editor.
-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
      <style type="text/css">

            body {background-color:black;}

            #main-body  {width: 350px;
                        height: 250px;
                        background-color: cornflowerblue;
                        border: 6px brown dashed;
                        margin-left: 300px;
                        margin-top: 200px;
                        padding: 10px;
                        }
           #main-body-inner

                        {

                        width: 340px;
                        height: 240px;

                        background-color: white;
                        margin-top:5px;
                        margin-left: 5px;

                        }

          #myform   {padding: 30px;}

          #myform label,input{ display: block;
                                width: 120px;
                                float: left;
                                margin-bottom: 10px;

                         }
          #myform label{

                       text-align: right;
                        padding-right: 20px;
                }

                br
                    {
                        clear: left;
                    }

           #submit  {width: 75px; float: left; margin-left: 175px;}

      </style>
    <title>INSERT DATA USING PHP TO MYSQL DATABASE</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
      <div id="main-body">

          <div id="main-body-inner">

              <form id="myform" name="myform" action="insert.php" method="post">
                  <label for="first-name">First Name</label>
                  <input type="text" id="fname" name="fname" /> <br />
                  <label for="last-name">Last Name</label>
                  <input type="text" id="lname" name="lname" /><br />
                  <label for="age">Age</label>
                  <input type="text" id="age" name="age" /><br />

                  <input type="submit" id="submit" value="Submit" />

              </form>
            </div>
      </div>
  </body>
</html>

Now we have created our html form and we have styled it. Below you can see my PHP code.

<?php
/*
 * author :damith.uwu@gmail.com
 *
 *
 *
 */
include 'connection.php';

    if(isset ($_POST['fname']) && isset ($_POST[ 'lname']) && isset ($_POST[ 'age'])) // getting the values from the post
    {

    $connection = new createConnection(); // creating connection

    $connect = $connection->connectToDatabase(); // connect to database

    $connection->selectDatabase(); // select database

    $fname = mysql_real_escape_string($_POST['fname']); // escape sql injection
    $lname =mysql_real_escape_string($_POST['lname']);
    $age = mysql_real_escape_string($_POST['age']);

    $query="insert into test  values ('$fname','$lname','$age')"; // query for insert data

    mysql_query($query); // execute the insert query

   echo ("User registration Successfull.."); 

   echo "<a href='index.html'><h1>Go Back </h1> </a>"; // link for go back

   $connection->closeConnection(); // closing the connection
    }

    else
    {
                echo "Error occured!"; // display error message
    }
?>

In here i have used a connection class. Below you can see my connection class.

<?php

/**
 * @author damith
 * @copyright 2011
 */

class createConnection
{
    var $host="localhost";
    var $username="username here";
    Var $password=" password here";
    var $database="databse name";
    var $myconn;

    function connectToDatabase()
    {

        $conn= mysql_connect($this->host,$this->username,$this->password);

        if(!$conn)
        {
            die ("Cannot connect to the database");
        }

        else
        {

            $this->myconn = $conn;

        }

        return $this->myconn;

    }

    function selectDatabase()
    {
        mysql_select_db($this->database);

        if(mysql_error())
        {

            echo "Cannot find the database ".$this->database;

        }

    }

    function closeConnection()
    {
        mysql_close($this->myconn);

    }

}

?>

When you completed the insert process you can see the message like this.

OK you done.. Download the full source from here

Download this video
Download Source Code

How to Print HTML Table Using PHP

Today i am going to say you how to print HTML table using PHP. Important of this method is if you are trying to display results retried from the database you can easily display it by using PHP ‘s  echo function.Lets we see how to do it.

This is how the out put will be on your web browser

Code.

<?php

        echo "<table border='4' class='stats' cellspacing='0'>

            <tr>
            <td class='hed' colspan='8'>WELCOME TO HTML TABLES</td>
              </tr>
            <tr>
            <th>ID</th>
            <th>FIRST NAME</th>
            <th>LAST NAME</th>
            <th>ADDRESS</th>
            <th>TELEPHONE</th>

            </tr>";

              echo "<tr>";
              echo "<td>" . "001" . "</td>";
              echo "<td>" . "Damith" . "</td>";
              echo "<td>" . "Wanninayake" . "</td>";
              echo "<td>" . "Uva wellassa University" . "</td>";
              echo "<td>" . "1234567890" . "</td>";

              echo "</tr>";

              echo "<tr>";
              echo "<td>" . "001" . "</td>";
              echo "<td>" . "Damith" . "</td>";
              echo "<td>" . "Wanninayake" . "</td>";
              echo "<td>" . "Uva wellassa University" . "</td>";
              echo "<td>" . "1234567890" . "</td>";

              echo "</tr>";

              echo "<tr>";
              echo "<td>" . "001" . "</td>";
              echo "<td>" . "Damith" . "</td>";
              echo "<td>" . "Wanninayake" . "</td>";
              echo "<td>" . "Uva wellassa University" . "</td>";
              echo "<td>" . "1234567890" . "</td>";

              echo "</tr>";

    echo "</table>";

?>

The above code is out put the so called table using PHP. Try this on your database projects.

How to write a Connection class to MySQL database using PHP

Hi friends to day i am going to tell you about something about PHP and MySQL. We all know that PHP is a one of best scripting language for web development.We can build awesome dynamic web pages using PHP.But we must build something valuable other than produce unimportant web sites. So today i am going to tel you how to write and test the OOP based connection class to MySQL database using PHP.

<?php

/**
 * @author damith
 * @copyright 2011
 */

class createConnection //create a class for make connection
{
    var $host="localhost";
    var $username="username";    // specify the sever details for mysql
    Var $password="password";
    var $database="database name";
    var $myconn;

    function connectToDatabase() // create a function for connect database
    {

        $conn= mysql_connect($this->host,$this->username,$this->password);

        if(!$conn)// testing the connection
        {
            die ("Cannot connect to the database");
        }

        else
        {

            $this->myconn = $conn;

            echo "Connection established";

        }

        return $this->myconn;

    }

    function selectDatabase() // selecting the database.
    {
        mysql_select_db($this->database);  //use php inbuild functions for select database

        if(mysql_error()) // if error occured display the error message
        {

            echo "Cannot find the database ".$this->database;

        }
         echo "Database selected..";       
    }

    function closeConnection() // close the connection
    {
        mysql_close($this->myconn);

        echo "Connection closed";
    }

}

?>

OK.. Now you have create the connection class and now you can call this class inside your project. To test the connection i will get another PHP file and create a object from my existing  connection class.. see below code how i am going to do it..

<?php

    include ('connection.php');

    $connection = new createConnection(); //i created a new object

    $connection->connectToDatabase(); // connected to the database

    echo "<br />"; // putting a html break

    $connection->selectDatabase();// closed connection

    echo "<br />";

    $connection->closeConnection();
?>

I think you know how to test this.Start wamp server and goto your local host’s folder and test it.. If you success you can see the below message on a browser

Now you have connected to the MySQL friends.. see you in next post