Newbie Help: Insert Csv to Mysql

import delimited data into mysql

[benghee]

Hi all,

>I would like to import csv file into mysql database. While im working on the testing database, no data from csv is display. Is it because of my comma seperated error? Could anyone take a look at my code? Many thanks to you all.

>Here is my csv file content:

"name","userid","pwd","date"
"john_smith","js","5621","june12"
"jane_doe","jd","8894","sept12"

>Here is my coding:

$file = "testdb.csv";
$fp = fopen($file, "r");
$data = fread($fp, filesize($file));
fclose($fp);

$output = str_replace("','", "", $data);
$output = explode("\n", $output);

foreach($output as $var) {
$tmp = explode("','", $var);
$name = $tmp[0];
$userid = $tmp[1];
$pwd = $tmp[2];
$date = $tmp[4];

$sql = "INSERT INTO table SET name='$name',userid=$userid, pwd=$pwd, date=$date";
mysql_query($sql);
}
echo "Done!";

[PHP_Chimp]

$sql = "INSERT INTO table SET name='$name',userid=$userid, pwd=$pwd, date=$date";

You have quoted $name, but nothing else. Are you sure that is what you want?
As mysql databases are not my strong point you will need to check the syntax. As isnt there supposed to be ` (thats a backtick) around the things to set?

Also you could use fgetcsv [uk3.php.net] to read your file in csv format.

<edit>
Also add some error handling. This is straight out of the mysql_query [uk3.php.net] manual page.

$result = mysql_query($sql);
if (!$result) {
 $message = 'Invalid query: ' . mysql_error() . "\n";
 $message .= 'Whole query: ' . $query;
 die($message);
}

[edited by: PHP_Chimp at 9:13 pm (utc) on June 11, 2008]

[benghee]

Hi all,

i've been trying many methods, mix and match, and finally comes up a solution for my problem. It may working well, if any mistakes or add-on code from experts here, please let me know to polish my work. Thanks and have a good weekend. :)

<?php

$username = "root";
$password = 'pwd';
mysql_connect("localhost", $username, $password) or die ("Unable to connect to server");

$handle = fopen("test.csv", "r");
fgetcsv($handle, 1000, ","); //ignore first header;

while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
{
$serial = str_replace(",", "", $data[0]);
$uid = str_replace(",", "", $data[1]);
$reloadDate = str_replace(",", "", $data[2]);
$status = str_replace(",", "", $data[3]);

$import ="INSERT into mycnx_billing.mycnx_card m('Serial',UID,reloadDate,''Status')values('$data[0]','$data[1]','$data[2]','$data[3]')";
//echo $serial."<br>";
mysql_query($import) or die(mysql_error());

}
fclose($handle);

?>