Creating a generic, open source, check-in app


Many years ago, when Foursquare was still cool, I built an app to let people check into chocolate bars. You know, "@edent is eating a Wonka Bar classic. 4/5🌟"

It was a quick and dirty hackday project. Ever since, I've had the hankering to build something a bit more generic. I currently use Untappd to check into beer and Vivino for wine.

I'd like to be able to check in to:

So, here's my half-day hack to make that happen. Code quality is awful!

Minimum Viable Check-In

At the most basic level, we need a list of things. A user searches through the list, finds the artwork / boardgame / movie / cheese, then posts it to their preferred social network.

That's pretty simple. Perhaps too simple. You might just as well type "Mmmm, delicious Cambanzola!"

I suppose a list gives you certainty of spelling. Ideally it should also have a canonical URl to share. Other metadata (who made it, image, price, style) can wait for now.

Users

The next thing we need is a way for users to register with the platform. Or do we? What a user really wants to do is...

  • Check in to something.
  • Retrieve their previous check ins.

Things like comparing with friends, recommendations, badges, mayors, cheeses near you, they can all wait until later.

So - for our awesome alpha we want

  1. A database of "things"
  2. A database of "users"
  3. A database of "user checked into this thing at this time and left this message"

My database-fu is puny, but this should be simple enough.

SHOW COLUMNS FROM things;
+------------+--------------+-----+
|   Field    |     Type     | Key |
+------------+--------------+-----+
| ID         | varchar(36)  | PRI |
| Name       | varchar(128) | UNI |
| Metadata1  | TINYTEXT     |     |
| Metadata2  | TINYTEXT     |     |
+------------+--------------+-----+

(I suppose at some point we'll want a foreign key for "Group who makes the thing" - for manufacturers and such like. But that can wait.)

Next, the users:

SHOW COLUMNS FROM users;
+------------+--------------+-----+
|   Field    |     Type     | Key |
+------------+--------------+-----+
| ID         | varchar(36)  | PRI |
| UserName   | varchar(128) | UNI |
| Metadata1  | TINYTEXT     |     |
+------------+--------------+-----+

(Again, we will probably want to store social media, email, passwords, avatar photos, in the future.)

Finally - storing the check-in themselves:

SHOW COLUMNS FROM checkins;
+---------+--------------+-----------+
|  Field  |     Type     |    Key    |
+---------+--------------+-----------+
| ID      | varchar(36)  | PRI       |
| time    | DATETIME     |           |
| userID  | varchar(36)  | users.id  |
| thingID | varchar(36)  | things.id |
| comment | TINYTEXT     |           |
+---------+--------------+-----------+

Let's Build This Thing!

CHeck-in as an Extremely Easy SErvice. Or, CHEESE for short.

Because I'm lazy, I'm going to use the Open Source HybridAuth to handle user registration. It allows people to sign in with a variety of social network logins. For the MVP, let's just use Twitter.

Creating the Databases

Using mySQL because why not. Obviously don't use password as your password. Move fast, break things.

CREATE DATABASE CHEESE;

USE CHEESE;

CREATE USER 'cheeser'@'localhost' IDENTIFIED BY 'password';
GRANT INSERT ON CHEESE . * TO 'cheeser'@'localhost';
GRANT SELECT ON CHEESE . * TO 'cheeser'@'localhost';

CREATE TABLE users(
   user_id VARCHAR(36) NOT NULL,
   twitter_username VARCHAR(128) NOT NULL,
   twitter_id VARCHAR(128) NOT NULL,
   PRIMARY KEY ( user_id )
);

CREATE TABLE cheeses(
   cheese_id VARCHAR(36) NOT NULL,
   cheese_name VARCHAR(128) NOT NULL,
   cheese_url VARCHAR(128) NOT NULL,
   PRIMARY KEY ( cheese_id )
);

CREATE TABLE checkins(
   checkin_id VARCHAR(36) NOT NULL,
   checkin_time DATETIME NOT NULL,
   user_id VARCHAR(36) NOT NULL,
   cheese_id VARCHAR(36) NOT NULL,
   comment TINYTEXT,
   PRIMARY KEY( checkin_id ),
   FOREIGN KEY( user_id ) references users( user_id ),
   FOREIGN KEY( cheese_id ) references cheeses( cheese_id )
);

Fill the Database with cheeses!

Did this manually for a dozen fromages.

INSERT into cheeses VALUES 
   (UUID(), "Gorwydd Caerphilly", "https://www.thecheesesociety.co.uk/product/gorwydd-caerphilly/");

Ending up with something like this:

select * from cheeses;
+--------------------------------------+----------------------+-----------------------------------------------------------------------------------------------------+
| cheese_id                            | cheese_name          | cheese_url                                                                                          |
+--------------------------------------+----------------------+-----------------------------------------------------------------------------------------------------+
| 48695c1b-1ad5-11e7-bfe7-a417319d3fd1 | Gorwydd Caerphilly   | https://www.thecheesesociety.co.uk/product/gorwydd-caerphilly/                                      |
| b1c19c27-1ad5-11e7-bfe7-a417319d3fd1 | Isle of Mull Cheddar | http://www.internationalcheese.co.uk/products/isle-of-mull-cheddar#.WERTzHWLTcc                     |
| c17ea994-1ad5-11e7-bfe7-a417319d3fd1 | Wellesley            | http://www.hillfarmdairy.co.uk/our-cheese/                                                          |
| d080a5bc-1ad5-11e7-bfe7-a417319d3fd1 | Wigmore              | http://www.villagemaidcheese.co.uk/wigmore_main.html                                                |
| e127fe73-1ad5-11e7-bfe7-a417319d3fd1 | Rollright            | https://www.thecourtyarddairy.co.uk/shop/buy-cheese/soft-cheese-smelly-cheese/buy-rollright-cheese/ |
| ed6bf65f-1ad5-11e7-bfe7-a417319d3fd1 | Tunworth             | http://hampshirecheeses.co.uk/our-cheeses/                                                          |
+--------------------------------------+----------------------+-----------------------------------------------------------------------------------------------------+

Let users register

Again, super basic, barebones, no real UI. Visit this website, log into Twitter, get your ID registered in the users database.

<?php
    //  Setup for HybridAuth
    $config = dirname(__FILE__) . '/hybridauth-2.9.1/hybridauth/config.php';
    require_once( "hybridauth-2.9.1/hybridauth/Hybrid/Auth.php" );

    function insert($tw_name,$tw_id)
    {
        //  Connect to the Database
        $mysqli = new mysqli("localhost", "cheeser", "password", "CHEESE");
        if ($mysqli->connect_errno) {
            echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
        }

        //  Does this already exist?
        $mysqli = new mysqli("localhost", "cheeser", "password", "CHEESE");
        $stmt = $mysqli->prepare("SELECT twitter_username FROM users WHERE twitter_id = ?");
        $stmt->bind_param("s",$tw_id);
        $stmt->execute();
        $stmt->bind_result($result);
        $stmt->fetch();
        if ($result == null) {
            //  Generate a GUID for the user
            $guid = guidv4();

            $stmt = $mysqli->prepare("INSERT INTO users(user_id, twitter_username, twitter_id) VALUES (?, ?, ?)");
            $stmt->bind_param('sss', $guid, $tw_name, $tw_id);

            $stmt->execute();

        }
        /* close statement and connection */
        $stmt->close();
        /* close connection */
        $mysqli->close();
    }

    function guidv4()
    {
        //  See http://php.net/manual/en/function.com-create-guid.php
        $data = random_bytes(16);   // PHP7+ only
        assert(strlen($data) == 16);
        $data[6] = chr(ord($data[6]) & 0x0f | 0x40); // set version to 0100
        $data[8] = chr(ord($data[8]) & 0x3f | 0x80); // set bits 6-7 to 10

        //  Magic
        return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
    }


    try{
        $hybridauth = new Hybrid_Auth( $config );

        $twitter = $hybridauth->authenticate( "Twitter" );

        $user_profile = $twitter->getUserProfile();
        $tw_name = $user_profile->displayName;
        $tw_id = $user_profile->identifier;


        echo "Hi there! " . $tw_name;
        insert($tw_name,$tw_id);
        //$twitter->setUserStatus( "testing!" );

    }
    catch( Exception $e ){
        echo "Ooophs, we got an error: " . $e->getMessage();
    }

Do The Check In!

We now end up with:

select * from checkins;
+--------------------------------------+---------------------+--------------------------------------+--------------------------------------+----------------------------+
| checkin_id                           | checkin_time        | user_id                              | cheese_id                            | comment                    |
+--------------------------------------+---------------------+--------------------------------------+--------------------------------------+----------------------------+
| 02463e2b-0052-40de-a105-ab59e8e8af6c | 2017-04-06 16:15:27 | c9c6ca60-66c5-4666-a632-0efac6957ef3 | e127fe73-1ad5-11e7-bfe7-a417319d3fd1 | That rollright was lovely. |
+--------------------------------------+---------------------+--------------------------------------+--------------------------------------+----------------------------+

Post it to Twitter

That's it!

We can display a user's history, and allow them to check in to further cheeses. Lean in!

A very basic looking HTML form

Look, there's nothing special about Check-In apps. As long as you have a fairly well sourced database of "things" you can pretty much build one in an afternoon. This was built over a couple of hours, interspersed with sampling cheese and wine. And my code quality shows!

I Would Like My One Billion Dollar Of VC Funding Now Please.

One thought on “Creating a generic, open source, check-in app

  1. "Ideally it should also have a canonical URl to share. Other metadata (who made it, image, price, style) can wait for now."

    You fool!™ This already exists. In Wikidata. Canonical URI? ✔ Other metadata? ✔

    For example, Q16920603 - aka https://www.wikidata.org/wiki/Q16920603 - is "Isle of Mull Cheddar", and that item (readable both as a web page, and as a linked-, open- data entity) tells you that it is a British cheese, made with cow's milk, with the potential to also include things like an image, IDs in other databases, official website, etc. It also has multilingual labels, so you can tell your French-speaking users that it's a "fromage britannique" and a "lait de vache" product - and do the same, even in languages you don't speak.

    And Wikidata has cheeses, beers, wines, artworks and, er, everything else. Like it's big sister Wikipedia, its crowd-sourced, and anyone can edit it.

What do you reckon?