Practising SQL

Copy the folder containing the five PHP practise pages (phppractise) and rename it sqlpractise.

Currently there is a list of products on the first page.  The client wants to have that list come from database so it can be easily added to without editing HTML.

Database definition

• if you can create databases you need to create a new database (skip to table definition if not):
  • use your PHP template to create a page called dbcreate.php (make sure you save it in the sqlpractise folder)
  • put a query in the page which creates the database and include an or die
  • use that page to create a new database called halcyon
  • use sqlshowdbs.php (in the folder/directory above) to check that it worked

Table definition

• use your PHP template to create a page called tablecreate.php (save it in sqlpractise)
• at the top of the code change the database selected by mysqli_select_db to halcyon (if you were able to create that)
• add a query to the page which creates a table called product with two fields (one called productName and one called productType both being varchar with a length of 255)
• copy sqlshowtables.php and sqlshowfields.php into the sqlpractise folder and:
  • in both files change the selected database from ywstutorial to halcyon
  • in sqlshowfields.php change the query to look at the new product table instead of first
  • save and load both pages and check that the table was created with the correct fields

Displaying the data

• open index.php from sqlpractise
• delete the list including all of the list items
• just under where the list was should be a block of PHP - put a new blank line above the first of the three lines of code already there and paste in both of these from sqllist.php:
  • the two lines which connect to the database server and select the database (change ywstutorial to halcyon)
  • the nine lines which get and display the list of people in the table (query, two echo lines and a while loop followed by a final echo)
• change the query to get productName and productType from the product table instead of the existing names from first
• on the next line change the text to something appropriate
• inside the while loop change the two variable names to match the field names from the table
• do the same with the indexes (inside the square brackets)
• if you have not already changed the variable names on the echo line which is the third line inside the loop do that as well

Load the page to test for errors but don't expect a list as there is no data yet.  You should just see the paragraph text.

Inserting the data

• create a new HTML page from your template called insertdataform.html with a form in it (you can copy this from another page) which lets the user add new products to the table (remember to change the form action, label wording etc.)
• create a PHP page from your template called insertdataprocess.php which receives the new data and inserts it into the table
• enter the five existing products into the table using the form (they all have the same product type grommet and the product names are springy, spongy, fiddly, fireproof, cheesy)

View index.php in a browser and you should see five things in the list just like it used to look (but with a paragraph before them).  You could add more products.

Links

The first item in the list was a link and the client would like all products in the list to be links.  At the moment there is only a page for the first product but don't worry about that for now.

You need to find a way to create a unique link href for each product.  The easiest way is to use the product name as the page name.  To do this:

1. Find the line which echoes the list items in index.php (inside the loop)
2. just before that line create a variable called $url and put the text product1.php into it for now ($url="product1.php")
3. in the echo line below add a link element (<a> and </a>) around the variable names (one link element around both of them not two link elements)
4. add an href attribute to the opening a tag with the value in escaped quotes being $url (href=\"$url\")
5. just for testing save the page and try any of the links (they should all take you to the one existing product page)
6. rename the file product1.php to springy.php
7. now change the $url line to create the page name for the link as $url="{$productName}.php";
8. save and load the page and you should find each link is different when you hover over them and the first one still works

You could now create the other product pages and as long as you name them properly the links will work no matter how many products are added.