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;
}