Ex[1].3 php db connectivity

Ex. No.3 DATABASE CONNECTIVITY - PHP
28.01.2011

AIM:
To write PHP script for connecting to database and perform insert, update, delete and select
operations.

DESCRIPTION:
Create a Connection to a MySQL Database:
The mysql_connect() function is used to establish a connection to the database. The syntax
is:
mysql_connect(servername,username,password);
where
servername Optional. Specifies the server to connect to. Default value is "localhost:3306"
username Optional. Specifies the username to log in with. Default value is the name of
the user that owns the server process
password Optional. Specifies the password to log in with. Default is ""

Create a database:
A database holds one or multiple tables. The CREATE DATABASE statement is used to
create a database in MySQL. The syntax is:
CREATE DATABASE database_name

Create a Table:
The CREATE TABLE statement is used to create a table in MySQL. The syntax is:
CREATE TABLE table_name
(
column_name1 data_type,
....
)

Insert Data Into a Database Table:
The INSERT INTO statement is used to insert new records in a table. It is possible to write
the INSERT INTO statement in two forms:
INSERT INTO table_name VALUES (value1, value2, value3,...)
(or)
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Update Data In a Database:
The UPDATE statement is used to update existing records in a table. The syntax is:
UPDATE table_name SET column1=value, column2=value2,...
WHERE some_column=some_value

Delete Data In a Database:
The DELETE FROM statement is used to delete records from a database table. The syntax is:
DELETE FROM table_name WHERE some_column = some_value

Select Data From a Database Table:
The SELECT statement is used to select data from a database. The syntax is:
SELECT column_name(s) FROM table_name

SOURCE CODE:

insertform.html:
<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css"/>
</head>
<body>
<pre>
<h3>STUDENT INFORMATION SYSTEM</h3>
<form name="myForm" action="insert.php" method="post">
<table>
<tr><td>Name</td><td><input type="text" name="name"></td></tr>
<tr><td>Reg. No.</td><td><input type="text" name="reg"></td></tr>
<tr><td>Age</td><td><input type=text name="age" size=2/></td></tr>
<tr><td>Gender</td><td><input type="text" name="gender"</td></tr>
<tr><td>Email</td><td><input type=text name="email"/></td></tr>
<tr><td><b>Academic Details</b></td></tr>
<tr><td>Semester 1 GPA</td><td><input type=text name="sem1"></td></tr>
<tr><td>Semester 2 GPA</td><td><input type=text name="sem2"></td></tr>
<tr><td>CGPA</td><td><input type=text name="cgpa"></td></tr></table>
<center><input type="submit" value="Submit"/> <input type="reset"
value="Clear"/>
</form>
</body>
</html>

insert.php:
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("student", $con);
$sql= "insert into stud (Name,Regno,Age,Gender,Email,Sem1,Sem2,Cgpa)
values('$_POST[name]',$_POST[reg],$_POST[age],'$_POST[gender]',
'$_POST[email]',$_POST[sem1],$_POST[sem2],$_POST[cgpa])";;
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($con)
?>

updateform.html:
<html>
<head>
</head>
<body>
<pre>
<form name="myForm" action="update.php" method="post">
<table>
<tr><td>Field to update</td><td><input type="text" name="field"></td></tr>
<tr><td>Value</td><td><input type=text name="new"/></td></tr>
<tr><td>where</td></tr>
<tr><td>Field name</td><td><input type=text name="where"/></td></tr>
<tr><td>value equals</td></tr>
<tr><td>Value</td><td><input type="text" name="old"></td></tr>
</table>
<center><input type="submit" value="Update"/> <input type="reset"
value="Clear"/>
</form>
</body>
</html>

update.php:
<?php
if (!$con)
{
}
$sql="UPDATE stud SET $_POST[field] = '$_POST[new]' WHERE $_POST[where]=
'$_POST[old]' ";
{
}
echo "1 record updated";
mysql_close($con);
?>

deleteform.html:
<html>
<head>
</head>
<body>
<pre>
<form name="myForm" action="delete.php" method="post">
<table>
<tr><td>Name</td><td><input type="text" name="name"></td></tr>
<tr><td>Regno</td><td><input type=text name="reg"/></td></tr>
</table>
<center><input type="submit" value="Delete"/> <input type="reset"
value="Clear"/>
</form>
</body>
</html>

delete.php:
<?php
if (!$con)
{
}
$sql="DELETE FROM stud WHERE Name='$_POST[name]' and Regno=$_POST[reg]";
{
}
echo "1 record deleted";
mysql_close($con);
?>

select.php:
<?php
if (!$con)
{
}
$result = mysql_query("SELECT * FROM stud");
echo "<center>";
echo "<table border=1>";
echo "<tr><th>Name</th><th>Regno</th><th>CGPA</th></tr>";
echo "<br/> <br/>";
while($row = mysql_fetch_array($result))
{
echo "<tr> <td>".$row['Name']. "</td><td>".$row['Regno']. "</td><td>".
$row['Cgpa']. "</td></tr>";
echo "<br />";
}
echo "</table>";
echo "</center>";
mysql_close($con);
?>

operation.html:
<html>
<body bgcolor="pink">
<b>
Select operation.</b><br><br>
<a href="select.php" target="main">Select</a><br>
<a href="insert form.html" target="main">Insert</a><br>
<a href="update form.html" target="main">Update</a><br>
<a href="delete form.html" target="main">Delete</a><br>
</body>
</html>

frame.html:
<html>
<frameset cols="20,80">
<frame src="operation.html" name="op">
<frame src="select1.php" name="main">
</frameset>
</html>

OUTPUT:

Select:

Delete:

RESULT:
Thus PHP scripts are executed to perform insert, update, delete and select operations in a
database.

Ex[1].3 php db connectivity

More Related Content

What's hot

Viewers also liked

Similar to Ex[1].3 php db connectivity

Ex[1].3 php db connectivity