Recent Posts
PHP Mailing Scripts
best way to learn php core php PHP - Sending Emails using PHP PHP Mailing Scripts
Inner Join Of three table (Country state and register)
Inner Join Of three table (Country state and register) learn mysql learn php learn sqlInner 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
How to calculate AGE from your birth-date learn mysql learn phpHere 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 QueryMySQL 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 QueryMySQL 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)