WIRKING WITH TWO DATABASES TOGETHER IN ONE PLACE USING PHP
 |
| output of using two databases together |
Hi everyone working with two database is relay easy and quite impotent. In this case i just take example of teacher and student. At first we make two tables 1 for student and other for teacher.
create a database and paste the following sql command to create the two tables
*****************************************************************************
Student table
CREATE TABLE IF NOT EXISTS `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `teacher_id` int(20) NOT NULL, `visibality` int(5) NOT NULL, `image` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=46 ; -- -- Dumping data for table `student` -- INSERT INTO `student` (`id`, `student_id`, `name`, `teacher_id`, `visibality`, `image`) VALUES (1, 1, 'Rahul Sen', 1, 1, ''), (2, 2, 'Pratick', 1, 1, ''), (3, 3, 'Sumanta', 1, 1, ''), (4, 4, 'Koushik', 2, 1, ''), (5, 5, 'Nur', 2, 1, ''), (6, 6, 'Indra', 2, 1, ''), (7, 7, 'Souvik', 1, 1, ''), (8, 8, 'Santaanu', 2, 1, ''), (9, 9, 'Asish', 2, 1, ''), (10, 10, 'Asutosh', 2, 1, '');
Teacher table
CREATE TABLE IF NOT EXISTS `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `teature_id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `contact` int(20) NOT NULL, `visibality` int(5) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `teacher` -- INSERT INTO `teacher` (`id`, `teature_id`, `name`, `contact`, `visibality`) VALUES (1, 1, 'XYZ', 2147483647, 1), (2, 2, 'ABC', 563427891, 1);
***********************************************************************
now here we can easily see that each teacher's has a unique id and every student use a
particulate teacher id , it shows that the two teacher have some student .....
Now open a php file and paste the following php code
index.php
//here we check that my sql connection is established or not
<?php
//we are creating connection
$dbhost="localhost";
$dbuser="root";
$dbpass="";
$db="test_connection";
$connection=mysqli_connect($dbhost,$dbuser,$dbpass,$db); //checking connection
//checkking connection
if(mysqli_connect_error()) //findout is there any error
{
die("Unable to connect with database".mysqli_connect_error()."(".mysqli_error().")");
//if any error find then 'die' will close the connection and
mysqli_error() shows the error message}else/*
{
echo "Successfully Connected";
}*/
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Working with 2 database</title>
</head>
<body>
<?php
//performing query form teacher table
$query="SELECT * ";
$query.="from teacher ";
$query.="WHERE visibality=1 ";
$query.="ORDER BY teature_id ASC ";
$teacher_set=mysqli_query($connection,$query);
//check query perform or not
if(!$teacher_set){
die("Database query faild");
}
?><ul><!--make list if teachers-->
<?php
//fetching teacher table data
while($teacher = mysqli_fetch_assoc($teacher_set)){ ?>
<li>
<?php echo $teacher["name"]."(".$teacher["contact"].") <br>"; ?>
<ul>
<?php
//performing query form student table
$query2="SELECT * ";
$query2.="FROM student ";
$query2.="WHERE visibality=1 ";
$query2.="AND teacher_id={$teacher["teature_id"]} ";//selecting techer_id which is in student table , inserting value from teacher table by using "teacher_id={$teacher["teature_id"]}"
$student=mysqli_query($connection,$query2);//this take the teacher id and using this associated student listdown under the teacher
?>
<?php
while($student_set=mysqli_fetch_assoc($student))
{ ?>
<li><?php echo $student_set["name"]."<br>"; }?></li>
</ul>
</li>
<?php } ?>
</ul>
</body>
</html>
<?php mysqli_close($connection) ?>
*********************************************************************************
FOR ANY QUREY ACCORDING TWO THIS PLEASE MAIL ME
Comments
Post a Comment