Insert array into MySQL database with PHP

Insert array into MySQL database with PHP, in this article i will step through  on how  to  loop through an Array and insert the Data into MySQL Database 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
4. A New table created , check out Here
5. Insert Data into the Table Here
6. Update Data into the Table Here

 

Step 1

Start your My SQL and Apache Server

Create the External Text File

 

11,casper,Lau,Andy@hotmail.com,2020-06-14
12,marian,kok,Aaron@gmail.com,2021-06-14
13,Sam,Hung,sammuel@hmail.com,2022-06-14
14,jen,cheong,Ken@hmail.com,2025-06-14

 

Step 2

Start your My SQL and Apache Server

(1) First to connect to the Database

(2) Open the External File

(3) Loop Through Every line in the external File

(4) Save the Result into an Array

(5) Check whether SQL connection succeeded

(6) If Succeed, Loop Through  and concatenate the SQL Query String “.”  

(7) Execute the Query

(8) Check whether the Query successfully executed.

 

 

<?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){

// Open the External File	
$file = fopen("C:/xampp/htdocs/testFile.txt", "r");

$i=0;

// Loop through all the Line in the External File

while(!feof($file)){
	
	// While is not end of the file continue Loop through
	
	   // Assign the Data into $thisline
	$thisLine = fgets($file);
	    // If detect ","  explode the sentence
	$personData[$i]= explode(",",$thisLine);
	

  
    $i++;
	
}
//Close the file
fclose($file);
// Get the length of the Array
$numRows = sizeof($personData);
//loop through the Array and print out the data

for ($x = 0; $x < $numRows; $x++) {
	
	for ($y = 0; $y <sizeof($personData[$x]); $y++) { echo $personData[$x][$y].","; } echo " "; } //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',	

					
		);
	}

	
// concantenate the SQL Queeries								
								
									
  $index =0;
 while($index<$numRows){ //* Normal Querries Example -> INSERT INTO mytable (id , firstname, lastname,email, reg_date)VALUES(9,'George','keen','keen@hotmail.com','17-04-2020');
	 
	 $person_SQLinsert = "INSERT INTO mytable (
									".$personField['id'].",
									".$personField['firstname'].",
									".$personField['lastname'].",
									".$personField['email'].",
									".$personField['reg_date']."							
									) ";
		
	 $person_SQLinsert .=  "VALUES ";	
	 
	 $person_SQLinsert .= "(
									".$personData[$index][0].",
									'".$personData[$index][1]."',
									'".$personData[$index][2]."',
									'".$personData[$index][3]."',
                                    '".$personData[$index][4]." '
									
									); ";
									
		
	
		//echo  $person_SQLinsert;
	
	    $insertdata = $dbConnectionStatus -> query($person_SQLinsert);
		
		if ($insertdata) {
                 echo "Data inserted into table ";
                 } else {
         echo "Error inserting data into table: " . $dbConnectionStatus->error;
 
           }
		
		// Insert Now
		
		$person_SQLinsert="";
									
	 
								

	$index ++; 


  }
 
 




}
   

?>

Step 3

Save this File as “.php” extension in

C:\xampp\htdocs

Step 4

Test it

(1)Click Admin

(2) Click the  PHP File you have just created  in this example i name it  ” updateDataFrom File.php”

 

Step 5

Check the result