Mysql Help: Insert Csv Data Into Mysql

programming
1

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 structure, assuming database is included:

$file = “testdb.csv”;
$fp = fopen($file, “r”);
$data = fread($fp, filesize($file));
fclose($fp);
$output = str_replace("’,’", “”, $data);
$output = explode("
“, $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!”;

2

Thanks for your reply. I’m not considering using phpMyAdmin, just want to write out simple coding. Anyone please help me check my coding structure, so i can import csv into mysql. Thanks. :slight_smile:

3

i rewrite my code, just asking someone can find any error for me coz it wont insert data into my database. Thanks :slight_smile:

<?php
$username = “root”;
$password = ‘ngv’;
mysql_connect(“localhost”, $username, $password) or die (“Unable to connect to server”);
$row = 1;
$handle = fopen(“test.csv”, “r”);
$first = fgetcsv($handle, 1000, “,”);
while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE) {

$import=“INSERT into billing.checkout(‘Serial’,UID,reloadDate,‘Status’)values(’”.mysql_real_escape_string($data[0])."’,’".mysql_real_escape_string($data[1])."’,’".mysql_real_escape_string($data[2])."’,’".mysql_real_escape_string($data[3])."’,’".mysql_real_escape_string($data[4])."’)";

mysql_query($import) or die(mysql_error());

}
fclose($handle);
?>

4

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. :slight_smile:

<?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."&lt;br&gt;";

mysql_query($import) or die(mysql_error());

}
fclose($handle);
?>