zsero zsero - 5 months ago 24
SQL Question

using Google Docs as a database?

I would like to create a very simple PHP page for a site, which would show a timetable / calendar like data, where each slot would be either free or would have some appointment in it.

Because all the data is actually just one table, something like {month, day, hour, talk_name, talk_description}, I thought why not use a Google Docs Spreadsheet as the database. OK, the main reason is that I'm just reading books about how to use MySQL in PHP, so I'm definately not on a level to:


  • create a nice admin interface for managing the events

  • make the whole thing safe (I mean all my idea about safety is to use .htaccess for an admin folder and make the site read-only elsewhere).



On the other hand everyone could use Google Spreadsheets for editing the table, so this way both the security aspects and the UI aspects would be solved.

My question is that how would you recommend me to do that? Google Docs can both publish in XML and CSV formats. Can I just use fgetcsv to get the datas? Can you give me some simple examples how to parse the csv, and if it would be efficient (ok, it will be less than 50 views a day), if I would do something like this (sorry for the abstract syntax)?

$source_csv = fgetcsv(...);

get_talk_name(x,y,z) {
for all rows in $source_csv {
if (month == x && day == y && hour == z) return talk_name
}
}

get_talk_desc(x,y,z) {
for all rows in $source_csv {
if (month == x && day == y && hour == z) return talk_name
}
}

Answer

So, while it might not be wise or scalable, yes, you can do it. Try this code:

<?php
$url = "https://spreadsheets.google.com/pub?hl=en&hl=en&key=0AupgXsRU8E9UdC1DY0toUUJLV0M0THM4cGJTSkNSUnc&output=csv";
$row=0;

if (($handle = fopen($url, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

Basically, publish the spreadsheet as public, change output to csv (from the default HTML) by manually editing the URL (ie, output=csv), fetch it, and then iterate over it line by line using fgetcsv.

If the spreadsheet looks like this:

enter image description here

This will output the following for the csv in question:

array(2) {
  [0]=>
  string(4) "Name"
  [1]=>
  string(5) "Value"
}
array(2) {
  [0]=>
  string(3) "Foo"
  [1]=>
  string(5) "13123"
}
array(2) {
  [0]=>
  string(3) "Bar"
  [1]=>
  string(3) "331"
}