Introducing Databases and SQL
Deciding How to Store Data
Whenever
you start work on a data - driven application, one of your first design
decisions should be: how will the
application store and access its data?
Database
Architectures Broadly
speaking, you have two main options: embedded and client - server.
Embedded
Databases
An
embedded database engine, as its name implies, sits inside the application
that uses it (PHP in this case). Therefore it always runs — and stores its data
— on the same machine as the host application.
The
database is not networked, and only one program can connect to it at any given
time. Moreover, the database can ’ t be shared between different machines
because each one would simply end up storing and manipulating its own separate
version of the data.
On
the plus side, embedded databases tend to be faster, easier to configure, and
easier to work with.
Client
- server databases are, generally speaking, more powerful and flexible than
embedded databases. They are usually designed for use over networks, enabling
many applications in a network to work simultaneously with the same data. The
database engine itself acts as a server, serving up data to its clients (much
like Web servers serve pages to Web browsers).
This
is the kind of database you ’ re more likely to find in a large company, where
large quantities of data need to be shared among many people, where access may
be needed from all sorts of different locations.
Simple Databases
Simple
database engines are, as the name implies, just about the simplest type of
database to work with. Essentially, the simple model is similar to an
associative array of data. Each item of data is referenced by a single key. It
’ s not possible to define any relationships between the data in the database.
Relational
databases offer more power and flexibility than simple databases, and for this
reason they tend to be a more popular choice. They are also known as RDBMSs
(Relational Database Management Systems).
RDBMSs
are often expensive and complex to set up and administer. The widely
acknowledged big three in this field are Oracle, DB2 (from IBM), and SQL Server
(from Microsoft).
Understanding Relational Databases
In
simple terms, a relational database is any database system that allows data to
be associated and grouped by common attributes.
Typically,
a relational database arranges data into tables, where each table is divided
into rows and columns of data.
Normalization
Talking
to Databases with SQL SQL, the Structured
Query Language, is a simple, standardized language for communicating with relational
databases. SQL lets you do practically any database - related task, including
creating databases and tables, as well as saving, retrieving, deleting, and
updating data in databases.
MySQL
Data Types MySQL
supports three main groups of data types — numeric, date/time, and string
Numeric
Data Types
Date and Time Data
Types
When you need to
specify a literal DATE , DATETIME , or TIMESTAMP value in MySQL, you can use
any of the following formats:
YYYY - MM - DD / YY
- MM - DD
YYYY - MM - DD
HH:MM:SS / YY - MM - DD HH:MM:SS
YYYYMMDD / YYMMDD
YYYYMMDDHHMMSS /
YYMMDDHHMMSS
String Data Types MySQL lets you
store text or binary strings of data in many different ways, as shown in the
following table:
The difference
between a CHAR and a VARCHAR field is that CHAR stores data as a fixed - length
string no matter how short the actual data may be, whereas VARCHAR uses exactly
as many characters as necessary to store a given value
Using Indexes and Keys
An index is a
separate sorted list of the values in a particular column (or columns) in a
table. Indexes are also often called keys ; the two words are largely interchangeable.
You can optionally add indexes for one or more columns at the time you create
the table, or at any time after the table is created.
Introducing SQL
Statements
To
actually work with databases and tables, you use SQL statements. Common
statements include:
SELECT
— Retrieves data from one or more tables
INSERT
— Inserts data into a table
REPLACE
— Replaces data in a table. If the same record exists in the table, the
statement
overwrites
the record with the new data
UPDATE
— Updates data in a table
DELETE
— Deletes data from a table
Other
often - used statements create or modify tables and databases themselves,
rather than manipulating the data stored in a table:
CREATE
— Creates a database, table or index
ALTER
— Modifies the structure of a table
DROP
— Wipes out a database or table.
MySQL
can deal with another special value known as NULL . In a MySQL table, a
NULL value for a field represents missing data in that field. NULL doesn ’ t
belong to any particular data type, but it can replace any value.
The
MySQL database system comes with a number of different programs. The two
important ones are:
The
MySQL server
— This is the database engine itself. The program is usually called mysqld or similar
The
MySQL command - line tool — You can use this tool to talk directly to the MySQL
server so that you can create databases and tables, and add, view, and delete
data. The program name is simply mysql
Ubuntu
—
Choose System Administration Services. In the dialog that appears, look for the
“
Database server (mysql) ” item in the list. If there ’ s a check mark to the
left of the item, it should already be running. If not, click Unlock, type your
password, and click Authenticate. Now click the checkbox to the left of the “
Database server (mysql) ” item. The MySQL database server ( mysqld ) should now
be running
Now
that the MySQL database engine is running on your computer, it ’ s time to
configure the MySQL root user.
When
MySQL installs, it creates the root account automatically, but doesn ’ t set a
password for it.
To
set up a root password, follow these steps:
1.
Bring up a shell prompt —
2.
Change to the correct folder —
cd
C:\wamp\bin\mysql\mysql5.0.51b\bin
and
press Enter.
3.
Start the MySQL command - line tool — On Ubuntu and Windows, type
mysql
-u root
4.
Inspect the current privileges — Enter the following at the mysql > prompt
and press Enter:
SELECT
Host, User, Password FROM mysql.user;
5.
Add passwords for the root users — Type the following lines, replacing mypass
with the password you want to use and mattscomputer with the host name of your
computer (shown in the Host column in the table), and pressing Enter after each
line:
SET
PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘mypass’);
SET
PASSWORD FOR ‘root’@’mattscomputer’ = PASSWORD(‘mypass’);
SET
PASSWORD FOR ‘root’@’127.0.0.1’ = PASSWORD(‘mypass’);
6.Check
that the passwords have been set — Retype the SELECT line from Step 4 and press
Enter.
7.
Exit the MySQL command - line tool — Type exit and then press Enter to return
to the shell prompt.
To
test the new password, run the mysql command again, but this time, add a - p
(hyphen followed by “ p ” ) to the end of the command line, as follows:
mysql
-u root -p # Ubuntu, Windows
./mysql
-u root -p # Mac OS X
It
’ s easy to create a new MySQL database. First, fire up the MySQL command -
line tool using the same method that you used when changing the root password.
Open a shell prompt, change to the correct folder (if using Windows or Mac OS
X), and then on Ubuntu or Windows type:
mysql
-u root –p
Press
Enter. Now enter the root password you specified earlier, and press Enter
again. You should see the prompt appear:
mysql
>
To
create a new database:
CREATE
DATABASE mydatabase;
To
list of all the databases in the system
mysql
> SHOW DATABASES;
Creating
a Table
The
first thing to do is select the database you just created.
Database
changed
Now create your table.
mysql > CREATE TABLE fruit (
- > id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- > name VARCHAR(30) NOT NULL,
- > color VARCHAR(30) NOT NULL,
- > PRIMARY KEY (id)
- > );
Press
Enter at the end of each line. Don ’ t enter the “ -> “ arrows; MySQL
displays these automatically each time you press Enter.
To
see a list of tables in your database
mysql > SHOW TABLES;
To
see the structure of your newly created table
mysql > EXPLAIN fruit;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| color | varchar(30) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
id
is the primary key. It uniquely identifies each row of the table. You created
the id field as
SMALLINT
UNSIGNED , which means it can hold integer values up to 65,535.
name
will store the name of each fruit.
color
was created in the same way as name
if
you wanted to add an index for the name
if
you ever want to create a regular key (as opposed to a primary key) for a field
in a table, use the keyword KEY or INDEX instead of PRIMARY KEY.
mysql > CREATE TABLE fruit (
- > id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
- > name VARCHAR(30) NOT NULL,
- > color VARCHAR(30) NOT NULL,
- > PRIMARY KEY (id),
- > KEY (name)
- > );
Adding
Data to a Table
To
add a new row to a table, you use the SQL INSERT statement. In its basic form,
an INSERT statement looks like this:
INSERT
INTO table VALUES ( value1 , value2 , ... );
Alternatively,
you can create a row with only some fields .The remaining fields will contain
NULL (if allowed), or in the case of special fields such as an AUTO_INCREMENT
field, the field value will be calculated automatically.
To
insert a row of partial data, use:
INSERT
INTO table ( field1 , field2 , ... ) VALUES ( value1 , value2 , ... );
So
you can add three rows to the fruit table by inserting data into just the name
and color fields (the id field will be filled automatically):
mysql
> INSERT INTO fruit ( name, color ) VALUES ( ‘banana’, ‘yellow’ );
mysql
> INSERT INTO fruit ( name, color ) VALUES ( ‘tangerine’, ‘orange’ );
mysql
> INSERT INTO fruit ( name, color ) VALUES ( ‘plum’, ‘purple’ );
Reading
Data from a Table
To
read data in SQL, you create a query using the SELECT statement. To retrieve a
list of all the data in your fruit table, you can use:
mysql > SELECT * from fruit;
mysql > SELECT * from fruit;
+----+-----------+--------+
| id | name | color |
+----+-----------+--------+
| 1 | banana | yellow |
| 2 | tangerine | orange |
| 3 | plum | purple |
+----+-----------+--------+
3 rows in set (0.00 sec)
You
can also specify just the field or fields you want to retrieve:
mysql > SELECT name, color from fruit;
To retrieve a selected row or rows, you need to introduce a WHERE clause at the end of the SELECT statement.
mysql > SELECT * from fruit WHERE name = ‘banana’;
Updating
Data in a Table
You
change existing data in a table with the UPDATE statement.
mysql > UPDATE fruit SET name = ‘grapefruit’, color = ‘yellow’ WHERE id = 2;
mysql
> SELECT * from fruit;
Deleting
Data from a Table
To
delete rows, you use the DELETE statement. If you add a WHERE clause, you can
choose which row or rows to delete; otherwise all the data in the table are
deleted (though the table itself remains).
Here
’ s an example:
mysql > DELETE FROM fruit WHERE id = 2;
mysql
> SELECT * from fruit;
Deleting
Tables and Databases
To
delete a table entirely, use the DROP TABLE statement. Similarly, you can
delete an entire database with DROP DATABASE .
mysql > DROP TABLE fruit;
DROP DATABASE works in a similar fashion:
mysql
> DROP DATABASE mydatabase;
Connecting to MySQL from PHP
PHP provides you with two main ways to connect to MySQL databases:
mysqli (MySQL improved) — This extension is specifically tied to MySQL, and provides the
most
complete access to MySQL from PHP. It features both procedural (function -
oriented) and object - oriented interfaces.
PDO
(PHP Data Objects)
— This is an object - oriented extension that sits between the MySQL server and
the PHP engine. It gives you a nice, simple, clean set of classes and methods
that you can use to work with MySQL databases.
Making a Connection
To
make a connection to a MySQL database in your PHP script, all you need to do is
create a new PDO object.
$conn = new PDO( $dsn, $username, $password );
DSN, which describes the database to connect to;
username
is the user you want to connect as;
user
’ s password.
A DSN , or Database Source Name, is simply a string that describes attributes of the connection such as the type of database system, the location of the database, and the database name .
For example, the following DSN can be used to connect to a MySQL database called mydatabase running on the same machine as the PHP engine:
$dsn
= “mysql:host=localhost;dbname=mydatabase”;
If host isn ’ t specified, localhost is assumed
So, putting it all together, you could connect to mydatabase database as follows (replacing mypass with your real root password of course):
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “mypass”;
$conn = new PDO( $dsn, $username,
$password );
To close the connection, just assign null to your connection variable. This effectively destroys the PDO object, and therefore the connection:
$conn = null;
Handling Errors
Database
errors can be notoriously difficult to track down and deal with. One of the
nice things about PDO is that you can get it to return MySQL errors in the form
of highly descriptive PDOException objects. You can then use the PHP keywords
try and catch to handle these exceptions easily and deal with them
appropriately.
To set PDO to raise exceptions whenever database errors occur, you use the PDO::SetAttribute method to set your PDO object ’ s error mode, as follows
Now you can capture any error that might occur when connecting to the database by using a try ... catch code block.
try
{
$conn
= new PDO( $dsn, $username, $password );
$conn->
setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch
( PDOException $e )
{
echo
“Connection failed: “ . $e-> getMessage();
}
PHP
runs the code within the try block. If an exception is raised by PDO, the catch
block stores the PDOException object in $e , then displays the error message
with
$e->getMessage()
.
For
example, if the $password variable in the script contained an incorrect
password, you ’ d see a message like this appear when you ran the script:
Connection failed: SQLSTATE[28000] [1045] Access denied for user ‘root’@’localhost’ (using password: YES)
Reading
Data
To
send SQL statements to the MySQL server, you use the query method of the PDO
object:
$conn->
query ( $sql );
The result returned by $conn -> query is actually another type of object, called a PDOStatement object. You can use this object along with a foreach loop to move through all the rows in the result set. Each row is an associative array containing all the field names and values for that row in the table.
For
example:
$sql
= “SELECT * FROM fruit”;
$rows
= $conn-> query( $sql );
foreach
( $rows as $row ) {
echo
“name = “ . $row[“name”] . “ < br / > ”;
echo
“color = “ . $row[“color”] . “ < br / > ”;
}
Read
a Database Table with PHP
<?php
$dsn
= “mysql:dbname=mydatabase”;
$username
= “root”;
$password
= “mypass”;
try
{
$conn
= new PDO( $dsn, $username, $password );
$conn->setAttribute(
PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch ( PDOException $e ) {
echo
“Connection failed: “ . $e->getMessage();
}
$sql
= “SELECT * FROM fruit”;
echo
“<ul>”;
try
{
$rows
= $conn->query( $sql );
foreach
( $rows as $row ) {
echo
“<li>A “ . $row[“name”] . “ is “ . $row[“color”] . “</li>”;
}
echo
“Query failed: “ . $e->getMessage();
}
echo
“</ul>”;
$conn
= null;
?>
output:
A banana is yellow
A tangerine is orange
A plum is purple
No comments:
Post a Comment