Inserting associative array into mysql table

Question

I need to insert the following array into a mysql table with columns name,title,url,logo. I created the table and wrote a query to transfer the array. But it was inserting the last line of the array and leaving remaining... plz help me... this is my array

$con = mysql_connect("localhost","","");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("media", $con);

$featured = array();

$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');

$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');

$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');

$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');



 $sql  = "INSERT INTO featured";


   $sql .= " (`".implode("`, `", array_keys($featured))."`)";


   $sql .= " VALUES ('".implode("', '", $featured)."') ";


   $result = mysql_query($sql) or die(mysql_error());

Your array is 2D. You must do this in a foreach() loop. We assume all the array values have already been escaped via mysql_real_escape_string()... — Michael Berkowski
– Michael Berkowski, Commented Sep 28, 2012 at 16:52
foreach ($featured as $sql){ $sql = "INSERT INTO featured"; // implode keys of $featured... $sql .= " (".implode(", ", array_keys($featured)).")"; // implode values of $featured... $sql .= " VALUES ('".implode("', '", $featured)."') "; // execute query... $result = mysql_query($sql) or die(mysql_error()); } — user1694724
– user1694724, Commented Sep 28, 2012 at 16:53
With that loop, you are overwriting $sql inside the loop. Use a different index: foreach ($featured as $feature) — Michael Berkowski
– Michael Berkowski, Commented Sep 28, 2012 at 16:54

rationalboss · Accepted Answer · 2015-01-21 09:19:23Z

5

You need to loop through the $featured array:

$con = mysql_connect("localhost","","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("media", $con);
$featured = array();
$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');

foreach ($featured as $array) {
    $sql  = "INSERT INTO featured";
    $sql .= " (`".implode("`, `", array_keys($array))."`)";
    $sql .= " VALUES ('".implode("', '", $array)."') ";
    $result = mysql_query($sql) or die(mysql_error());
}

Or to INSERT several records in one query as suggested:

$sql  = "INSERT INTO featured";
$sql .= " (`".implode("`, `", array_keys($featured[0]))."`) VALUES"; // make sure that $featured contains the same number of key=>value pairs
foreach ($featured as $array) {
    $sql .= " ('".implode("', '", $array)."'),";
}
$sql = substr($sql,0,-1); // remove last comma
$result = mysql_query($sql) or die(mysql_error());

Please read on SQL injections.

edited Jan 21, 2015 at 9:19

answered Sep 28, 2012 at 16:53

rationalboss

5,3973 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hakre Over a year ago

do not run a single query per row, insert multiple rows at once instead.

Michael Berkowski Over a year ago

Right - initialize the INSERT INTO (columns...) VALUES outside the loop, then use the loop to append parenthetical values lists ('a','b','c'),('d','e','f'),...

Patrick Q Over a year ago

Or use a prepared statement with parameterized values that get assigned each time through the loop

rationalboss Over a year ago

i was thinking there is a possibility that some of the records do not contain all the fields. this is okay if all of the inserted fields have the same number of elements with the same position of all the key and value pairs

Teena Thomas · Accepted Answer · 2012-09-28 17:13:56Z

1

you can use multiple insert query as INSERT INTO (column1, column2,...) VALUES ('row1a', 'row1b'..), ('row2a', 'row2b'..)...

    $featured = array();
    $featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
    $featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
    $featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
    $featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');

    $sql  = "INSERT INTO featured ";


   $sql .= " (`".implode("`, `", array_keys($featured))."`)";
   $sql .= " VALUES ";


  for( $i=0; $i < count($featured); $i++) {
    $sql .= "('". implode("', '", $featured[$i])."') "; 
    if( $i < (count($featured) - 1) )
      $sql .= ", ";
  }

See it in action here.

edited Sep 28, 2012 at 17:13

answered Sep 28, 2012 at 17:01

Teena Thomas

5,2091 gold badge15 silver badges17 bronze badges

Comments

Jeffory J. Beckers · Accepted Answer · 2012-09-28 17:05:10Z

As hakra suggested you can insert multiple rows using one mysql query by making the following change to your code:

$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');
$featured[] = array('name' => '', 'title' => '', 'url' => '', 'logo' => '');

$sql  = "INSERT INTO featured";
$sql .= " (`".implode("`, `", array_keys($featured))."`)";
$sql .= " VALUES "; // moved parenthetical grouping to foreach loop below
foreach ($featured as $feature) {
    $sql .= "('".implode("', '", $featured)."'), "; // note comma after each parenthetical grouping
}
$sql = substr($sql, 0, -2); // we'll cut off the last ", " added to the string

$result = mysql_query($sql) or die(mysql_error());

David Grenier · Accepted Answer · 2012-09-28 17:18:48Z

This is a job for PDO! You should be using PDO or mysqli instead of mysql_ anyway, but that's a whole other discussion.

What you want to do is this:

// ========================================================
// Define insert rows. Note the colon before each key name. 
// That's there because we'll be passing this to a PDOStatement as bound 
// parameters. Depending on the logic of your program there may be different 
// ways you want to handle this.
// ========================================================
$featured = array();
$featured[] = array(':name' => '', ':title' => '', ':url' => '', ':logo' => '');
$featured[] = array(':name' => '', ':title' => '', ':url' => '', ':logo' => '');
$featured[] = array(':name' => '', ':title' => '', ':url' => '', ':logo' => '');
$featured[] = array(':name' => '', ':title' => '', ':url' => '', ':logo' => '');


// ========================================================
// Create the PDO object. Define your query and prepare a statement. I use the 
// INSERT SET syntax rather than the INSERT VALUES syntax purely for readability. 
// Either will work with PDO. Also, most programmers don't set the query_str as a 
// separate variable as I do here. I find it improves readability.
// ========================================================
$db_obj = new PDO (/*connection params go here */); 
$query_str = "INSERT INTO featured SET
    name = :name, 
    title = :title, 
    url = :url, 
    logo = :logo"; 
$query_obj = $db_obj->query ($query); 


// ========================================================
// Loop through your rows in $featured and execute the SQL query, passing the
// new row of parameters each time.
// ========================================================
foreach ($featured as $row) {
    $query_obj->execute ($row);
}

Collectives™ on Stack Overflow

Inserting associative array into mysql table

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related