[SOLVED] Array to database question?

[psunshine]

Hi,

 

I have looked using search and several other places trying to find a good detailed descriåtion on how to put differebt arrays in to my db table and have still not come up with anything concrete I have an array that looks like this:

 

Array
(
    [1] => Array
        (
            [1] => Description1
            [4] => Name1
            [6] => Date1
            [8] => URL1
            [2] => none
            [3] => none
            [5] => none
            [7] => none
            [9] => none
            [10] => none
        )

    [2] => Array
        (
            [1] => Description2
            [4] => Name2
            [6] =>Date2
            [8] => URL2
            [9] => none
            [10] => Picture2
            [2] => none
            [3] => none
            [5] => none
            [7] => none
        )

    [3] => Array
        (
            [1] => Description3
            [2] => Homepage3
            [3] => City3
            [4] => Name3
            [6] => Date3
            [7] => Year3
            [8] => none
            [5] => none
            [9] => none
            [10] => Picture3
        )

    [4] => Array
        (
            [1] => Description4
            [2] => Homepage4
            [3] => City4
            [4] => Name4
            [5] => Product4
            [6] => none
            [7] => Year4
            [8] => none
            [9] => none
            [10] => none
        )
)

 

^Courtesy of some much appreciated help from effigy on this threadhttp://www.phpfreaks.com/forums/index.php/topic,204808.0.html

 

I am searching high and low to find an answer or at least a good tutorial on how to insert this type of array in to my table where each value of each array represents a different column. Any ideas or pointers how to do this?

 

Thanks

Pat

[fenway]

They should be different rows, not columns...

[psunshine]

Yes, sorry, I think I was VERY unclear with what I said. Each array should be a new row and each value in array inserted to corresponding field. Any suggestions or pointers on how to accomplish the insert? Im a newbie and am trying to get my head around this but there arent many tutorials about on array to db table

 

Thx

Pat

[rhodesa]

try this out...read through it first, cus you have to fill in some stuff

 

<?php
  // Map of index numbers to table columns
  $map = array(
    1 => 'description',
    4 => 'name',
    6 => 'date',
    // etc
  );
  // $data is your array
  // I also assume a mysql connection already exists
  foreach($data as $row){
    $values = array();
    foreach($map as $n => $key){
      if(isset($row[$n]))
        $values[$key] = mysql_real_escape_string($row[$n]);
    }
    $sql = "INSERT INTO tablename (`".implode("`,`",array_keys($values))."`) VALUES ('".implode("','",$values)."')";
    mysql_query($sql) or die("Error: ".mysql_error());
  }
?>

[psunshine]

;D

 

Thanks Rhodesa, worked like a charm and I kinda understand how it works as well which is a bonus of course 

 

So here is the full code for my specific data to array to mysql database. Hopefully it can help some other lost souls out there!

 

<?php
$lines = array(
	'<item><1>abc1</1><2>abc2</2><3>abc3</3><4>abc4</4></item>',
	'<item><1>abc1</1><3>abc3</3><5>abc5</5><6>abc6</6></item>',
	'<item><1>abc1</1><6>abc6</6><2>abc2</2><3>abc3</3></item>'
);
$data = array();
$num = 1;
foreach ($lines as $line) {
	preg_match_all('%<(\d+)>(.+?)</\1>%', $line, $matches);
	foreach ($matches[1] as $key => $value) {
		$data[$num][$value] = $matches[2][$key];
	}
	++$num;
}
foreach ($data as &$line) {
	for ($key = 1; $key <= 6; $key++) {
		if (!array_key_exists($key, $line)) {
			$line[$key] = 'none';
		}
	}
}

  $map = array(
        1 => 'field1name',
2 => 'field2',
3 => 'field3',
4 => 'field4',
5 => 'field5',
6 => 'field6',

  );


  foreach($data as $row){

$dbname = "databasename";
$dbuser = "meyou";
$dbpass = "abracadabra";
$dbserver = "localhost";
$tablename = "mytable";

mysql_connect($dbserver, $dbuser, $dbpass) or die('Error connecting to mysql');
mysql_select_db($dbname);

    $values = array();
    foreach($map as $n => $key){
      if(isset($row[$n]))
        $values[$key] = mysql_real_escape_string($row[$n]);
    }
    $sql = "INSERT INTO `brands` (`".implode("`,`",array_keys($values))."`) VALUES ('".implode("','",$values)."')";
    mysql_query($sql) or die("Error: ".mysql_error());
  }
?>

 

I used regex preg_replace and preg_match/all on raw data to reach the initial $lines.

 

Once again big thx.

 

Pat

[bluejay002]

oh... nice kick! I never thought of using implode that way... thanks also.