<<

. 19
( 132 .)



>>


Outer joins will come up frequently out of necessity. Additionally, it is often
good practice to use outer joins even when you feel an inner join will do the trick.
It™s just a matter of being safe: You™d rather not have important rows of data come
up missing because you forgot to account for null values. Throughout the book you
will see occasions when we have used outer joins because we just wanted to be
extra careful.
There may come times when you will need to do more than one outer join. Say,
for instance (and for no particularly good reason), you wanted to store information
regarding spouses™ siblings. You™d add another table listing the siblings, and add a
column to the desired_spouse table, which maintains the relationship. So if you
Chapter 3: Inserting, Editing, and Selecting Data 85

were to design a query that maintained everyone in the contacts table and every-
one returned from the spouses table, you™d have to throw in two outer joins:

select *
from contacts
left join desired_spouse on contacts.spouse_id =
desired_spouse.fantasy_spouse_id
left join on siblings desired_spouse.sibling_id =
siblings.sibling_id;


The self join
As bizarre as it may sound, the time will come when you™ll need to join a table to a
copy of itself. You™ll usually run into the need to do this when looking for dupli-
cates in a table. If you had a sneaking suspicion that there was a bigamist in Table
3-15, how would you search out the two people with the same spouse?


TABLE 3-15 THE CONTACTS TABLE

contact_id first_name last_name spouse_id

Jason Fullman
1 1

Brad Bulger
2

John James
3 2

Elliot Simms
4 2




You would need to discover if the value in this spouse_id field was repeated (in
this case, the number 2 appears more than once). You could do a group by, but
then there would be no way of getting the names of the people involved. Using
group by along with the count() function, you could find the occasions on which
one person appears more than once, but it would take a second query to find out
who those people were. With a self join you can do it all in one step. But it needs to
be a carefully considered step.
You might think that the following query would do the trick. Notice that we
again use an alias, so that we have two table names we can address:

select t1.first_name, t1.last_name, t2.first_name, t2.last_name
from contacts t1, contacts t2
where t1.spouse_id = t2.spouse_id;
86 Part I: Working with MySQL

But this is going to return more rows than we need. Specifically, each name will
match itself, providing duplicates of each returned entry. Given this query, when the
row for Jason is compared to itself, it will test true and be returned in the result.
You can eliminate redundancy here by ensuring that the contact_id field from the
first table is not equal to the contact_id field in the second table:

select t1.first_name, t1.last_name
from contacts t1, contacts t2
where t1.spouse_id = t2.spouse_id
and t1.contact_id != t2.contact_id;

This is good but not perfect. Take the example of Elliot and John. A row will be
returned when Elliot is in t1 and John is in t2; another will be returned when John
is in t1 and Elliot is in t2. The easiest way to address that problem here is to make
use of the numeric primary key. You know one ID will be greater than the other,
and by using that information you can get rid of all duplicates.

select t1.first_name, t1.last_name
from contacts t1, contacts t2
where t1.spouse_id = t2.spouse_id
and t1.countact_id < t2.contact_id;


Unions
Unions allow queries with the same number of columns to be returned in one result
set. For instance, if you have two tables storing user names, you can have all of the
names in one query returned with a statement like this:

select first_name, last_name
from table_1
union
select first_name, last_name
from table_2;


Correlated subqueries
Subqueries are a new feature in MySQL version 4.1. Their addition is welcome, par-
ticularly for developers with Oracle or PostgreSQL familiarity, who have been able
to use them all along.
For those new to the concept, subqueries enable you to define an entire query in
the where clause. For example, if you have a table that stores students and their test
scores, you can easily find all the students with better-than-average test scores:

select first_name, last_name, score
from test_scores
where score > (select avg(score) from test_scores);
Chapter 3: Inserting, Editing, and Selecting Data 87

You can achieve the same effect by running two queries, storing the results of the
first query in program variables or a temporary table, and then using those results in
the second query. In most cases you can work around the absence of subqueries by
running additional queries. You lose some elegance, but the effect is identical.



Summary
You can get through the better part of your life without committing some portions
of SQL to memory. If you are using graphical tools you may not need to learn the
specifics of the create or alter commands. The same cannot be said of the insert,
update, delete, and select statements.
Everything covered in this chapter is really important to your life as an applica-
tions developer. The insert, update, and delete statements are what enable you
to have a database in the first place. They™re what you need to add records, change
records, and remove records. The select statement enables you to efficiently
retrieve and sort information from your databases, and if you understand the intri-
cacies of the select statement you™ll be able to write applications more efficiently
and elegantly. And if you™re able to combine all of those statements with effective
table-joining techniques, you™re well on your way to managing data in MySQL
efficiently.
Part II
Working with PHP
CHAPTER 4
Getting Started with PHP ” Variables
CHAPTER 5
Control Structures
CHAPTER 6
PHP™s Built-in Functions
CHAPTER 7
Writing Organized and Readable Code
Chapter 4

Getting Started with
PHP ” Variables
IN THIS CHAPTER

— Assigning variables within PHP scripts

— Handling data passed from HTML forms

— Working with PHP™s built-in variables, including Apache variables

— Testing for and assigning variable types



PHP with variables extremely easy. PHP is smart about under-
MAKES WORKING
standing variable types and keeps the syntax to an absolute minimum. Those com-
ing to PHP from a C, Java, or Perl background may find PHP comparatively easy to
deal with, but the ease of syntax can present its own problems.
All variables in PHP start with a dollar sign ($). It doesn™t matter what kind of
variables they are, whether strings, integers, floating-point numbers, or even
arrays. They all look identical in the code. The PHP engine keeps track of the type
of information you are storing.
In general, variables will come from three places: They are either assigned within
a script, passed from an HTML page (often from form input), or are part of your
PHP environment. We™ll talk about each of these in the following sections.



Assigning Simple Variables
Within a Script
PHP does not require explicit variable declaration. All you have to do is assign a
value to a variable and it exists. And as we already mentioned, all variable types
look identical. The following code shows how to assign values of string, integer,
and floating-point (double) types to variables:

$a = “this is a string”; //this is a string
$b = 4; //this is an integer
$c = 4.837; //this is a floating-point number
91
$d = “2”; //this is another string
92 Part II: Working with PHP

Notice that the = is the assignment operator. For comparison, you must use two
consecutive equals signs (= =). For example, if($x==1).
If you try to use a variable without having assigned a value to it, your code still
runs ” treating the unheard-of variable as having a value of NULL ” but PHP issues
a notice (an E_NOTICE level error message) warning you that the variable is unde-
fined. Since it™s best practice to make sure your variables have been initialized with
some value, even if it™s just NULL, this is a good reason to make sure your error
reporting levels are set to display or log all PHP errors, at least during development.


For more information about error reporting, see the Error Handling and
Logging Functions section of the PHP Manual (http://www.php.net/
errorfunc).



Typing is flexible, and PHP is pretty smart about handling changes in types. For
example, given the code you just saw, the following would evaluate as you™d prob-
ably hope:

$e = $b + $d;
echo $e;

PHP would recognize that you want to treat the string in $d as an integer. The
variable $e will be an integer type and will equal 6. In fact, PHP will also evaluate
the following as an integer:

$a = 2;
$b = “2 little piggies”;
$c = $a + $b;

Here, $c will equal 4. If an integer or floating-point number is at the beginning
of a string, PHP can evaluate it as such. Similarly, PHP handles automatic conver-
sion sensibly as well, as seen in the following code:

$f = 2; //$f is an integer
$g = 1.444; // $g is a double (floating-point) type
$f = $f + $g; //$f is now a double type

PHP will also do type conversions when comparing two variables. This is a good
thing, because the most common values for a script, entries submitted from an
HTML form, always come in as strings. Here™s an example:

$a = ˜1.3™;
if ($a == 1.3)
{
Chapter 4: Getting Started with PHP ” Variables 93

echo “˜$a™ is 1.3\n”;
}
else
{
echo “˜$a™ is not 1.3\n”;
}



The result:

˜1.3™ is 1.3

If you need to make a strict comparison, where the types as well as the values
must match, you can use a triple equal sign (===) operator (or its inverse, !==). This
most commonly arises when you need to distinguish between 0, NULL, FALSE, and
an empty string, since in a normal comparison these will all be treated as equal. The
following code demonstrates:

$a = 0;
if ($a === 0)
{
echo “˜$a™ is 0\n”;
}
else
{
echo “˜$a™ is not 0\n”;
}
if ($a === FALSE)
{
echo “˜$a™ is FALSE\n”;
}
else

<<

. 19
( 132 .)



>>