file_get_contents (or other suggested)
Moderator: General Moderators
-
seattle_newbie
- Forum Newbie
- Posts: 10
- Joined: Mon Jan 18, 2016 6:35 pm
file_get_contents (or other suggested)
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
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)
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?
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?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: file_get_contents (or other suggested)
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.
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.
(#10850)
-
seattle_newbie
- Forum Newbie
- Posts: 10
- Joined: Mon Jan 18, 2016 6:35 pm
Re: file_get_contents (or other suggested)
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:
- 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.
- 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')- 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.
-
seattle_newbie
- Forum Newbie
- Posts: 10
- Joined: Mon Jan 18, 2016 6:35 pm
Re: file_get_contents (or other suggested)
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:
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.
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) #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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: file_get_contents (or other suggested)
My guess is that the problem is either the backslashes (use forward slashes in PHP) or the space in the file name.
(#10850)
-
seattle_newbie
- Forum Newbie
- Posts: 10
- Joined: Mon Jan 18, 2016 6:35 pm
Re: file_get_contents (or other suggested)
on further review, the following code:
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?
Code: Select all
$new_text_file2 = 'C:\\xampp\\reskey_downloads\\new 3';
copy('http://mywebpage', $new_text_file2) Re: file_get_contents (or other suggested)
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.
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.
-
seattle_newbie
- Forum Newbie
- Posts: 10
- Joined: Mon Jan 18, 2016 6:35 pm
Re: file_get_contents (or other suggested)
here are the first few lines cut and pasted. emails and last names xxx'd out.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.
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)
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.
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.
-
seattle_newbie
- Forum Newbie
- Posts: 10
- Joined: Mon Jan 18, 2016 6:35 pm
Re: file_get_contents (or other suggested)
if I "view page source", there is one row. here is the cut and paste from that: (just a bit of it)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.
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>
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: file_get_contents (or other suggested)
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).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>
(#10850)
-
seattle_newbie
- Forum Newbie
- Posts: 10
- Joined: Mon Jan 18, 2016 6:35 pm
Re: file_get_contents (or other suggested)
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.
-
seattle_newbie
- Forum Newbie
- Posts: 10
- Joined: Mon Jan 18, 2016 6:35 pm
Re: file_get_contents (or other suggested)
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.
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)
How many records are we talking about? Prepared statement and iterate over your parsed list could work.