The Science of Web Art, Design and Development

Simple hint, how to select a number random items from a MySQL database:

SELECT *
FROM your_table
ORDER BY rand()
LIMIT 2;

The code above, selects all the fields from a database table named “your_table” orders the results randomically and gets the first two. Change two for your favourite number.

Want to see an example of this working? Just look to your right! On the sidebar, there is a section named “Recommended Reading”. This is a section with books I like and recommend. I keep a list of books on a database and I pick two six at random with a query like this, each time a page is loaded.

Maybe you did knew this command already, or at least you heard about the ORDER BY command, but do you exactly know how it works and how further you can go with it?

I’ll show you some nice things in this article.

read full post…

In my opinion, one of the coolest things of the site I’ve built to Paulo gazela is the event agenda on the navigation bar.

Of course there are many calendar programs around to add to your site and it’s also possible to integrate google calendar or a similar tool, but all Paulo needed was a way to show the events he was going to be in for the folowing, say, 15 days, and an easy interface to add them.

The solution is very simple. We’ve built a database with the date, time and details of the events and we created a SQL query to get all the events 15 days from the current date.

A single SQL query like the one below can handle everything

SELECT *
FROM event, place
WHERE event.place=place.id
  AND CURDATE() < = event.thedate
  AND DATE_ADD(CURDATE(),INTERVAL 15 DAY) >= event.thedate
ORDER BY
  event.thedate ASC,
  event.thetime ASC;

If you want to understand better what this is, to build the database tables and get some more detail, read on.

read full post…