SQL: Using Unions in Queries

By: Caleb Jonasson - April 12th, 2011  Filed under: SQL Add Comment

The union is a very nice feature to have when working with pulling multiple items from multiple tables. This article is going to go over how to use a union to join information under designated columns.

What is a Union

A union is a feature of sql that allows us to join information from multiple tables and store it into one singular table. There are a few requirements in order to do this properly and if the requirements are not followed you are going to get a few error messages and/or incorrect data returned.

Rules of the Union Operator
1. The union operator requires the same amount of columns.
2. All of the columns need to have the same name.
3. The union operator needs to be placed between queries.

Column Setup

Because you need to have the same amount of columns and the same names for each columns there is a high chance that rather than parsing the content you will just be displaying generic information such as type, title and content.
Note: There is no limit to the amount of queries you are able to union together but it would end up being a rather large query.

An example of a working union operator.

1
2
3
4
5
6
7
8
9
10
11

SELECT name, job, title, salary
FROM job_1_employees
WHERE job
LIKE ?
 
UNION
 
SELECT name, job, title, salary
FROM job_2_employees
WHERE job
LIKE ?

In this query we are pulling the information from both tables and merging the content into one large list of company and sub company employees. The search is done based on the like operator.

An example of a broken union operator

1
2
3
4
5
6
7
8
9
10
11

SELECT book_nid, author, title, YEAR
FROM book_listings
WHERE title
LIKE ?
 
UNION
 
SELECT magazine_nid, publisher, title, YEAR
FROM magazine_listings
WHERE title
LIKE ?

In order to get a query like this to work properly we are going to have to store the information into some renamed columns to get it to work. Whenever dealing with a work around it is best to create an extra column with a null value rather than changing up the entire query format.

How to Fix the Union Query

1
2
3
4
5
6
7
8
9
10
11

SELECT book_nid nid, author creator, title, YEAR, 'book' AS TYPE
FROM book_listings
WHERE title
LIKE ?
 
UNION
 
SELECT magazine_nid nid, publisher creator, title, YEAR, 'magazine' AS TYPE
FROM magazine_listings
WHERE title
LIKE ?

You will notice that we have changed some of the different columns and created another column that will store the type of field that is being returned. This is a good thing to have when you need to create a link to a specific kind of page or display a special icon.

Tags: ,

Searching With %?% In a Prepared Statement

By: Caleb Jonasson - April 8th, 2011  Filed under: php, SQL Add Comment

A while back when working on a project I learned that when using the wild card: ‘%’ in SQL with a prepared statement you are going to get an error message. This is because the prepared statement class has an issue with a wild card surrounding wild cards. ‘%?%’.

Because of this a simple sql query such as this is going to have issues pulling data from the database.

1

$sql = "SELECT * FROM tbl_comments WHERE comment LIKE %?% LIMIT 20";

Rather than attempt to get the above code to work you are going to need to take a separate approach and store the wild cards in with the search variable. This only takes one extra line of code and your search will be back up and running in no time at all.

1
2

$comment = '%'.$comment.'%';
$sql = "SELECT * FROM tbl_comments WHERE comment LIKE ? LIMIT 20";
Tags: , , , ,

SQL – An introduction to Keys

By: Caleb Jonasson - May 23rd, 2010  Filed under: SQL Add Comment

The Primary Key

Cells inside of your database are all located through an X-axis and a Y-axis. The X-axis is a row and the Y-axis is the column. Because the information inside of a database is stored downward as you go through the table each added record needs a unique identifier to be found. This is where the primary key comes in.

For every table in your database a unique ID is required to find the records and because of this every table has exactly one Primary Key. Without a primary key there would be no way to reference the information stored inside of that row and data would become inaccessible very quickly. This is also the reason why the primary key cannot have the value of “null.”

In a table there can never two of the same key. Two keys would conflict with each other and return bad records, which is why it is impossible to have this. Generally when working on a database you never want to modify the key after it has been created. This would change how the data is accessed and would be a bad idea when it comes to referencing the information should anything go wrong. There are two different kinds of keys. The first kind is a simple key and which is made of one column. The second kind of key is a composite key, which is made of 2 or more columns.

In most cases the Primary key is an integer. This allows simple ordering and counting of records on system however there are cases where a hex number can be used or even a pattern of numbers and letters.

For example, say that we were creating a database for items in a store. Each item has a unique SKU but this is something can change over time so we would still need our primary key to be a number that is completely unique. The naming also will not work for a unique ID due to name changes or the same product name with a change in SKU, which happens over time with different versions and packaging.

Foreign Keys

Foreign keys are used to reference objects or rows from other tables. Theses keys generally reference parent tables from child tables and are used to cross data between tables. This means that the table with the foreign key is always going to be the child in the link.

Unlike the primary key, the foreign key can have a null value, which leads to no link being made between the two tables for that object. These keys also do not need to be unique; in fact they often never are unique do to the nature of how they are used.

An example of the foreign key, continuing from the example used in primary keys, will require us to add another column to the initial table so we will now have ID, SKU, and Vendor. The tables will look like this.

As you can see, in this example we have the foreign key doubling as a primary key in the lower table, which is used in as a vendor in the top table. This is a quick and simple way to store the values easily in the database and comes in handy when working with relationships.

Tags: , , , , ,