April 18th, 2005 
[Tutorial:SQL] Basic Selects, Basic SQL
( http://forums.neverside.com/view/thread86911/ )
Hi.
To go through the SQL tutorials, you'll need acces to a server with PHP and MySQL. Once you have these, and access to them, install PHPMyAdmin so you have an easy to use MySQL console for the first portion of the series.
Download the most recent version here:
Downloads (http://www.phpmyadmin.net/home_page/downloads.php)
Install it. You'll need a mysql user/pass, and at least one database. If it's on a public server, password protect it. This is all covered in substantial detail n the PHPMyAdmin (hereafter referred to as PMA) docs.
Lets go over a very important SQL basic.
Whitespace doesnt count
Like in PHP, white space is not considered by the parser. Use this to your advantage! Putting queries on multiple lines and indents will greatly simplify debugging and increase legibility.
Which of the following is easier to read?
PHP:<?php
SELECT title,news.summary,news.entry,news.user,news.email,news.date,COUNT(news_comments.id) num_comments FROM news LEFT JOIN news_comments ON news.id = news_comments.news_id GROUP BY news.id ORDER BY news.date DESC;
OR
SELECT
title
, news.summary
, news.entry
, news.user
, news.email
, news.date,
COUNT(news_comments.id) num_comments
FROM
news LEFT JOIN
news_comments
ON news.id = news_comments.news_id
GROUP BY news.id
ORDER BY news.date DESC;
?>
Thats the main reason for spacing your code 
What is SQL? A language used to interact with a "relational database management system", or RDBMS. MySQL is technically a RDBMS (well, not in the truest sense because its not fully relational), as opposed to SQL. A little specific to be aware of.
What does Relational Database Model mean? Basically, your data is organized in multiple tables, with rows and coloumns. It's relational because tables refer to each other - a table "news_topics" might have a column "author_id". The table "authors" might have coloumns "id" and "name". There is a connection between id on authors (authors.id) and author_id on news_topics (news_topics.author_id). The advantage of this relational system is normality.
What is normality? In the simplest sense, it is ensuring that no data is ever repeated. It also involves something called referential integirty (sp?), but that is beyond the current scope of this tutorial. Using the relational system, you can ensure no data is repeated.
So lets move on to some statements.
The first thing we'll take a look at is the SELECT statement - this is actually the most powerful (and, in the same breath, complex) statement MySQL supports. The SELECT statement does what it says - retrieves data from the database.
First, let's create some tables. I'll cover this in depth in a later tutorial; for now, just copy and paste.
Pick a database to make these tables - it doesnt matter which one. If you dont have one, create one!. Click on the SQL tab, and you should see a text box with " Run SQL query/queries on database tutorials" above it. The word "tutorials" will have whatever the name of your database is.
PHP:<?php
#
# Table structure for table `articles`
#
CREATE TABLE `articles` (
`id` int(10) unsigned NOT NULL auto_increment,
`author` int(10) unsigned NOT NULL default '0',
`submitted` datetime NOT NULL default '0000-00-00 00:00:00',
`topic` varchar(255) NOT NULL default '',
`content` text NOT NULL,
PRIMARY KEY (`id`),
KEY `author` (`author`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
#
# Dumping data for table `articles`
#
INSERT INTO `articles` VALUES (1, 1, '2005-04-17 20:42:00', 'SQL Tutorial', 'Quick update on my life, I\'m writing some SQL tutorials.');
INSERT INTO `articles` VALUES (2, 1, '2005-04-15 14:30:30', 'College', 'I\'m double majoring in business management and actuarial science. How cool is that?');
INSERT INTO `articles` VALUES (3, 4, '2004-04-01 08:01:01', 'April Fools!', 'Hi guys, april fools! Be safe!');
INSERT INTO `articles` VALUES (4, 2, '2004-02-14 10:32:32', 'Happy Valentines Day', 'Hi guys! Be cool, stay in school');
# --------------------------------------------------------
#
# Table structure for table `authors`
#
CREATE TABLE `authors` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
#
# Dumping data for table `authors`
#
INSERT INTO `authors` VALUES (1, 'Phil', 'phil@philbrodeur.com');
INSERT INTO `authors` VALUES (2, 'Scott', 'scott@bigtoach.com');
INSERT INTO `authors` VALUES (3, 'Raja', 'aonic@aonic.net');
INSERT INTO `authors` VALUES (4, 'James', 'angelessme@hotmail.com');
# --------------------------------------------------------
#
# Table structure for table `comments`
#
CREATE TABLE `comments` (
`id` int(10) unsigned NOT NULL auto_increment,
`article_id` int(10) unsigned NOT NULL default '0',
`author_id` int(10) unsigned NOT NULL default '0',
`submitted` datetime NOT NULL default '0000-00-00 00:00:00',
`topic` varchar(255) NOT NULL default '',
`content` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
#
# Dumping data for table `comments`
#
INSERT INTO `comments` VALUES (1, 2, 3, '2005-04-17 13:04:32', 'Congrats', 'You\'re so cool dude');
INSERT INTO `comments` VALUES (2, 2, 4, '2004-04-17 06:00:00', 'awesome', 'good work phil!');
INSERT INTO `comments` VALUES (3, 4, 1, '2004-03-03 15:06:06', 'happy birthday to me!', 'wise tips from a great man.');
?>
That created tables articles. authors, and comments: a pretty common set up.
Authors is simple - it has columns 'id', 'name', and 'email'. ID is a unique identifier for every row, and name and email are self explanatory.
Articles is for news topics, or articles, whatever. It has 5 columns. ID, a unique identifier (not to be confused with authors.id), author (a link to author.id), submitted (when was the article psoted), topic (header line), and content.
Comments has comments on articles. It has 6 rows. ID (same as above), article_id (connects the comment to an article), author_id (connects the comment to an author), and then submitted, topic, and content (same as above).
Lets atart simple - lets get everything from the articles table. Go to where you can execute queries.
PHP:<?php
SELECT * from articles;
?>
That was simple! The * selects all columns, but using it is bad practice - what if we expand our table, but dont use all the columns in this query? We're wasting memory. Lets revise our query to:
PHP:<?php
SELECT
`id`
, `author`
, `submitted`
, `topic`
, `content`
FROM
articles
?>
Good - same result, as I expected. You'll notice I used ` around column names - do this. (Note that ` is different then ', and apostrophe). You'll see how I seperated column names by a comma. If you put the comments in front of a column name (as shown here), you wont accidently have an extra one. This way of formatting comma-seperated lists is accepted as standard good practice.
Hmm - I want to know the author name, not just the id. Can we do this? Yup!
PHP:<?php
SELECT
articles.id
, `author`
, authors.name
, `submitted`
, `topic`
, `content`
FROM
articles
, authors
?>
You'll notice I added another table to from. I also un-quoted column names - why? Well, we had the column id in both authors and articles. This makes it ambiguous - MySQL doesn't know which one we're referring to. By putting the table name infront, we can clear this up. However, when you put the table name in front, you dont apostrophe the name. Just something to remember
If you run this, you'll notice something is wrong: we're getting way too many results. Why? MySQL is selecting all the rows from BOTH authors and articles. We can use a WHERE clause to limit what we want selected.
We only want author names where articles.author matches authors.id:
PHP:<?php
SELECT
articles.id
, `author`
, authors.name
, `submitted`
, `topic`
, `content`
FROM
articles
, authors
WHERE
authors.id = articles.author
?>
There we go - that works as expected.
You know, I dont like using the plural form in my queries - I'd rather use author as a table name instead of authors. How can I do this? I'll simply add a table alias, and change column names to match:
PHP:<?php
SELECT
article.id
, `author`
, author.name
, `submitted`
, `topic`
, `content`
FROM
articles AS article
, authors AS author
WHERE
author.id = article.author
?>
There we go - no change in results, and a simpler query. But wait - can we do this with a column to? Yup:
PHP:<?php
SELECT
article.id as article_id
, `author` as author_id
, author.name as author
, `submitted`
, `topic`
, `content`
FROM
articles article
, authors author
WHERE
author.id = article.author
?>
If you run that, you'll see things have been cleared up. You can further simplify column aliasing by dropping the word as (not for tables though):
PHP:<?php
SELECT
article.id article_id
, `author` author_id
, author.name author
, `submitted`
, `topic`
, `content`
FROM
articles article
, authors author
WHERE
author.id = article.author
?>
There we go! No change. Hmm, this is looking a bit messy - lets add whitespace. A bit of tab space clears things up. Edit - looks like the tabs didnt work quite right in Jeremie-Code, i'll space them
PHP:<?php
SELECT
article.id article_id
, `author` author_id
, author.name author
, `submitted`
, `topic`
, `content`
FROM
articles article
, authors author
WHERE
author.id = article.author
?>
You know, now that we have the author name, we dont need the id. Lets not select it.
PHP:<?php
SELECT
article.id article_id
, author.name author
, `submitted`
, `topic`
, `content`
FROM
articles article
, authors author
WHERE
author.id = article.author
?>
There we go! Simple results.
Now... I want to see these from oldest to newest. I'll add an order by clause. I'll order it ascending, or ASC (1,2,3,4..). Descending is the reverse. For letters, ASC is (A,B,C) while DSC is (Z,Y,X).
PHP:<?php
SELECT
article.id article_id
, author.name author
, `submitted`
, `topic`
, `content`
FROM
articles article
, authors author
WHERE
author.id = article.author
ORDER BY
`submitted` ASC
?>
That is basic relational selecting.
Next time, we'll get into some advanced selects:
* Selecting Multiple Tables
* Select Distinct
* Advanced Where Clauses
* Advanced Ordering
* Counting
* Grouping
* Joins
* Date/Time Stuff
And more!'
To see what I meant about the tabs, get this as a txt file and peek. http://www.philbrodeur.com/public/basic_select.txt
___________________
http://www.philbrodeur.com - Expert PHP Development and Tutorials
Last edited by Phil, April 18th, 2005 03:08 AM (Edited 3 times)
. I dont think it's too bad if you tab it so everything is aligned. 
