Storing contents of a PHP array in a MySQL database

Question

I am having trouble storing array contents in mysql I am using the code below

foreach($found_entry as $key => $sd) {
         echo "$key => $sd <br>";
$insert_found_row = mysql_query("INSERT INTO myTable values ID = \"$sd[0]\" ,Folder = \"NULL\", Tsi = \"$sd[11]\", PT= \"NA\" ") or die(mysql_error());  
            echo "$sd";

If I echo the values I get

0 => ST10928
1 =>
2 => 2010-02-19 03:37:16
3 => \\fs1\
4 =>
5 =>
6 =>
7 =>
8 => M1
9 =>
10 =>
11 => LOG1.TXT 

Answer 1

Your query syntax is wrong. Use SET instead of VALUES. Also, you probably don't need the foreach loop at all, as you are referencing both $sd[0] and $sd[11]. Try something like this:

$query = sprintf("INSERT INTO
             myTable
         SET
             ID = '%s',
             folder = NULL,
             Tsi = '%s',
             PT = 'NA'",
             mysql_real_escape_string($found_entry[0]),
             mysql_real_escape_string($found_entry[11])
         );

mysql_query($query) or die(mysql_error());

Answer 2

If I use $sd[0], only the first character is getting displayed.

$sd is the value (e.g. "ST10928") of your array, but you're treating the value as an array itself, so $sd[0] must return only the first element (= letter).

If you're trying to insert values from your array into the database (instead of iterating through the array and slicing the values), maybe you should use $found_entry[0] instead of $sd[0] and $found_entry[11] for $sd[11] ??

Answer 3

Adding data to MySQL with a PHP Array

Inserting data into MySQL

In your code you are performing an insert for every field.

However, in most cases you know the field names before hand and so you can do a single INSERT with all of the fields.

$sql = 'INSERT INTO tbl_name (field_a,field_b,field_c) VALUES('.$found_entry['field_a'].','.$found_entry['field_b'].','.$found_entry['field_c'].');';

This cuts down on the number of queries required and so makes your code run faster and use less resources.

Default values

You can pass NULL when a value is not known and the DB should insert a default value.