Insert all array elements into MySQL database table’s different fields

Posted by Shek on December 28, 2008 under PHP | Read the First Comment | Total View: 20,734 views

Array is a good way to store many elements, in some operations you might like to store all of the array elements into different row of a table in a MySQL database. To store into different row, we can use a for loop to go through each elements and run a query to insert them 1 by 1. This example, I go show you to insert into different fields.

First I have created a MySQL table named “atable” with 4 fields named “name”, “address”, “hobby”, “telephone”.

And an array is created here:

<?php 
$arrayOne[0] = "MrX";
$arrayOne[1] = "1234Street";
$arrayOne[2] = "Football";
$arrayOne[3] = "123456";
?>

As your can see, we are going to insert
“MrX” into “name” field,
“1234Street” into “address” field,
“Football” into “hobby” field,
“123456” into “telephone” field.

In order to do so, we have to know the inserting query:
mysql_query(“INSERT INTO table VALUES (‘value1′,’value2′,’value3′,’value4’)”);

and so here we have to find a way to convert the array into a format of ‘value1′,’value2′,’value3′,’value4’. And replace it into the array.

In my past post Join all elements of an array together and become a string, I show you how to make all the elements of an array into a string and in this case, we could use this to help us.

Now we use the implode( ) function to make all the elements of an array into a string and to put ‘,’ as the separators. We create a variable here to store the string:

<?php 
$arrayOne[0] = "MrX";
$arrayOne[1] = "1234Street";
$arrayOne[2] = "Football";
$arrayOne[3] = "123456";
 
$astring = implode("', '", $arrayOne);
?>

The above operation, we get: MrX’,’1234Street’,’Football’,’123456 which is not quite as the format of ‘value1′,’value2′,’value3′,’value4’, so we have to do a little more here:

1
2
3
4
5
6
7
8
9
<?php 
$arrayOne[0] = "MrX";
$arrayOne[1] = "1234Street";
$arrayOne[2] = "Football";
$arrayOne[3] = "123456";
 
$astring = implode("', '", $arrayOne);
$astringTwo = "'".$astring."'";
?>

So now, we included a at the begging and end of the string and we get the format:
‘MrX’,’1234Street’,’Football’,’123456′
we create another variable $astringTwo to store the newest string.

Now we run the inserting query to complete the job:

1
2
3
4
5
6
7
8
9
10
11
<?php 
$arrayOne[0] = "MrX";
$arrayOne[1] = "1234Street";
$arrayOne[2] = "Football";
$arrayOne[3] = "123456";
 
$astring = implode("', '", $arrayOne);
$astringTwo = "'".$astring."'";
 
mysql_query("insert into atable VALUES (".$astringTwo.")");
?>

Not what you want? Try a Search

  • Bmg said,

    Thanks!

Add A Comment

*