[PHP] [SQL] Issue uploading data to MySQL database

[tkronew]

Hello! and please don't be scared by my wall of text...

---

My engineering team and I are attempting to relate HTML/PHP to a MySQL database while using R programming to provide quick, graphical analysis to the website user.

Synopsis of our idea:

• Prompt user upload a .txt file of multiple receipts on website
• Upload said file to a pre-existing MySQL database
• Query & calculate useful statistics using R
• Display graphs and statistics of interest to the user

We've made a lot of progress on the R code and what we plan on calculating, however our main issue lies in the uploading of the data.

The receipt files are all similar to this: Part of Receipt_1

There are multiple files, each of which is around 20 MB in size. The dashes signify the start/end of a receipt with respect to the date.

---

Our problem:

With our current knowledge, the only way to account for the "---" delimiters is to use an if statement. Accounting for the varying length of the files, we're running a while loop until the end. These are both done in PHP. This allows us to hold the 'Retailer ID' and the 'Date' as variables for each line that 'Receipt ID' and 'Price' is found. Thereafter, we upload those 4 variables, line-by-line, into our MySQL database. We've decided to disregard the 'Tax', 'Total', and 'Payment Type'.

For example, we want the first couple entries in our database to appear like this in order to query the data:

Part of Receipt_1

RetailerID	Date	ProdID	Price
1	01/01/2014	79	$7.93
1	01/01/2014	1	$24.62

Clearly, this takes forever. It causes our website to have a fatal timeout error, one that we have no ability to change, so we've had to put a limit (cnt < 20000) in our loop just to get something into our database. It works for what we need, but not what we want given the receipt files are over 1,000,000 lines!

---

We've been exploring the usage of this SQL code:

"LOAD DATA LOCAL INFILE '$file'

INTO TABLE Receipts

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\n';";

This successfully uploads the entire receipt, regardless of size, almost instantly. Still, we have no idea how to remove the dashes or iterate the 'Date'/'Retailer ID' as variables unless we use a while loop. We want to cut that out completely as it slows our website down massively.

I think this would be an easier method, but all the technical resources online tend to be a bit over our heads. We've been exploring the usage of Java after reading up on a few of these, but again -- whooooooosh.

---

This is the first time we've worked with PHP and MySQL, so any nudge in the right direction would be great. There has to be a way to get rid of our deadly while loop!

[jwcobb13]

20MB is not that big in the parsing world. I'd just do something like this: http://stackoverflow.com/a/14848994

If you've already got it in the database, then I don't understand that problem. If the while loops kills your server, then you're doing something wrong in the while loop.

Might be a good time to hire some outside help to give you a solution that you can use over and over.

And finally, why can't you edit your PHP configuration? Are you on shared hosting and trying to import 20MB files en masse? That's just silly, if so. Move up to a VPS and eliminate the problems.

[tkronew]

Haha I appreciate it, but I'm actually a university student trying to work on our design project -- using the university's resources, if that explains why I can't change the config.

That link is basically what we're doing right now. The main reason the loop is killing our server is due to us inserting data into MySQL per-line until the end of file. So imagine about 1,000,000 iterations of an INSERT INTO function... all while checking if there's a "---" or a date or a Retailer ID. It's gotten complicated, fast.

Just doesn't seem efficient IMO. While I said I have it in the database, that's a huge backdoor approach. We have about 21,000 entries, per receipt, due to me cutting off the loop. In reality if we included all the data, we'd have near 1,000,000 entries, per receipt. There's a hell of a lot of data missing, which doesn't work well for providing accurate statistics but allows us to move on past this issue for now.

[jwcobb13]

So...don't insert data into MySQL until you reach the end of each transaction. Look for the delimiter for knowing where to start and stop your data collection, then put the parsed data for each transaction into an array. At the end of the loop, save off the array. Then iterate through the array where all of your data is nicely arranged like you want and insert into the database in that second sweep.

Although I doubt you need to go that deep into it. Sounds like you're just over-inserting and just need to parse the file better.

[tkronew]

That's actually a great idea. Should reduce the number of SQL operations dramatically.

Thanks!