Database work with MySQL and SQL

Jumping back to the server, an understanding the technologies behind Joomla! can't be considered complete without a discussion on Structured Query Language (SQL). SQL is the language of choice for most databases, and anytime we want to add or retrieve data from the database of a Joomla! site, we will need to do it via SQL.

SQL is a relatively straightforward and simple language to pick up, but it can get quite complex at higher levels of development. It is designed purely to interact with data in a database and can do very little else.

The four most common SQL commands we will use are:

There are others, but these are the ones we will need to know most.

Following one of these initial commands will usually be a preposition and a table name. Or it could be a comma separated list of columns, then the proposition and table name and is written in fairly easy to understand language. For example:

SELECT name, address FROM person_table …
INSERT INTO person_table …
UPDATE person_table …
DELETE FROM person_table …

For SELECT, UPDATE, and DELETE, there will also usually be a WHERE clause that provides the criteria upon which to do the search.

SELECT name, address FROM person_table WHERE age > "16"
UPDATE person_table … WHERE name = "John Doe"
DELETE FROM person_table WHERE name = "John Doe"

For INSERT queries, there will also be a set of values to insert, and possibly a list of columns to INSERT INTO.

INSERT INTO person_table (name, address, age)
VALUES ( "John Doe","10 This St, Atown","25")

For UPDATE queries, they are again a little different, but easy to understand.

UPDATE person_table SET address="25 Anew St, Atown" WHERE name = "John Doe"

Something we will sometimes see with SELECT statements is the use of an asterix (*) character in place of a column list. This is shorthand to indicate that we want to use all of the available columns, arranged in the same order as they appear in the database.

SELECT * FROM person_table WHERE age > "16"

Something we will quickly notice if we are looking over existing Joomla! database queries is that almost all of them will have a hash and two underscores before the table name. For example:

SELECT * FROM #__person_table

This hash underscore is replaced at run time with the current database prefix. So #__person_table will be converted into something similar to jos_person_table by the Joomla! database functions when they run the query.

The database prefix exists to allow people to install multiple Joomla! sites and use the same database for all of them, without the data for each one overwriting the others. The different sites can then just use different prefixes, and that way they keep all their data in separate tables.