Home

Monday, October 29, 2012

PHP Interview Questions


PHP Interview Questions 
1. What's PHP ?

The PHP Hypertext Preprocessor is a programming language that allows web developers to create dynamic content that interacts with databases. PHP is basically used for developing web based software applications.

2. How can we know the number of days between two given dates using PHP?

$date1 = date('Y-m-d');
$date2 = '2006-07-01';
$days = (strtotime($date1) - strtotime($date2)) / (60 * 60 * 24);
echo "Number of days since '2006-07-01': $days";

3. How do you define a constant?
 define ("MYCONSTANT", 100);

4. What is meant by urlencode and urldecode?

urlencode() returns the URL encoded version of the given string. URL coding converts special characters into % signs followed by two hex digits.
For example:
urlencode("10.00%") will return "10%2E00%25". URL encoded strings are safe to be used as part of URLs.
urldecode() returns the URL decoded version of the given string.

5. How To Get the Uploaded File Information in the Receiving Script?

 Uploaded file information is organized in $_FILES as a two-dimensional array as:
$_FILES[$fieldName]['name'] - The Original file name on the browser system.
$_FILES[$fieldName]['type'] - The file type determined by the browser.
$_FILES[$fieldName]['size'] - The Number of bytes of the file content.
$_FILES[$fieldName]['tmp_name'] - The temporary filename of the file in which the uploaded file was stored on the server.
$_FILES[$fieldName]['error'] - The error code associated with this file upload.

6. What is the difference between mysql_fetch_object and mysql_fetch_array?

MySQL fetch object will collect first single matching record where mysql_fetch_array will collect all matching records from the table in an array

7. How do you pass a variable by value?

Just like in C++, put an ampersand in front of it, like $a = &$b.

8. How can we send mail using JavaScript?
No. There is no way to send emails directly using JavaScript.

But you can use JavaScript to execute a client side email program send the email using the "mailto" code. Here is an example:

function myfunction(form)
{
tdata=document.myform.tbox1.value;
location="mailto:mailid@domain.com?subject=...";
return true;
}

9. What is the difference between ereg_replace() and eregi_replace()?

eregi_replace() function is identical to ereg_replace() except that it ignores case distinction when matching alphabetic characters.

10. How do I find out the number of parameters passed into function ?
func_num_args() function returns the number of parameters passed in.

11. Are objects passed by value or by reference?
Everything is passed by value.

12. What are the differences between DROP a table and TRUNCATE a table?

DROP TABLE table_name - This will delete the table and its data.
TRUNCATE TABLE table_name - This will delete the data of the table, but not the table definition.

13. How do you call a constructor for a parent class?
parent::constructor($value)

14. How can we submit a form without a submit button?

If you don't want to use the Submit button to submit a form, you can use normal hyper links to submit a form. But you need to use some JavaScript code in the URL of the link.
<a href="javascript: document.myform.submit();">Submit Me</a>

15. How can we extract string 'abc.com ' from a string http://info@abc.com using regular expression of php?

We can use the preg_match() function with "/.*@(.*)$/" as the regular expression pattern.
For example:
preg_match("/.*@(.*)$/","http://info@abc.com",$data);
echo $data[1];

16. What is the difference between the functions unlink and unset?

unlink() is a function for file system handling. It will simply delete the file in context.
unset() is a function for variable management. It will make a variable undefined.

17. What is the difference between characters \047 and \x47?
The first one is octal 47, the second is hex 47.

18. How can we create a database using PHP and mysql?

We can create MySQL database with the use of mysql_create_db($databaseName) to create a database.

19. How can we destroy the session, how can we unset the variable of a session?
session_unregister() - Unregister a global variable from the current session
session_unset() - Free all session variables

20. How can we know the count/number of elements of an array?

a) sizeof($array) - This function is an alias of count()
b) count($urarray) - This function returns the number of elements in an array.
Interestingly if you just pass a simple var instead of an array, count() will return 1

21. How many values can the SET function of MySQL take?

MySQL SET function can take zero or more values, but at the maximum it can take 64 values.

22. What are the other commands to know the structure of a table using MySQL commands except EXPLAIN command?

DESCRIBE table_name;

23. How can we find the number of rows in a table using MySQL?

SELECT COUNT(*) FROM table_name;

24. How can we find the number of rows in a result set using PHP?

$result = mysql_query($any_valid_sql, $database_link);
$num_rows = mysql_num_rows($result);
echo "$num_rows rows found";

25. What is the difference between CHAR and VARCHAR data types?

CHAR is a fixed length data type. CHAR(n) will take n characters of storage even if you enter less than n characters to that column. For example, "Hello!" will be stored as "Hello! " in CHAR(10) column.
VARCHAR is a variable length data type. VARCHAR(n) will take only the required storage for the actual number of characters entered to that column. For example, "Hello!" will be stored as "Hello!" in VARCHAR(10) column.

26. What are the differences between mysql_fetch_array(), mysql_fetch_object(), mysql_fetch_row()?

mysql_fetch_array - Fetch a result row as an associative array and a numeric array.
mysql_fetch_object - Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead. Returns an object with properties that correspond to the fetched row, or FALSE if there are no more rows
mysql_fetch_row() - Fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

27. What is the difference between htmlentities() and htmlspecialchars()?

htmlspecialchars() - Convert some special characters to HTML entities (Only the most widely used)
htmlentities() - Convert ALL special characters to HTML entities

28. How can we get the properties (size, type, width, height) of an image using php image functions?

image size use getimagesize() function
image width use imagesx() function
image height use imagesy() function

29. How can we increase the execution time of a php script?

By the use of void set_time_limit(int seconds)

30. What are the difference between abstract class and interface?

Abstract class: abstract classes are the class where one or more methods are abstract but not necessarily all method has to be abstract. Abstract methods are the methods, which are declare in its class but not define. The definition of those methods must be in its extending class.
Interface: Interfaces are one type of class where all the methods are abstract. That means all the methods only declared but not defined. All the methods must be define by its implemented class.

31. What is the maximum size of a file that can be uploaded using PHP and how can we change this?

change maximum size of a file set upload_max_filesize variable in php.ini file

32. Explain the ternary conditional operator in PHP?

Expression preceding the ? is evaluated, if it’s true, then the expression preceding the : is executed, otherwise, the expression following : is executed.

33. What’s the difference between include and require?

It’s how they handle failures. If the file is not found by require(), it will cause a fatal error and halt the execution of the script. If the file is not found by include(), a warning will be issued, but execution will continue.

34. How many ways can we get the value of current session id?

session_id() returns the session id for the current session.

35. What is the difference between $message and $$message?

They are both variables. But $message is a variable with a fixed name. $$message is a variable who's name is stored in $message. For example, if $message contains "var", $$message is the same as $var.

36. How can we get the browser properties using php?

<?php
echo $_SERVER['HTTP_USER_AGENT'] . "\n\n";
$browser = get_browser(null, true);
print_r($browser);
?>

37. How can we know that a session is started or not?

A session starts by session_start()function.
this session_start() is always declared in header portion.it always declares first.then
we write session_register().

38. What is the use of obj_start()?

Its intializing the object buffer, so that the whole page will be first parsed (instead of parsing in parts and thrown to browser gradually) and stored in output buffer so that after complete page is executed, it is thrown to the browser once at a time.

39. What is the difference between Split and Explode?

split()-used for JavaScript for processing the string and the explode()-used to convert the String to Array, implode()-used for convert the array to String
Here the Example
<?php
$x="PHP is a ServerSide Scripting Language";
$c=explode(" ",$x);
print_r($c);
$d=implode(" ",$c);
echo "
".$d;
?>
Javascript Example:
list($month, $day, $year) = split('[/.-]', $date);

40. Which will execute faster on php POST or GET?

Both are same while performing the action but using POST security is there.
Because using GET method in the action, form field values send along with URL, so at the time of sending password, problem will occur means password also will shown in the URL.
Using of POST there is no problem.

GET method has a limit of sending parameters 100 characters but POST method does not have a limit of sending data

GET is faster than POST. Because GET fetch the data directly from the URL but POST method fetch the encrypted data from the page.

41. What is the use of sprintf() function?

The sprintf() function writes a formatted string to a variable.

42. What Is a Session?

Sessions are commonly used to store temporary data to allow multiple PHP pages to offer a complete functional transaction for the same visitor.

43. What is the use of header() function in php?

The header() function is used for redirect the page.if you want to redirect one page to another we can use it.

44. How can i get ip address?

REMOTE_ADDR - the IP address of the client
REMOTE_HOST - the host address of the client

45. What is htaccess?

.htaccess files (or "distributed configuration files") provide a way to make configuration changes on a per-directory basis.

46. What is the diffrence between Notify URL and Return URL?

Notify URL is used to just notify the status while processing.
Return URL is used to return after processing.

47. What is the difference between ucfirst and ucwords?

ucfirst() to convert the first letter of every string to uppercase, and ucwords(), to convert the first letter of every word in the string to uppercase.

48. What is meant by nl2br()?

nl2br() inserts a HTML tag <br> before all new line characters \n in a string.

49. How To Read the Entire File into a Single String?

<?php
$file = file_get_contents("/windows/system32/drivers/etc/services");
print("Size of the file: ".strlen($file)."n");
?>

50. What are the different functions in sorting an array?

Sorting functions in PHP:
asort()
arsort()
ksort()
krsort()
uksort()
sort()
natsort()
rsort()  

Structure Of A WordPress Theme


There are a couple different types of pages in WordPress. Each type of page can have a customized look and feel. This customization is done by templates. Templates are how themes work. Each template is a seperate PHP file inside the theme’s directory. Here are the standard templates:
  • 404 Template = 404.php – It’s handy to create a custom 404 page (Page Not Found) template. You can list common links and ways for users to find what they are looking for.
  • Archive Template = archive.php
  • Archive Index Page = archives.php
  • Comments Template = comments.php – This template defines how comments look under individual posts on the “Post Template”.
  • Footer Template = footer.php – HTML that is placed at the bottom of each page, saves you time and produces less duplicate HTML code in your templates.
  • Header Template = header.php – HTML that is placed at the top of each page, usually has the <head> section in it.
  • Links = links.php
  • Main Template = index.php – Usually this page lists your most recent posts. This is the “home” page of your site.
  • Page Template = page.php – Used for single pages, instead of blog posts. Your “About” page uses this template. This is the default page template, you can make your own custom page templates. I’ll cover this more in detail later.
  • Popup Comments Template = comments-popup.php
  • Post Template = single.php – This is where you customize pages that display single posts. Click on a post title (permalink) and you will be at it’s single post page using the post template.
  • Search Form = searchform.php
  • Search Template = search.php
  • Sidebar Template = sidebar.php – Usually the right hand side bar of the page. Some themes have more than one side bar so you’ll see templates like left_sidebar.php.
  • Stylesheet = style.css – Default place to put your CSS. It also contains the name and description of the theme. You must always have this file with the name and description of your theme. Many themes will have multiple CSS files that are linked to from the header.php file or the individual page templates to customize individual pages with different style rules.   

Thursday, October 18, 2012

Display submenu on left side on all pages

<?php
if($post->post_parent)
$children = wp_list_pages("title_li=&child_of=".$post->post_parent."&echo=0");
else
$children = wp_list_pages("title_li=&child_of=".$post->ID."&echo=0");
if ($children) { ?>
    <?php echo $children; ?>
<?php } ?>

Tuesday, September 18, 2012

How to Upload and Unpack a Zip File using PHP


<?php
error_reporting(0);
if($_FILES["zip_file"]["name"]) {
 $filename = $_FILES["zip_file"]["name"];
 $source = $_FILES["zip_file"]["tmp_name"];
 $type = $_FILES["zip_file"]["type"];
 
 $name = explode(".", $filename);
 $accepted_types = array('application/zip', 'application/x-zip-compressed', 'multipart/x-zip', 'application/x-compressed');
 foreach($accepted_types as $mime_type) {
  if($mime_type == $type) {
   $okay = true;
   break;
  } 
 }
 
 $continue = strtolower($name[1]) == 'zip' ? true : false;
 if(!$continue) {
  $message = "The file you are trying to upload is not a .zip file. Please try again.";
 }
 
 $target_path = "/home/var/yoursite/httpdocs/".$filename;  // change this to the correct site path
 if(move_uploaded_file($source, $target_path)) {
  $zip = new ZipArchive();
  $x = $zip->open($target_path);
  if ($x === true) {
   $zip->extractTo("/home/var/yoursite/httpdocs/"); // change this to the correct site path
   $zip->close();
 
   unlink($target_path);
  }
  $message = "Your .zip file was uploaded and unpacked.";
 } else { 
  $message = "There was a problem with the upload. Please try again.";
 }
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
 
<body>
<?php if($message) echo "<p>$message</p>"; ?>
<form enctype="multipart/form-data" method="post" action="">
<label>Choose a zip file to upload: <input type="file" name="zip_file" /></label>
<br />
<input type="submit" name="submit" value="Upload" />
</form>
</body>
</html>

htaccess rewrite rule to remove a subfolder from a URL


This will assume you have http://domain.com/sub as where the content you want to load is.  And the resulting URL to only show http://domain.com butstill load the content in in the /sub folder.

This could be modified many ways to suit the needs for the application at hand.

RewriteEngine On
RewriteRule ^$ sub/
RewriteCond %{REQUEST_FILENAME} !-f 
RewriteCond %{REQUEST_FILENAME} !-d 
RewriteRule ^(.*)$ sub/$1

Wednesday, August 22, 2012

How to get Computer Information Using JavaScript


<Script language="javascript">
          var WshNetwork = new ActiveXObject("WScript.Network");
          alert('domain ='+   WshNetwork.UserDomain);
          alert('machine name = '+  WshNetwork.ComputerName );
          alert('user name = '+   WshNetwork.UserName);
</script>

Import a CSV File Using PHP and MySQL


<?php
error_reporting(0);
//connect to the database
$connect = mysql_connect("localhost","root","");
mysql_select_db("test",$connect); //select the table
//

if ($_FILES[csv][size] > 0) {

    //get the csv file
    $file = $_FILES[csv][tmp_name];
    $handle = fopen($file,"r");
   
    //loop through the csv file and insert into database
 
    $row = 0;
while ($data = fgetcsv($handle,1000,",","'"))
{
if($row!=0)// First (Title) Row is not inserted
{
if ($data[0]) {
mysql_query("INSERT INTO contacts (contact_first, contact_last, contact_email) VALUES
(
'".addslashes($data[0])."',
'".addslashes($data[1])."',
'".addslashes($data[2])."'
)
");
}
}
$row++;
}
    //

    //redirect
    header('Location: index.php?success=1'); die;

//echo "success";

}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Import a CSV File with PHP & MySQL</title>
</head>

<body>

<?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?>

<form action="" method="post" enctype="multipart/form-data" name="form1" id="form1">
  Choose your file: <br />
  <input name="csv" type="file" id="csv" />
  <input type="submit" name="Submit" value="Submit" />
</form>

</body>
</html>

Saturday, August 18, 2012

Retrieve windows username in php

<?php
              $nw = new COM("WScript.Network");
              print "username: " . $nw->username . "<br><br>";

              $computername = $nw->computername;
              print "computername: $computername<br><br>";

              $owmi = new COM("winmgmts:\\\\$computername\\root\\cimv2");
              $comp = $owmi->get("win32_computersystem.name='$computername'" );

                print "username: " . $comp->username;
?>

Export Mysql data to CSV with PHP


<?php
$database="wordpress";
$table="wp_member";
mysql_connect("localhost","root","");
mysql_select_db("wordpress");
$result = mysql_query("SELECT * FROM wp_member");

$out = '';

// Get all fields names in table "wp_member" in database "wordpress".
$fields = mysql_list_fields($database,$table);

// Count the table fields and put the value into $columns.
$columns = mysql_num_fields($fields);

// Put the name of all fields to $out.
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$out .= '"'.$l.'",';
}
$out .="\n";

// Add all values in the table to $out.
while ($l = mysql_fetch_array($result)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="\n";
}

// Open file export.csv.
$f = fopen ('export.csv','w');

// Put all values from $out to export.csv.
fputs($f, $out);
fclose($f);

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');
?>

Friday, August 17, 2012

How to develop a website in Hindi Gujarati language

Step : 1 -> Create Database: `language`

Step : 2 -> Create Table
                  CREATE TABLE IF NOT EXISTS `lan` (
                          `id` int(11) NOT NULL AUTO_INCREMENT,
                          `hindi` mediumtext COLLATE utf8_bin NOT NULL,
                           PRIMARY KEY (`id`)
      ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

Step : 3 -> Index.php
                 <html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>How to develop a website in Hindi Gujarati language</title>    
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

      // Load the Google Transliterate API
      google.load("elements", "1", {
            packages: "transliteration"
          });

      function onLoad() {
        var options = {
            sourceLanguage:
                google.elements.transliteration.LanguageCode.ENGLISH,
            destinationLanguage:
                [google.elements.transliteration.LanguageCode.HINDI],
               // if u want Gujarati Typing Please Replace HINDI With GUJARATI
            shortcutKey: 'ctrl+g',
            transliterationEnabled: true
        };

        // Create an instance on TransliterationControl with the required
        // options.
        var control =
            new google.elements.transliteration.TransliterationControl(options);

        // Enable transliteration in the textbox with id       
control.makeTransliteratable(['hindi']);
      }
      google.setOnLoadCallback(onLoad);
    </script>
</head>


<?php

mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("language") or die(mysql_error());

mysql_query('SET character_set_results=utf8');
mysql_query('SET names=utf8');
mysql_query('SET character_set_client=utf8');
mysql_query('SET character_set_connection=utf8');
mysql_query('SET character_set_results=utf8');

if($_SERVER['REQUEST_METHOD']=="POST")
{
$hindi = addslashes($_POST['hindi']);
$qry = "INSERT INTO lan (hindi) VALUES ('".$hindi."')";
mysql_query($qry) or die(mysql_error());
}

$sql = "SELECT * FROM lan";
$rs = mysql_query($sql) or die(mysql_error());

?>

<table width="100%" border="1">
<tr>    
        <td>Hindi Content</td>       
    </tr>
    <?php while($row = mysql_fetch_assoc($rs)){?>
    <tr>    
        <td><?php echo $row['hindi'];?></td>        
    </tr>
    <?php } ?>
</table>

<form method="post">
    Type in Hindi<br>    
    <!-- <textarea id="guj" name="guj" style="width:600px;height:200px"></textarea><br />-->
    <textarea id="hindi" name="hindi" style="width:600px;height:200px"></textarea><br />    
    <input type="submit" />
</form>

Thursday, August 16, 2012

Tinymce editor with Indian Lanugage using google translation


<!-- TinyMCE -->

    <script type="text/javascript" src="../jscripts/tiny_mce/tiny_mce.js"></script>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
        // Load the Google Transliteration API
        google.load("elements", "1", {
            packages: "transliteration"
        });
    </script>
    <script type="text/javascript">

        tinyMCE.init({
            mode: "textareas",
            theme: "advanced",
            plugins: "translitration,pagebreak,style,layer,table,save,advhr,advimage,advlink,emotions,iespell,inlinepopups,insertdatetime,preview,media,searchreplace,print,contextmenu,paste,directionality,fullscreen,noneditable,visualchars,nonbreaking,xhtmlxtras,template,wordcount,advlist,autosave",
         
// Theme options
theme_advanced_buttons1 : "save,newdocument,|,bold,italic,underline,strikethrough,|,justifyleft,justifycenter,justifyright,justifyfull,styleselect,formatselect,fontselect,fontsizeselect,google",
theme_advanced_buttons2 : "cut,copy,paste,pastetext,pasteword,|,search,replace,|,bullist,numlist,|,outdent,indent,blockquote,|,undo,redo,|,link,unlink,anchor,image,cleanup,help,code,|,insertdate,inserttime,preview,|,forecolor,backcolor",
theme_advanced_buttons3 : "tablecontrols,|,hr,removeformat,visualaid,|,sub,sup,|,charmap,emotions,iespell,media,advhr,|,print,|,ltr,rtl,|,fullscreen",
theme_advanced_buttons4 : "insertlayer,moveforward,movebackward,absolute,|,styleprops,|,cite,abbr,acronym,del,ins,attribs,|,visualchars,nonbreaking,template,pagebreak,restoredraft,visualblocks",
theme_advanced_toolbar_location : "top",
theme_advanced_toolbar_align : "left",
theme_advanced_statusbar_location : "bottom",
theme_advanced_resizing : true,
        });
    </script>


 <textarea name="content" style="width: 100%" rows="40"></textarea>

Tinymce In Gujarati, Hindi Language


Step 1 : Download Tinymce From this side
                            http://www.tinymce.com/download/download.php

Step 2 : Download PramukhIME TinyMCE Plugin   
                            http://www.vishalon.net/PramukhIME /TinyMCEPlugin.aspx

Step 3 :   Plugin Installation:
                 1. Unzip the file on your local hard disk.
                 2. Upload "pramukhime" folder to TinyMCE_ROOT/plugins/

Step 4 :    This Javascript Add in Your Editor Page   
                   <!-- TinyMCE -->
                      <script type="text/javascript" src="../jscripts/tiny_mce/tiny_mce.js"></script>
                     <script type="text/javascript">
                          tinyMCE.init({
                               theme : "advanced",
                               language : "en",
                               mode : "textareas",
                               safari_warning : false,
                               plugins : "advlink, advimage,  preview, visualchars, media, nonbreaking,           pramukhime",
                              theme_advanced_buttons3_add : "styleprops, visualchars, media, nonbreaking, pramukhime",
                              debug : false,
                              fullscreen_settings : {
                                     theme_advanced_path_location : "top"
                             }
                         });
                  </script>


Step 5  : Enjoy :)

Tuesday, August 14, 2012

How to use Curl with json (API)


Step - 1 --> index.php
<?php
  $url = 'http://your_domain_name.com/api.php';
 
  $ch = curl_init();
  curl_setopt($ch, CURLOPT_URL, $url);
  curl_setopt($ch, CURLOPT_POST, 1);
  curl_setopt($ch, CURLOPT_POSTFIELDS, 1);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

  $result = curl_exec($ch);
  $info = curl_getinfo($ch);
  curl_close($ch);
  $data = json_decode($result, true);
  echo "<pre>";
  print_r($data);
  echo "</pre>";
?>

Step - 2 --> api.php

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("medical",$con) or die("database Error".mysql_error());

$aData = getAll();

if (count($aData))
 {
echo json_encode(array('data' => $aData));
} else {
echo json_encode(array('data' => 'Nothing found'));
}

 function getAll()
 {
        $query = "SELECT * FROM user";
if (! $query)
            return array();

        $res = mysql_query($query) or die("Query Error".mysql_error());
        $arr_res = array();
        if ($res) {
            while ($row = mysql_fetch_assoc($res))
                $arr_res[] = $row;
            mysql_free_result($res);
        }
        return $arr_res;
 }
?>

Friday, July 20, 2012

MySQL LEFT, RIGHT JOIN tutorial

For example we have two tables: products and buyers with the following structures.
Table products:
mysql> SELECT * FROM products;
+----+--------------+--------------+
| id | product_name | manufacturer |
+----+--------------+--------------+
|  1 | Shoes        | Company1     |
|  2 | Laptop       | Company2     |
|  3 | Monitor      | Company3     |
|  4 | DVD          | Company4     |
+----+--------------+--------------+
4 rows in set (0.00 sec)
Table buyers:
mysql> SELECT * FROM buyers;
+----+------+------------+----------+
| id | pid  | buyer_name | quantity |
+----+------+------------+----------+
|  1 |    1 | Steve      |        2 |
|  2 |    2 | John       |        1 |
|  3 |    3 | Larry      |        1 |
|  4 |    3 | Michael    |        5 |
|  5 | NULL | Steven     |     NULL |
+----+------+------------+----------+
5 rows in set (0.00 sec)

Left Join

mysql> SELECT buyer_name, quantity, product_name FROM buyers LEFT JOIN products ON
 buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve      |        2 | Shoes        |
| John       |        1 | Laptop       |
| Larry      |        1 | Monitor      |
| Michael    |        5 | Monitor      |
| Steven     |     NULL | NULL         |
+------------+----------+--------------+
5 rows in set (0.00 sec)

What happened?
Mysql starts with the left table (buyers). For each row from the table buyers mysql scans the table products, finds the id of the product and returns the product name. Then the product name is joined with the matching row from the table buyers. For unmatched rows it returns null.
To make it simpler, the above query is same as (except the unmatched rows are not returned):
mysql> SELECT buyers.buyer_name, buyers.quantity, products.product_name FROM buyer
s,products WHERE buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve      |        2 | Shoes        |
| John       |        1 | Laptop       |
| Larry      |        1 | Monitor      |
| Michael    |        5 | Monitor      |
+------------+----------+--------------+
4 rows in set (0.00 sec)

Right Join

mysql> SELECT buyer_name, quantity, product_name FROM buyers RIGHT JOIN products ON 
buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve      |        2 | Shoes        |
| John       |        1 | Laptop       |
| Larry      |        1 | Monitor      |
| Michael    |        5 | Monitor      |
| NULL       |     NULL | DVD          |
+------------+----------+--------------+
5 rows in set (0.00 sec)
What happens here is Mysql starts with the Right table (products). For each id from the table products MySQL scans the left table - buyers to find the matching pid. When it finds the matching pid it returns the buyer_name and the quantity. For unmatched rows it returns null. From my example above it returns NULL for DVD because no one bought DVD.
rathoddhirendra.blogspot.com-Google pagerank and Worth