This is a demo page for the csvtoservice.php script that uses YQL to convert CSV resources to web interfaces.

What you can do with this

This script allows you to get any CSV file from the internet and turn it into a web interface that empowers users to search and filter the data.

The following is an example, try it out. Simply hit the search button and see the results.

The code

The above search form and table result is powered by a simple dataset: http://winterolympicsmedals.com/medals.csv. All you need to convert it to what you see above is the following code in your PHP documents:

<?php 
  include('csvtoservice.php');
  $content = csvtoservice(
    'http://winterolympicsmedals.com/medals.csv',
    array(
      'filter'=> array(
        'eventgender',
        'city'
      ),
      'rename'=> array(
        'noc'=>'country'
      ),
      'preset'=> array(
        'year'=> '1992'
      ),
      'prefill'=> array(
        'discipline'=> 'Alpine Skiing',
        'medal'=> 'Gold'
      ),
      'uppercase'=>true
    )
  );
  if($content){

    if($content['form']){
      echo '<h4>Filters</h4>';
      echo $content['form'];
    }

    if($content['table']){
      echo '<h4>Results</h4>';
      echo $content['table'];
    }

  }
?>

This is already a complex example, the simplest way to show a form and a result table for any CSV file is the following:

<?php 
  include('csvtoservice.php');
  $content = csvtoservice('http://winterolympicsmedals.com/medals.csv');
  if($content){

    if($content['form']){
      echo '<h4>Filters</h4>';
      echo $content['form'];
    }

    if($content['table']){
      echo '<h4>Results</h4>';
      echo $content['table'];
    }

  }
?>

You can see this in action here and it is part of the source code on GitHub.

The parameters and options

In essence, all you need to provide is a URL that points to a CSV file and the script does the rest. You assign a variable to the main function that will get the HTML as properties. For example:

$myservice = csvtoservice('http://winterolympicsmedals.com/medals.csv');

The returned properties will be:

• $myservice['form'] - the HTML form with all possible fields contained in the dataset.
• $myservice['table'] - the data table of the information returned by the form submission - this will only show up once the form is submitted.
• $myservice['json'] - the data in raw JSON format (for debugging).
• $myservice['yql'] - the YQL statement (for debugging).

You can see all of the information in the example with logging.

If you want to tweak the outcome of the form and the table and you want to change the data names or remove parts of it you can set an options array:

$myservice = csvtoservice(
  'http://winterolympicsmedals.com/medals.csv',
  array(
    'filter' => array(fieldnames),
    'rename'=> array(
      'field'=>'new name',
      'field2'=>'new name 2'
    ),
    'preset'=> array(
      'field'=>'preset value',
      'field2'=>'preset value 2'
    ),
    'prefill'=> array(
      'field'=> 'value',
      'field2'=> 'value 2',
    ),
    'uppercase'=>Boolean 
  )
);

• rename allows you to rename fields. In the above example the country who won the medals was defined as NOC, which makes sense, but reads much better as country.
• filter contains an array of fields to not show in the form or the table. This allows you to get rid of some parts of the data.
• preset is an array of fields to preset with a hard value. These fields will be part of the query of the data but will not be added to the form or displayed. This allows you to pre-filter the data. In the above example this was the year of the games.
• prefill is an array of fields to pre-fill the form with in case you want to give the end user a hint what they can search for.
• uppercase is an boolean value if the script should uppercase the first letter of the field name or not ("City" instead of "city").

That's it, really... Have fun!