How to Create Tables and Insert Data into MYSQL Databases

This post will show you how to create   a new table in MYSQL Database and then Insert new data into the table.

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

insertAccessTable.php

<?php

 
// Include config file
include ("C:/xampp/htdocs/dbconnection.php");


if ($dbSuccess) {

        // Table Definition 
		$tableName = "accessControl";	
        // Declare Table Fields
		$tableField = array('accessLevel','userType' );
		// size of Table
		$numFields = sizeof($tableField);
		// Create the Querries
	    // Table Field include ID , accessLevel ,userType
        $createTable_SQL ="CREATE TABLE $tableName (ID INT( 11 ) NOT NULL  AUTO_INCREMENT,accessLevel INT( 11 ) NOT NULL ,userType VARCHAR( 50 ) NOT NULL ,PRIMARY KEY ( ID ) ,UNIQUE (accessLevel ,userType))";

        // Data to be Inserted
		
		
		
		   // Explode the Data when  detect "," and store into individual Array
		   // $tableData[0][0]  ,  $tableData[0][1]
		    $tableData[0] = explode(",", "99,Administrator");
			$tableData[1] = explode(",", "21,Editor");
			$tableData[2] = explode(",", "11,Registered User");
			
			$numRows = sizeof($tableData);
			
			
			$indx = 0;	
			
			$normalised_table_SQLinsert_1 ="";
			
			$queryArray =array();
			
			$table_SQLinsert_1 = 'INSERT INTO '.$tableName .'(';
			
			
		// Create a New Table ---------------------------------
			
				
	     // Execute Data
             
           if ($dbConnectionStatus->query($createTable_SQL))  {  
                        echo 'Add Table Successful<br /><br />';
                   } 
           else {
                       echo '<span style="color:red; ">FAILED to  Add Table.</span><br /><br />';
                          
                  }
				  
	
			
			
			
			
			// Get the Field Setting
			foreach($tableField as $tableFieldName) {
				
				// Insert the TableField VALUES
				   $table_SQLinsert_1 .=  $tableFieldName;
				
				
				// If Last Array Value ("userType") not equal to tableField[1]
				if($tableFieldName <> $tableField[$numFields-1]) {
					// concatenate  comma ", "to the String
					$table_SQLinsert_1 .=  ",";
				}
		  
			
			}
			// Contruct the Field string
			
			     $table_SQLinsert_1 .=  ") VALUES(";
				 
			     $normalised_table_SQLinsert_1 = $table_SQLinsert_1;
				  
			
			
			
		   // Get  the field Value that you want to Insert 
		          // Joint the Value
				  
				     // Push the Querries  into the Arrays....
					 
	       // Loop Through 1st Dimension Array $tableField , Length is $numRows ....
		   
		   while($indx < $numRows) {			
				
				// Loop Through the Second Inner Dimension , Array 
				     // $key  -> is the index Key for the Second Inner Dimension Array
					 
				foreach($tableField as $key => $tableFieldName) {
					
				    // loop  $tableData  ->  $tableData[0][0]  following     $tableData[0][1]   ... to tableData[2][1]               
					//$temp = $tableData[$indx][$key];
					// Concatenate the tableData
					   $table_SQLinsert_1 .=  "'".$tableData[$indx][$key]."'";
					
					// Check if the Second Inner Dimension not equal to the last Array VALUES
					
				  // If Last Array Value ("userType") not equal to tableField[1]
					
					if($tableFieldName <> $tableField[$numFields -1]) {
						
						
						 $table_SQLinsert_1 .= ",";
						
					}
					
				
					
					
				}
				
				$table_SQLinsert_1 .=  ") ";
				
				// Push Querry String into Array
				
				array_push($queryArray,$table_SQLinsert_1);
				
				echo $table_SQLinsert_1;

				 echo '<br>';
				 
				 // Normalise $table_SQLinsert_1 Variable
				$table_SQLinsert_1 = $normalised_table_SQLinsert_1;
				 
				
				$indx++;
			}
			
			
			// Loop through the Arrays and Execute the Queriies
			foreach($queryArray as $querries){
				
				echo $querries.'<br>';
			   $dbConnectionStatus->query($querries);
			
				
			}
		   
	




}

?>