Our social:
Propellerads

Latest Post

Tuesday, 26 April 2016

PHP Mailing Scripts

PHP Mailing Scripts 



Here I will Show you How To Use Email System In PHP


DOWNLOAD CODE FROM HERE

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>

Wednesday, 20 April 2016

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);
?>

MySQL WHERE Clause

MySQL WHERE Clause



We have seen SQL SELECT command to fetch data from MySQL table. We can use a conditional clause called WHERE clause to filter out results. Using WHERE clause, we can specify a selection criteria to select required records from a table.

Syntax:

Here is generic SQL syntax of SELECT command with WHERE clause to fetch data from MySQL table:
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
  • You can use one or more tables separated by comma to include various conditions using a WHERE clause, but WHERE clause is an optional part of SELECT command.
  • You can specify any condition using WHERE clause.
  • You can specify more than one conditions using AND or OR operators.
  • A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.
The WHERE clause works like an if condition in any programming language. This clause is used to compare given value with the field value available in MySQL table. If given value from outside is equal to the available field value in MySQL table, then it returns that row.
Here is the list of operators, which can be used with WHERE clause.
Assume field A holds 10 and field B holds 20, then:
OperatorDescriptionExample
=Checks if the values of two operands are equal or not, if yes then condition becomes true.(A = B) is not true.
!=Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.(A != B) is true.
>Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.(A > B) is not true.
<Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.(A < B) is true.
>=Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.(A >= B) is not true.
<=Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.(A <= B) is true.
The WHERE clause is very useful when you want to fetch selected rows from a table, especially when you use MySQL Join. Joins are discussed in another chapter.
It is a common practice to search records using Primary Key to make search fast.
If given condition does not match any record in the table, then query would not return any row.

Fetching Data from Command Prompt:

This will use SQL SELECT command with WHERE clause to fetch selected data from MySQL table tutorials_tbl.

Example:

Following example will return all the records from tutorials_tbl table for which author name is Sanjay:
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author='Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>
Unless performing a LIKE comparison on a string, the comparison is not case sensitive. You can make your search case sensitive using BINARY keyword as follows:
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl \
          WHERE BINARY tutorial_author='sanjay';
Empty set (0.02 sec)

mysql>

Fetching Data Using PHP Script:

You can use same SQL SELECT command with WHERE CLAUSE into PHP functionmysql_query(). This function is used to execute SQL command and later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Example:

Following example will return all the records from tutorials_tbl table for which author name is Sanjay:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl
        WHERE tutorial_author="Sanjay"';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>
Pages (12)1234567 Next »