phpmysql3.3

 Retrieving Data from MySQL with PHP

Setting Up the Book Club Database

You can  type all the lines directly into the MySQL command - line tool, you can create a text file — say, book_club.sql — and enter the lines in there. Save the file in the same folder as you run the MySQL command - line tool from. Run the tool, then type:

source book_club.sql;

This command reads the lines of the text file and executes them, just as if you ’ d manually entered the SQL statements into the tool line - by - line.

 

USE mydatabase;

 

CREATE TABLE members (

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

username VARCHAR(30) BINARY NOT NULL UNIQUE,

password CHAR(41) NOT NULL,

firstName VARCHAR(30) NOT NULL,

lastName VARCHAR(30) NOT NULL,

joinDate DATE NOT NULL,

gender ENUM( ‘m’, ‘f’ ) NOT NULL,

favoriteGenre ENUM( ‘crime’, ‘horror’, ‘thriller’, ‘romance’, ‘sciFi’, ‘adventure’, ‘nonFiction’ ) NOT NULL,

emailAddress VARCHAR(50) NOT NULL UNIQUE,

otherInterests TEXT NOT NULL,

PRIMARY KEY (id)

);

 

INSERT INTO members VALUES( 1, ‘sparky’, password(‘mypass’), ‘John’,

‘Sparks’, ‘2007-11-13’, ‘m’, ‘crime’, ‘jsparks@example.com’, ‘Football, fishing and gardening’ );

 

INSERT INTO members VALUES( 2, ‘mary’, password(‘mypass’), ‘Mary’, ‘Newton’,

‘2007-02-06’, ‘f’, ‘thriller’, ‘mary@example.com’, ‘Writing, hunting and travel’ );

 

INSERT INTO members VALUES( 3, ‘jojo’, password(‘mypass’), ‘Jo’, ‘Scrivener’,

‘2006-09-03’, ‘f’, ‘romance’, ‘jscrivener@example.com’, ‘Genealogy, writing, painting’ );

 

INSERT INTO members VALUES( 4, ‘marty’, password(‘mypass’), ‘Marty’,

‘Pareene’, ‘2007-01-07’, ‘m’, ‘horror’, ‘marty@example.com’, ‘Guitar playing, rock music, clubbing’ );

 

INSERT INTO members VALUES( 5, ‘nickb’, password(‘mypass’), ‘Nick’,

‘Blakeley’, ‘2007-08-19’, ‘m’, ‘sciFi’, ‘nick@example.com’, ‘Watching movies, cooking, socializing’ );

 

INSERT INTO members VALUES( 6, ‘bigbill’, password(‘mypass’), ‘Bill’, ‘Swan’,

‘2007-06-11’, ‘m’, ‘nonFiction’, ‘billswan@example.com’, ‘Tennis, judo, music’ );

INSERT INTO members VALUES( 7, ‘janefield’, password(‘mypass’), ‘Jane’,

‘Field’, ‘2006-03-03’, ‘f’, ‘crime’, ‘janefield@example.com’, ‘Thai cookery, gardening, traveling’ );

 

CREATE TABLE accessLog (

memberId SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

pageUrl VARCHAR(255) NOT NULL,

numVisits MEDIUMINT NOT NULL,

lastAccess TIMESTAMP NOT NULL,

PRIMARY KEY (memberId, pageUrl)

);

 

INSERT INTO accessLog( memberId, pageUrl, numVisits ) VALUES( 1, ‘diary.php’,2 );

INSERT INTO accessLog( memberId, pageUrl, numVisits ) VALUES( 3, ‘books.php’,2 );

INSERT INTO accessLog( memberId, pageUrl, numVisits ) VALUES( 3, ‘contact.php’, 1 );

INSERT INTO accessLog( memberId, pageUrl, numVisits ) VALUES( 6, ‘books.php’,4 );

 

Why is the password field exactly 41 characters long? Further down in the code, you can see that you insert the members ’ passwords in encrypted form by calling MySQL ’ s password() function. The encrypted password strings returned by password() are always 41 characters long, so it makes sense to use CHAR(41) for the password field.

 

The BINARY Attribute and Collations

 

By adding the BINARY attribute after the data type definition, you switch the field to a binary collation, which is case sensitive; when sorting,  Furthermore, this means that matches are case sensitive too; “ banana ” will only match “ banana ”, not “ Banana ”.

In this case, you created the username field of the members table with the BINARY attribute, making it case sensitive:

 username VARCHAR(30) BINARY NOT NULL UNIQUE,

 The UNIQUE Constraint

The UNIQUE constraint is similar to PRIMARY KEY in that it creates an index on the column and also ensures that the values in the column must be unique.

 The main differences are:

You can have as many UNIQUE keys as you like in a table, whereas you can have only one

primary key The column(s) that make up a UNIQUE key can contain NULL values; primary key columns cannot contain NULL s

 In the members table, you add UNIQUE constraints for the username and emailAddress columns because, although they ’ re not primary keys, you still don ’ t want to allow multiple club members to have the same username or email address.

 The ENUM Data Type

An ENUM (enumeration) column is a type of string column where only predefined string values are allowed in the field. For the members table, you created two ENUM fields:

ENUM fields serve two purposes.

First, by limiting the range of values allowed in the field, you ’ re effectively validating any data that is inserted into the field. If a value doesn ’ t match one of the values in the predefined set, MySQL rejects the attempt to insert the value. Second, ENUM fields can save storage space.

 The TIMESTAMP Data Type

A TIMESTAMP field is a bit different from the other date/time types in that it can automatically record the time that certain events occur. For example, when you add a new row to a table containing a TIMESTAMP column, the field stores the time that the insertion took place. Similarly, whenever a row is updated, the TIMESTAMP field is automatically updated with the time of the update.

 

Retrieving Data with SELECT

Limiting the Number of Rows Returned

Use a WHERE clause to limit the results of a query based on field values:

mysql > SELECT * from fruit WHERE name = ‘banana’;

+----+--------+--------+

| id | name | color |

+----+--------+--------+

| 1 | banana | yellow |

+----+--------+--------+

1 row in set (0.08 sec)

Instead of a WHERE clause, you can set an upper limit on the number of returned rows by using the LIMIT keyword.

mysql > SELECT id, username FROM members LIMIT 4;

+----+----------+

| id | username |

+----+----------+

| 1 | sparky |

| 2 | mary |

| 3 | jojo |

| 4 | marty |

+----+----------+

4 rows in set (0.00 sec)

The LIMIT clause always comes at the end of the query. By default, LIMIT counts(the start row counts from zero) from the first row of the results. However, by including two numbers after the LIMIT keyword, separated by a comma, you can specify both the row from which to start returning results, as well as the number of results to return:

 

mysql > SELECT id, username FROM members LIMIT 1, 2;

Sorting Results

ORDER BY keyword used to sort the column

 mysql > SELECT username, firstName, lastName FROM members ORDER BY firstName;

+-----------+-----------+-----------+

| username | firstName | lastName |

+-----------+-----------+-----------+

| bigbill      | Bill           | Swan |

| janefield | Jane          | Field |

| jojo              Jo        | Scrivener |

| sparky     | John | Sparks |

| marty   | Marty | Pareene |

| mary     | Mary | Newton |

| nickb     | Nick | Blakeley |

+-----------+-----------+-----------+

7 rows in set (0.00 sec)

You can even sort by more than one column at once by separating the column names with commas:

mysql > SELECT favoriteGenre, firstName, lastName FROM members ORDER BY

favoriteGenre, firstName;

 By default, MySQL sorts columns in ascending order. If you want to sort in descending order, add the keyword DESC after the field name. To avoid ambiguity, you can also add ASC after a field name to explicitly sort in ascending order:

 mysql > SELECT favoriteGenre, firstName, lastName FROM members ORDER BY

favoriteGenre DESC, firstName ASC;

 

Using Pattern Matching for Flexible Queries

LIKE operator

This operator allows you to specify a string in the form of a pattern to search for, rather than an exact string:

 SELECT ... WHERE fieldName LIKE pattern ;

 Within the pattern string, you can include the following wildcard characters in addition to regular characters:

 % matches any number of characters (including no characters at all)

_(underscore) matches exactly one character

 So to retrieve a list of members that list travel as one of their interests, you could use:

 mysql > SELECT username, firstName, lastName, otherInterests FROM members

WHERE otherInterests LIKE ‘%travel%’;

+-----------+-----------+----------+------------------------------------+

| username | firstName | lastName     | otherInterests |

+-----------+-----------+----------+------------------------------------+

| mary            | Mary            | Newton | Writing, hunting and travel |

| janefield      | Jane              | Field       | Thai cookery, gardening, traveling |

+-----------+-----------+----------+------------------------------------+

2 rows in set (0.00 sec)

You can use the (underscore) wildcard character to match a single character — for example:

mysql > SELECT firstName, lastName FROM members WHERE firstName LIKE ‘Mar_y’;

 

Summarizing Data(MySQL ’ s aggregate functions)

count() — Returns the number of rows selected by the query

 

count( fieldname ) — Returns the number of rows selected by the query where fieldname

isn ’ t NULL

count( * ) — Returns the number of rows selected by the query, regardless of whether the

rows contain any NULL values

 mysql > SELECT COUNT( * ) FROM members;

 This example, on the other hand, counts only the number of women in the members table:

mysql > SELECT COUNT( * ) FROM members WHERE gender = ‘f’;

 sum() — Returns the total of all the values of a given field selected by the query

For example, this query returns the total number of visits to the book club Web site across all members:

mysql > SELECT SUM( numVisits ) FROM accessLog;

 min() — Returns the minimum value of all the values of a given field selected by the query

mysql > SELECT MIN( joinDate ) FROM members;

 max() — Returns the maximum value of all the values of a given field selected by the query

 avg() — Returns the average of all the values of a given field selected by the query

 

Eliminating Duplicate Results

DISTINCT removes any rows that are exact duplicates of other rows from the result set. To eliminate such duplicates, you can place the keyword DISTINCT after SELECT in the query:

 SELECT DISTINCT Country FROM Customers;

 

Grouping Results

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country;

 Pulling Data from Multiple Tables

The real strength of a relational database is that you can query multiple tables at once, using selected columns to relate the tables to each other. Such a query is known as a join , and joins enable you to create complex queries to retrieve all sorts of useful information from your tables.

INNER JOIN: Returns records that have matching values in both tables

 SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

 

LEFT JOIN: Returns all records from the left table, and the matched records from the right table

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

 

RIGHT JOIN: Returns all records from the right table, and the matched records from the left table

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

 

CROSS JOIN: Returns all records from both tables

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

 

SELF JOIN

A self join is a regular join, but the table is joined with itself.

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City

FROM Customers A, Customers B

WHERE A.CustomerID <> B.CustomerID

AND A.City = B.City ORDER BY A.City;

 

Using Aliases

SQL lets you create short table aliases by specifying an alias after each table name in the FROM clause. You can then use these aliases to refer to the tables, rather than using the full table names each time

 mysql > SELECT DISTINCT al.memberId, m.firstName, m.lastName FROM accessLog

al, members m WHERE al.memberId = m.id;

Manipulating MySQL Data with PHP

Inserting Records

INSERT INTO table VALUES ( value1 , value2 , ... );

If you want to insert only some values, leaving NULL s or other default values in the remaining fields, use:

INSERT INTO table ( field1 , field2 , ... ) VALUES ( value1 , value2 , ... );

 So how do you insert records using your PHP script? You pass INSERT statements to MySQL via PDO in much the same way as you pass SELECT statements. If you don ’ t want to pass data from any PHP variables, you can use the simpler PDO::query() method — for example:

 

< ?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 = “INSERT INTO members VALUES ( 8, ‘derek’, password(‘mypass’), ‘Derek’,

‘Winter’, ‘2008-06-25’, ‘m’, ‘crime’, ‘derek@example.com’, ‘Watching TV,

motor racing’ )”;

try {

$conn- > query( $sql );

} catch ( PDOException $e ) {

echo “Query failed: “ . $e-> getMessage();

}

?>

 Updating Records

you can alter the data within an existing table row by using an SQL UPDATE statement:

 

mysql > UPDATE fruit SET name = ‘grapefruit’, color = ‘yellow’ WHERE id = 2;

 

mysql > SELECT * from fruit;

+----+------------+--------+

| id | name | color |

+----+------------+--------+

| 1 | banana | yellow |

| 2 | grapefruit | yellow |

| 3 | plum | purple |

+----+------------+--------+

3 rows in set (0.00 sec)

As with inserting new records, updating records via your PHP script is simply a case of using

PDO::query() if you ’ re passing literal values in the UPDATE statement, or PDO::prepare() with placeholders if you ’ re passing variable values. For example, the following script changes the email address field in the “ Derek Winter ” record that was added in the previous section:

< ?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();

}

$id = 8;

$newEmailAddress = “derek.winter@example.com”;

$sql = “UPDATE members SET emailAddress = :emailAddress WHERE id = :id”;

try {

$st = $conn- > prepare( $sql );

$st- > bindValue( “:id”, $id, PDO::PARAM_INT );

$st- > bindValue( “:emailAddress”, $newEmailAddress, PDO::PARAM_STR );

$st- > execute();

} catch ( PDOException $e ) {

echo “Query failed: “ . $e- > getMessage();

}

? >

 

Deleting Records

Deleting rows of data via PHP is a similar process to updating. To delete rows from a table using the SQL DELETE keyword:

 mysql > DELETE FROM fruit WHERE id = 2;

 To delete rows using PHP, you pass a DELETE statement directly via PDO::query() , or create the statementusing PDO::prepare() with placeholders, passing in values (such as the criteria for the WHERE clause) with PDOStatement::bindValue() and running the query with PDOStatement::execute() .

The following script deletes the member record with the ID of 8 from the members table:

< ?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();

}

$id = 8;

$sql = “DELETE FROM members WHERE id = :id”;

try {

$st = $conn- > prepare( $sql );

$st- > bindValue( “:id”, $id, PDO::PARAM_INT );

$st- > execute();

} catch ( PDOException $e ) {

echo “Query failed: “ . $e-> getMessage();

}

? >

Prev Topic Introducing Databases and SQL