How to Join Two Tables in MySQL

This post  will show you how to joint 2 MySQL Table and print out the results in  the browser

(1) Query & select the column in the 1st Table (“mytable”) that you w ant to joint

(2) Query & select  the Column in the 2nd Table(“mytable1”)that  you want to joint

(3) Joint the Table

(4) Save the Result in an Array

(5) Print out the Array

Prior to that you need to complete  the items listed Below

1. Mamp or xampp with phpMyAdmin install into your Computer , check out here
2.  A Sample Database created Check out Here
3.  New User Created . Check out Here
4. A New table created , check out Here
5. Insert Data into the Table Here
6. Insert Data from a CSV File to  the Newly created table  Here

 

Joint both Table

 

<style>
table, th, td {
  border: 1px solid black;
}
</style>


<?php

/* Declare User connecting Credential*/

$hostName  ="localhost";
$userName ="jane";
$userPassword ="jane";
$database ="janedb";



/* return connection status through $dbConnectionStatus  Boolean Variable*/

/* using mysqli Object*/

$dbConnectionStatus  = new mysqli($hostName, $userName, $userPassword,$database);


/* Check whether the User Succesfully connect to localhost and mydata Database*/
/* ->  Access the instance "$dbConnectionStatus" variable property "connect_error " boolean */

if ($dbConnectionStatus->connect_error){     

        
     die("Connection failed: " . $dbConnectionStatus->connect_error);

}

// If Connection Status is Success 

if($dbConnectionStatus->connect_error==false){
	
// Table Structure	
	
echo'<table style="width:100%">';
  
  echo'<tr>';
     echo'<th>id</th>';
     echo'<th>First Name</th>';
     echo'<th>Company</th>';
     
   echo'</tr>';
 




//Setup Field Name array . value and key
    {	//	setup ARRAY of field names 
		$personField = array(
					'id' => 'id',
					'firstname' => 'firstname',
					'lastname' => 'lastname',
					'email' => 'email',	
					'reg_date' => 'reg_date',	

					
		);
	}
	
	
	// Select the the table columns that you want to joint for the first and second table
	// The First table " mytable "  2 column selected "id" & "firstname"
	// The Second Table " mytable 1" 1 column is selected  " company "
	// Joint both Table when ID equals the same
	$sql = " SELECT mytable.id,mytable.firstname,mytable1.company FROM mytable LEFT OUTER JOIN mytable1 ON mytable.id = mytable1.id";
	// Send Select Query
	$resultData = mysqli_query($dbConnectionStatus,$sql);
	// Declare Array
	$arrayData = array();
	 // Check if results more than 1 row
	 if (mysqli_num_rows($resultData)>0){
		      // while there is still  data in row
			       // assign selected mySQL data into datas array 
		      while($row=mysqli_fetch_assoc($resultData)){
				  
		  
				   $arrayData[] = $row; 
				  
				  
			  }
		 
		 
		 
	 }
	 // Foreach datas  assign to $data
	foreach($arrayData as $data){
		
		echo'<tr>';
       // Search through the array print out value if see the Key  eg: 'id', 'firstname ' etc.
        echo'<td>'.$data['id'].'</td>';
        echo'<td>'.$data['firstname'].'</td>';
        echo'<td>'.$data['company'].'</td>';
        
     
     
    echo'</tr>';
		
		
		
		
		
	}

	
        		
echo'</table>';
	 
								
	}

 
 
  

?>

 

 

Results