Insert into table using arrays for field names and values

[Maarten]

I've found some answers to my problem on this forum, but not exactly the answer I was looking for. Sorry if I've missed something.

This is my situation:

I am trying to make an insertion into an MySQL table, using an array for the field list AND another array for the values. I know by know that you can't just use:

INSERT INTO table_name ($array1) VALUES ($array2);

So i've stripped the arrays first. My problem however is that I can only strip one array at the time, so this:[php]foreach ($array as $value)
mysql_query("INSERT INTO table_name VALUES ($array)");[/php]would work, but how can I define the field names, wich are also in a array?

I tried the following, but it doesn't work (didn't really expect it to also)
[php]foreach ($array1 as $value)
foreach ($array2 as $value)
INSERT INTO table_name ($array1) VALUES ($array2);[/php]
I hope you understand this poor explanation. Also, I wasn't sure where to post it (PHP or MySQL), but since my problem seemed more of a PHP issue I thought i'd start here.

[ronverdonk]

Welcome to The Scripts!

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[ronverdonk]

Important question on this:
Are the entries of both arrays 1-1, i.e. do the indices of related entries have the same number in both arrays? E.g. is $array1[4] related to $array2[4]?

When this is true, you can just implode() each array and use the result in your select statement. Sample[php]for ($i=0; $i<count($array1);$i++) {
$fields=implode(',', $array1[$i]);
$values="'".implode("','", $array2[$i])."'";
$sql="INSERT INTO table_name ($fields) VALUES ($values)";
$res=mysql_query($sql)
or die("Insert error: ".mysql_error());
}[/php]
Ronald

[Maarten]

Sorry about that, I will pay attention to it next time.

Thank you very much for your help Ronald, I'm going to try that right away and keep you posted!

[ronverdonk]

Important question on this:

Are the entries of both arrays 1-1, i.e. do the indices of related entries have the same number in both arrays? E.g. is $array1[4] related to $array2[4]?

But you can only test that sample when the answer to my question (see quote) is affirmative.

Ronald

[Maarten]

But you can only test that sample when the answer to my question (see quote) is affirmative.

Ronald

I'm sorry, forgot to answer that. But the answer is indeed affirmative.

I'm trying to write a content management system, and I've got a form that sends 125 values at the same time. To keep things a bit organised and clear, I thought i'd put those values in 8 different arrays, than merge those arrays. but I didn't know you can't do an Insert from an array (I'm not that advanced yet).

So if I understand correctly, your code converts my array to a string, wich I CAN insert into the db. Genius, thank you very much.

A bit off-topic maybe, I've never quite understood why you have to place the dots in your MySQL statement. In your example:

[PHP]or die("insert error: ".mysql_error());[/PHP]

I mean the dot before mysql_error.

Thanks a lot for your help and patience so far, you really helped me out here

[ronverdonk]

First the dots. The dot is a concatenation character that connects fields, variables etc. Examples:[php]you want to print a string 'ABCDEF' from 2 different literals ABC and DEF:
echo 'ABC' . 'DEF';

You want to combine the content of 2 variables into 1 variable:
$new_var = $var1 . $var2;

You want to print out a constant value and a function result:
die ("MySQL error: " . mysql_error());

You want to append a string to the end of an existing string:
$string .= "new text";[/php]Ronald

[ronverdonk]

About your 125 form fields. Why do you store them in separate arrays? I.e. why don't you use the existing, and already used by your form, $_POST array?

E.g. when you have form fields with name 'myname1 , 'myname2'' and the values 'myvalue1' and 'myvalue2' this shows in the $_POST array like

Array {
          [myname1] => myvalue1
          [myname2] => myvalue2
      }

And you can walk that $_POST array with[php]foreach ($_POST as $name => $value) {
// here you have the form field name and the value
// so you can build your SQL string here
}[/php]Ronald

[Maarten]

I think the only appropriate answer would be; lack of knowledge. But thanks a lot for the lead. I'm not sure if I fully understand it yet, but I'm gonna look into it straight away!

[ronverdonk]

I think the only appropriate answer would be; lack of knowledge. But thanks a lot for the lead. I'm not sure if I fully understand it yet, but I'm gonna look into it straight away!

Any time you need help on this, don't hesitate to ask for mit and show the code here (within code tags please).

I propose this, becaus it seems a waste of a lot of lines of code with all these arrays you were talking about.

Ronald

[Maarten]

Any time you need help on this, don't hesitate to ask for mit and show the code here (within code tags please).

I propose this, becaus it seems a waste of a lot of lines of code with all these arrays you were talking about.

Ronald

That sounds good of course, but I would still have to make a foreach statement for every field name if I understand correctly. So wouldn't I end up with 125 foreach statements?

[Maarten]

That sounds good of course, but I would still have to make a foreach statement for every field name if I understand correctly. So wouldn't I end up with 125 foreach statements?

Nevermind, I get it! It's so simpleyet so clever and I'd never thought about it if you hadn't pointed it out for me. Learned a lot within a day!

[Maarten]

Okay, so I tried what you said, and it works, but...

Now it's adding a new row for each value that I send with the form. This is my code:

[PHP]if (isset($_POST['submit'])) {

if ($dbc=@mysql_connect('localhost', 'x', 'x')){

print "Connected to db!<p />";

//select database
if (@mysql_select_db ('x')) {

print "db selected<p />";

}else {

die('Could not select that database because'.mysql_error().'<p />');
}

//Insert data from into the database
foreach ($_POST as $name => $value) {
mysql_query("INSERT INTO table ($name) VALUES ($value)");

}

mysql_close();
}else {

die('Could not connect to MySQL because:'.mysql_error.'<p />');
}
}[/PHP]


How can I make sure that the values per submitted form will all be inserted in one row?

[ronverdonk]

No, that way you have 125 inserts. Just do as was shown in the beginning, but now you make

1. an array of the keys in the $_POST array and implode them
2. an array of the values in the $_POST array and implode them

See this sample:[php]$fields=implode(',', array_keys($_POST));
$values="'".implode("','", array_values($_POST))."'";
$sql="INSERT INTO table_name ($fields) VALUES ($values)";
$res=mysql_query($sql)
or die("Insert error: ".mysql_error());[/php]All field names and corresponding values inserted in one statement. Try it out.

Ronald

[Maarten]

Works like a charm!

My question has totally been answered. Just have to figure out a way to filter the submit value out of the array_keys, since my script now wants to have a column for submit.

But I'll manage to do that. Thanks again for your help! (and patience), I really learned a lot!

[ronverdonk]

Welcome any time. See you around.

Ronald