LAMP App Workshop

From Knowledge Kitchen
Jump to navigation Jump to search


In this workshop, we will create a LAMP app based on data in a MySQL table of prominent works of literature.

Books App

MySQL table

Run the following script in order to create the MySQL table necessary for this workshop:

DROP TABLE IF EXISTS books;
CREATE TABLE books (
 title varchar(32) NOT NULL,
 author varchar(40) NOT NULL,
 year_written int(4) default 0,
 edition varchar(20) NOT NULL,
 price decimal(7,3),
 purchaseDate date NOT NULL,
 PRIMARY KEY(title)
);

INSERT INTO books VALUES ("Northanger Abbey", "Austen, Jane", 1814,"Penguin",18.2,"2011-12-01");
INSERT INTO books VALUES ("War and Peace", "Tolstoy, Leo", 1865, "Penguin",12.7,"2009-03-31");
INSERT INTO books VALUES ("Anna Karenina", "Tolstoy, Leo", 1875, "Penguin",13.5,"2010-02-25");
INSERT INTO books VALUES ("Mrs. Dalloway", "Woolf, Virginia", 1925, "Harcourt Brace",25,"2012-01-10");
INSERT INTO books VALUES ("The Hours", "Cunnningham, Michael", 1999, "Harcourt Brace",12.34,"2012-11-08");
INSERT INTO books VALUES ("Huckleberry Finn", "Twain, Mark", 1865, "Penguin",5.76,"2012-02-29");
INSERT INTO books VALUES ("Bleak House", "Dickens, Charles", 1870, "Random House",5.75,"2009-10-31");
INSERT INTO books VALUES ("Tom Sawyer", "Twain, Mark", 1862, "Random House",7.75,"2009-09-20");
INSERT INTO books VALUES ("A Room of One's Own", "Woolf, Virginia", 1922,"Penguin",29,"2011-11-30");
INSERT INTO books VALUES ("Harry Potter","Rowling, J.K.",2000,"Harcourt Brace",19.95,"2013-01-01");
INSERT INTO books VALUES ("One Hundred Years of Solitude","Marquez",1967,"Harper Perennial",14.00,"2013-03-01");
INSERT INTO books VALUES ("Hamlet, Prince of Denmark","Shakespeare",1603,"Signet Classics",7.95,"2013-09-24");
INSERT INTO books VALUES ("Lord of the Rings","Tolkien, J.R.",1937,"Penguin",27.45,"2013-09-25");
INSERT INTO books VALUES ("Der Steppenwolf","Hesse, Herman",1928,"Fischer Verlag", 95,"1929-01-01");


Functional specification

Given the MySQL script above, write all of the HTML, CSS, PHP and MySQL required to do the following:

  1. The user should have a choice on whether to sort the books by title, author, or year (ascending or descending).
  2. All data sent to the server along via either the GET or POST HTTP requests should be escaped before using in a database query
  3. The web pages should use a .CSS file to set up styles and a uniform background color across the pages.
  4. The output of the query should list the books in the following format:
Austen, Jane Pride and Prejudice (Penguin, 1814)
...

Administrator interface

Make a page where a site administrator can do the following:

  • add a new book to the table
  • delete any book in the table


What links here