How to insert extra data into mysql while using implode

[IreneLing]

Hi all.

Here is the code:(thanks to jcbones)

    if (($handle = fopen($source_file, "r")) !== FALSE) {
        $columns = fgetcsv($handle, $max_line_length, ",");
        foreach ($columns as &$column) {
            $column = str_replace(".","",$column);
        }
        while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
            while(count($data) < count($columns)) {
			array_push($data, NULL);
		}
		$c = count($data);
		for($i = 0; $i < $c; $i++) {
			$data[$i] = "'{$data[$i]}'";
		}

		$sql[] = '(' . implode(',',$data) . ','.$_POST[group].')';
        }
                $sql = implode(',',$sql);

	$query = "INSERT INTO mytable (".mysql_real_escape_string(implode(",",$columns)).",Custgroup,user_id) VALUES " 
                          . $sql . "\n";
	mysql_query($query) or trigger_error(mysql_error());

        fclose($handle);
    }
} 

 

If my csv file is:

lastname,firstname,gender

bob,ah,male

 

So now the query will be :  INSERT INTO mytable (lastname,firstname,gender) VALUES ('bob',ah','male').

But how if I want to insert extra data into mysql together with the data in csv file?

Such as I have a value $_POST['group'] = 'family', so I tried:

$sql[] = '(' . implode(',',$data) . ','.$_POST[group].')';

$query = "INSERT INTO $target_table (".mysql_real_escape_string(implode(",",$columns)).",custgroup) VALUES " 
                          . implode(',',$sql) . "\n";

 

But in the query it will become :  INSERT INTO mytable (lastname,firstname,gender,custgroup) VALUES ('bob',ah','male',family).

It didn't have single quote , so I have error to insert the record.Can I know how to solve this problem?

 

Thanks.

[requinix]

jcbones gave you a great start, but there are a few little issues with it. Here's my version.

if (($handle = fopen($source_file, "rt")) !== FALSE) {
// first row is column names. hope they're in the table!
$columns = fgetcsv($handle, $max_line_length, ",");
foreach ($columns as &$column) {
	$column = str_replace(".", "", $column);
} unset($column); // destroy lingering $column reference

// additional columns from $_POST
$additional_columns = array("group");
foreach ($additional_columns as $ac) {
	if (isset($_POST[$ac])) $columns[] = $ac;
}

$allvalues = array();
while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
	$data = array_combine($columns, $data);
	// data is $_POST + CSV data
	$data = array_merge($_POST, $data);

	$values = array();
	foreach ($columns as $column) {
		$values[] = (isset($data[$column])
			// provided
			? "'" . mysql_real_escape_string($data[$column]) . "'"
			// not provided, use NULL
			: "NULL");
	}

	$allvalues[] = implode(", ", $values);
}

// insert if there are values
if ($allvalues) {
	$query = "INSERT INTO mytable (" . implode(", ", $columns) . ") VALUES (" . implode("), (", $allvalues) . ")";
	mysql_query($query) or trigger_error(mysql_error());
}

fclose($handle);
}

[IreneLing]

Really thanks for your code requinix , I tried to understand it ( a little hard for me) and try it , but here is the errors:

 

Warning: array_combine() [function.array-combine]: Both parameters should have an equal number of elements

Warning: array_merge() [function.array-merge]: Argument #2 is not an array

Warning: array_combine() [function.array-combine]: Both parameters should have an equal number of elements

Warning: array_merge() [function.array-merge]: Argument #2 is not an array

 

INSERT INTO UserAddedRecord (lastname, firstname, ceLL, group) VALUES (NULL, NULL, NULL, NULL), (NULL, NULL, NULL, NULL)

 

$additional_columns = array("group");

 

If I change 'group' to other column name that do not exist in my table then it will just insert data in csv file and works fine , but if I put existed column name then everything will become NULL , and I have problem in trying to solve it since do not really understand the error..

 

[requinix]

I got the array_combine() and array_merge() lines backwards. Switch them around.