Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>this a very basic example with with PHP and CURL</p> <p>I used your <strong>input file</strong> exactly and saved it as <code>input.txt</code></p> <pre><code>species,mrgids Sp1,4279 Sp1,8366 Sp1,21899 Sp1,21834 Sp1,7130 Sp1,1905 Sp1,21900 Sp1,5679 Sp1,5696 </code></pre> <p>and this is the <strong>PHP and CURL</strong> doing its stuff: </p> <pre><code>&lt;?php $base_url = "http://www.marineregions.org/rest/getGazetteerRecordByMRGID.json/%s/"; // just get the input file into an array to use $csv = read_file("input.txt"); // if you want to see the format of $csv print "&lt;pre&gt;".print_r($csv,true)."&lt;/pre&gt;"; // go through each csv item and run a curl request on it foreach($csv as $i =&gt; $data) { $mrgids = $data['mrgids']; $url = sprintf($base_url,$mrgids); $response = run_curl_request($url); if ($response!==false) { //http://us2.php.net/manual/en/function.json-decode.php $json = json_decode($response,true); if (!is_null($json)) { // this is where you would write the code to stick this info in // your DB or do whatever you want with it... print "&lt;pre&gt;$url \n".print_r($json,true)."\n\n&lt;/pre&gt;"; } else { print "error: response was not proper JSON for $url &lt;br/&gt;&lt;br/&gt;"; print $response."&lt;br/&gt;&lt;br/&gt;&lt;br/&gt;"; } } else { print "error: response was false for $url &lt;br/&gt;&lt;br/&gt;"; } } function read_file($filename, $has_headers=true, $assoc=true) { $headers = array(); $row = 1; if (($handle = fopen($filename, "r")) !== FALSE) { $return = array(); if ($has_headers) { if (($data = fgetcsv($handle, 1000, ",")) !==false) { $headers = $data; } } while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { if ($assoc) { $temp = array(); foreach($headers as $hi =&gt; $header) { $temp[$header] = (isset($data[$hi])) ? $data[$hi] : ''; } $return[] = $temp; } else { $return[] = $data; } } fclose($handle); } else { $return = false; } return $return; } // requires PHP CURL extension // http://php.net/manual/en/function.curl-setopt.php function run_curl_request($url) { // create curl resource $ch = curl_init(); $defaults = array( CURLOPT_POST =&gt; false, CURLOPT_HEADER =&gt; false, CURLOPT_URL =&gt; $url, CURLOPT_FRESH_CONNECT =&gt; true, CURLOPT_FAILONERROR =&gt; true, CURLOPT_RETURNTRANSFER =&gt; true, CURLOPT_FORBID_REUSE =&gt; true, CURLOPT_TIMEOUT =&gt; 4 ); curl_setopt_array($ch, $defaults); // $output contains the output string $output = curl_exec($ch); // close curl resource to free up system resources curl_close($ch); return $output; } ?&gt; </code></pre> <p>And if it worked, you get a bunch of these as <strong>output</strong>:</p> <pre><code>http://www.marineregions.org/rest/getGazetteerRecordByMRGID.json/4279/ Array ( [MRGID] =&gt; 4279 [gazetteerSource] =&gt; IHO 23-3rd: Limits of Oceans and Seas, Special Publication 23, 3rd Edition 1953, published by the International Hydrographic Organization. [placeType] =&gt; IHO Sea Area [latitude] =&gt; 39.749996185303 [longitude] =&gt; 5.0942182540894 [minLatitude] =&gt; 35.071937561035 [minLongitude] =&gt; -6.0326728820801 [maxLatitude] =&gt; 44.42805480957 [maxLongitude] =&gt; 16.221109390259 [precision] =&gt; 1079464.0796258 [preferredGazetteerName] =&gt; Mediterranean Sea - Western Basin [preferredGazetteerNameLang] =&gt; English [status] =&gt; standard [accepted] =&gt; 4279 ) </code></pre> <p><strong>notes:</strong></p> <ul> <li>I had to <a href="https://stackoverflow.com/questions/10939248/php-curl-not-working-wamp-on-windows-7-64-bit">do this</a> to get CURL to work on WAMP for PHP 5.3.13</li> <li><a href="http://us2.php.net/manual/en/function.json-decode.php" rel="nofollow noreferrer">json_decde()</a></li> <li><a href="http://php.net/manual/en/function.curl-setopt.php" rel="nofollow noreferrer">curl_setopt()</a></li> <li><a href="http://www.php.net/manual/en/function.curl-exec.php" rel="nofollow noreferrer">curl_exec()</a></li> <li><a href="http://php.net/manual/en/function.fgetcsv.php" rel="nofollow noreferrer">fgetcsv()</a></li> <li><a href="http://www.php.net/manual/en/function.curl-multi-exec.php" rel="nofollow noreferrer"><strong>curl_multi_exec()</strong></a> - look into this if you chose this route, you will want it </li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POretrieve data from online database using input file
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USgloomy.penguin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POretrieve data from online database using input file
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks a lot for the example! Great help. PHP is totally new to me so far...
      singulars
      1. PO
      1. USjO.
    2. COOh... okay... so what are you doing with this data? sticking it in your DB? make sure to use `pdo` or `mysqli`... and look for posts like this: [PHP PDO batch insert from multi dimensional array failing to insert into MySQL](http://stackoverflow.com/questions/13507496/).. anything with inserting with an associative array. instead of outputting the array to the screen, that's where your insert should take place. to make it easy on yourself, make the column names the same as their field names.
      singulars
      1. PO
      1. USgloomy.penguin
    3. COYeah, guess I would use PostGIS and import the data into my Postgres database. Then the plan is to use R for geo-statistics. Im familiar with R but I found [a good thread](http://gis.stackexchange.com/questions/64950/which-is-the-best-way-of-working-with-postgis-data-in-r) on the workflow from Postgres to R
      singulars
      1. PO
      1. USjO.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload