Page 1 of 1

Uploading Data to MySQL Table from Desktop .csv File

Posted: Wed Apr 15, 2009 1:18 pm
by jdmfontz
Hello,

I am new to php and have problem uploading a .csv file residing in desktop to mysql table.
Wondering if I first need to bring file to the server or can I upload directly from desktop?
My sample code is below but it looks like the form values are not being pased to the php code as indicated by the empty fields in output. Just ran out of ideas and nothing jumps out. Any help is appreciated.


This is the output -->:

Description:

File to upload:

File ID: 7


File Name:


File Size:


File Type:
To upload another file Click Here

Here is the script -->:

<form method="post" action="test4.php" enctype="multipart/form-data">
Description:<br>
<input type="text" name="form_description" size="40">
<br>File to upload:<br>
<input type="file" name="form_data" size="40">
<p><input type="submit" name="submit" value="submit">
</form>

<?php
mysql_connect("192.168.110.100","adm","pwhere");
mysql_select_db("mydata");
$data = addslashes(fread(fopen($form_data, "r"), filesize($form_data)));
$result=MYSQL_QUERY("INSERT INTO uploads (description, data,filename,filetype) ". "VALUES ('$form_description','$data','$form_data_name','$form_data_type')");
$id= mysql_insert_id();
print "<p>File ID: <b>$id</b><br>";
print "<p>File Name: <b>$form_data_name</b><br>";
print "<p>File Size: <b>$form_data_size</b><br>";
print "<p>File Type: <b>$form_data_type</b><p>";
print "To upload another file <a href=http://www.yoursite.com/yourpage.html> Click Here</a>";
?>

Re: Uploading Data to MySQL Table from Desktop .csv File

Posted: Thu Apr 16, 2009 8:25 am
by jdmfontz
Thank you. Also thank you for the link. It was good but it took me a while to step thru. The script now uploads to the server. However, I am having problems getting it to do an insert into the MySQL table.

Script:

<form enctype="multipart/form-data" action="angela.php" method="POST">
Please choose a file: <input name="file" type="file" /><br />
<input type="submit" value="Upload" /><br />

<?php

//Upload the file to server

$uploaddir = '/usr/local/apache2/htdocs/files/';
if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "text/plain"))
&& ($_FILES["file"]["size"] < 20000))
{
if ($_FILES["file"]["error"] > 0)
{
echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
}
else
{
echo "Upload: " . $_FILES["file"]["name"] . "<br />";
echo "Type: " . $_FILES["file"]["type"] . "<br />";
echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";
if (file_exists("$uploaddir" . $_FILES["file"]["name"]))
{
echo $_FILES["file"]["name"] . " already exists. ";
}
else
{
move_uploaded_file($_FILES["file"]["tmp_name"],
"$uploaddir" . $_FILES["file"]["name"]);
echo "Stored in: " . "$uploaddir" . $_FILES["file"]["name"];
}
}
}
else
{
echo "Invalid file";
}

//Upload data to mysql

mysql_connect("100.100.100.100","adm","test");
mysql_select_db("data");
$data = addslashes(fread(fopen($file, "r"), filesize($file)));
$result=MYSQL_QUERY("INSERT INTO contacts (first,last,phone,mobile,fax,email,web) ". "VALUES ('$first','$last','$phone','$mobile','$fax','$email','$web')");
$id= mysql_insert_id();
print "<p>File ID: <b>$id</b><br>";
print "<p>File Name: <b>$file_name</b><br>";
print "<p>File Size: <b>$file_size</b><br>";
print "<p>File Type: <b>$file_type</b><p>";

print "<p>To upload another file <a href=http://100.100.100.100/socdev/angela.php> Click Here</a></b><p>";

?>

</form>

Table:mysql> desc contacts
-> ;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(6) | NO | PRI | NULL | auto_increment |
| first | varchar(15) | NO | | NULL | |
| last | varchar(15) | NO | | NULL | |
| phone | varchar(20) | NO | | NULL | |
| mobile | varchar(20) | NO | | NULL | |
| fax | varchar(20) | NO | | NULL | |
| email | varchar(30) | NO | | NULL | |
| web | varchar(30) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql>

Re: Uploading Data to MySQL Table from Desktop .csv File

Posted: Thu Apr 16, 2009 2:01 pm
by jdmfontz
Thanks. Ran a quick select test and it looks like case does not matters.
Anyway, got problems with the part of the code below. Somehow need to get the rows in the file into some sort of array, assign it to variables to insert into corresponding fields on the MySQL table. If any ideas or references that would be great.

//Upload data to mysql

mysql_connect("100.100.100.100","adm","test");
mysql_select_db("data");
$data = addslashes(fread(fopen($file, "r"), filesize($file)));
$result=mysql_query("INSERT INTO contacts (first,last,phone,mobile,fax,email,web) ". "VALUES ('$first','$last','$phone','$mobile','$fax','$email','$web')");
$id= mysql_insert_id();
print "<p>File ID: <b>$id</b><br>";
print "<p>File Name: <b>$file_name</b><br>";
print "<p>File Size: <b>$file_size</b><br>";
print "<p>File Type: <b>$file_type</b><p>";

print "<p>To upload another file <a href=http://100.100.100.100/socdev/angela.php> Click Here</a></b><p>";

Re: Uploading Data to MySQL Table from Desktop .csv File

Posted: Thu Apr 16, 2009 2:57 pm
by jdmfontz
I think I got it. This seems to insert nicely into table fm file:

<?php
// Insert file to table
//Connect to db string goes here
$fcontents = file ('./martin.csv');
// expects the csv file to be in the same dir as this script

for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode(",", $line);
$sql = "insert into Persons values ('".
implode("','", $arr) ."')";
mysql_query($sql);
echo $sql ."<br>\n";
if(mysql_error()) {
echo mysql_error() ."<br>\n";
}
}
?>

Pardon my naiveté, as I am new to this.