php+mysql: insert a php array into mysql

Question 1

I have an array with 30000 plus entries that need to go into a MySQL table.

What is the best practice? From here? Lets say [0], [1] and [2] in the database would be 'title', 'type' and 'customer'

Is it add key names matching the column names in the table and call som "magic" function? Or build the query manually...

Array
(
    [0] => Array
        (
            [0] => 2140395946
            [1] => 1SAP
            [2] => 0041451463
        )

    [1] => Array
        (
            [0] => 2140411607
            [1] => 2SAP
            [2] => 0041411940
        )

    [2] => Array
        (
            [0] => 2140706194
            [1] => 4SAP
            [2] => 0041411943
        )
etc. etc.

UPDATE - based on answers

Thanks for the answers.

The solution would normally be to manually create the SQL-string and all rows can be inserted as one:

INSERT INTO `tx_opengate_table` (`machine` ,`customer` ,`type`)
VALUES
  ('m123', 'dfkj45', 'A'),
  ('m137', 'kfkj49', 'A'), "repeat this line for each entry in the array"
  ... ... ...
  ('m654321', '34dgf456', 'C4') "end with out comma, or remove last comma"
;

Special for TYPO3

I happen to do this in the CMS TYPO3 and just came across a new function added not that long ago:

//Insert new rows
$table = 'tx_opengate_stuff';
$fields = array('machine','type','customer');
$lines = "array as given above"
$GLOBALS['TYPO3_DB']->exec_INSERTmultipleRows($table,$fields,$lines);

Question 2

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Question 3

there is another way

      $family=array()
     while($row = mysql_fetch_array($selected)){
           $familyId = $row[0];
           $familyName = $row[1];

      $family[]=$familyName.$familyId;            

    }

 $insertInfo = "INSERT INTO `family_info`.`info` 
            (`name`, `info`, `family`)
            VALUES (
            '$name', '$info', '$family');";

Question 4

Your main issue is, declaring $query outside the loop. It makes it a constant, and that too, takes values $a and $b which are that time, undefined, so results in invalid syntax for SQL.

foreach($Cust_PN as $a => $b) {
   $query1 = "INSERT INTO SO_Items (Timestamp, SO_Num, SO_Rev, SO_Line_Item, Cust_PN, Cust_PN_Rev, My_PN, My_PN_Rev, Description, Qty, Sale_Price, UOM, Program, Required_Date) VALUES (NOW(), '$SO_Num', '$SO_Rev', '$SO_Line_Item[$a]', '$Cust_PN[$a]', '$Cust_PN_Rev[$a]', '$My_PN[$a]', '$My_PN_Rev[$a]', '$Description[$a]', '$Qty[$a]', '$Sale_Price[$a]', '$UOM[$a]', '$Program[$a]', '$Required_Date[$a]');";

   $q = mysql_query($query1) or die ('Error posting data');

 }

Try that. Fixed your SQL query too. Correct syntax is

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

You had put SELECT in place of VALUES

Let me know if it works, and otherwise please tell what error it ives exactly.

Question 5

Simple solution:

$sql = "INSERT INTO questions (`question_name`) VALUES (:question_name)";
// prepare a stamemnt only once
$stmt = $db->prepare($sql);
$stmt->bindParam(':question_name', $question_name);
// iterate over your POST[question_name] array
foreach ($_POST['question_name'] as $question_name) {
    $stmt->execute();
}

Question 6

If you are simply wanting to store an array in a MySQL Field for later retrieval, then you could use implode()[PHP Docs] as suggested above (which will destroy the array's keys, but retain the array's values), or serialize()[PHP Docs] which will retain both the values and the associated keys.

$theArray = array(
  'key1' => 'One' ,
  'key2' => 'Two'
);
$serArray = serialize( $theArray ); // a:2:{s:4:"key1";s:3:"One";s:4:"key2";s:3:"Two";}
$sqlStr = 'INSERT INTO `table` ( `name` , `arrayField` ) VALUES ( "Test Row" , "'.$serArray.'" )';

If you are talking about using two related table to store data, then you are probably best advised to refer to tutorials like http://www.sql-tutorial.net/SQL-JOIN.asp, http://www.databasejournal.com/features/oracle/article.php/3527921/Just-SQL-Part-IV--Joining-Tables.htm

php+mysql: insert a php array into mysql

Answers

php,mysql,arrays,insert