Update Query using joins in MySql

Update Query using joins in MySql 


In this Article, I will show how to write Mysql update query using joins.You can use multiple tables in your single Update query.

Example :



Departments Table :
id Name


1 Accounts
2 R&D
3 Sales


Employees Table :
id Name Salary Department Id
1 Rajesh 50000 2
2 Sachin 80000 2
3 Rahul 20000 1

MySql Query to update employee record whose salary is greater than 50000 and department name is R&D.


Update employees 
inner join departments ON empoyees.department_id=departments.id
SET employees.salary=40000
WHERE  employees.salary>50000 and department.name='R&D'



MySql Query to delete employee record whose salary is greater than 50000 and department name is R&D.


DELETE employees 
inner join departments ON empoyees.department_id=departments.id
WHERE employees.salary>50000 and department.name='R&D'


Updating Query Using Joins in PHP Script:

<?php
$host = 'localhost:3036';
$user = 'root';
$pass = 'rootpassword';
$conn = mysql_connect($host, $user, $pass);
mysql_select_db('sampledb');

$sql = "Update employees 
join departments ON empoyees.department_id=departments.id
SET employees.salary=40000
WHERE  employees.salary>50000 and department.name='R&D'";

$result = mysql_query( $sql, $conn );
echo "data updated successfully\n";
mysql_close($conn);
?>


Bookmark and Share

Create Complex polygon on GMap using Google Maps API v3

Create Complex polygon on GMap using Google Maps API v3

In this Section, I will show you how to create polygon on gmap using Google Maps API v3 that has complex edges.This example creates polygon using polyline based on user clicks.I have written this script to create complex geofences.

This example provides everything you need to draw polylines and polygons.You can use the search box to search & zoom location on the map .

How to Draw polygon on Gmap using Google Maps API v3. :

1. Click on the gmap to add a Marker.
2. Click on a Marker to remove it.
3. Drag a Marker to move it.


Getting Started :

/**
 * Include the Google Maps API JavaScript using a script tag.
*/

<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>

/**
* The following code instructs the application to load the Maps API after the page has fully loaded .
*/


 function initialize() {
 
    map = new google.maps.Map(document.getElementById("gmap"), {
      zoom:13,
      center: new google.maps.LatLng(18.520430, 73.856744),
      mapTypeId: google.maps.MapTypeId.ROADMAP
    });

    p = new google.maps.Polygon({
      strokeWeight: 3,
      fillColor: '#5555FF'
    });
    p.setMap(map);
    p.setPaths(new google.maps.MVCArray([path]));

    google.maps.event.addListener(map, 'click', addMarker);
  }


 /** 

* Handles click events on a map, and adds a new point to the Polyline.
*/
   
    function addMarker(event) {
    path.insertAt(path.length, event.latLng); //The path element used to draw polygon and polylines.


    var marker = new google.maps.Marker({
      position: event.latLng,
      map: map,
      draggable: true
    });
    markers.push(marker);
 
    google.maps.event.addListener(marker, 'click', function() {
      marker.setMap(null);
      for (var i = 0, I = markers.length; i < I && markers[i] != marker; ++i);
      markers.splice(i, 1);
      path.removeAt(i);
      }
    );

   google.maps.event.addListener(marker, 'dragend', function() {
      for (var i = 0, I = markers.length; i < I && markers[i] != marker; ++i);
      path.setAt(i, marker.getPosition());
      }
    );
  }



 /**
* Search and Zoom Location on Map.
*/

  function findAddress(address) {
        if (!address)
    var address=document.getElementById("address").value;

    if ((address != '') && geocoder) {
      geocoder.geocode( { 'address': address}, function(results, status) {
        if (status == google.maps.GeocoderStatus.OK) {
          if (status != google.maps.GeocoderStatus.ZERO_RESULTS) {
           if (results && results[0] && results[0].geometry && results[0].geometry.viewport)
             map.fitBounds(results[0].geometry.viewport);


          } else {
            alert("No results found");
          }
        } else {
          alert("Geocode was not successful for the following reason: " + status);
        }
      });
    }

  }


Example :


<html>
<title>Create Complex polygon on Gmap using Google Maps API v3</title>
<head>
<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>
<script type="text/javascript">
  var poly, map;
  var markers = [];
  var path = new google.maps.MVCArray;
  var geocoder = new google.maps.Geocoder();

  function initialize() {
 
    map = new google.maps.Map(document.getElementById("gmap"), {
      zoom:13,
      center: new google.maps.LatLng(18.520430, 73.856744),
      mapTypeId: google.maps.MapTypeId.ROADMAP
    });

    p = new google.maps.Polygon({
      strokeWeight: 3,
      fillColor: '#5555FF'
    });
    p.setMap(map);
    p.setPaths(new google.maps.MVCArray([path]));

    google.maps.event.addListener(map, 'click', addMarker);
  }

  function addMarker(event) {
    path.insertAt(path.length, event.latLng); //The path element used to draw polygon and polylines.


    var marker = new google.maps.Marker({
      position: event.latLng,
      map: map,
      draggable: true
    });
    markers.push(marker);
 
    google.maps.event.addListener(marker, 'click', function() {
      marker.setMap(null);
      for (var i = 0, I = markers.length; i < I && markers[i] != marker; ++i);
      markers.splice(i, 1);
      path.removeAt(i);
      }
    );

   google.maps.event.addListener(marker, 'dragend', function() {
      for (var i = 0, I = markers.length; i < I && markers[i] != marker; ++i);
      path.setAt(i, marker.getPosition());
      }
    );
  }


function findAddress(address) {
        if (!address)
    var address=document.getElementById("address").value;

    if ((address != '') && geocoder) {
      geocoder.geocode( { 'address': address}, function(results, status) {
        if (status == google.maps.GeocoderStatus.OK) {
          if (status != google.maps.GeocoderStatus.ZERO_RESULTS) {
           if (results && results[0] && results[0].geometry && results[0].geometry.viewport)
             map.fitBounds(results[0].geometry.viewport);


          } else {
            alert("No results found");
          }
        } else {
          alert("Geocode was not successful for the following reason: " + status);
        }
      });
    }
  }


</script>
</head>
<body style="margin:0px; padding:0px;" onLoad="initialize()">

  Find Place: <input type="text" id="address"/><input type="button" value="Go" onClick="findAddress()">
   <div id="gmap" style="width:100%; height: 580;"></div>
</body>
</html>




This Example will help you to create any shape of polygons on gmap.In next article, I will show you how to integrate Gmaps in Drupal.


Bookmark and Share

Find second and fourth saturday's of the month in PHP-MySql

Find second and fourth saturday's of the month in PHP-MySql


In this section, I have created simple script in PHP to find second(2nd) and fourth(4th) Saturday of the month.

<?php
// Mysql Connection
$conn = mysql_connect('localhost','root','');
if (!$conn) {
die('Could not connect to MySQL: ' . mysql_error());
}
echo 'Connection OK'; mysql_close($conn);

// Select Database
mysql_select_db("test");

// Array of the Month's
$month_array=array("jan","Feb","Mar","Apr","May","Jun","july",'Aug',"Sep","Oct","Nov","Dec");

// Array of the Years's
$year_array=array("2013","2014");

foreach($year_array as $year)
{

foreach($month_array as $month)
{
echo $second=date('Y-m-d', strtotime("second sat of $month $year"));
echo "<br>";
echo $fourth=date('Y-m-d', strtotime("fourth sat of $month $year"));
echo "<br>";

}
}

?>



You can use this PHP script to find second and fourth saturday of particular month and year.


Bookmark and Share

php.ini configuration file Default values

php.ini configuration file Default values

The PHP configuration file(php.ini) is read when PHP is initialized. php.ini includes the core php.ini directives you can set these directives to configure your PHP setup.php.ini will be located at /etc/php5/apache2/php.ini or /etc/php.ini

php.ini configuration file Default values

php.ini default values:

display_errors
;Default Value: On
This determines whether errors should be printed to the screen or not.

---------------------------------------------------------------------
error_reporting
;Default Value: E_ALL & ~E_NOTICE

// Turn off all error reporting
error_reporting(0);

// Report simple running errors
error_reporting(E_ERROR | E_WARNING | E_PARSE);

// Reporting E_NOTICE can be good too (to report uninitialized
// variables or catch variable name misspellings ...)
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);

// Report all errors except E_NOTICE
// This is the default value set in php.ini
error_reporting(E_ALL ^ E_NOTICE);

// Report all PHP errors (see changelog)
error_reporting(E_ALL);

// Report all PHP errors
error_reporting(-1);

--------------------------------------------------------------------------------
max_input_time
;Default Value: -1 (Unlimited)
This sets the maximum time in seconds a script is allowed to parse input data, like POST and GET.

-----------------------------------------------------------------------------------
short_open_tag
;Default Value: On
whether the short form ( ) of PHP's open tag should be allowed
---------------------------------------------------------------------------------
max_execution_time = 30
Maximum execution time of each script, in seconds
----------------------------------------------------------------------------------
file_uploads :"1"
Whether or not to allow HTTP file uploads.
---------------------------------------------------------------------------------
upload_max_filesize : 2M
The maximum size of an uploaded file.

-------------------------------------------------------------------------------
max_file_uploads :20
The maximum number of files allowed to be uploaded simultaneously.

------------------------------------------------------------------------------
max_input_vars : 1000
How many input variables may be accepted .
-------------------------------------------------------------------------------
upload_tmp_dir : NULL
The temporary directory used for storing files when doing file upload.
--------------------------------------------------------------------------------
error_log
Name of the file where script errors should be logged.
------------------------------------------------------------------------------
log_errors : "0"
Tells whether script error messages should be logged to the server's error log
-----------------------------------------------------------------------------------
memory_limit : 128M
This sets the maximum amount of memory in bytes that a script is allowed to allocate.
-----------------------------------------------------------------------------------
session.gc_maxlifetime : 1440
session.gc_maxlifetime specifies the number of seconds after which data will be seen as 'garbage'.
------------------------------------------------------------------------------------
session.cookie_lifetime : 0
session.cookie_lifetime specifies the lifetime of the cookie in seconds which is sent to the browser.
The value 0 means "until the browser is closed." Defaults to 0.
---------------------------------------------------------------------------------

Check List of php.ini directives: http://php.net/manual/en/ini.list.php


Bookmark and Share

MySQL delete duplicate rows from table using single query

MySQL delete duplicate rows from table using single query:

In this article,I will show how to delete duplicate records from table using single MySQL Query.

MySQL delete duplicate rows from table using single query

Example:

I have a table contain departments (department_table).

CREATE TABLE IF NOT EXISTS `department_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `department` VARCHAR(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


Department table contains duplicate records of department.

id     Department
1 Sales
2 R&D
3 Support
4 Account
5 Sales

You can use following query to remove duplicate records from table using single query:

DELETE D2
FROM   department_table D1
JOIN   department_table D2 ON (D2.department = D1.department )  AND( D2.id > D1.id); 

OUTPUT:

id     Department
1 Sales
2 R&D
3 Support
4 Account

I hope this MySQL query will help you to remove duplicate records from table.


Bookmark and Share