How would I scrape this JSON info using PHP and MySQL?

Question

Here's the info I'm trying to break up into a database. I'm going to be using this only for my own use to analyse statistics and all that. I have been manually doing it with Excel but I'd like to save myself some work in future.

URL IS: http://fantasy.premierleague.com/web/api/elements/537/

Any idea how to scrape that info or easily convert it to excel format? I know a bit of php and mysql, but nothing about JSON and very little about scraping (I tried messing with SIMPLE_HTML_DOM).

First, use jsbeautifier.org to make it human readable. it will make your life easier. Also, everyone is right, use json_decode and you will get back an array with data and more arrays. Take out what you want and create a csv file (en.wikipedia.org/wiki/Comma-separated_values and php.net/manual/en/function.fputcsv.php) — Levi
– Levi, Commented Dec 31, 2012 at 5:19
Wow. thanks for that. I saw a ton of JSON formatters, but it was still hard to read as it is a complicated one, but your link is a lot better. Thanks so much. — Cully
– Cully, Commented Dec 31, 2012 at 5:23

ravi404 · Accepted Answer · 2012-12-31 05:32:08Z

3

You can convert it into an array as

 $array = json_decode(file_get_contents('http://fantasy.premierleague.com/web/api/elements/537/'));

json_decode()

You could use PEAR excel writer to convert it into excel

edited Dec 31, 2012 at 5:32

answered Dec 31, 2012 at 5:21

ravi404

7,3094 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ravi404 Over a year ago

@shiplu.mokadd.im 'your data' does not mean a normal string. It is clear that I meant the JSON data

Cully Over a year ago

Thanks ravz. This looks like it'll get me what I want, and thanks also for the PEAR thingy... It looks very useful and exactly what I'll want to do once I get the database up and running.

Sir · Accepted Answer · 2012-12-31 05:24:47Z

2

You need to JSON_decode the data in PHP.

$obj = JSON_decode($mydata));
print_r($obj);

Extra information for you: http://php.net/manual/en/function.json-decode.php

edited Dec 31, 2012 at 5:24

answered Dec 31, 2012 at 5:18

Sir

8,28517 gold badges88 silver badges154 bronze badges

2 Comments

Cully Over a year ago

Thanks so much. I thought it was complicated, but turns out to be simple.

Sir Over a year ago

You can also use var_dump($obj); to see the contents of $obj

Shiplu Mokaddim · Accepted Answer · 2012-12-31 05:37:56Z

2

PHP has a json parser function json_decode().

So:

Use the file_get_contents() function to read the json content from the URL into a string.
Use json_decode() to create a PHP structure representation.
Use PEAR Spreadsheet_Excel_Writer module to create your excel spreadsheet.

Yeah. Easy as 1, 2, 3.

edited Dec 31, 2012 at 5:37

Shiplu Mokaddim

57.5k20 gold badges147 silver badges193 bronze badges

answered Dec 31, 2012 at 5:22

Ethan Brown

7136 silver badges12 bronze badges

1 Comment

Cully Over a year ago

Yeah.. I wasn't aware of the JSON_DECODE() command even after googling, but that looks to be exactly what I'm looking to do. Thanks!

danish hashmi · Accepted Answer · 2012-12-31 05:18:30Z

1

<?php

$x=json_decode(file_get_contents('http://fantasy.premierleague.com/web/api/elements/537/'));
print_r($x);//$x will contain all the values in an array format.

?>

answered Dec 31, 2012 at 5:18

danish hashmi

4845 silver badges12 bronze badges

Comments

Arun Killu · Accepted Answer · 2012-12-31 05:22:27Z

1

<?php
// Create a stream
$opts = array(
  'http'=>array(
    'method'=>"GET"
  )
);

$context = stream_context_create($opts);

// Open the file using the HTTP headers set above
$json= file_get_contents('http://fantasy.premierleague.com/web/api/elements/537/', false, $context);
$arr= json_decode($json);

$xml = new SimpleXMLElement('<root/>');
array_walk_recursive($arr, array ($xml, 'addChild'));
print $xml->asXML();

answered Dec 31, 2012 at 5:22

Arun Killu

14.3k5 gold badges38 silver badges66 bronze badges

1 Comment

Cully Over a year ago

Looks complicated but I'll check it out and see if it does what I need. Thanks a lot!

gview · Accepted Answer · 2012-12-31 05:23:57Z

1

PHP makes it very easy:

$str = file_get_contents('http://fantasy.premierleague.com/web/api/elements/537/');
$jsonarray = json_decode($str, true);
var_dump($jsonarray);

Of course you will have to analyze the structure of the array and figure out how to decompose it to what you are actually looking for.

answered Dec 31, 2012 at 5:23

gview

15.5k3 gold badges53 silver badges56 bronze badges

2 Comments

Cully Over a year ago

Thanks! Yeah the structure is complicated but now that I know how to break it up, I can figure out how to sort the data.

gview Over a year ago

One other thing you can do, for KISS loading, is spit out a .csv file, which excel can easily load, as an alternative to some of the other more complicated suggestions like the pear library. With csv, all you really need to do is make sure you have double quotes around the strings, commas between the column values, and a CRLF at the end of each line.

Jake Sankey · Accepted Answer · 2012-12-31 05:24:29Z

1

Try using $obj = json_decode($jsonStr) after you receive your response string by running curl on the URL you mentioned in PHP. Then you can grab params from the json object like

$obj['paramName'];

Then you can do anything you want with the information including putting it into a database.

For simple MySQL interaction in php, check out MySQLConnector class.

http://jakesankey.com/blog/2011/12/php-mysql-helper-class/

answered Dec 31, 2012 at 5:24

Jake Sankey

5,15712 gold badges41 silver badges54 bronze badges

Comments

Yogesh Suthar · Accepted Answer · 2012-12-31 05:31:40Z

1

use just json_decode and get the converted data like this edit

$arr = json_decode('your JSON data',true);

echo $arr['transfers_out'];  // output 490374  //for array
echo $arr->transfers_out;  // output 490374  //for stdClass

edited Dec 31, 2012 at 5:31

answered Dec 31, 2012 at 5:18

Yogesh Suthar

30.5k18 gold badges75 silver badges100 bronze badges

2 Comments

Shiplu Mokaddim Over a year ago

-1 Fatal error: Cannot use object of type stdClass as array

Shiplu Mokaddim Over a year ago

The array version will not work. you need to pass the second parameter of json_decode

Collectives™ on Stack Overflow

How would I scrape this JSON info using PHP and MySQL?

8 Answers 8

2 Comments

2 Comments

1 Comment

Comments

1 Comment

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

2 Comments

2 Comments

1 Comment

Comments

1 Comment

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related