How to insert array of data into mysql using php

Question

Currently I have an Array that looks like the following when output thru print r     Array        0    gt  Array                        R ID    gt  32              email    gt  a a com              name    gt  Bob                 1    gt  Array                        R ID    gt  32              email    gt  b b com              name    gt  Dan                 2    gt  Array                        R ID    gt  32              email    gt  c c com              name    gt  Paul                 3    gt  Array                        R ID    gt  35              email    gt  d d com              name    gt  Mike                 I would like to insert this data into one table with each element value belonging to its respective field   Currently my php code looks like the following  if is array  EMailArr        foreach  EMailArr as  R ID   gt   email   gt   name         sql    INSERT INTO email list  R ID  EMAIL  NAME  values    R ID     email     name         mysql query  sql  or exit mysql error                 Note   R ID is NOT the primary key in this table    Can someone help me understand how I should approach this situation  Thank you for reading and your help   Regards

User · Answer

First of all you should stop using mysql_*. MySQL supports multiple inserting like

INSERT INTO example
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

You just have to build one string in your foreach loop which looks like that

$values = "(100, 'Name 1', 'Value 1', 'Other 1'), (100, 'Name 1', 'Value 1', 'Other 1'), (100, 'Name 1', 'Value 1', 'Other 1')";

and then insert it after the loop

$sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) VALUES ".$values;

Another way would be Prepared Statements, which are even more suited for your situation.

User · Answer

if is array  EMailArr        foreach  EMailArr as  key   gt   value         R ID    int   value  R ID         email   mysql real escape string   value  email           name   mysql real escape string   value  name            sql    INSERT INTO email list  R ID  EMAIL  NAME  values    R ID     email     name         mysql query  sql  or exit mysql error                A better example solution with PDO     q    sql- gt prepare  INSERT INTO  email list                        SET  R ID        EMAIL        NAME           foreach  EMailArr as  value       q - gt execute  array   value  R ID     value  email     value  name

User · Answer

I ve a PHP library which helps to insert array into MySQL Database  By using this you can create update and delete  Your array key value should be same as the table column value    Just using a single line code for the create operation  DB  create  db   YOUR TABLE NAME    dataArray     where  db is your Database connection   Similarly  You can use this for update and delete  Select operation will be available soon  Github link to download   https   github com pairavanvvl crud

User · Answer

I would avoid to do a query for each entry   if is array  EMailArr          sql    INSERT INTO email list  R ID  EMAIL  NAME  values          valuesArr   array        foreach  EMailArr as  row             R ID    int   row  R ID             email   mysql real escape string   row  email               name   mysql real escape string   row  name                valuesArr         R ID     email     name                 sql    implode       valuesArr        mysql query  sql  or exit mysql error

[php] How to insert array of data into mysql using php

Examples related to php

Examples related to mysql