Inserting a lot of info into a database?

PHP
1

Alright, so i have to insert a lot (20 rows) of information into the database. I tried putting all the post variables into an array and tried a foreach statement



$insert_query = 'insert into info values(';

foreach($post_variables as $field){
       $insert_query .= '"'.$value.'",';
       };
$insert_query .= ')';

this did not work…could someone please tell me why it didn’t work? And give me a way to do this correctly? thanks!

2

sorry, can’t help you with the php, as this is the database forum

what you want the php to generate is this –


INSERT
  INTO tablename
     ( [I]list,of,columns[/I] )
VALUES
  ( [I]list,of,values[/I] ) 
, ( [I]list,of,values[/I] ) 
, ( [I]list,of,values[/I] ) 
, ...

if it’s really just the php you want help with, click on the little red flag at the left under your avatar and stats, and ask a moderator to move this thread to the php forum

3

If you have an INSERT statement you should also define each field that the data should be added for. For example, INSERT INTO info(field1, field2, field3, …, fieldn) VALUES (v1, v2, v3, …, vn).

Looking at your PHP code you are referencing $value when this does not appear to be set anywhere? Should you code be doing


foreach($post_variables as $field => $value) {
  $insert_query .= '"' . $value . '",';
}

Also you need to take care with the value of $value variable. If this contains a double quote then your code may end up doing something very different. I suggest you should escape any double quotes if you are creating your SQL in this way.

4

What do you mean by that? How could it have double quotes?

5

Alright, so i have to insert a lot (20 rows) of information into the database. I tried putting all the post variables into an array and tried a foreach statement

$insert_query = 'insert into info values(';

foreach($post_variables as $field => $value){
       $insert_query .= '"'.$value.'",';
       };
$insert_query .= ')';
echo $insert_query;

this did not work…could someone please tell me why it didn’t work? And give me a way to do this correctly? thanks!

6

A lot of rows? Or a lot of columns?
The code you posted creates a query that will insert 1 row with a number of column values.

And what does ‘did not work’ mean? Do you get an error? If so, what error?

And if you look closely at the query that is displayed by the echo statement, you should notice there is one , too many…
Hint: take a look at implode()

7

If your database table has these fields:


mytable
======
id (auto-incrementing integer)
first_name 
last_name

To add say 2 new rows from an array, say:


$people[0]['first'] = "Joe";
$people[0]['last'] = "Bloggs";
$people[1]['first'] = "John";
$people[1]['last'] = "Doe";

You have to build a string which, as r937 says looks like this:


insert into mytable (first_name, last_name) 
values 
('Joe', 'Bloggs'), 
('John','Doe');

So that is what you have to get PHP to do for you.

You should echo the resulting string out onto the screen as you go, to check it against what you think it should be AND to then paste it into your database to make sure it really is a valid sql statement.

Bear in mind, if you have other columns in the target table without a default value, and you do not set one, this will cause an sql error.

If your id (in the above example table) is not of the kind “auto-increment” then it will cause an sql error.

Any other typo or syntax error on your part could cause an sql error.

With an auto-incrementing field, this will also work:


insert into mytable (id, first_name, last_name) 
values 
(0, 'Joe', 'Bloggs'), 
(0, 'John','Doe');