hello hello - 1 month ago 6
MySQL Question

Insert two arrays into one mysql row

I have two arrays:

name - facebook,google,yahoo
url - facebook.com,google.com,yahoo.com

In my database, I have a table (tbl_websites).

structure : id, site_name, site_url

I want to insert the data from those two arrays into a single row in that table.

(row 1)

id : 1
site_name : facebook
site_url : facebook.com

(row 2)

id : 2
site_name : google
site_url : google.com

(row 3)

id : 3
site_name : yahoo
site_url : yahoo.com

How do I do that?

Any help will be appreciated.


Assuming there is a direct 1:1 match between the arrays, you can simply loop over one of them as in:

foreach ($name as $key => $value) {
  $name_value = mysql_real_escape_string($value);
  $url_value = mysql_real_escape_string($url[$key]);

  // Addendum: If there was a third array, just use `$key` as the array key
  // same as done with $url[$key]
  $thrid_value = mysql_real_escape_string($third_arr[$key]);

  $result = mysql_query("INSERT INTO tbl_websites (site_name, site_url, third_value) VALUES ('$name_value', '$url_value', '$third_value')");

This assumes the id is an auto-increment value. I have, for simplicity brevity, used the mysql_query() function here. But it is recommended to use a prepared statement via an api like PDO instead. The process of retrieving them from the arrays is the same.

PDO version (assumes connection already made in $db):

foreach ($name as $key => $value) {   
  $stmt = $db->prepare("INSERT INTO tbl_websites (site_name, site_url) VALUES (:name, :url)");
  $stmt->execute(array(':name' => $name_value, ':url' => $url_value));

The code above doesn't include any error checking. Read the PDO manual for examples on error checking with a PDO statement. It can be done either with a try/catch exception block or with PHP warnings.