loading...
Cover image for Why SQL is overcomplicated & SateMage Release - SateMage Day 2

Why SQL is overcomplicated & SateMage Release - SateMage Day 2

skiilaa profile image skiilaa ・2 min read

And SQL still can't work on first try.

Searching

This was the easiest of all functions(at least after I figured out reversing the natural sort of MySQL).

I basically used SELECT with a % wildcard in the LIKE operator, like %query%, then place the results in the search HTML template.

NOTE: I also added the natural sort-reverse tweak.

The frontend was also changed: I added a search form into the header.

The Index Page

The index page wasn't easy to make.

I used the card-group class in BootStrap for keeping the cards that contain the images in a line, and replaced some placeholder texts with the cards.

I executed 2 SQL queries:

  1. 8 newest images
  2. 8 most popular images

1. Newest images

I used the "natural sort-reverse" tweak that I mentioned earlier.

It uses variables to reverse the natural sort:

SELECT *, @counter := @counter + 1 AS 'counter' FROM images, (SELECT @counter := 0) r ORDER BY counter DESC LIMIT 8

wut

I would much prefer the following than that solution:

SELECT * FROM images ORDER BY NATURAL DESC LIMIT 8

But of course this doesn't exist, so deal with it I guess.

After getting the results of this, I use a foreach loop to build the HTML that will replace the placeholder text.

2. Most popular images

This is a simple SELECT query with an ORDER BY operator:

SELECT * FROM images ORDER BY views DESC LIMIT 8

This selects the top 8 rows based on the field views.

The viewcounter

The viewcounter was fairly easy to make.

The following SQL query is executed after the image is sent to the user, and it is ignored if an error happens:

UPDATE images SET views=views+1 WHERE id='imageId'

This query increments the view field by 1 where the id matches the specified ID.

The Release

SateMage has been released! Check it out!

Also, donate to me, if you want to help this become better.

Posted on by:

Discussion

markdown guide
 

So cool project, Moricz! Any thoughts of open-sourcing this?

 
 

Great. Let me know when you do. I think I could help drive some design/front end folks towards the project to make it look really crisp.

Cool! Thanks! I will reply here with the repository link. I should be able to open-source it today. The code needs improvement, but I am actively working on that.

I open-sourced the project! GitHub