Playing with MySQL and HTTP

What is this?

Hi, you're reading the fourth part of this series if you're interested in reading what I've written so far you can find the posts here:

• Preamble
• First week of Learning
• Thinking about conf files

That being said, here's a very quick run down for if you're just jumping in here; I work in IT Support, the job requires interaction with the terminal directly but people make mistakes so I wrote some software to do user creation through Samba. Unfortunately, the software felt a little clunky, though it was a hit in the office, and wanted to re-write it properly. I decided I'd opensource the project and document my journey.

MySQL

I haven't decided to which database software to use yet (there is an open issue on GitHub, if you want to have your say), I've had MongoDB suggested to me but as I'm used to MySQL I'm look into that too. I think it's important for me to learn new things during this process even if I don't end up using them in the final project.

Code

As with most things Node, I found a module I could use and set to work writing a very simple test program.

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'hostname',
  user: 'hostuser',
  password: 'password',
  database: 'database'
});

connection.connect();

let table = "user_details";

connection.query(`SELECT * FROM ${table}`, (err, res) => {
  if (err) throw err;
  for (let result of res) {
    console.log(`${JSON.stringify(result)}`)
  }
});

connection.end();

As I said, very simple. It simply outputs a line of data to the console for each user in the table.

HTTP

I will, in the long run, want to use HTTPS or perhaps even HTTP/2 but I need to get the basics down first, I have a habit of running ahead in a project, getting bogged down/confused and then losing interest. Slow and steady truly does win the race.

Code

I'm sure many, if not all, of you, have seen a simple hello world HTTP example but here it is again, just in case.

const http = require('http');

http.createServer((req, res) => {
  res.write('Hello World!');
  res.end();
}).listen(80);

Now simply browse to localhost/ and there we have it a white page with Hello World! written at the top. Another very simple, boring, example.

Putting it together

As both of these examples were straightforward I decided to go off-piste and mix the two together, I haven't quite reached routing yet so my code was in the server script which, I guess, is really bad practice.

I decided I wanted to make a simple script that could look in a MySQL table and output the results to an HTML table. My main thoughts were, it's simple enough to throw together and I'll have something to look at by the end.

Code

const http = require('http');
const mysql = require('mysql');

const database = mysql.createConnection({
  host: 'hostname',
  user: 'hostuser',
  password: 'password',
  database: 'database'
});

let table = "user_details";

// Variable for storing HTML
let outputTable = "";

// Database connection
database.connect();
database.query(`SELECT * FROM ${table}`, (err, res) => {
  if (err) throw err;
  // Push results to `outputTable`
  outputTable += "<div class='table-container'><table>"
  for (let key of Object.keys(res[0])) {
    outputTable += `<th>${key}</th>`
  }
  for (let result of res) {
    outputTable += `<tr>`
    for (let key of Object.keys(result)) {
      outputTable += `<td>${result[key]}</td>`
    }
    outputTable += `</tr>`
  }
  outputTable += "</table>"
});

database.end();

// CSS
let styles = `<style>
  .table-container {
    max-width: 85%;
    width: 1024px;
    border-radius: 4px;
    overflow: hidden;
    margin: 0 auto;
    box-shadow: 0 3px 6px rgba(0,0,0,0.16), 0 3px 6px rgba(0,0,0,0.23);
  }
  table {
    border-collapse: collapse;
    width: 100%;
  }
  th {
    font-size: 18px;
    color: #fff;
    line-height: 1.4;
    padding: 2px 5px;
    background-color: #6c7ae0
  }
  td {
    font-size: 15px;
    color: #808080;
    line-height: 1.4;
    padding: 0px 5px;
  }
  tr:nth-child(even) {
    background-color: #f8f6ff;
  }
</style>`

http.createServer((req, res) => {
  res.write(styles);
  res.write(outputTable);
  res.end();
}).listen(80);

I'm sure many of you are cringing at my code but, as I said, this was just a way to put together what I'd learnt as time goes on I'll learn the correct way to do things.

If you do want to set me straight you can leave a comment here and I'll read it and try and incorporate it in my coding practices from now on, anything you contribute will really help.

The output

Just in case you're curious, here's the table:

That's another one done.

Woah, this was a long one. Thank you for reading. I was wondering if the titles of these posts needed changing Adventures of a Hobbyist ~ Part ${n} is fine but feels like it might be a bit bland, what do you lot think? As always if there is anything I'm doing that's blindingly wrong or you just have something to add feel free to leave a comment or pop over to my GitHub and leave an issue.

I'm on vacation next week but have actually done a little more this week I could write about, mostly regarding more config stuff continuing on from the last post. I may pencil a post and then publish it next week. If not I won't post until I'm back (08/28).

Thanks again for reading ❤️