Simple Queries

The only SQL queries that Plotalot allows are of the form "Get Me Something from Somewhere". In SQL that translates to:

SELECT field_name(s) FROM table_name(s)

These queries extract data from a database but do not allow any changes to be made to the database. You can add many functions to format the results the way you want, select the rows that come back, sort them, and restrict the number of rows returned. One of the simplest ways to query a database is to ask for all the columns of all the rows in a particular table. Try this:

SELECT * FROM xxx_content

Note that we use UPPER CASE to denote SQL language constructs, and lower case to denote names of things. You don't have to type SQL that way, we just do it here to help you see what is SQL and what are data names.

The query above brought back all the columns in the table, including many that we aren't very interested in. Try restricting the number of columns you bring back by naming the ones you want.

SELECT title, hits FROM xxx_content

Order By

SQL can sort rows into any order you want. For our "Most Popular" chart, we will want the content articles with the most hits, so we need to sort them. Try this:

SELECT title, hits FROM xxx_content ORDER BY hits

Well, it's close, but we got the least popular ones first - ascending order is the default. We can reverse the order by adding the DESCending clause:

SELECT title, hits FROM xxx_content ORDER BY hits DESC

Limit

We're almost there, but at the moment we are still bringing back all the rows in the whole table, and that would make a real mess of a pie chart. We really only want the ten most popular articles. In MySql, we can use the LIMIT clause:

SELECT title, hits FROM xxx_content ORDER BY hits DESC LIMIT 10

That's it. Now we have a list of the ten most popular articles on the site, starting with the most popular at the top. This is exactly the query we need for our first chart!

Where

Another, more powerful, way to restrict the dataset, is to tell the database more about the rows we really want. Let's say we want all the articles that have been read more than 10,000 times:

SELECT title, hits FROM xxx_content WHERE hits > 10000

Or how about the ten most popular articles published since a specific date:

SELECT * FROM xxx_content WHERE created > "2020-01-01" ORDER BY hits DESC LIMIT 10

Or between two dates:

SELECT * FROM xxx_content WHERE created BETWEEN "2020-01-01" AND "2020-12-31" ORDER BY hits DESC LIMIT 10