Day 12: Handling Multiple Tables In PHP With MySql Join
Note : In this assignment, I am taking the same database (mydb) that we was created in Day9, but create below new tables for this assignment and add some records in all three table i.e. tbcity, tbcourse and tbstudent.
create table tbcity (id int primary key auto_increment, name varchar(50))
create table tbcourse(id int primary key auto_increment, name varchar(50))
create table tbstudent (id int primary key auto_increment, name varchar(50), fee int, address varchar(50), cityId int, coursetId int)
This assignment contains more than 1 files, I am taking Day12 to put all those below mentioned files.
Program 39: Handling more then 1 tables in PHP.
File 1. connect.php : Used to define connection and select database.
We will include this file where database connection will required, so after this we don’t have to write connection code again and again, and also we can easily change our server and database (if required) from one file only.
$con = mysql_connect(“localhost”,”root”,””);
mysql_select_db(“mydb”,$con);
?>
File 2. menu.php : Used for navigation.
<a href=”display.php”>Display</a>
<hr>
File 3. insert.php : Used to save the records in database.
require_once “connect.php”;
$msg = “”;
$cityQuery = “select * from tbcity”;
$cityData = mysql_query($cityQuery);
$courseQuery = “select * from tbcourse”;
$courseData = mysql_query($courseQuery);
if(isset($_REQUEST[‘btnSubmit’])) {
$name = $_REQUEST[‘txtName’];
$fee = $_REQUEST[‘txtFee’];
$address = $_REQUEST[‘txtAddress’];
$cityid = $_REQUEST[‘drpCity’];
$courseid = $_REQUEST[‘drpCourse’];
$query = “insert into tbstudent (name,fee,address,cityId,courseId) values (‘$name’,’$fee’,’$address’,’$cityid’,’$courseid’)”;
if(mysql_query($query)){
$msg = “Record Saved!”;
} else {
$msg = “Unable to Save!”;
}
}
?>
<html>
<?php include “menu.php”; ?>
<body>
<form method=”POST”>
Name : <input type=”text” name=”txtName”/> <br/>
Fee : <input type=”text” name=”txtFee”/> <br/>
Address : <textarea name=”txtAddress”></textarea<<br/>
City : <select id=”drpCity” name=”drpCity”>
<option value=””>Select</option>
<?php while($recCity = mysql_fetch_array($cityData)) { ?>
<option value=”<?php echo $recCity[‘id’];?>” > <?php echo $recCity[‘name’];? > </option>
<?php } ? >
</select> <br/>
Course : <select id=”drpCourse” name=”drpCourse”>
<option value=””>Select</option>
<?php while($recCourse = mysql_fetch_array($courseData)) { ? >
<option value=”<?php echo $recCourse[‘id’];?>”> <?php echo $recCourse[‘name’];? > </option>
<?php } ? >
</select> <br/>
<input type=”submit” name=”btnSubmit” value=”Save”/> <br/>
<?php echo $msg; ?>
</form>
</body>
</html>
File 4. display.php : Used to display the records from database.
Note : Query string is used to send “id” to file “edit.php” and “delete.php” to edit and delete the records. In query string we can send only string values to the next page.
require_once “connect.php”;
$query = “select tbstudent.name,tbstudent.fee,tbstudent.address,tbstudent.id as studentid,tbcity.name as city, tbcourse.name as course from tbstudent left join tbcity on tbstudent.cityid = tbcity.id left join tbcourse on tbstudent.courseid = tbcourse.id“;
$data = mysql_query($query);
?>
<html>
<?php include “menu.php”; ?>
<body>
<table border=”1″ cellpadding=”5″>
<tr>
<th>Name</th> <th>Fee</th> <th>address</th> <th>City</th> <th>Course</th> <th colspan=”2″>Action</th>
</tr>
<?php while($rec = mysql_fetch_array($data)) { ?>
<tr>
<td> <?php echo $rec[‘name’]; ?> </td>
<td> <?php echo $rec[‘fee’]; ?> </td>
<td> <?php echo $rec[‘address’]; ?> </td>
<td> <?php echo $rec[‘city]; ?> </td>
<td> <?php echo $rec[‘course’]; ?> </td>
<td> <a href=”edit.php?id=<?php echo $rec[‘studentid’]; ?>”>edit</a> </td>
<td> <a onClick=”return confirm(‘Sure to delete!’)” href=”delete.php?id=<?php echo $rec[‘studentid’]; ?>”>delete</a> </td>
</tr>
<?php } ?>
</table>
</body>
</html>
File 5. edit.php : Used to edit and update the records.
require_once “connect.php”;
$msg = “”;
$id = isset($_REQUEST[‘id’]) ? $_REQUEST[‘id’] : “0”;
$cityQuery = “select * from tbcity”;
$cityData = mysql_query($cityQuery);
$courseQuery = “select * from tbcourse”;
$courseData = mysql_query($courseQuery);
if(isset($_REQUEST[‘btnSubmit’])) {
$name = $_REQUEST[‘txtName’];
$fee = $_REQUEST[‘txtFee’];
$address = $_REQUEST[‘txtAddress’];
$cityid = $_REQUEST[‘drpCity’];
$courseid = $_REQUEST[‘drpCourse’];
$query = “update tbstudent set name=’$name’,fee=’$fee’,address=’$address’,cityId=’$cityid’,courseId=’$courseid’ where id = “.$id;
if(mysql_query($query)){
$msg = “Record updated!”;
} else {
$msg = “Unable to update!”;
}
}
$query = “select * from tbstudent where id=”.$id;
$studentData = mysql_query($query);
$recStudent = mysql_fetch_array($studentData);
?>
<html>
<body>
<form method=”POST”>
Name : <input type=”text” value=”<?php echo $recStudent[‘name’]; ?>” name=”txtName”/> <br/>
Fee : <input type=”text” value=”<?php echo $recStudent[‘fee’]; ?>” name=”txtFee”/> <br/>
Address : <textarea name=”txtAddress”><?php echo $recStudent[‘address’]; ?></textarea><br/>
City : <select id=”drpCity” name=”drpCity”>
<option value=””>Select</option>
<?php
$sel = “”;
while($recCity = mysql_fetch_array($cityData)) {
if($recCity[‘id’]==$recStudent[‘cityId’]) {
$sel = “selected”;
} else {
$sel = “”;
}
?>
<option <?php echo $sel; ?> value=”<?php echo $recCity[‘id’];?>”><?php echo $recCity[‘name’];?></option>
<?php } ?>
</select> <br/>
Course : <select id=”drpCourse” name=”drpCourse”>
<option value=””>Select</option>
<?php
$sel = “”;
while($recCourse = mysql_fetch_array($courseData)) {
if($recCourse[‘id’]==$recStudent[‘courseId’]) {
$sel = “selected”;
} else {
$sel = “”;
}
?>
<option <?php echo $sel; ?> value=”<?php echo $recCourse[‘id’];?>”><?php echo $recCourse[‘name’];?> </option>
<?php } ?>
</select> <br/>
<input type=”submit” name=”btnSubmit” value=”Update”/> <br/>
<?php echo $msg; ?>
</form>
</body>
</html>
File 5. delete.php : Used to delete the records.
require_once “connect.php”;
$msg = “”;
$id = isset($_REQUEST[‘id’]) ? $_REQUEST[‘id’] : “0”;
$query = “delete from tbstudent where id=”.$id;
if(mysql_query($query)) {
header(“location:display.php”);
} else {
echo “unable to delete!”;
}
?>
About Author
bestonetechnologies
I am a software developer.
