PHP insert MySQL Loop help

PHP
1

Hi.

I am basically a designer I have not very good skill at programming. I am trying to insert multiple rows into database for that I need some help.

This is how my form looks;

<label for=“lang_eng2”>Pashto:
<input name=“language” type=“hidden” id=“language” value=“Pashto” />
</label>
<select name=“reading” id=“reading”>
<option value=“fair”>Fair</option>
<option value=“good” selected=“selected”>Good</option>
<option value=“excellent”>Excellent</option>
</select>
<select name=“speaking” id=“speaking”>
<option value=“fair”>Fair</option>
<option value=“good” selected=“selected”>Good</option>
<option value=“excellent”>Excellent</option>
</select>
<select name=“writing” id=“writing”>
<option value=“fair”>Fair</option>
<option value=“good” selected=“selected”>Good</option>
<option value=“excellent”>Excellent</option>
</select>

This is database query I use to enter to enter single record.

          INSERT INTO  `jobs`.`languages` (
            `language` ,
            `reading` ,
            `speaking` ,
            `writing`
            )
            VALUES (
            'English',  'Good',  'Good',  'Good'
            );

Thank you very much.

2

What extension are you using for interacting with MySQL: the mysqli_* extension or PDO?

3

I think I am not using any extensions. I use plain mysql_query procedure.

4

When you have after the name of an input in the form, what is being passed for each name is an array.

If you added this to the top of your page

echo "<pre>";
print_r($_POST);
echo "</pre>";

You would see that this array like so.

Array
(
    [language] => Array
        (
            [0] => Pashto
        )

    [reading] => Array
        (
            [0] => good
        )

    [speaking] => Array
        (
            [0] => excellent
        )

    [writing] => Array
        (
            [0] => fair
        )

    [submit] => Submit
)

Notice that the array key is zero in this example. If you were looping those form inputs, with each loop the key would go up incrementally, 1,2,3 etc.
For processing, you can get the values of each of these by using a foreach statement and using the same key to build a values array that could then be used to insert into database.

foreach($_POST['language'] as $key => $language){
	$reading = $_POST['reading'][$key];
	$speaking = $_POST['speaking'][$key];
	$writing = $_POST['writing'][$key];
	$values[] = "('$language','$reading','$speaking','$writing')";
}

You can then implode this values array like so.

$sql = "INSERT INTO `jobs`.`languages` (
`language` ,
`reading` ,
`speaking` ,
`writing`
)
VALUES ";
      $sql .= implode(",",$values);	
	
	 // Test
	  echo "&lt;br /&gt;$sql&lt;br /&gt;";

Here’s the complete test page.

&lt;?php
echo "&lt;pre&gt;";
print_r($_POST);
echo "&lt;/pre&gt;";

foreach($_POST['language'] as $key =&gt; $language){
	$reading = $_POST['reading'][$key];
	$speaking = $_POST['speaking'][$key];
	$writing = $_POST['writing'][$key];
	$values[] = "('$language','$reading','$speaking','$writing')";
}
$sql = "INSERT INTO `jobs`.`languages` (
`language` ,
`reading` ,
`speaking` ,
`writing`
)
VALUES ";
      $sql .= implode(",",$values);	
	
	 // Test
	  echo "&lt;br /&gt;$sql&lt;br /&gt;";
?&gt;
&lt;html&gt;
&lt;body&gt;
	&lt;form action="" method="post"&gt;
		&lt;label for="lang_eng2"&gt;Pashto:
		&lt;input name="language[]" type="hidden" id="language[]" value="Pashto" /&gt;
		&lt;/label&gt;
		&lt;select name="reading[]" id="reading[]"&gt;
			&lt;option value="fair"&gt;Fair&lt;/option&gt;
			&lt;option value="good" selected="selected"&gt;Good&lt;/option&gt;
			&lt;option value="excellent"&gt;Excellent&lt;/option&gt;
		&lt;/select&gt;
		&lt;select name="speaking[]" id="speaking[]"&gt;
			&lt;option value="fair"&gt;Fair&lt;/option&gt;
			&lt;option value="good" selected="selected"&gt;Good&lt;/option&gt;
			&lt;option value="excellent"&gt;Excellent&lt;/option&gt;
		&lt;/select&gt;
		&lt;select name="writing[]" id="writing[]"&gt;
			&lt;option value="fair"&gt;Fair&lt;/option&gt;
			&lt;option value="good" selected="selected"&gt;Good&lt;/option&gt;
			&lt;option value="excellent"&gt;Excellent&lt;/option&gt;
		&lt;/select&gt;
		&lt;input type="submit" name="submit" value="Submit" /&gt;
	&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;
5

To further explain the array keys, I added a loop to the form fields in this example. Again looking at the print POST you will see matching array keys for each loop of post that match all input names.

Array
(
    [name] => Array
        (
            [0] => Bob
            [1] => Carol
            [2] => Ted
            [3] => Alice
        )

    [language] => Array
        (
            [0] => Pashto
            [1] => Pashto
            [2] => Pashto
            [3] => Pashto
        )

    [reading] => Array
        (
            [0] => fair
            [1] => good
            [2] => excellent
            [3] => fair
        )

    [speaking] => Array
        (
            [0] => good
            [1] => excellent
            [2] => good
            [3] => excellent
        )

    [writing] => Array
        (
            [0] => excellent
            [1] => good
            [2] => fair
            [3] => good
        )

    [submit] => Submit
)

Here’s the sample page.

&lt;?php
echo "&lt;pre&gt;";
print_r($_POST);
echo "&lt;/pre&gt;";
if(isset($_POST['submit'])){
	foreach($_POST['language'] as $key =&gt; $language){
		$reading = $_POST['reading'][$key];
		$speaking = $_POST['speaking'][$key];
		$writing = $_POST['writing'][$key];
		$values[] = "('$language','$reading','$speaking','$writing')";
	}
	$sql = "INSERT INTO `jobs`.`languages` (
	`language` ,
	`reading` ,
	`speaking` ,
	`writing`
	)
	VALUES ";
	$sql .= implode(",",$values);	
	
	// Test
	echo "&lt;br /&gt;$sql&lt;br /&gt;";	
}
?&gt;
&lt;html&gt;
&lt;body&gt;
	&lt;form action="" method="post"&gt;
	&lt;?php
	//An example of form inputs in a loop.  This could be a query loop.
	$names = array("Bob","Carol","Ted","Alice");
	foreach($names as $name):
	?&gt;	
		&lt;label for="name"&gt;Name: &lt;?php echo $name;?&gt;
		&lt;input name="name[]" type="hidden" id="name[]" value="&lt;?php echo $name;?&gt;" /&gt;
		&lt;/label&gt;
		&lt;label for="lang_eng2"&gt;Pashto:
		&lt;input name="language[]" type="hidden" id="language[]" value="Pashto" /&gt;
		&lt;/label&gt;
		&lt;select name="reading[]" id="reading[]"&gt;
			&lt;option value="fair"&gt;Fair&lt;/option&gt;
			&lt;option value="good" selected="selected"&gt;Good&lt;/option&gt;
			&lt;option value="excellent"&gt;Excellent&lt;/option&gt;
		&lt;/select&gt;
		&lt;select name="speaking[]" id="speaking[]"&gt;
			&lt;option value="fair"&gt;Fair&lt;/option&gt;
			&lt;option value="good" selected="selected"&gt;Good&lt;/option&gt;
			&lt;option value="excellent"&gt;Excellent&lt;/option&gt;
		&lt;/select&gt;
		&lt;select name="writing[]" id="writing[]"&gt;
			&lt;option value="fair"&gt;Fair&lt;/option&gt;
			&lt;option value="good" selected="selected"&gt;Good&lt;/option&gt;
			&lt;option value="excellent"&gt;Excellent&lt;/option&gt;
		&lt;/select&gt;&lt;br /&gt;
	&lt;?php
	endforeach;
	?&gt;
		&lt;input type="submit" name="submit" value="Submit" /&gt;
	&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;

And the Values array for query now holds four sets of values.

INSERT INTO `jobs`.`languages` ( `language` , `reading` , `speaking` , `writing` ) VALUES ('Pashto','fair','good','excellent'),('Pashto','good','excellent','good'),('Pashto','excellent','good','fair'),('Pashto','fair','excellent','good')
6

Drummin, it worked like a charm. Thank you very much.