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 */;
How to calculate AGE from your birth-date in PHP
Here I will Provide Code for How to calculate AGE from your birth-date
all data will be dynamically this is no static script
PAGE LIST:
1.Register Page2.View Page3.Database query
features:
1.Here i provide advance register Page Code with implode explode function2.count age from birthdate is fully dynamically without any function
CODE:
-----------------------------------------
Create database 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 */;
--------------------------------
Save this code as conection.php
-------------------------------
<?php
$con= new mysqli("localhost","root","","db_harsh"); //( server name ,user name , password, database name )
?>
--------------------------------
Save this code as view.php
-------------------------------
<?php
include_once("conection.php");
$sel="select * from reg";
$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>
</tr>
<?php
while($fet=$sql->fetch_object())
{
$birth=$fet->dob;
$dob = new DateTime($birth);
$interval = $dob->diff(new DateTime);
?>
<tr>
<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->cid; ?> </td>
<td><?php echo $fet->sid; ?> </td>
<td><?php echo $fet->dob; ?> </td>
<td><?php echo "Your Age: ".$interval->y; ?> </td>
</tr>
<?php
}
?>
</table>
</form>
---------------------------------------
save this code as reg.php
----------------------------------------
<?php
include_once('conection.php');
//fetching country
$sel = "select * from country";
$count = $con->query($sel); // country table connect to databse
//fetching state
$sels = "select * from state";
$state = $con->query($sels);
if(isset($_POST["insert"]))
{
$uname=$_POST["uname"];
$pass=$_POST["pass"];
$gender=$_POST["gender"];
$hobby=$_POST["chk"];
$hby=implode(",",$hobby); // convert array to string
$coun=$_POST["country"];
$sta=$_POST["state"];
$dob=$_POST["dob"];
$dob=implode(",",$dob); // convert array to string
$ins = "INSERT INTO reg(`uname`, `pass`, `gender`, `hobby`, `cid`, `sid`, `dob`) VALUES ('$uname','$pass','$gender','$hby','$coun','$sta','$dob')";
$con->query($ins);
}
?>
<h3>Register Form</h3>
<form method="post">
<table width="400" border="1" align="center" cellspacing="0" background="freebg1.jpg" bordercolor="#E87400">
<tr>
<th scope="row">Username</th>
<td><input type="text" name="uname" required="required"/></td>
</tr>
<tr>
<th scope="row">Password</th>
<td><input type="password" name="pass" required="required"/></td>
</tr>
<tr>
<th scope="row">Gender</th>
<td><input type="radio" name="gender" value="male"/>Male
<input type="radio" name="gender" value="female"/>Female
</td>
</tr>
<tr>
<th scope="row">Hobby</th>
<td><input type="checkbox" name="chk[]" value="music" />Music
<input type="checkbox" name="chk[]" value="travel" />Travel
<input type="checkbox" name="chk[]" value="game" />Game</td>
</tr>
<tr>
<th scope="row">Country</th>
<td> <select name="country" required="required">
<option value="0">------select-----</option>
<?php
while($coun=$count->fetch_object())
{
?>
<option value="<?php echo $coun->cid; ?>" > <?php echo $coun->cname; ?> </option>
<?php
}
?>
</select></td>
</tr>
<tr>
<th scope="row">State</th>
<td><select name="state" required="required">
<option value="0">------select-----</option>
<?php
while ($sta=$state->fetch_object())
{
?>
<option value="<?php echo $sta->sid;?>"> <?php echo $sta->sname;?> </option>
<?php
}
?>
</select></td>
</tr>
<tr>
<th scope="row">Dob</th>
<td>
<select name="dob[]" value="year" required="required">
<option>Year</option>
<?php
for($i=1994;$i<=2016;$i++)
{
?>
<option value="<?php echo $i; ?>"> <?php echo $i;?> </option>
<?php
}
?>
</select>
<select name="dob[]" value="month" required="required">
<option>Month</option>
<?php
for($m=01;$m<=12;$m++)
{
?>
<option value="<?php echo $m; ?>"> <?php echo $m;?> </option>
<?php
}
?>
</select>
<select name="dob[]" value="date" required="required">
<option>Date</option>
<?php
for($d=01;$d<=31;$d++)
{
?> <option value="<?php echo $d; ?>" > <?php echo $d;?> </option>
<?php
}
?>
</select>
</td>
</tr>
<tr align="center">
<td colspan="2"><input type="submit" name="insert" value="Register" class="btn btn-success"/></td>
<a href="view.php" >View All Record</a>
</tr>
</table>
</form>
</body>
</html>
MySQL DELETE Query
MySQL DELETE Query
If you want to delete a record from any MySQL table, then you can use SQL command DELETE FROM. You can use this command at mysql> prompt as well as in any script like PHP.
Syntax:
Here is generic SQL syntax of DELETE command to delete data from a MySQL table:
DELETE FROM table_name [WHERE Clause]
- If WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
- You can specify any condition using WHERE clause.
- You can delete records in a single table at a time.
The WHERE clause is very useful when you want to delete selected rows in a table.
Deleting Data from Command Prompt:
This will use SQL DELETE command with WHERE clause to delete selected data into MySQL table tutorials_tbl.
Example:
Following example will delete a record into tutorial_tbl whose tutorial_id is 3.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)
mysql>
Deleting Data Using PHP Script:
You can use SQL DELETE command with or without WHERE CLAUSE into PHP function mysql_query(). This function will execute SQL command in similar way it is executed at mysql> prompt.
Example:
Try out the following example to delete a record from tutorial_tbl whose tutorial_id is 3.
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'DELETE FROM tutorials_tbl
WHERE tutorial_id=3';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
?>
MySQL UPDATE Query
MySQL UPDATE Query
There may be a requirement where existing data in a MySQL table needs to be modified. You can do so by using SQL UPDATE command. This will modify any field value of any MySQL table.
Syntax:
Here is generic SQL syntax of UPDATE command to modify data into MySQL table:
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
- You can update one or more field altogether.
- You can specify any condition using WHERE clause.
- You can update values in a single table at a time.
The WHERE clause is very useful when you want to update selected rows in a table.
Updating Data from Command Prompt:
This will use SQL UPDATE command with WHERE clause to update selected data into MySQL table tutorials_tbl.
Example:
Following example will update tutorial_title field for a record having tutorial_id as 3.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> UPDATE tutorials_tbl
-> SET tutorial_title='Learning JAVA'
-> WHERE tutorial_id=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
Updating Data Using PHP Script:
You can use SQL UPDATE command with or without WHERE CLAUSE into PHP function mysql_query(). This function will execute SQL command in similar way it is executed at mysql> prompt.
Example:
Try out the following example to update tutorial_title field for a record having tutorial_id as 3.
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'UPDATE tutorials_tbl
SET tutorial_title="Learning JAVA"
WHERE tutorial_id=3';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>
Subscribe to:
Posts (Atom)