DEV Community

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
Enter fullscreen mode Exit fullscreen mode

wut

I would much prefer the following than that solution:

SELECT * FROM images ORDER BY NATURAL DESC LIMIT 8
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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.

Discussion

pic
Editor guide
Collapse
ben profile image
Ben Halpern

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

Collapse
skiilaa profile image
skiilaa Author

Yup! Working on it!

Collapse
ben profile image
Ben Halpern

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.

Thread Thread
skiilaa profile image
skiilaa Author

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.

Thread Thread
skiilaa profile image
skiilaa Author

I open-sourced the project! GitHub