pop quiz

Here's a short exercise to practice what we've learned so far.

Create SQL statements to accomplish the following tasks. Post your answers in your blog. Hint: you might find this easiest to do using phpmyadmin.

1. Create a customers table with an auto-incremented custno field, an indexed custname field and a single address field.
2. Write the SQL code that would insert 'J. Smith' into the custname field and 'Leeds' into the address field.
3. Write the SQL code that would extract all the customer details from the customers table in alphabetical order

For bonus marks:

Create an html page with a form for entering new customers. Link the form to a php page that inserts these values into your table. To include this code into your blog, you're going to have to do some formatting (blogs don't like < and > characters) - cut and paste your code into and out of the postable site to fix this.

---

Image: 'We Won the Quiz'
www.flickr.com/photos/24204505@N00/148260617

Cutting Code

catching up with some things I glossed over last week...

Creating a table

once you have a database server and a web server set up on your local machine, you'll need to open a web browser and start up phpMyAdmin. Something like: http://127.0.0.1/phpmyadmin (the capital letters may or may not be important)

The first time you log in the default administration account for mySQL is root with a blank password. Since this database will only be used for development we can keep this, but if you were setting up a database on an internet facing web server you'd want to set the password (or better, create another user and delete the root user altogether).

Once you're logged in to the database server you'll need to create a database (test) and create a table on it (contacts) with the following fields (as set out in the tutorial):

Name	Type	Length	Description
id	INT	6	A unique identifier for each record
first	VARCHAR	15	The person's first name
last	VARCHAR	15	The person's last name
phone	VARCHAR	20	The person's phone number
mobile	VARCHAR	20	The person's mobile number
fax	VARCHAR	20	The person's fax number
email	VARCHAR	30	The person's e-mail address
web	VARCHAR	30	The person's web address

Inserting some data

In order for our php interpreter to use data in the database you need to tell it a few things:

1. where the database server is located (on the development machine that's easy, it's the localhost, but in the real world the database server is usually another computer with an ip address all of its own
2. Who's trying to connect and the password (to keep out casual snoopers)
3. The name of the database you're trying to access (a database server can host mutliple databases - each database consisting of a collection of tables and indexes)

Typically this information will be kept in a single file which gets included on all the php pages that access the database. This means you can change the details in just one spot to update the whole site when you come to move it from the development environment to the production server.

I keep this information in a connect.php file which has the following code in it:

<?
$user="root";
$password="";
$database="test";
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
?>

$user, $password and $database are all variables (php variables all start with a $ sign) which can be changed at any time. You can include it in your php pages by inserting the following code:

include "connect.php"

the actual process of inserting records is outlined quite well in part 3 of the tutorial.

This week we went on with part four looking at how we get new values from a form to insert into the table. We then worked through the rest of that part to see how to display the contents of the table on a web page.

And that was about it for this week - next week we'll look into how to create a search form, and how to sort the results.

Still to come: editing and deleting records, and then: using a multi-table database.

---

Image: 'Neon scissors'
www.flickr.com/photos/48889087714@N01/9658510

prequel to SQL

Time to star thinking about using structured query language (SQL) to create database structures and manipulate data

But before we do, we'll be needing a local development environment with a web server, database server and php scripting engine all rolled into one... XAMPP

Install this on your operating system of choice and bingo - you've got a grown up web server in a tiny little package.

To check that it's working start the program (on a windows machine that will be something like running C:\xampplite\xampp_start.exe) and then type: http://127.0.0.1 into your web browser's address bar. This should display the home page for your new local web server.

If everything's working OK you should see the xampp home page - and on the left a link to your phpMyAdmin page where we'll be setting up a database and single table as outlined in this PHP/mySQL tutorial

Try it out and post some php pages to the local server's root (somewhere like: C:\xampplite\htdocs) remember, test them by calling the local host ip address (e.g. http://127.0.0.1/test.php)

---

Image: 'Will code for food'
www.flickr.com/photos/67523311@N00/114420037