Connecting PHP with MySQL
Content
* Connectivity
* Selecting Database
* Insert, Update and Delete
* Select data from database
* Sample Application
Connectivity
* PHP provides several predefined functions to access MySQL, but before accessing MySQL, application need to connect MySQL as a user (similar to login from MySQL client)
* To establish connection between PHP and MySQL, PHP provides fine tuned methods like mysqli_connect().
mysqli_connect()
* Following is the format of mysqli_connection function
mysqli_connect(host,username,password,dbname);
* host: host ip address of database server, in our case it is localhost
* username: Name of the MySQL user
* password: Password of the user
* dbname: Database name (optional)
Example
<?php
$con=mysqli_connect('localhost','root');
if(!$con)
echo "Connection failed";
else
echo "Connection done";
?>
mysqli_close()
* This method is used to disconnect PHP with MySQL, similar to logout.
mysqli_close($con);
Example
<?php
$con=mysqli_connect('localhost','root');
if(!$con)
echo "Connection failed";
else
echo "Connection done";
/* do needful database operations here */
mysqli_close($con);
?>
Selecting Database
* We can select database after getting connection, using mysqli_select_db() method
mysqli_select_db($con,$databsename);
Insert, Update and Delete
* We can insert, update or delete data using mysqli_query() method.
mysqli_query($con,$query);
* $query is query string, it can be any insert, update or delete sql statement.
Select data
* Selecting data is somewhat requires more handling, as the result of execution of select query, we may get several rows of a table.
$result = mysqli_query($con,$query);
* mysqli_query() method returns extracted data as two dimensional associative array.
$row=mysqli_fetch_array($result);
* We can fetch first row as an array of values using method mysqli_fetch_array()
* you can also count number of rows using mysqli_num_rows() method
$num=mysqli_num_rows($result);
Sample Application
* Application gives interfaces to the user to manage book records. The following functionalities must be provided:
- Insertion of new book record
- Deletion of record
- Updation of record
- View all records
- Search book records
* First of all create a database 'library'.
* Create a table 'book' in the 'library' database.
* Columns:
- bookid int(5) AUTO_INCREMENT PK
- title varchar(255)
- price float(6,2)
* Define following php files:
- home.php
- insert.php
- delete.php
- update.php
- bookfun.php
home.php
<?php include('bookfun.php'); ?>
<html>
<head>
<title> Book Store Management</title>
<style type="text/css">
#buttons
{
margin-left:50px;
width:50%;
border:2px solid silver;
}
#activityarea
{
margin-left:50px;
width:50%;
border:2px solid silver;
height:50%;
}
table {
position:relative;
left:25%;
width:300px;
}
#insertform { display:none;}
#back {display:none;}
#view,#edit,#delete {display:none;}
#view table{ border:2px solid navy; }
#view table th {background-color:orange;}
#view table td {background-color:khaki;}
</style>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$("button").click(function(){
if($(this).html()=="Insert")
{
$("#insertform").show();
$(".b1").hide();
$("#back").show();
}
if($(this).html()=="View")
{
$("#view").show();
$(".b1").hide();
$("#back").show();
}
if($(this).html()=="Edit")
{
$("#edit").show();
$(".b1").hide();
$("#back").show();
}
if($(this).html()=="Delete")
{
$("#delete").show();
$(".b1").hide();
$("#back").show();
}
});
$("#back").click(function(){
$("#insertform").hide();
$("#view").hide();
$("#edit").hide();
$("#delete").hide();
$(".b1").show();
$("#back").hide();
});
});
</script>
</head>
<body>
<h1> Book Store Management</h1>
<div id="buttons">
<button class="b1">Insert</button>
<button class="b1">View</button>
<button class="b1">Edit</button>
<button class="b1">Delete</button>
<button id="back">Back</button>
</div>
<div id="activityarea">
<form id="insertform" action="insert.php" method="post">
<table>
<tr>
<td>Book Title</td>
<td><input type="text" name="title"/></td>
</tr>
<tr>
<td>Price</td>
<td><input type="text" name="price"/></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="store"/></td>
</tr>
</table>
</form>
<div id="view" > <?php viewbook(); ?></div>
<div id="edit" > <?php editbook(); ?></div>
<div id="delete" > <?php deletebook(); ?></div>
</div>
</body>
</html>
insert.php
<?php
$title=$_POST['title'];
$price=$_POST['price'];
$con=mysql_connect('localhost','root');
if(!$con)
echo "Could not connect";
mysql_select_db("lms",$con);
$q="insert into book (title,price) values ('$title',$price)";
mysql_query($q);
mysql_close($con);
header('location:http://localhost/ex2/home.php');
?>
update.php
<?php
/* extracting HTML form data */
$i=1;
while(isset($_POST["title".$i]))
{
$bookid[$i]=$_POST["bookid".$i];
$title[$i]=$_POST["title".$i];
$price[$i]=$_POST["price".$i];
$i++;
}
$length=$i-1;
/* Establishing Connection */
$con=mysql_connect('localhost','root');
if(!$con)
echo "Could not connect";
mysql_select_db("lms",$con);
for($i=1;$i<=$length;$i++)
{
$q="update book set title='"."$title[$i]"."' ,price=$price[$i] where bookid=$bookid[$i]";
mysql_query($q);
}
mysql_close($con);
header('location:http://localhost/ex2/home.php');
?>
delete.php
<?php
/* extracting HTML form data */
$i=1;
$j=1;
while(isset($_POST["title".$i]))
{
if(isset($_POST["cb".$i]))
{
$bookid[$j]=$_POST["bookid".$i];
$title[$j]=$_POST["title".$i];
$price[$j]=$_POST["price".$i];
$j++;
}
$i++;
}
$length=$j-1;
/* Establishing Connection */
$con=mysql_connect('localhost','root');
if(!$con)
echo "Could not connect";
mysql_select_db("lms",$con);
for($j=1;$j<=$length;$j++)
{
$q="delete from book where bookid=$bookid[$j]";
$s= mysql_query($q);
}
mysql_close($con);
header('location:http://localhost/ex2/home.php');
?>
bookfun.php
<?php
/* Function 1*/
function viewbook()
{
$con=mysql_connect('localhost','root');
if(!$con)
echo "Could not connect";
mysql_select_db("lms",$con);
$q="select * from book";
$result=mysql_query($q);
?>
<table>
<tr><th>Title</th><th>Price</th></tr>
<?php
while($row=mysql_fetch_array($result))
{
echo "<tr><td>".$row['title']."</td><td>".$row['price']."</td></tr>";
}
echo "</table>";
}
?>
<?php
/* Function2*/
function editbook()
{
$con=mysql_connect('localhost','root');
if(!$con)
echo "Could not connect";
mysql_select_db("lms",$con);
$q="select * from book";
$result=mysql_query($q);
?>
<form action="update.php" method="post">
<table>
<tr><th></th><th>Title</th><th>Price</th></tr>
<?php
$i=0;
while($row=mysql_fetch_array($result))
{
$i++;
echo "<tr><td><input type='hidden' value='".$row['bookid']."' name='bookid".$i."' /></td>";
echo "<td><input type='text' value='".$row['title']."' name='title".$i."'/></td><td><input type='text' value='".$row['price']."' name='price".$i."' /></td></tr>";
}
?>
<tr><td></td><td></td><td><input type="submit" value="save" /></td></tr>
<?php
echo "</table></form>";
}
?>
<?php
/* function 3 */
function deletebook()
{
$con=mysql_connect('localhost','root');
if(!$con)
echo "Could not connect";
mysql_select_db("lms",$con);
$q="select * from book";
$result=mysql_query($q);
?>
<form action="delete.php" method="post">
<table>
<tr><th></th><th>Title</th><th>Price</th></tr>
<?php
$i=0;
while($row=mysql_fetch_array($result))
{
$i++;
echo "<tr><td><input type='checkbox' name='cb".$i."' /><input type='hidden' value='".$row['bookid']."' name='bookid".$i."' /></td>";
echo "<td><input type='text' value='".$row['title']."' name='title".$i."'/></td><td><input type='text' value='".$row['price']."' name='price".$i."' /></td></tr>";
}
?>
<tr><td></td><td></td><td><input type="submit" value="Delete" /></td></tr>
<?php
echo "</table></form>";
}
?>
* Do not forget to include jquery.js file
<<Prev Home Next>>
