Page 1 of 2

file_get_contents (or other suggested)

Posted: Mon Jan 18, 2016 6:57 pm
by seattle_newbie
i am trying to do what i think should be simple, but have two days into trying to do it so finally decided to ask for help.

I have a third party webpage for an application i am using, that has been set up to supply information in a comma delimited format. it is very easy for me to copy and paste this data into a text file, and then load that data into my mysql database tables as i need to, using that text file.

however this information changes daily, and will be used on my website to give customers up-to-date information. rather than 'manually' transfer this data daily as described above, i am trying to write code that will read the webpage and then load the data into the tables per my code. from my research, i think i need to:

1. either have the data from the webpage read and then written into a text file, then use that text file to load into the tables, or just have the data read and then loaded directly into the tables (skipping the text file step). i assume the second option would be simpler however i cannot figure out how to do either option. again, i can do it by cut and paste method from web page, to text file, then load data; i want code that does it all at once so that i can:

2. set up a cron job to do this. the way i see it, i have to figure out #1 first. so not worried about this step yet.

it seems to me, from my searching, that i need to use 'file_get_contents', (which i have been able to get to read the web page and echo results), but have not been able to figure out how to use the data read to load directly to the tables.

just looking for a resource to learn how to do this.

note: in case it matters: the table data does not need to be updated, it is better if it is completely replaced each time. i think that makes it simpler, as i just delete all rows in the tables and re-load with the new data. that has been working fine to date.

thanks in advance

Re: file_get_contents (or other suggested)

Posted: Mon Jan 18, 2016 10:03 pm
by requinix
You say you already have it loading data into the database? How? Is that a manual process too? If not then you can use copy to "copy" the webpage to a local file, then do whatever you're doing now on that file. (Then delete the file after.)

Otherwise it's manual. Where is the database? Is it on the same server that PHP is running on?

Otherwise, if it's on another machine, then what all is the situation with PHP and how it will be running? Is this stuff going to be part of an existing PHP website? Does it need to run automatically, or when someone does something, or how?

And finally:
It sounds like you have code already? What is it and what are the problems you're having with it? Any error messages?

Re: file_get_contents (or other suggested)

Posted: Mon Jan 18, 2016 10:57 pm
by Christopher
There are lots of ways to get data from a remote URL. I'm not sure you even need PHP for that. There are lots of command line utils like curl or wget that will put the fetch contents at a URL and put it in a file. There are lots of examples of fetching a file with curl, wget, etc.

Once you have a tab delimited file saved, it can be loaded into the database with a single LOAD DATA INFILE '/path/to/myfile.csv' INTO TABLE mytable FIELDS TERMINATED BY ','. You will need to do housekeeping like clearing the previous records. Maybe use a temp table to load the data into and then SELECT it to the live table. And you may want to check that the file actually has data before importing, etc., etc. But the basic database loading is a very short script. Not sure if you need an example of a PHP script that connects to a database and runs a couple SQL commands?

A cron job is just adding a line in a file. The main problem you will have is probably the database server not having permission to read the file unless you put it somewhere accessible. Again, lots of examples online. Depending on you server you many have a control panel to set it up.

You could do all of the above in a single script by fetching the data using file_get_contents() or curl. Then convert the string to an array with explode() or str_getcsv(). Then do the database stuff using the array and INSERT. Same general idea.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 8:59 am
by seattle_newbie
Thank you for the responses. I guess i should have been clearer- I thought I was but being a newbie maybe not. to answer the questions above:
- the files are all on the same server, except the webpage I am trying to load into the database. right now I am in a test environment but they will be loaded to server when complete.
- yes, I already have code. here it is:

Code: Select all

DELETE FROM midweek_res_future WHERE 1;
DELETE FROM weekend_res_future WHERE 1;
DELETE FROM res_key_dump WHERE 1;
LOAD DATA LOCAL INFILE 'C:\\xampp\\reskey_downloads\\new 2' INTO TABLE `res_key_dump`
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
SET use_date_mysql = str_to_date(use_date,'%m/%d/%Y'), booked_date_mysql = str_to_date(booked_date, '%m/%d/%Y %h:%i:%s %p'), day_of_week = DATE_FORMAT(use_date_mysql,'%W');
insert into weekend_res_future
SELECT * FROM `res_key_dump`
WHERE  use_date_mysql >= CURRENT_DATE and 
(`day_of_week` LIKE 'saturday'
or day_of_week LIKE 'sunday')
order BY use_date_mysql;
DELETE FROM res_key_dump
WHERE use_date_mysql >= CURRENT_DATE and
(`day_of_week` LIKE 'saturday'
or day_of_week LIKE 'sunday');
insert into midweek_res_future
SELECT * FROM `res_key_dump`
WHERE  use_date_mysql >= CURRENT_DATE and 
(`day_of_week` not LIKE 'saturday'
or day_of_week not LIKE 'sunday')
order BY use_date_mysql;
DELETE FROM res_key_dump
WHERE use_date_mysql >= CURRENT_DATE and
(`day_of_week` not LIKE 'saturday'
or day_of_week not LIKE 'sunday')
- this code above works fine, but yes I am getting error messages when I try to add copy or 'file_get_contents' to eliminate the manual cut and paste to create the text file (".../new 2" above)- been getting error messages for two straight days. I cannot figure out the correct format to write it.
- the problem is in searching: the key words return so much that I have yet to be able to find my answer. I have over 20 hours into trying to write the last couple lines of code.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 10:28 am
by seattle_newbie
UPDATE:

I guess this is part of learning. I used this code, just to test, in a webpage in the test environment and it worked:

Code: Select all

$new_text_file2 = 'C:\\xampp\\reskey_downloads\\new 3';

copy('http://mywebpage', $new_text_file2) 
However, I have been working in phpmyadmin up until now. the previously posted code (previous post) works flawlessly in phpmyadmin, but this part (this post) does not work in phpmyadmin. it returns the error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '$new_text_file2 = 'C:\\xampp\\reskey_downloads\\new 3'' at line 1

But, works in a webpage. All my frustration and hours of trying, and it was just the place I was trying to test it? I was trying to implement it into the rest of the code to update database, so it seemed right to me. That is two days of my life I will never get back! Maybe someone can explain why this happens.

Thanks again for responses. Onward.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 2:26 pm
by Christopher
My guess is that the problem is either the backslashes (use forward slashes in PHP) or the space in the file name.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 3:19 pm
by seattle_newbie
on further review, the following code:

Code: Select all

$new_text_file2 = 'C:\\xampp\\reskey_downloads\\new 3';

copy('http://mywebpage', $new_text_file2) 
does not work, as it inserts just one row into the text file. the '<br>' tags are copied as "<br>", and I have just one really long row. again, I searched for hours (since the last post, when I thought it worked) with no solution. any ideas? how do I successfully copy to the file so that I can use it to insert into a table?

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 3:54 pm
by requinix
What is the actual content of that webpage? Redact stuff if you need to.

Because at first it sounded like the page was really just CSV data, but if it has things like <br>s then that suggests it's actually an HTML page.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 4:34 pm
by seattle_newbie
requinix wrote:What is the actual content of that webpage? Redact stuff if you need to.

Because at first it sounded like the page was really just CSV data, but if it has things like <br>s then that suggests it's actually an HTML page.
here are the first few lines cut and pasted. emails and last names xxx'd out.

reservation_number,email,boat,first_name,last_name,price,tax,total,payments,booked_date,use_date,check_in,status
"912929","pspraxxx@xxx.com","Searay 260","Patrick","xxxxx","200","19.2","438","219.2","12/22/2014 10:08:20 PM","7/31/2015","9:30am","final"
"921329","jharxxxx@gmail.com","Chaparral 256","John","xxxxxx","100","9.6","109.5","109.6","1/2/2015 9:21:26 PM","5/2/2015","10:00am","final"
"921337","jharxxxx@gmail.com","Chaparral 256","John","Hxxxxx","200","19","219","219","1/2/2015 9:31:15 PM","7/3/2015","10:30am","final"
"924008","freixxxx@gmail.com","Chaparral 256","Veronica","xxxxx","180","17.1","197.1","197.1","1/5/2015 11:48:00 PM","7/4/2015","10:00am","final"

if I go to 'inspect element', I can see that it is an html page, with the rows defined by two <br> tags.

when I said "comma delimited format", I meant fields separated by commas. of course when I was copying and pasting this 'manually' it worked great.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 4:45 pm
by requinix
Okay, yeah, the page is HTML and the content of the page is some CSV text. What you copy and paste is not the same as what you get when actually retrieve the page using something like file_get_contents().

So you what you need to do is extract the text from within the page itself. What is the HTML source of that page? Not the copy/paste version but the View Source version.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 5:19 pm
by seattle_newbie
requinix wrote:Okay, yeah, the page is HTML and the content of the page is some CSV text. What you copy and paste is not the same as what you get when actually retrieve the page using something like file_get_contents().

So you what you need to do is extract the text from within the page itself. What is the HTML source of that page? Not the copy/paste version but the View Source version.
if I "view page source", there is one row. here is the cut and paste from that: (just a bit of it)

reservation_number,email,boat,first_name,last_name,price,tax,total,payments,booked_date,use_date,check_in,status<br>"912929","pspxxxe@xxxcom","Searay 260","Patrick","xxxxxx","200","19.2","438","219.2","12/22/2014 10:08:20 PM","7/31/2015","9:30am","final"<br>"921329","jhxxxx@gmail.com","Chaparral 256","John","xxxx","100","9.6","109.5","109.6","1/2/2015 9:21:26 PM","5/2/2015","10:00am","final"<br>

Re: file_get_contents (or other suggested)

Posted: Tue Jan 19, 2016 11:41 pm
by Christopher
seattle_newbie wrote:reservation_number,email,boat,first_name,last_name,price,tax,total,payments,booked_date,use_date,check_in,status<br>"912929","pspxxxe@xxxcom","Searay 260","Patrick","xxxxxx","200","19.2","438","219.2","12/22/2014 10:08:20 PM","7/31/2015","9:30am","final"<br>"921329","jhxxxx@gmail.com","Chaparral 256","John","xxxx","100","9.6","109.5","109.6","1/2/2015 9:21:26 PM","5/2/2015","10:00am","final"<br>
That's not one row, that is multiple rows separated by "<br>' tags. You could str_replace('<br>', "\n", $data) and then parse as CSV. Or explode('<br>', $data) and then foreach() through the array and explode(',', $line).

Re: file_get_contents (or other suggested)

Posted: Wed Jan 20, 2016 10:32 am
by seattle_newbie
Got it. all I had to do was change the "LINES TERMINATED BY" from "\r\n" to "<br>" in the original (loading) code that I first posted. no need to modify the data at all.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 26, 2016 2:57 pm
by seattle_newbie
I am now trying to get this code to work in a php page, that will be called by a cron job. the cron is not the issue, getting the code to work in the page is the issue.

it works fine in phpadmin, and i was able to solve the line termination issue. but the code does not work when executing with php. if i use:

load data local infile 'mywebpage into table my table; this works in phpadmin but

mysqli_query ($db, "load data local infile 'mywebpage into table my table") does not work in the php page.

$reservations = file_get_contents('mywebpage') works to get the string as i have tested with a var_dump, but
i cannot figure out how to use that variable ($reservations) to load into the table.

about 12 hours into this recent event... any help appreciated.

thanks.

Re: file_get_contents (or other suggested)

Posted: Tue Jan 26, 2016 3:00 pm
by Celauran
How many records are we talking about? Prepared statement and iterate over your parsed list could work.