Displaying Mysql data to HTML using ejs

I have decided to start a blog to share my journey as I work on different projects, I think it would be good for me to take some time to reflect on problems I've had and how I dealt with them - it will be good for me to organize my thoughts and share my frustration with others. And hopefully, if you are having similar problems, this blog would somehow solve the problems you are having.

Warning: This is not a tutorial, everything I talk about here is problems I came across. If you are looking for a step-by-step guide you can look for another comprehensive post out there, this is not that post.

I am currently developing a project that shows a timeline and location of an artist commissioned work. Information on his early till late-career commissioned work, what types of media he worked with and the companies that commissioned him. Since this post is about dev I will not have too much "art talk" but that's a basic idea of what the project is about. The data for the project are from archival materials, I had to input all the data into markdown and convert it into CSV as I will be using MySql for the backend database. The reason for choosing Mysql is because of its ability to handle relational data. For this project, relational data is important as users would be able to explore different ideas when they click on a record rather than a dead end.

The beginning

I will be starting with Nodejs, Express and PlanetScale (Mysql database). This is the first time I use PlanetScale, it took me some time to get used to but once I got everything running it works perfectly (so far). If you have experience using Pscale, let us know what you think of it!

The first thing I want to do is display a table containing all the data in HTML. It sounds like such an easy task, but it took me a while to get it working. It's also a good time to mention that I've only had a year of experience with coding, so if you are an expert reading this thinking "this guy has no idea what he's doing" well, yes, please let me know if there are better ways to do things!! Anyhow, after connecting the database to my Nodejs project I was able to console log the data - which is a good start. The next thing to do is display it in a table in HTML. I've done that in PHP and it was really easy to do, however, I had no idea Nodejs is a bit more complicated.

Using .ejs

I used .ejs to act as my .HTML which I haven't done before. EJS stands for Embedded JavaScript which sets the template to read data and render to HTML page.

This is what a .ejs file looks like, not too different to an HTML file. It took me a while to understand the tags which I will talk more about later.

table.ejs:

Ejs is a static page that I'm telling my index.js to read, so I added the following line to my index.js - It is telling it to read the 'views' folder, where my .ejs file is in.

index.js:

app.use(express.static(__dirname + '/views'));

I also have to npm install ejs and require it like so.

index.js

const ejs = require('ejs');

app.set('view engine', 'ejs');
}));

The next important thing to add, the res.render() function code, it is telling the app to render index(.ejs) and callback the object. The line below is getting data from my database table and rendering the data (result) into index.ejs.

index.js:

app.get('/database_table', function(req, res) {
    db.query('SELECT * FROM database_table', function (err, result) {
      if (err) throw err;

      ///res.render() function
      res.render('index', {data: result});
    });
  });

Now that the data are now reaching index.ejs, it's time to set up the template and display the data into a table format. It is straightforward HTML table format . The only confusing part is the tags I've mentioned earlier and the include function. I had trouble getting it to work, after another few hours of troubleshooting I've finally figured out the right way to write the code.

table.ejs

...
  <tr>
    <% data.forEach(entry => { %>
      <td><%= entry.year %></td>
      <td><%= entry.Location %></td>
      <td><%= entry.Medium %></td>
      <td><%= entry.Region %></td>
  </tr>

<% }) %>
      </table>
    </div>
  </body>
</html>

After all that is done, npm run dev in my terminal, head to my localhost:port/database_table, my data are showing on a table within the correct columns. It seems easy now that I put all this together, but it took me 3 days to get this working.

The next thing I will be working on is a timeline, I will be using d3js to visualize the data. That will be on the next blog. If you are still reading this - I don't know why you would but thank you!!! I have started many first blog posts in my life, let's hope that I will write another one...I will see you at the next one(or not)?

Follow me on Twitter Pixelcaveman