300 Bottles of Beer on the Wall…

300 beers!

It’s known to some that I’ve been keeping a beer log for — I’m not exactly sure how long, maybe a couple of years. I wanted to know how many unique beers I was going through over time. I know certain people that do this, and who have long-since crossed the 600 mark. I’ve most likely done that myself over the many years that I’ve been tasting beer, but without the records to back that up, it’s a guess.

I’ve made a bit of a habit of trying to only purchase beers that I haven’t yet tried. Surprisingly, the available pool of such hasn’t dried up in poor little Adelaide just yet. I have of course supplemented the local availability with beers from my travels around the world in the last two years, including Paris, London, Germany, and Italy.

I started by writing a list of each and every commercial (available in either bottles, cans, or on tap, through the exchange of currency) beer I tasted. To be fair, these aren’t all beers that I’ve paid full price for and consumed in their entirety; a sip counts if I tasted it, even if someone else bought it. Under this definition, I’m also excluding home brews. For the sake of simplicity, I’m also excluding most things that I personally don’t count as “beer” (don’t get me started on the difficulties of pigeonholing that definition) such as ginger beer and the like. There’s also no need to point out some of the lower-quality beers (typically American mainstream lagers) on the list that I would gladly refrain from calling “beer”. For the purposes of this list, they count.

I’m still debating whether or not to go through my many beer texts and add the beers that I 100% recall tasting. I may get around to doing that in the future.

The list on its own would be a little unweildy – not only is it by definition compiled under the influence of alcohol, but it lacks most of the useful information such as ‘is this beer unique on the list’ and in several cases, either the brewery or beer name itself, or in some cases is simply a shorthand designation such as ‘LCPA’ which I instantly recognise as Little Creatures Pale Ale. I very quickly gave up on the idea of keeping tasting notes, simply because I know that almost any beer I get to at the end of a large session will be completely biased in judgement of its finer qualities.

As I was beginning to re-learn MySQL at the time of starting the list, I decided to put some of my programming skills and the availability of my self-hosted website to good use. Given that my iPhone was also jailbroken at the time, the extraction process was able to be contained nicely in a shell script that was run (via crontab) every night at midnight, which:

  • Checked to see if my iPhone responded to pings on my wifi network (DHCP controlled, designated IP)
  • If so, opened an ssh tunnel to my iPhone, and rsync’d my Notes to a backup directory
  • Scanned the Notes SQLlite file for a Note beginning with the tag ‘BEERLIST’
  • If found, extracted the body of the note into a CSV (under the vague assumption that I’d entered data in a usable format)

At this point, a little human intervention was required, in which I fixed up the CSV list of beers/breweries by hand. From there, I re-exported to a CSV file. I had MySQL create a database table, read in the CSV, remove duplicate beers, order by brewery, and generate linked tables of breweries and beers. It also counts the number of occurances of each beer, though I’m not currently leveraging that functionality. The less of that code anyone sees, the better. It’s a mess, and I won’t be posting it here.

That’s all well and good if I wanted everyone to come over and SELECT DISTINCT(BREWERY) FROM BEERLIST from within the database. The next step was to get it visible. A little PHP was all I needed to query the database on each page load (with the login details nicely hidden by the HTML-spitting script) and a tiny bit more HTML/css/PHP to get it neatly presented in a clean table containing appropriate links to each alphabetical category of brewery. I’ve tilted the formatting towards my own iPhone screen, since that’s mainly where I want to view the site.

The entire page is dynamic based on what the current state of the database is. The code is provided in case anyone’s interested.

<html>
<head>
<title>Jono's Beer List</title>
&lt;style type=&quot;text/css&quot;&gt;

@media screen {

body {
width: 640px;
font: 12pt Helvetica, sans-serif;
} 

img, table {
max-width: 640px;
} 

}

&lt;/style&gt;

&lt;/head&gt;
&lt;meta name=&quot;viewport&quot; content=&quot;width=300;&quot; /&gt;
&lt;body bgcolor=&quot;#FFFF66&quot;&gt;

&lt;div class=&quot;content_letters&quot;&gt;
<a id="top">&nbsp;</a><br />
      <?php
      $array_letter = array(&quot;A&quot;,&quot;B&quot;,&quot;C&quot;,&quot;D&quot;,&quot;E&quot;,&quot;F&quot;,&quot;G&quot;,&quot;H&quot;,&quot;I&quot;,&quot;J&quot;,
	                    &quot;K&quot;,&quot;L&quot;,&quot;M&quot;,&quot;N&quot;,&quot;O&quot;,&quot;P&quot;,&quot;Q&quot;,&quot;R&quot;,&quot;S&quot;,&quot;T&quot;,
                            &quot;U&quot;,&quot;V&quot;,&quot;W&quot;,&quot;X&quot;,&quot;Y&quot;,&quot;Z&quot;,&quot;#&quot;,&quot;END&quot;);

      for ($i=0;$i<9;$i++) {
        echo "<a href='#{$array_letter[$i]}'>{$array_letter[$i]}  |    ";
      }
      echo &quot;&lt;br /&gt;&quot;;
      for ($i=10;$i<19;$i++) {
        echo "<a href='#{$array_letter[$i]}'>{$array_letter[$i]}  |    ";
      }
      echo &quot;&lt;br /&gt;&quot;;
      for ($i=20;$i<25;$i++) {
        echo "<a href='#{$array_letter[$i]}'>{$array_letter[$i]}  |    ";
      }
      echo &quot;&lt;a href='#1'&gt;#  |  &lt;/a&gt;&quot;;
      echo &quot;&lt;a href='#bottom'&gt;END&lt;/a&gt;&quot;;
    ?&gt;
&lt;br /&gt;&lt;br /&gt;
&lt;/div&gt;

&lt;div id=&quot;container&quot; align=&quot;center&quot;&gt;
<?php
$con=mysqli_connect(&quot;localhost&quot;,&quot;jono&quot;,&quot;password&quot;,&quot;beerlog&quot;);
// Check connection
if (mysqli_connect_errno())
  {
  echo &quot;Failed to connect to MySQL: &quot; . mysqli_connect_error();
  }

$result = mysqli_query($con,&quot;SELECT beer, brewery FROM beers ORDER BY brewery, beer&quot;);

echo &quot;&lt;table border='0' width='95%'&gt;
&lt;tr align=\&quot;left\&quot;&gt;
&lt;th height='20px'&gt;#&lt;/th&gt;
&lt;th&gt;Brewery&lt;/th&gt;
&lt;th&gt;Beer&lt;/th&gt;
&lt;td&gt;&lt;/th&gt;
&lt;/tr&gt;&quot;;

$j = 1;
$prev = &quot;&quot;;
while($row = mysqli_fetch_array($result))
  {
  $newbrewery = ($row['brewery'][0] > $prev[0]);
  echo &quot;&lt;tr&gt;&quot;;
  if ($newbrewery) {
    echo &quot;&lt;tr&gt;&lt;td colspan=\&quot;4\&quot;&gt;&lt;hr&gt;&lt;/td&gt;&lt;/tr&gt;&quot;;
  }
  echo &quot;&lt;td&gt;&quot; . $j . &quot;&lt;/td&gt;&quot;;
  if ($newbrewery) {
    echo &quot;&lt;td id=\&quot;&quot; . $row['brewery'][0] . &quot;\&quot;&gt;&quot; . $row['brewery'] . &quot;&lt;/td&gt;&quot;;
  /*  echo &quot;&lt;td&gt;&quot; . $row['brewery'][0] . &quot;&lt;/td&gt;&quot;; */
  } else {
    echo &quot;&lt;td&gt;&quot; . $row['brewery'] . &quot;&lt;/td&gt;&quot;;
  /*  echo "<td> &nbsp; </td>"; */
  }
  echo &quot;&lt;td&gt;&quot; . $row['beer'] . &quot;&lt;/td&gt;&quot;;
  if ($newbrewery) {
    echo "<td> <a href=#top style=\"text-decoration: none\">&uarr;</a> </td>";
  } else {
    echo "<td> &nbsp; </td>";
  }
  echo &quot;&lt;/tr&gt;\n&quot;;
  $j = $j + 1;
  $prev = $row['brewery'][0];
  }
echo &quot;&lt;/table&gt;&quot;;

mysqli_close($con);
?&gt;
&lt;/div&gt;

&lt;div class=&quot;content_letters&quot;&gt;
<a id="bottom">&nbsp;</a><br />
      <?php
      $array_letter = array(&quot;A&quot;,&quot;B&quot;,&quot;C&quot;,&quot;D&quot;,&quot;E&quot;,&quot;F&quot;,&quot;G&quot;,&quot;H&quot;,&quot;I&quot;,&quot;J&quot;,
	                    &quot;K&quot;,&quot;L&quot;,&quot;M&quot;,&quot;N&quot;,&quot;O&quot;,&quot;P&quot;,&quot;Q&quot;,&quot;R&quot;,&quot;S&quot;,&quot;T&quot;,
                            &quot;U&quot;,&quot;V&quot;,&quot;W&quot;,&quot;X&quot;,&quot;Y&quot;,&quot;Z&quot;,&quot;#&quot;);

      for ($i=0;$i<9;$i++) {
        echo &quot;&lt;a href='#{$array_letter[$i]}'&gt;{$array_letter[$i]}  |  &lt;/a&gt;  &quot;;
      }
      echo &quot;&lt;br /&gt;&quot;;
      for ($i=10;$i&lt;19;$i++) {
        echo &quot;&lt;a href='#{$array_letter[$i]}'&gt;{$array_letter[$i]}  |  &lt;/a&gt;  &quot;;
      }
      echo &quot;&lt;br /&gt;&quot;;
      for ($i=20;$i&lt;25;$i++) {
        echo &quot;&lt;a href='#{$array_letter[$i]}'&gt;{$array_letter[$i]}  |  &lt;/a&gt;  &quot;;
      }
      echo &quot;&lt;a href='#1'&gt;#  |  &lt;/a&gt;&quot;;
      echo &quot;&lt;a href='#top'&gt;TOP&lt;/a&gt;&quot;;
    ?&gt;
&lt;br /&gt;&lt;br /&gt;
&lt;/div&gt;

&lt;/body&gt;
&lt;/html&gt;

Last but not least, an update to the A records on the DNS host I use allowed me to use beer.jcarroll.com.au as a sub-domain (separate to this site) for free. It’s not the prettiest site, but it’s quite functional now for what I’m after. The important fact being that the index of unique beers (keeping in mind the possibility of a few undetected duplicates) has crossed 300. Woo!

The (duplicate-filtered, alphabetical, searchable) list itself has been quite useful in situations where I’m asked the “have you tried X?” question. There’s still plenty to add to the list, and a stack of new breweries coming on line regularly means there’s no better time for us to catch up for a drink sometime.

The site: beer.jcarroll.com.au — why not share a beer that’s not on the list with me?

Cheers!

Leave a Reply