Uploading Data to MySQL Table from Desktop .csv File

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jdmfontz
Forum Newbie
Posts: 17
Joined: Wed Apr 15, 2009 12:38 pm

Uploading Data to MySQL Table from Desktop .csv File

Post 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>";
?>
jdmfontz
Forum Newbie
Posts: 17
Joined: Wed Apr 15, 2009 12:38 pm

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

Post 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>
jdmfontz
Forum Newbie
Posts: 17
Joined: Wed Apr 15, 2009 12:38 pm

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

Post 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>";
jdmfontz
Forum Newbie
Posts: 17
Joined: Wed Apr 15, 2009 12:38 pm

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

Post 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.
Post Reply