Automatic import from local machine to mysql database

[Xuthus]

Hi, New to PHP and this site, so go gentle with me :-)

Shop - PrestaShop 1.4
Host 1&1
MySql5

In a nut shell, I recieve a stock list from my suppliers which I store locally, I want to load this file automatically, into the backend of my database into a table I have already created, from there I can manipulate to my hearts content if I can get it there.

When I run the script via my browser, I do not get any error codes, but no data is loaded into the table, where am I going wrong, and how do I check the stages its running through eg connected to the db ok, found file ok etc...

Many Thanks

Here is my code

<?php
// --------------------------------------------------
// VIP_Qty_update.php
// Purpose to Change MySql database quantity via VIP Stock csv file
// This is a standalone script - and needs to be run via web browser
// Last Updated: 21/06/11
// Created By Xuthus
//
// Instructions
//
// Upload this file to your server via an FTP program.
// Enter PHP file Name
//
// To run open in your browser.
// Example: [mysite.com...]
//
// Connect to your database
//
//

$databasehost = "abc";
$databasename = "abc";
$databaseusername ="abc";
$databasepassword = "abc";
$dbvsmtable = "VIP_Stock_Movements";
$VIPExtract = "d:/VIPUpload/VIPStock.csv";


$query = "load data LOCAL infile '$VIPExtract' INTO TABLE `$dbvsmtable` FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
ESCAPED BY '\\' LINES TERMINATED BY '\\r\\n'";

$result = @mysql_query($query)
?>

[penders]

Hi Xuthus and welcome to WebmasterWorld, just a few queries to get going...

(1) Where are you connecting to your database?

(2) Is your database (and website) also running on a local server?

$result = @mysql_query($query)

(3) The '@' prefixing this statement is going to prevent any errors from being output. So even if there are errors, you're not going to see any!

// Last Updated: 21/06/11

(4) Isn't that next Tuesday?! ;)

[Xuthus]

Hi And thank you for getting back to me

On point (4) Typo I'm affraid, should of said 12/06/11 :-)
On Point (2) The website and SqlDb are located at my host 1&1, they are not local to my machine, hope I have understood you correctly here!
On Point (3) I will remove the @ symbol, and try and run the script again

On Point (1) see code below with db con

<?php
// --------------------------------------------------
// VIPUpdate.php
// Purpose to Change MySql database quantity via VIPStock csv file
// This is a standalone script - and needs to be run via web browser
// Last Updated: 17/06/11
// Created By Xuthus
//
// Instructions
//
// Upload this file to your server via an FTP program.
// Enter PHP file Name
//
// To run open in your browser.
// Example: [mysite.com...]
//
// Database Details
//
//

$databasehost = "abc";
$databasename = "abc";
$databaseusername ="abc";
$databasepassword = "abc";
$dbvsmtable = "VIP_Stock_Movements";
$VIPExtract = "f:/VIPExtract/VIPStock.csv";


// Connect to my database


$con = @mysql_connect($databasehost,$databaseusername,$databasepassword); //or die(mysql_error());//
@mysql_select_db($databasename); //or die(mysql_error());//


$query = "load data LOCAL infile '$VIPExtract' INTO TABLE `$dbvsmtable` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\\r\\n'";

$result = @mysql_query($query);


mysql_close($con);

?>

[penders]

On Point (2) The website and SqlDb are located at my host 1&1, they are not local to my machine, hope I have understood you correctly here!

And your script (above) is located at your host at 1&1? Where is your 'stock list' located exactly? You mention this is "store[d] locally" - if you mean locally on your home computer then this is unlikely to work as is. I've not used this myself, but I assume the "LOCAL" option refers to the local file system on which the MySQL client is located? Which could well be the same place as the server?!

[Xuthus]

Hi Penders

Yes the script is located at my 1&1 host, and ran via my web browser at present, the CSV file I want to import is located on my home computer, you metion if this is the case then it is unlikely to work, could you explain why?, and what you would recomend as a solution please.

As apointer for you the LOCAL line of code comes from myPHPAdmin, which was created, when I did a manual import of the csv file in question, I thought by using the generated code, it would fall into place and run automatically

Many Thanks

[penders]

...the CSV file I want to import is located on my home computer, you metion if this is the case then it is unlikely to work, could you explain why?

$VIPExtract = "f:/VIPExtract/VIPStock.csv";

How is your host (a remote server located many miles away) going to know that "f:/" refers to the F: drive on your home computer and not the F: drive on my home computer?! :) This script runs on the remote server, not your local machine.

I assume that when you created the statement on myPHPAdmin it had to upload the file somehow?

I think you will need to do the same... create an HTML form through which to upload the file (or perhaps via FTP). Then run your script on that file.

An alternative (although more complex method) would be to run a web server on your home computer. You would then run the script locally, on your local file. The local web server could connect to your host at 1&1 or perhaps connect directly with your MySQL database and upload it. The result would be the same. However, depending on your setup there could be some restrictions with this method.

[edited by: penders at 7:02 pm (utc) on Jun 17, 2011]

[Xuthus]

Hi, the remote server is located miles away, yes F:\ is a drive on my home pc, I have checked my ftp application, I can set an automated scheduled transfer, to load the csv file up to my host, I will alter the code to load the file from there.

I will stick to the easy method for now, just getting my teeth into php.

Thank you for your guidance, i could of spent a lot of time messing with this to no avail
Regards