Our social:
Propellerads

Tuesday, 26 April 2016

Inner Join Of three table (Country state and register)

Inner Join Of three table (Country state and register)


$sel="select reg.*,country.cname,state.sname from reg join country on reg.cid=country.cid join state on reg.sid=state.sid";
$sql=$con->query($sel);

////////For fetch record/////
<table>
 while($fet=$sql->fetch_object())
  {

  ?>

  <tr>
      <td><?php echo $fet->cname; ?> </td> ////Enter the column cname here because country name resides in this after inner join
      <td><?php echo $fet->sname; ?> </td>
  </tr>
  <?php
  }
 ?>  
</table> 





Here i will join 3 table reg+country+state
in register i have value of cid and sid
find country name cname from cid using joinsalso find state name sname from sid using joins

Full Page Code Here


<?php
include_once("conection.php");
$sel="select reg.*,country.cname,state.sname from reg join country on reg.cid=country.cid join state on reg.sid=state.sid";

$sql=$con->query($sel);


 ?>

<form method="post">
<table border="1">
  <tr>
  <th></th>
    <th scope="col">RID</th>
    <th scope="col">USERNAME</th>
    <th scope="col">PASSWORD</th>
    <th scope="col">GENDER</th>
    <th scope="col">HOBBY</th>
    <th scope="col">COUNTRY</th>
    <th scope="col">STATE</th>
    <th scope="col">DATE OF BIRTH </th>
<th scope="col">Age</th>
<th scope="col">DELETE </th>
 <th scope="col">EDIT </th>
  </tr>

  <?php
  while($fet=$sql->fetch_object())
  {

  ?>

  <tr>
  <td><input type="checkbox" name="chk[]" value="<?php echo $fet->rid; ?>" /></td>
    <td><?php echo $fet->rid; ?> </td>
<td><?php echo $fet->uname; ?> </td>
<td><?php echo $fet->pass; ?> </td>
<td><?php echo $fet->gender; ?> </td>
<td><?php echo $fet->hobby; ?> </td>
<td><?php echo $fet->cname; ?> </td>
<td><?php echo $fet->sname; ?> </td>
<td><?php echo $fet->dob; ?> </td>


  </tr>

  <?php
  }
 ?>

</table>
</form>

Create Database Code is here (db_harsh) 
-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 25, 2016 at 07:22 AM
-- Server version: 5.6.17
-- PHP Version: 5.5.12
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `db_harsh`
--
-- --------------------------------------------------------
--
-- Table structure for table `country`
--
CREATE TABLE IF NOT EXISTS `country` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `country`
--
INSERT INTO `country` (`cid`, `cname`) VALUES
(1, 'india'),
(2, 'australia');
-- --------------------------------------------------------
--
-- Table structure for table `reg`
--
CREATE TABLE IF NOT EXISTS `reg` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) NOT NULL,
  `pass` varchar(20) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `hobby` varchar(25) NOT NULL,
  `cid` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  `dob` date NOT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;
--
-- Dumping data for table `reg`
--
INSERT INTO `reg` (`rid`, `uname`, `pass`, `gender`, `hobby`, `cid`, `sid`, `dob`) VALUES
(17, 'harsh', 'suthar7576', 'male', 'music', 1, 1, '1994-01-31'),
(19, 'harsh', 'suthar7576', 'male', 'music', 2, 1, '1994-02-06'),
(20, 'harsh', 'suthar7576', 'male', 'travel,game', 2, 1, '1994-06-03'),
(21, 'harshawaerstdfyj', 'suthar7576', '', '', 0, 0, '2000-02-29'),
(22, 'harshewretertrytry', 'suthar7576', 'male', 'music', 1, 3, '2000-02-29');
-- --------------------------------------------------------
--
-- Table structure for table `state`
--
CREATE TABLE IF NOT EXISTS `state` (
  `cid` int(11) NOT NULL,
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `cid` (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `state`
--
INSERT INTO `state` (`cid`, `sid`, `sname`) VALUES
(1, 1, 'Gujarat'),
(1, 2, 'Goa'),
(1, 3, 'Mumbai'),
(2, 4, 'Melbourn'),
(2, 5, 'Sydney');
--
-- Constraints for dumped tables
--
--
-- Constraints for table `state`
--
ALTER TABLE `state`
  ADD CONSTRAINT `state_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `country` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

0 comments:

Post a Comment