Selecting Data From the my

Selecting Data From the my_music Table

The next four sections contain scripts that are variations on a theme: selecting and displaying data. A large portion of the scripts is exactly the same, but repetition makes perfection, I was always told.

The only new function in these scripts is the mysql_fetch_array() function. This function takes the result of a SQL query and places the rows in array format. Using a simple while loop, you can extract and display these elements.

Hang on to your hat, and start with the first script, which just returns the results ordered by their ID numbers.

Displaying Records Ordered by ID

One of the required fields in the record addition script is ID. In this script, you'll select all the records in the my_music table, ordered by the ID numbers. The default value of the ORDER BY clause is ASC (ascending), so the records are returned with ID #1 first, followed by #2, #3, and so on.

Open a new file in your text editor and start a PHP block:
```
<?
```
Create a variable to hold the name of the database on which the table resides:
```
$db_name = "testDB";
```
Create a variable to hold the name of the table you're selecting from, using this script:
```
$table_name = "my_music";
```

Add the connection information as you have been:

$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());

Select the database as you have learned:

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

Create the SQL statement:

$sql = "SELECT id, format, title, artist_fn, artist_ln, rec_label,
   my_notes, date_acq FROM $table_name ORDER BY id";

Tip

Because you're selecting all the fields, you could use a * in the SQL statement instead of naming all the fields. In this case, the line would look like this:

$sql = "SELECT * FROM $table_name ORDER BY id";

Create a variable to hold the result of the mysql_query() function, as you have learned:
```
$result = @mysql_query($sql,$connection) or die(mysql_error());
```
Start the while loop. The while loop will create an array called $row for each record in the result set ($result):
```
while ($row = mysql_fetch_array($result)) {
```

Get the individual elements of the record, and give them good names. Add the stripslashes() function around any free text field that might have had slashes added to it:

$id = $row['id'];
$format = $row['format'];
$title = stripslashes($row['title']);
$artist_fn = stripslashes($row['artist_fn']);
$artist_ln = stripslashes($row['artist_ln']);
$rec_label = stripslashes($row['rec_label']);
$my_notes = stripslashes($row['my_notes']);
$date_acq = $row['date_acq'];

Do a little formatting with the artists' names. Because some artists have only a first name, some artists use both first and last names, and group names are thrown into the artist_ln field, start an if…else block to deal with this. Start by looking for groups:
```
if ($artist_fn != "") {
```
Create a variable called $artist_fullname, which will contain a string with $artist_fn, followed by a space, followed by $artist_ln, all within the trim() function:
```
$artist_fullname = trim("$artist_fn $artist_ln");
```
Note
The trim() function gets rid of extraneous space at the beginning and end of a string.
Continue the block, assigning the trimmed value of $artist_ln to $artist_fullname:
```
} else {
     $artist_fullname = trim("$artist_ln");
}
```
Do a little more formatting. If you didn't enter a date in the date_acq field, MySQL will enter a default value of 0000-00-00. Create an if block that looks for this value and then replaces it with something more friendly:
```
if ($date_acq == "0000-00-00") {
     $date_acq = "[unknown]";
}
```
Create a variable called $display_block to hold all the formatted records. The formatting in this block places the title of the recording in bold, followed by the name of the record label and the artist. Next comes a line break, and then your notes, and then an emphasized parenthetical statement that holds the date acquired and format:
```
$display_block .= "$title on $rec_label,
 by $artist_fullname 
$my_notes (acquired:$date_acq, format:$format)";
```
Close the while loop, and then your PHP block:
```
}
?>
```

Add this HTML:

<HTML>
<HEAD>
<TITLE>My Music (Ordered by ID)</TITLE>
</HEAD>
<BODY>
<H1>My Music: Ordered by ID</H1>

Display the results:
```
<? echo "$display_block"; ?>
```
Add a link back to the main menu, and then add some more HTML to make a valid document:
```
<a href="my_menu.html">Return to Menu</a>
</BODY>
</HTML>
```
Save the file with the name sel_byid.php, and place this file in the document root of your web server.
Open your web browser and type http://127.0.0.1/my_menu.html.
Click on the link called ordered by ID.

Your records will be different from mine, but you should see a screen like the following, where the records are ordered by internal ID number.

In the next section, you create the script that displays results ordered by date acquired.

Displaying Records Ordered by Date Acquired

Although it isn't a required field, the record addition script has a space for the date the recording made its way into your music collection. In this script, you'll select all the records in the my_music table, ordered by this date, with the most recent acquisition appearing first in the list.

Open the sel_byID.php file and change the SQL statement to:

$sql = "SELECT * FROM $table_name ORDER BY date_acq DESC";

Change the HTML title and heading to reflect the new ordering method:

<HTML>
<HEAD>
<TITLE>My Music (Ordered by Date Acquired)</TITLE>
</HEAD>
<BODY>
<H1>My Music: Ordered by Date Acquired</H1>

Save the file with the name sel_bydateacq.php, and place this file in the document root of your web server.
Open your web browser and type http://127.0.0.1/my_menu.html.
Click on the link called ordered by date acquired.

Your records will be different from mine, but you should see a screen like the following, where the records are ordered by the date the recordings were acquired. Those without dates would appear at the end of the list.

In the next section, you create the script that displays results ordered by title.

Displaying Records Ordered by Title

As you might imagine, the recording title is a required field in the record addition script. In this script, you'll select all the records in the my_music table, ordered alphabetically by title.

Open the sel_bydateacq.php file and change the SQL statement to:
```
$sql = "SELECT * FROM $table_name ORDER BY title";
```

Change the HTML title and heading to reflect the new ordering method:

<HTML>
<HEAD>
<TITLE>My Music (Ordered by Title)</TITLE>
</HEAD>
<BODY>
<H1>My Music: Ordered by Title</H1>

Display the results:
```
<? echo "$display_block"; ?>
```
Add a link back to the main menu, and then add some more HTML to make a valid document:
```
<a href="my_menu.html">Return to Menu</a>
</BODY>
</HTML>
```
Save the file with the name sel_bytitle.php, and place this file in the document root of your web server.
Open your web browser and type http://127.0.0.1/my_menu.html.
Click on the link called ordered by title.

Your records will be different from mine, but you should see a screen like the following, where the records are ordered by title of the recording.

In the final section, you create the script that displays results ordered by artist name.

Displaying Records Ordered by Artist

This script is a bit trickier because you have to take into consideration issues associated with artist names: some have only a first name, some have first and last names, and group names are thrown into the artist_ln field as well. In this script, you select all the records in the my_music table, ordered alphabetically by the full name of the artist.

Open a new file in your text editor and start a PHP block:
```
<?
```
Create a variable to hold the name of the database on which the table resides:
```
$db_name = "testDB";
```
Create a variable to hold the name of the table you're selecting from, using this script:
```
$table_name = "my_music";
```

Add the connection information as you have been:

$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());

Select the database as you have learned:

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

Create the SQL statement. Go back to the method that names all the fields in the SELECT statement:

$sql = "SELECT id, format, title, trim(concat(artist_fn,' ',artist_ln)) as
  artist_fullname, rec_label, my_notes, date_acq FROM $table_name
  ORDER BY artist_fullname";

Note

Within this SQL statement, you're essentially creating a new field from two fields that already exist, using the concat() function (a MySQL string function) to combine artist_fn and artist_ln, with a space in-between. Using artist_fullname assigns this new value to a field called artist_fullname. For example, suppose you own the album White Ladder by David Gray. The artist's first name ("David") and last name ("Gray") would go in their respective areas of the form, but would be output as one string ("David Gray").

The trim() function still strips the white space. The phrase trim(concat(artist_fn,' ',artist_ln)) as artist_fullname replaces the if…else block usually seen within the while loop in previous scripts.

Create a variable to hold the result of the mysql_query() function, as you have learned:
```
$result = @mysql_query($sql,$connection) or die(mysql_error());
```
Start the while loop. The while loop will create an array called $row for each record in the result set ($result):
```
while ($row = mysql_fetch_array($result)) {
```

Get the individual elements of the record, and give them good names. Remember, you have a new field called artist_fullname. Add the stripslashes() function around any free text field that might have had slashes added to it:

$id = $row['id'];
$format = $row['format'];
$title = stripslashes($row['title']);
$artist_fullname = stripslashes($row['artist_fullname']);
$rec_label = stripslashes($row['rec_label']);
$my_notes = stripslashes($row['my_notes']);
$date_acq = $row['date_acq'];

If you didn't enter a date in the date_acq field, MySQL will enter a default value of 0000-00-00. Create an if block that looks for this value and then replaces it with something more friendly:
```
if ($date_acq == "0000-00-00") {
     $date_acq = "[unknown]";
}
```
Create a variable called $display_block to hold all the formatted records. The formatting in this block places the title of the recording in bold, followed by the artist's name in parentheses. Next comes a line break, and then your notes, and then an emphasized parenthetical statement that holds the date acquired and format:
```
$display_block .= "
$title ($artist_fullname) 
$my_notes (acquired: $date_acq, format: $format)";
```
Close the while loop, and then your PHP block:
```
}
?>
```

Add this HTML:

<HTML>
<HEAD>
<TITLE>My Music (Ordered by Artist)</TITLE>
</HEAD>
<BODY>
<H1>My Music: Ordered by Artist</H1>

Display the results:
```
<? echo "$display_block"; ?>
```
Add a link back to the main menu, and then add some more HTML to make a valid document:
```
<a href="my_menu.html">Return to Menu</a>
</BODY>
</HTML>
```
Save the file with the name sel_byartist.php, and place this file in the document root of your web server.
Open your web browser and type http://127.0.0.1/my_menu.html.
Click on the link called ordered by artist.

Your records will be different from mine, but you should see a screen like the following, where the records are ordered by the name of the artist.

The next chapters give you a break from database work, as you learn a bit about user authentication, cookies, and sessions.