PHP Data Insert and Select using OOP

In this tutorial i will show you that how to perform and use object oriented programming in PHP with MySql to Select and Insert Data. Using OOP(s) in PHP it's become so easy to manage and perform such operations like Insert and Select data from MySql tables, So let's have a look.

Download Script

Database Crediantials.

Database name : dbtuts
Table name : users
Table Columns : user_id , first_name , last_name , user_city

Copy-Paste the following sql schema in your MySql database to create database and table.


CREATE DATABASE `dbtuts` ;
CREATE TABLE `dbtuts`.`users` (
`user_id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 25 ) NOT NULL ,
`last_name` VARCHAR( 25 ) NOT NULL ,
`user_city` VARCHAR( 45 ) NOT NULL
) ENGINE = InnoDB;

Now we need to create dbMySql.php file which contains host connection , database selection and functions which are used to insert and select data from MySql users table.
dbMySql.php


<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'dbtuts');

class DB_con
{
 function __construct()
 {
  $conn = mysql_connect(DB_SERVER,DB_USER,DB_PASS) or die('localhost connection problem'.mysql_error());
  mysql_select_db(DB_NAME, $conn);
 }
 
 public function insert($fname,$lname,$city)
 {
  $res = mysql_query("INSERT users(first_name,last_name,user_city) VALUES('$fname','$lname','$city')");
  return $res;
 }
 
 public function select()
 {
  $res=mysql_query("SELECT * FROM users");
  return $res;
 }
}

?>

script explained.
class DB_con has constructor function which creates the localhost connection and database selection.
insert() function have some parameters like $fname , $lname and $city which accepts input values from html form.
select() function fetch all the data from users table.

Data Insert

To insert Data into mysql table we need to create html form containing all the fields the users table has. and HTML code of insert form will be as follows.


<html>
<head>
<body>
<form method="post">
    <table align="center">
    <tr>
    <td><input type="text" name="first_name" placeholder="First Name" /></td>
    </tr>
    <tr>
    <td><input type="text" name="last_name" placeholder="Last Name" /></td>
    </tr>
    <tr>
    <td><input type="text" name="city_name" placeholder="City" /></td>
    </tr>
    <tr>
    <td>
    <button type="submit" name="btn-save"><strong>SAVE</strong></button></td>
    </tr>
    </table>
</form>
</body>
</html>

Put the following PHP script just above <html> tag.


<?php
include_once 'dbMySql.php';
$con = new DB_con();

// data insert code starts here.
if(isset($_POST['btn-save']))
{
 $fname = $_POST['first_name'];
 $lname = $_POST['last_name'];
 $city = $_POST['city_name'];
 
 $con->insert($fname,$lname,$city);
 header("Location: index.php");
}
// data insert code ends here.

?>

script explained.
This script contains variables like $fname , $lname and $city .
By including the dbMySql.php file in this script we can access and use all the functions of it, by creating $con object.
$con->insert($fname,$lname,$city) inserts the value from html form into the users table.

Data Select

Next is how to select data from users table.
By using $con->select() function you can fetch data from users table like this.


<?php
include_once 'dbMySql.php';
$con = new DB_con();

$res=$con->select();

while($row=mysql_fetch_row($res))
{ 
        echo $row[1];
        echo $row[2];
        echo $row[3]; 
}
?>

script explained:
we included here dbMySql.php file in this script.
$con->select() function select all the rows from users table.

that's it

Complete Script with Design...

dbMySql.php


<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'dbtuts');

class DB_con
{
 function __construct()
 {
  $conn = mysql_connect(DB_SERVER,DB_USER,DB_PASS) or die('localhost connection problem'.mysql_error());
  mysql_select_db(DB_NAME, $conn);
 }
 
 public function insert($fname,$lname,$city)
 {
  $res = mysql_query("INSERT users(first_name,last_name,user_city) VALUES('$fname','$lname','$city')");
  return $res;
 }
 
 public function select()
 {
  $res=mysql_query("SELECT * FROM users");
  return $res;
 }
}
?>

index.php


<?php
include_once 'dbMySql.php';
$con = new DB_con();
$table = "users";
$res=$con->select($table);
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP Data Insert and Select Data Using OOP - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>PHP Data Insert and Select Data Using OOP - By Cleartuts</label>
    </div>
</div>
<div id="body">
 <div id="content">
    <table align="center">
    <tr>
    <th colspan="3"><a href="add_data.php">add data here...</a></th>
    </tr>
    <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>City</th>
    </tr>
    <?php
 while($row=mysql_fetch_row($res))
 {
   ?>
            <tr>
            <td><?php echo $row[1]; ?></td>
            <td><?php echo $row[2]; ?></td>
            <td><?php echo $row[3]; ?></td>
            </tr>
            <?php
 }
 ?>
    </table>
    </div>
</div>

<div id="footer">
 <div id="content">
    <hr /><br/>
    <label>for more tutorials and blog tips visit : <a href="http://cleartuts.blogspot.com">cleartuts.com</a></label>
    </div>
</div>

</center>
</body>
</html>

add_data.php
This script shows the values from tha users table.


<?php
include_once 'dbMySql.php';
$con = new DB_con();

// data insert code starts here.
if(isset($_POST['btn-save']))
{
 $fname = $_POST['first_name'];
 $lname = $_POST['last_name'];
 $city = $_POST['city_name'];
 
 $res=$con->insert($fname,$lname,$city);
 if($res)
 {
  ?>
  <script>
  alert('Record inserted...');
        window.location='index.php'
        </script>
  <?php
 }
 else
 {
  ?>
  <script>
  alert('error inserting record...');
        window.location='index.php'
        </script>
  <?php
 }
}
// data insert code ends here.

?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP Data Insert and Select Data Using OOP - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>PHP Data Insert and Select Data Using OOP - By Cleartuts</label>
    </div>
</div>
<div id="body">
 <div id="content">
    <form method="post">
    <table align="center">
    <tr>
    <td><input type="text" name="first_name" placeholder="First Name" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="last_name" placeholder="Last Name" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="city_name" placeholder="City" required /></td>
    </tr>
    <tr>
    <td>
    <button type="submit" name="btn-save"><strong>SAVE</strong></button></td>
    </tr>
    </table>
    </form>
    </div>
</div>

</center>
</body>
</html>

style.css
This stylesheet makes beautify all the pages.


@charset "utf-8";
/* CSS Document */

*
{
 margin:0;
 padding:0;
}
#header
{
 width:100%;
 height:50px;
 background:#00a2d1;
 color:#f9f9f9;
 font-family:"Lucida Sans Unicode", "Lucida Grande", sans-serif;
 font-size:35px;
 text-align:center;
}
#header a
{
 color:#fff;
 text-decoration:blink;
}
#body
{
 margin-top:50px;
}
table
{
 width:40%;
 font-family:Tahoma, Geneva, sans-serif;
 font-weight:bolder;
 color:#999;
 margin-bottom:80px;
}
table a
{
 text-decoration:none;
 color:#00a2d1;
}
table,td,th
{
 border-collapse:collapse;
 border:solid #d0d0d0 1px;
 padding:20px;
}
table td input
{
 width:97%;
 height:35px;
 border:dashed #00a2d1 1px;
 padding-left:15px;
 font-family:Verdana, Geneva, sans-serif;
 box-shadow:0px 0px 0px rgba(1,0,0,0.2);
 outline:none;
}
table td input:focus
{
 box-shadow:inset 1px 1px 1px rgba(1,0,0,0.2);
 outline:none;
}
table td button
{
 border:solid #f9f9f9 0px;
 box-shadow:1px 1px 1px rgba(1,0,0,0.2);
 outline:none;
 background:#00a2d1;
 padding:9px 15px 9px 15px;
 color:#f9f9f9;
 font-family:Arial, Helvetica, sans-serif;
 font-weight:bolder;
 border-radius:3px;
 width:100%;
}
table td button:active
{
 position:relative;
 top:1px;
}
#footer
{
 margin-top:50px;
 position:relative;
 bottom:30px;
 font-family:Verdana, Geneva, sans-serif;
}

Download Script

18 comments:

Anonymous4/02/2015 2:08 PM
nice tutorial.thanks
Anoop Singh5/14/2015 11:28 AM
The code is not work.Error is data not insert in the table and show the error in inserting.
Fully Help8/04/2015 4:53 PM
Nice blog...Very useful information is providing by ur blog. Great beginning php tutorials Very clear and helpful for beginners.
Robin khan10/20/2015 10:07 PM
thanks
New Internet Community11/19/2015 1:36 PM
Its too old . not complete oops . I am searching with PDO extension... any way great job.
Sailatha Kashamoni1/10/2016 3:48 PM
Class 'DB_con' not found in D:\wamp\www\dbtuts\index.php on line 3
i am getting this error
Bharat Koshti1/22/2016 4:30 PM
good tutorials....nice...
Shradha !!3/03/2016 4:08 PM
hello sir! i want to continue my project as when i login the page the home page should be a database where the person can add information,update,delete,retrieve
Kharen Umacam7/07/2016 12:12 PM
insert doesnt work.. waste of time
RecuperoDatiFAsTec7/24/2016 11:38 AM
Hello, this tutorial is "recent", Dec 2014.
You teach in a great way, but you forget to teach security as well.
Why don't you edit the post with use of PDO for the DB connection?
manni8/17/2016 2:25 PM
unable to download. Your code is not working
Reuben Murituh9/12/2016 3:24 PM
Hey there mr khodke, when downloading the codes, am being redirected to box where the files are not downloading, how can i solve this please?
Unknown12/29/2016 11:40 AM
when i run index.php then Fatal error: Class 'DB_con' not found in C:\wamp\www\Oops\index.php on line 3 occur
Rahul Sharma12/29/2016 11:41 AM
not working properly
tejas1/22/2017 1:59 PM
How to convert into mysqli
Ti Brown3/17/2017 1:52 AM
super helpful, thx!