Given a table SALARIES, such as the one below, that has m and f values. Swap all f and m values with a single update query and no intermediate temp table.


Id Name Sex Salary 1 A m 2500 2 B f 1500 3 C m 5500 4 D f 500

UPDATE SALARIES SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END

To Create a View of Table

CREATE VIEW name_view AS SELECT column1, column2, column3.... FROM tablename

Find out nth order salary in MySQL

To find out the nth highest salary from employee table :
SYNTAX:

SELECT * from employee emp WHERE (n) = (SELECT COUNT(emp2.salary) FROM employee emp2 WHERE emp2.salary >= emp.salary)

where n is the number of salary which you want.

OR

To Find the 2nd Highest Salary in following ways

SELECT name, salary FROM employee where salary NOT IN (SELECT MAX(salary) from employee)

SELECT MAX(salary) FROM employee where salary NOT IN (SELECT MAX(salary) from employee)

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1,1;

Send Mail via PHP

Send an Email via PHP
Step 1: Create the form for an Email

<form action="mail_send.php" method="post" name="email">

<table width="354" height="279" >

<tr><td>To:</td><td><input type="email" name="to" value="" />

</td></tr>

<tr><td>From Name:</td><td><input type="text" name="name" value="" />

</td></tr>

<tr><td>Subject:</td><td><input type="subject" name="subject" value="" />

</td></tr>

<tr><td>From Email:</td><td><input type="email" name="femail" value="" />

</td></tr>

<tr><td colspan="2"><input type="submit" name="submit" value="SEND" />

</td></tr>

<tr><td>Message:</td><td><textarea cols="20" name="message" rows="8">

</textarea></td>

</tr>
</table>

</form>

Step 2: Write the php script for email

<?php 
if(isset($_POST[femail])){
    $to = $_POST["to"];
    $femail = $_POST["femail"];
    $name = $_POST["name"];
    $subject = $_POST["message"];
    $message = wordwrap ($message, 70);
    mail($to, $subject, $message, "From: $femail\n");
    echo "Thank you for sending a mail";
}else{
    echo 'mail not send..!!';
}
?>

Make dropdown as a required field in HTML5

If you want to make <select> field as a required field then you have to do like this

<select name="" required x-moz-errormessage="Please Select the Field">
<option value="">Select the Field</option>
<option value="One">One</option>
<option value="Two">Two</option>
</select>

INNER JOIN of two tables

if you have two tables like product and product_description  and you want the inner join of these tables then the query will be like this:

select product.model, product_description.name, product.price, product.weight, 
product_description.description from product INNER JOIN product_description on
product.product_id=product_description.product_id

ADD Foreign Key in existing Table

How to add Foreign Key in existing table

if you want to add any column as a foreign key in your existing table then you have to write following MySQL query for that

Syntax:

ALTER TABLE tablename ADD CONSTRAINT FOREIGN KEY column_name REFERENCES ref_table_name(column_name)

if you want to add multiple column as a foreign key in the same table then you have to write the following MySQL query:

Syntax:

ALTER TABLE tablename 
ADD CONSTRAINT FOREIGN KEY column_name REFERENCES ref_table_name(column_name)
ADD CONSTRAINT FOREIGN KEY column_name REFERENCES ref_table_name(column_name)
ADD CONSTRAINT FOREIGN KEY column_name REFERENCES ref_table_name(column_name)

Confirm before DELETE the record by javascript

<script type="text/javascript">

function ConfirmDelete() {

if (confirm("Delete Account?"))

location.href='linktoaccountdeletion';

}

</script> echo '<input type="button" onclick="ConfirmDelete()" value="DELETE ACCOUNT">';

File Upload Validation using javascript

Below are the script by which you can validate file when you upload

Make the form

<form>
<div class=”form-level“>Upload Resume
<input name=”image” type=”file” id=”upload_resume” placeholder=”Upload Resume”class=”form-control” /></div>
</form>

var fup = document.getElementById('upload_resume'); 
var fileName = fup.value;
var ext = fileName.substring(fileName.lastIndexOf('.') + 1);
if(ext == "doc" || ext == "docx")
{
return true;
}
else
{
alert("Please select your Resume. Only pdf, doc and docx files are accepted");
fup.value="";
fup.focus();
return false;
}
}

3rd Highest Salary from employee table

SELECT * FROM employee one1 WHERE ( 3 ) = ( SELECT COUNT( one2.emp_salary ) FROM employee one2 WHERE one2.emp_salary >= one1.emp_salary )

Redirect to target page by Dropdown List

Redirect to other page by dropdown list :

Step 1: Write down the bit of php code

<?php

if (isset($_POST[‘nav’])) {

 header(“Location: $_POST[nav]”);

}

?>

Step 2: javascript code

<script type=”text/javascript”>

$(function() {

    $(“#submit”).hide();

    $(“#page-changer select”).change(function() {

        window.location = $(“#page-changer select option:selected”).val();

    })

});

</script>

Step 3: Make Html form & fields

<form name=”form1″ id=”page-changer” action=”” method=”post”>

    <div align=”center”><strong>Select Your Option  &nbsp;&nbsp;&nbsp;&nbsp;</strong>

      <select name=”nav”>

  <option value=””>Select Your Option…</option>

  <option value=”paidstp.php”>All Paid Students</option>

  <option value=”paidlocation.php”>Search Date & Location Wise</option>

</select>

      <input type=”submit” value=”Go” id=”submit” />

    </div>

</form>

Email ID & mobile number validation in JS

Below are the script by which you can validate email id and mobile number field.

Step 1: Make the form

<div class="form-level">
     <input type="text" class="form-control" name="email" placeholder="Email Id" />
     <span class="form-icon fa fa-envelope"></span>
</div>
<div class="form-level">
      <input name="mobile" placeholder="Enter your Mobile Number" id="mobile" class="form-control" value="" type="text">
      <span class="form-icon fa fa-phone"></span>
</div>

Now apply the below script in your head section

var email = document.fromValidate.email;
var mobile = document.fromValidate.email;
if(email.value==''){
   window.alert('Pleaseer the email id');
   email.focus();
   return false;
}
if(email.value.indexOf('@',0 window.alert('Pleaseer the valid email');
  email.focus();
  return false;
}
if(email.value.indexOf('.',0 window.alert('Pleaseer the valid email');
  email.focus();
  return false;
}
if(mobile.value==''){
  window.alert('Pleaseer the mobile number')
  mobile.focus();
  return false;
}
if(mobile.value.length<10  window.alert('Pleaseer valid mobile number');
  mobile.focus();
}
if(mobile.value.length>10 window.alert('Pleaseer valid mobile number');
 mobile.focus();
}
if(!parseInt(mobile.value)){
  window.alert("Enterts only");
  mobile.focus();
  return false;
}
return true;
}

Fetch the month and year from date where date datatype is datetime

if you want to fetch the records from the below table

Order(varchar)	Order Date(datetime)
150	2014-09-26 10:36:18
1500	2014-08-26 09:36:18
5200	2014-06-26 01:36:18
352	2014-09-26 22:36:18
8654	2014-05-26 20:36:18
2554	2014-09-26 14:36:18
780	2014-03-26 18:36:18
1200	2014-09-26 15:36:18

Ques: Find out Total amount and month name of order in the Sep 2015

QUERY: SELECT SUM( total ) , month( date_added ) FROM `order` WHERE month( date_added ) =9

OUTPUT: 

Order	month
4256	9

How to create Captcha using PHP..?

How to create Captcha using PHP..?

Step 1: First of all create the function for random numbers
               <?php
                      function randomString($len=4){
                        $validChar="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()";
$validCharNumber=strlen($validChar);
$result="";

for($i=0;$i<$length;$i++){
$index=mt_rand(0,$validChar-1);
$result .= $validChar[$index];
}
return $result;
}
?>

Step 2: Now Write the Script for the captcha
<script>
function checkCaptcha(name1,name2){
if(name1!=name2){
alert("Security code incorrect or left blank, Please enter correct security code carefully....!");
return false;
}else{
return true;
}
if(!chkcaptcha(jj.value,captcha.value)
 
)
{
return false;
}
}
</script>

Step3: Now Create the HTML Fom for the Captcha

<form>
<table>
<tr>
<td>Enter the Captcha</td><td><input name="jj" type="text" disabled="disabled" class="dropdown" id="jj"  style="background-image:url(images/captcha2.png); text-align:center; color:green; font-size:14px; size:15;   font-weight:bold; letter-spacing:2px;" value="<?php echo getRandomString(); ?>" size="10" readonly/>
    <input name="captcha" type="text" style="color:#000;" id="captcha"  size="10" maxlength="4" /></td>
</tr>
</table>
</form>

Validate Form with HTML5

We can easily validate the form by html5

<form method="post" action="get-student-info.php" name="WtpForm" >  <table width="415" height="98" border="0"  bgcolor="#FFFFFF" align="center">       <tr>     <td height="40"  colspan="2"><div align="center">Student Information</div></td>   </tr>     <tr><td width="166" height="24"><strong>Name<font color="#FF0000">*</font></strong></td><td width="177" ><input name= "name" type= "text" value="" required x-moz-errormessage="Enter your name" / ></td></tr><tr><td width="166" height="24"><strong>Father Name<font color="#FF0000">*</font></strong></td><td width="177" ><input name= "fname" type= "text" value="" required x-moz-errormessage="Enter your Father name" / ></td></tr><tr><td width="166" height="24"><strong>Age<font color="#FF0000">*</font></strong></td><td width="177" ><input name= "age" type= "number" value="" min="18" max="99" required x-moz-errormessage="Enter your Age" / ></td></tr><tr><td width="166" height="24"><strong>Gender<font color="#FF0000">*</font></strong></td><td width="177" ><input name= "gender" type= "text" value="" required x-moz-errormessage="Enter your Gender" ></td></tr><tr><td width="166" height="24"><strong>Mobile No.<font color="#FF0000">*</font></strong></td><td width="177" ><input name= "mobile" type= "text" value="" required x-moz-errormessage="Enter your Mobile Number"></td></tr><tr><td width="166" height="24"><strong>Email ID<font color="#FF0000">*</font></strong></td><td width="177"><input name="email" type="email" value="" required x-moz-errormessage="Enter your Email Id"/></td></tr><tr><td width="166" height="24"><strong>College Name<font color="#FF0000">*</font></strong></td><td width="177" ><input name= "clgname" type= "text" value="" required x-moz-errormessage="Enter your College name" / ></td></tr><tr><td width="166" height="24"><strong>City<font color="#FF0000">*</font></strong></td><td width="177" ><input name= "city" type= "text" value="" required x-moz-errormessage="Enter your City name" / ></td></tr><td height="1" colspan="2"><div align="center">  <input type="submit" name="Submit" value="PRINT" ></div></td></tr>  </table>  <div align="center"></div>  <div align="center"></div></form>

Validation for Mobile text Field by JavaScript


<script>
function Validate()
        {
           
            var y =document.getElementById("y").value;
 
            if(isNaN(y)||y.indexOf(" ")!=-1)
            {
              alert("Enter numeric value")
              return false;
           }
           if (y.length>10)
           {
                alert("enter 10 characters");
                return false;
           }
           return true;
        }
</script>

Redirect to other page by dropdown list :

<?php

if (isset($_POST['nav'])) {

header("Location: $_POST[nav]");

}

?>

<script type="text/javascript">

$(function() {

    $("#submit").hide();

    $("#page-changer select").change(function() {

        window.location = $("#page-changer select option:selected").val();

    })

});

</script>

<h1 align="center"><strong>Search PAID Students Records</strong></h1>

<p align="center">&nbsp;</p>

<form name="form1" id="page-changer" action="" method="post">

    <div align="center"><strong>Select Your Option  &nbsp;&nbsp;&nbsp;&nbsp;</strong>

      <select name="nav">

  <option value="">Select Your Option...</option>

  <option value="paidstp.php">All Paid Students</option>

  <option value="paidlocation.php">Search Date & Location Wise</option>

</select>

      <input type="submit" value="Go" id="submit" />

    </div>

</form>

Search records by entering multiple fields

Search records by entering multiple fields 

<form name="form1" action="searchdrop2.php" method="post" onsubmit="return ValidateContactForm()"; >

<table width="533" height="108" border="0" bgcolor="#FFFF99">

<tr>

  <td width="210" height="31">

    <strong>BATCH DATE</strong></td><td width="313"><select name="term2" >

<option value="" >Select option</option>

<option value="22nd Apr 2013"  >22nd Apr 2013</option>

<option value="29th April 2013" >29th April 2013</option>

<option value="06th May 2013" >06th May 2013</option>

<option value="13th May 2013"  >13th May 2013</option>

<option value="20th May 2013" >20th May 2013</option>

<option value="27th May 2013" >27th May 2013</option>

<option value="03rd June 2013"  >03rd June 2013</option>

<option value="10th June 2013" >10th June 2013</option>

<option value="17th June 2013" >17th June 2013</option>

<option value="24th June 2013"  >24th June 2013</option>

<option value="01st July 2013" >01st July 2013</option>

<option value="08th July 2013" >08th July 2013</option>

 <option value="15th July 2013">15th July 2013</option>

</select></td></tr>

<tr>

<td height="24">

  <strong>PAID/UNPAID </strong></td><td><select name="term3" >

<option value="" >Select option</option>

<option value="PAID"  >Paid</option>

<option value="0 ">Unpaid</option>

</select></td>

</tr>

<tr>

<td height="24">

  <strong>SELECT LOCATION </strong></td><td><select name="term4" >

<option value="" >Select option</option>

<option value="Delhi"  >Delhi</option>

<option value="Noida" >Noida</option>

<option value="Gwalior" >Gwalior</option>

<option value="Haryana"  >Haryana</option>

<option value="Ahmedabad" >Ahmedabad</option>

<option value="Bangalore" >Bangalore</option>

<option value="Bhopal"  >Bhopal</option>

<option value="Chandigarh" >Chandigarh</option>

<option value="Chennai" >Chennai</option>

<option value="Dehradun"  >Dehradun</option>

<option value="Ghaziabad" >Ghaziabad</option>

<option value="Hyderabad" >Hyderabad</option>

<option value="Jaipur">Jaipur</option>

<option value="Kolkata">Kolkata</option>

<option value="Lucknow">Lucknow</option>

<option value="Mumbai">Mumbai</option>

<option value="Nagpur">Nagpur</option>

</select></td>

</tr>

<tr><td colspan="2">

  <div align="center">

    <input class="button" type="submit" name="submit" value="Search">

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

    <input class="button" type="reset" name="reset" value="Reset">

    

  </div></td>

  </tr>

</table>

</form>

php code

<table>

 <tr>

   <td align="center"><h1><strong>Details of Student</strong></h1></td>

 </tr>

 <tr>

   <td>

     <table width="524" height="55" border="1">

     <tr>

       <td width="59" bgcolor="#000000"><div align="center"><font color="#FFFFFF"><strong>Name</strong></font></div></td>

            <td width="60" bgcolor="#000000"><div align="center"><font color="#FFFFFF"><strong>STP ID</strong></font></div></td>

       <td width="69" bgcolor="#000000"><div align="center"><font color="#FFFFFF"><strong>Course Name</strong></font></div></td>

<td width="79" bgcolor="#000000"><div align="center"><font color="#FFFFFF"><strong>Batch Date</strong></font></div></td>

            <td width="81" bgcolor="#000000"><div align="center"><font color="#FFFFFF"><strong>Contact No.</strong></font></div></td>

            <td width="68" bgcolor="#000000"><div align="center"><font color="#FFFFFF"><strong>Location</strong></font></div></td>

            <td width="62" bgcolor="#000000"><div align="center"><font color="#FFFFFF"><strong>Fee Status</strong></font></div></td>

         

     </tr>

<?php

include('config.php');

$term2=$_POST['term2'];

$term3=$_POST['term3'];

$term4=$_POST['term4'];

$query=mysql_query("SELECT * FROM tbltransaction WHERE batch_date='$term2' and status='$term3' and training_location='$term4'");

//$result=mysql_query($query);

$num=mysql_numrows($query);

$i=0;

while ($i < $num){

@$b_name=mysql_result($query,$i,"b_name");

@$refno=mysql_result($query,$i,"refno");

@$course_name=mysql_result($query,$i,"course_name");

@$batch_date=mysql_result($query,$i,"batch_date");

@$b_telephone=mysql_result($query,$i,"b_telephone");

@$training_location=mysql_result($query,$i,"training_location");

@$status=mysql_result($query,$i,"status");

echo "<tr><td> $b_name</td><td>RST/STP/$refno</td><td> $course_name</td><td>$batch_date</td><td>$b_telephone</td><td>$training_location</td><td>$status</td></tr>";

$i++;

}

?>

</table></td></tr></table>

To Prevent Copy, Cut and Paste into HTML Input Text Fields

If you want to prevent copy, paste and cut from the textbox the you should do like this....

<input type="text" name="name" id="name" onpaste="return false;" onCopy="return false" onCut="return false">  

Dynamic Dropdown list

<form>

 <table align="center" bgcolor="gray">

 <tr><td>State</td>

<td>

<select id="course_name" onChange="updateSecondSelection();">

<option value="0">Select your courses</option>

<option value="Andhra Pradesh">Andhra Pradesh</option>

<option value="Assam">Assam</option>

<option value="Arunachal Pradesh">Arunachal Pradesh</option>

<option value="Andaman and Nicobar Islands">Andaman and Nicobar Islands</option>

<option value="Bihar">Bihar</option>

<option value="Chhattisgarh">Chhattisgarh</option>

<option value="Delhi">Delhi</option>

<option value="Gujarat">Gujarat</option>

<option value="Goa">Goa</option>

<option value="Haryana">Haryana</option>

<option value="Himachal Pradesh">Himachal Pradesh</option>

<option value="Jammu and Kashmir">Jammu and Kashmir</option>

<option value="Jharkhand">Jharkhand</option>

<option value="Karnataka">Karnataka</option>

<option value="Kerala">Kerala</option>

<option value="Uttar Pradesh">Uttar Pradesh</option>

<option value="Uttarakhand">Uttarakhand</option>

</select></td>

</tr>

<tr>

<td>District</td>

<td>

<select id="training_location">

<option value="NA"></option>

</select>

<td></tr>

</table></form>

<script type="text/javascript" language="JavaScript">

function updateSecondSelection(){

var embeddedBasic = new Array('Hyderabad','Warangal');

var pcbBasic = new Array('Silchar','Guwahati','Golaghat','Nagaon','Sivasagar','Tezpur');

var arunanchalPradesh = new Array('Bubang','Changlang','Deban','Dharampur','Gandhigram','Jairampur','Rajanagar','Ranglum');

var andamanandNicobarIslands = new Array('');

var bihar = new Array('Patna','Gaya','Bhagalpur','Muzaffarpur','Purnia','Siwan','Motihari','Nawada','Buxar','Aurangabad');

var chhattisgarh = new Array('Raipur','Bilaspur','Rajnandgaon','Raigarh','Jagdalpur','Ambikapur','Dhamtari');

var delhi = new Array('New Delhi');

var gujarat = new Array('Ahmedabad','Surat','Surat','Rajkot','Rajkot','Jamnagar',' Junagadh','Gandhinagar','Nadiad','Morbi',' Surendranagar','Gandhidham','Godhra');

var goa = new Array('Goa');

var haryana = new Array('Faridabad','Gurgaon','Panipat','Yamunanagar','Rohtak','Hisar','Karnal','Sonipat','Panchkula','Bhiwani','Sirsa','Kurukshetra','Rewari');

var himachalPradesh = new Array('Chamba','Bharmaur','Masrur','Baijnath','Diyar','Khokhan','Manali','Simla');

var jammuandKashmir = new Array('Srinagar','Jammu','Anantanag');

var jharkhand = new Array('Dhanbad','Ranchi','Bokaro','Deoghar');

var karnataka = new Array('Bagalkot','Bangalore','Belgaum','Bellary','Bangalore','Mysore');

var kerala = new Array('Kakkanad','Painavu','Kannur','Palakkad');

var uttarPradesh = new Array('Agra','Aligarh','Allahabad',' Azamgarh','Bareilly','Basti','Chitrakoot','Gonda','Faizabad','Gorakhpur','Jhansi','Kanpur','Lucknow','Meerut','Mirzapur','Moradabad','Saharanpur','Varanasi');

var uttarakhand = new Array('Almora','Bageshwar','Chamoli ','Champawat','Dehradun','Haridwar','Nainital','Pauri','Pithoragarh','Rudraprayag','New Tehri','Rudrapur','Uttarkashi');

var course_name = document.getElementById('course_name');

var training_location = document.getElementById('training_location');

var selected = course_name.value;

/** Remove all options in second dropdown list selection **/

training_location.options.length=0;

if(selected=='Andhra Pradesh'){

for(node in embeddedBasic){

training_location.options[node] 

= new Option(embeddedBasic[node],embeddedBasic[node]);

}

}else if(selected=='Assam'){

for(node in pcbBasic){

training_location.options[node] 

= new Option(pcbBasic[node],pcbBasic[node]);

}

}else if(selected=='Arunachal Pradesh'){

for(node in arunanchalPradesh){

training_location.options[node] 

= new Option(arunanchalPradesh[node],arunanchalPradesh[node]);

}

}else if(selected=='Andaman and Nicobar Islands'){

for(node in andamanandNicobarIslands){

training_location.options[node] 

= new Option(andamanandNicobarIslands[node],andamanandNicobarIslands[node]);

}

}else if(selected=='Bihar'){

for(node in bihar){

training_location.options[node] 

= new Option(bihar[node],bihar[node]);

}

}else if(selected=='Chhattisgarh'){

for(node in chhattisgarh){

training_location.options[node] 

= new Option(chhattisgarh[node],chhattisgarh[node]);

}

}else if(selected=='Delhi'){

for(node in delhi){

training_location.options[node] 

= new Option(delhi[node],delhi[node]);

}

}else if(selected=='Gujarat'){

for(node in gujarat){

training_location.options[node] 

= new Option(gujarat[node],gujarat[node]);

}

}else if(selected=='Goa'){

for(node in goa){

training_location.options[node] 

= new Option(goa[node],goa[node]);

}

}else if(selected=='Haryana'){

for(node in haryana){

training_location.options[node] 

= new Option(haryana[node],haryana[node]);

}

}else if(selected=='Himachal Pradesh'){

for(node in himachalPradesh){

training_location.options[node] 

= new Option(himachalPradesh[node],himachalPradesh[node]);

}

}else if(selected=='Jammu and Kashmir'){

for(node in jammuandKashmir){

training_location.options[node] 

= new Option(jammuandKashmir[node],jammuandKashmir[node]);

}

}else if(selected=='Jharkhand'){

for(node in jharkhand){

training_location.options[node] 

= new Option(jharkhand[node],jharkhand[node]);

}

}else if(selected=='Karnataka'){

for(node in karnataka){

training_location.options[node] 

= new Option(karnataka[node],karnataka[node]);

}

}else if(selected=='Kerala'){

for(node in kerala){

training_location.options[node] 

= new Option(kerala[node],kerala[node]);

}

}else if(selected=='Uttar Pradesh'){

for(node in uttarPradesh){

training_location.options[node] 

= new Option(uttarPradesh[node],uttarPradesh[node]);

}

}else if(selected=='Uttarakhand'){

for(node in uttarakhand){

training_location.options[node] 

= new Option(uttarakhand[node],uttarakhand[node]);

}

}else{

training_location.options[0] = new Option('No option selected','NA');

}

}

</script>