• Tweet
• Share

• Tweet
• Share

Inserting an array into a database

A few days ago, a user came into #phphelp on UnderNet wanting to know how to insert an array into a database. A simple enough concept, but how exactly does one look up information on the subject? The goal is to take a form with multiple input sets (like a list of people, their phone numbers, etc.) and input multiple rows into a database.

We’ll start off by creating the table that we’ll insert all our rows into. A very simple table will suffice. All we need to know is the person’s username (`username`) and their phone number (`phonenum`). Here is the query I used to create such a table:

CREATE TABLE `mytable` (

   `username` TEXT,

   `phonenum` INT

)

Now that we have our table set up, we need to create the form to input our users. Create a file called form.php to hold this form. We want to insert multiple users at a time, so we’ll need to be able to send an array. PHP has a built-in method of gathering an array from user input. Whenever you create an input, just append [] to the end of its name. PHP will then interpret each sequentially named input as another item in that array.

With that in mind, we are able to construct our form. Start with a simple number, like three users. There’s no need to make this form very fancy, so we can keep it very easy:

<form method="POST" action="input.php">

<!-- Person #1 -->

<input type="text" name="username[]" />

<input type="text" name="phonenum[]" />

<!-- Person #2 -->

<input type="text" name="username[]" />

<input type="text" name="phonenum[]" />

<!-- Person #3 -->

<input type="text" name="username[]" />

<input type="text" name="phonenum[]" />



<input type="submit" />

</form>

As you can see, all three of the rows are identical. This is the simplest way to get dynamically-generated, user-input arrays. Pretty neat, huh?

Before we attempt to do anything with the database, you may want to check the output of the form. Create a file called input.php, which will parse the form data. To see the output of the form, we’ll use the function print_r(). Place this line in the file:

. If you were to enter consecutive numbers in the fields (i.e. one through six), you will end up without output similar to this:

Array

(

[username] => Array

(

[0] => one

[1] => three

[2] => five

)

[phonenum] => Array

(

[0] => two

[1] => four

[2] => six

)

)

Now that we know how the array is formed after submission, we can begin to parse it into a usable format. For this, we’ll use the foreach. A foreach allows you to iterate through an array, one key at a time. It allows you to assign the current value to a variable, and can also assign the key to a variable. Here is the foreach we will be using to iterate through our array, assigning the username and the phone number to a variable:

foreach ($_POST['username'] as $row=>$name)

{

$username = $name;

$phonenum = $_POST['phonenum'][$row];

}

$username and $phonenum are the variables we have assigned for the username and phone number. While $name was supplied by the foreach, we had to manually assign the value of the phone number based on the key ($row) that the foreach assigned.

Now that we have our variables assigned, we can send them to the database. In an ideal situation, you would employ error checking on the variables. Since the phone number’s field is INT, it should be numeric, and you should always use mysql_real_escape_string() to help protect against SQL injection. However, for ease of this tutorial, I’ll leave things a bit more bear and let you pick up the pieces. The query to send the information is very simple: ‘INSERT INTO `mytable` VALUES(“‘ . $username . ‘”, ‘ . $phonenum . ‘)’. Note that I have separated the variables from the rest of the string by using the concat operator, . (a period). This tends to make things easier to read, and definately speeds up the processing speed of PHP.

Voila, three new rows are entered into the database. Here is the copy of the full foreach from my input.php:

foreach ($_POST['username'] as $row=>$name)

{

$username = mysql_real_escape_string($name);

$phonenum = mysql_real_escape_string($_POST['phonenum'][$row]);

mysql_query(‘INSERT INTO `mytable` VALUES(“‘ . $username . ‘”, ‘ . $phonenum . ‘)’) or die(mysql_error());

}

coditzaaa from #phphelp has offered a way to speed this up, if you have a lot of rows. It will queue the rows up and insert them at a specified interval:

$sql_start = ‘INSERT INTO `mytable` VALUES ‘; // We’ll use this at the beginning of each query

$sql_array = array(); // This is where we’ll queue up the rows

$queue_num = 20; // How many rows should be queued at once?

foreach ($_POST['username'] as $row=>$name)

{

$username = $name;

$phonenum = $_POST['phonenum'][$row];

$sql_array[] = ‘(‘ . $username . ‘, ‘ . $phonenum . ‘)’; // Add a new entry to the queue

if (count($sql_array) >= $queue_num)

{ // We have reached the queue limit

mysql_query($sql_start . implode(‘, ‘, $sql_array)); // Insert those that are queued up

$sql_array = array(); // Erase the queue

}

}

if (count($sql_array) > 0) // There are rows left over

{

mysql_query($sql_start . implode(‘, ‘, $sql_array));

}