[RESOLVED] Insert array into database table

cbrknight · Nov 27, 2006

I have this array

Array
(
[0] => Consumer Electronics
[1] => TV and Video
[2] => TVs
[3] => 30 & Over
)

Array
(
[0] => Home and Garden
[1] => Furniture
[2] => Other
)

I need to put it into one table so the names are all in one field with no duplicates and a unique ID. I know that I could do something like this...

$sql = "INSERT IGNORE INTO table ('category_name )VALUES($array[0])";

This would get the first dimension of the array but I would like to get all dimensions at the same time.

Thanks in advance

bogu · Nov 27, 2006

U could use [man]serialize[/man] or [man]implode[/man] ...

cbrknight · Nov 27, 2006

I am not sure how that can help me. I had to explode the data originally to get it into an array. Imploding it is just going to put it back in the same format it originally was. I still dont know how to put it into a single column in a table.

I have been trying for over 6 months to solve this problem. Nobody seems to be able to make this work. I know it must be able to be done but it seems to be beyond my coding skills.

I am getting frustrated and that is not helping.

cbrknight · Nov 27, 2006

I did look at serialize and came back with this as output:

a:4:{i:0;s:20:"Consumer Electronics";i:1;s:12:"TV and Video";i:2;s:3:"TVs";i:3;s:9:"30 & Over";}

a:3:{i:0;s:15:"Home and Garden";i:1;s:9:"Furniture";i:2;s:5:"Other";}

Problem is I dont really know how to interpret this output...

NogDog · Nov 27, 2006

If you are looking for how to insert the values of an array in one insert statement:

$values = "('".implode("'),('", $array)."')";
$sql = "INSERT IGNORE INTO table (`column`) VALUES $values";

rincewind456 · Nov 27, 2006

I'd be more inclined to do it this way

<?php
$a = Array
(0 => 'Consumer Electronics',
1 => 'TV and Video',
2 => 'TVs',
3 => '30 & Over'
);

$b = Array
(
0 =>'Home and Garden',
1 => 'Furniture',
2 => 'Other'
);

$c = Array
(
0 => 'Home and Garden',
1 => 'Furniture',
2 => 'Other'
);

$d = array_merge($a,$b);
$e = array_merge($c,$d);

$f = array_unique($e);

$sql ='';
foreach ($f as $key => $value){
	$sql .= 'INSERT INTO table (category_name )VALUES(\''.$value.'\');';
}
//Normal Database query routine here.
?>

Not knowing how you receive the data I was restricted to doing it like this.

NogDog · Nov 27, 2006

On further review, it looks like you want to do this with a multi-dimension array, so I came up with this:

// test data:
$data = array(array('a','b','c'), array('c','d','e'),array('a','c','e','f'));
// callback function:
function get_values($val, $key, &$array)
{
   if(is_array($val))
   {
      array_walk($val, 'get_values', &$array);
   }
   else
   {
      $array[] = $val;
   }
}
// get all the values:
array_walk($data, 'get_values', &$values);
// get rid of duplicate values:
$unique = array_unique($values);
// make the values ready for the SQL value list:
$values = "('".implode("'),('", $unique)."')";
// create the SQL statement:
$sql = "INSERT INTO table (`column`) VALUES $values";
// see what the SQL looks like:
echo $sql;

cbrknight · Nov 27, 2006

sorry... the data originally comes from a .csv file.

I read the data into a table... the category field is in this format

category1|category2|category3

I then explode that field which creates the array... here is the code I am using...

while($data = mysql_fetch_array($result)){

$categories = $data['CATEGORIES'];
//echo $categories . "<br />";
$category_array = explode("|", $categories);
echo('<pre>');
print_r($category_array);
echo('</pre>');

}

bogu · Nov 27, 2006

cbrknight wrote:
I am not sure how that can help me. I had to explode the data originally to get it into an array.

Maybe I didnt understand u correctly or u didnt explain yourself, but u said

I need to put it into one table so the names are all in one field

How do u intend to add an array into one field without using serialize or implode, and the use explode or unserialized to extract it?

cbrknight · Nov 27, 2006

bogu wrote:
Maybe I didnt understand u correctly or u didnt explain yourself, but u said How do u intend to add an array into one field without using serialize or implode, and the use explode or unserialized to extract it?

It is entirely possible I am not explaining well. Forgive me... I am totally self taught and dont use the "Lingo"... let me try to clarify....

data comes from a .csv file...

I read the data into a table... the category field is in this format

category1|category2|category3

I then explode that field which creates the array... here is the code I am using...

while($data = mysql_fetch_array($result)){

$categories = $data['CATEGORIES'];
//echo $categories . "<br />";
$category_array = explode("|", $categories);
echo('<pre>');
print_r($category_array);
echo('</pre>');

}

this data has to go into a table with this structure...

CREATE TABLE va_categories (
category_id int(11) NOT NULL auto_increment,
parent_category_id int(11) NOT NULL default '0',
category_path varchar(255) NOT NULL default '',
category_name varchar(255) NOT NULL default '',
category_order int(11) NOT NULL default '1',
is_showing int(11) default '0',
short_description text,
full_description text,
image varchar(255) default NULL,
PRIMARY KEY (category_id,category_name),
KEY category_path (category_path),
KEY parent_category_id (parent_category_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I need to get the $category_array parsed into the table.

Basically I need to get all the values in the array into the category_name field wth its own unique category_id...

Then I need to get the first level of the array as the parent_category_id and the rest of the array dimensions as the child categories. which is represented in the table in category_path like so

parent_category = 0
child1 = 1
child2 = 2
child3 = 3

0,1
0,2
0,1,3

etc....

I hope this makes more sence...

cbrknight · Nov 27, 2006

I assume that sudden silence is because this is harder then it sounds?

rincewind456 · Nov 27, 2006

Not to sure what else you want! You have worked out how to get the data from the csv file to an array and you have mine and NogDog's examples to work with.

NogDog · Nov 27, 2006

I think you want at least one change:

while($data = mysql_fetch_array($result)){

$categories = $data['CATEGORIES'];
//echo $categories . "<br />";
$category_array[b][color=red][][/color][/b] = explode("|", $categories);    
}
echo('<pre>');
print_r($category_array);
echo('</pre>');

This is so that you keep adding to the array $category_array on each loop iteration rather than overwriting it. Then use $category_array in place of $data in my previous post.

cbrknight · Nov 28, 2006

RINCEWIND456

I went with your code with some minor modification...

while($data = mysql_fetch_array($result)){

$categories = $data['CATEGORIES'];

$category_array = explode("|", $categories);
/*echo('<pre>');
print_r($category_array);
echo('</pre>');*/
$sql = '';
foreach ($category_array as $key => $value){
echo('<pre>');
print_r($value);
echo('</pre>');
//$sql .= 'INSERT INTO temp_categories (`category_name`)VALUES(\''.$value.'\')';
$sql .= "INSERT IGNORE INTO temp_categories (`category_name`)VALUES('".$value."')";
}
//echo $sql . "<br />";
$database->Query($sql);
$results = $database->result;
}

this outputs the way I want to but I am getting an error when I try to insert it into the database....

Class Database: Error while executing Query -> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT IGNORE INTO temp_categories (category_name)VALUES('TV a

any ideas why?

Thanks

cbrknight · Nov 28, 2006

OK.....

Following the posted advice I was able to get categories to import into table with this code....

while($data = mysql_fetch_array($result)){

$categories = $data['CATEGORIES'];

$category_array = explode("|", $categories);
/*echo('<pre>');
print_r($category_array);
echo('</pre>');*/
//$sql = '';
foreach ($category_array as $key => $value){
echo('<pre>');
print_r($value);
echo('</pre>');
$sql_values = 'INSERT IGNORE INTO temp_categories (`category_name`)VALUES(\''.$value.'\')';
}
//echo $sql . "<br />";
$database->Query($sql_values);
$results = $database->result;
$row_values = $database->rows;

}
	echo("Number of Records: ". $row_values);

Problem is that I have it so it doesnt import duplicates.... using a unique index for category_name field

It works with most of them but not all categories are importing... in actuality only 16 unique names are importing...

Any Ideas?

Thanks again for all help so far...

cbrknight · Nov 29, 2006

cbrknight wrote:
OK.....

Following the posted advice I was able to get categories to import into table with this code....
while($data = mysql_fetch_array($result)){

$categories = $data['CATEGORIES'];

$category_array = explode("|", $categories);
/*echo('<pre>');
print_r($category_array);
echo('</pre>');*/
//$sql = '';
foreach ($category_array as $key => $value){
echo('<pre>');
print_r($value);
echo('</pre>');
$sql_values = 'INSERT IGNORE INTO temp_categories (`category_name`)VALUES(\''.$value.'\')';
}
//echo $sql . "<br />";
$database->Query($sql_values);
$results = $database->result;
$row_values = $database->rows;

}
	echo("Number of Records: ". $row_values);
Problem is that I have it so it doesnt import duplicates.... using a unique index for category_name field

It works with most of them but not all categories are importing... in actuality only 16 unique names are importing...

Any Ideas?

Thanks again for all help so far...

Figured it out myself....

In case anyone is interested. I had to put my query processing script inside the foreach loop. It was only picking up the last offset instead of all offsets.

while($data = mysql_fetch_array($result)){

$categories = $data['CATEGORIES'];

$category_array = explode("|", $categories);
/*echo('<pre>');
print_r($category_array);
echo('</pre>');*/
//$sql = '';
foreach ($category_array as $key => $value){
echo('<pre>');
print_r($value);
echo('</pre>');
$sql_values = 'INSERT IGNORE INTO temp_categories (`category_name`)VALUES(\''.$value.'\')';
$database->Query($sql_values);
$results = $database->result;
$row_values = $database->rows;
}
//echo $sql . "<br />";


}
    echo("Number of Records: ". $row_values);

[RESOLVED] Insert array into database table

Ccbrknight

Bbogu

Ccbrknight

Ccbrknight

NogDog

Rrincewind456

NogDog

Ccbrknight

Bbogu

Ccbrknight

Ccbrknight

Rrincewind456

NogDog

Ccbrknight

Ccbrknight

Ccbrknight