G64DBS EXERCISE 4: PHP, MYSQL AND HTML - Nottingham
G64DBS EXERCISE 4: PHP, MYSQL AND HTMLINTRODUCTIONDuring this exercise we will cover how to use PHP to produce dynamic web pages based onour database. SQL is great for declarative queries using a DBMS, but for outputting useable,formatted documents, it falls short. Instead of trying to adapt SQL to improve the output,we can use PHP to retrieve our database results, and convert them into good looking HTML.You might wonder why we need to learn HTML and PHP in a module on databases. BothHTML and PHP are extremely useful skills to learn, and learning them here will also help usunderstand how databases fit into websites. Almost all big websites feature a back-enddatabase that runs behind the scenes, feeding information to servers that generate dynamicHTML documents. This exercise will be a brief introduction into this.For this exercise, we're going to extend our database tables from Exercise 3 a bit. We'regoing to add a "Rating" column to the CD that represents review scores these CDs mightreceive. We'll also add a "Year" column that represents the release date for the album.Finally, we'll add a "Nationality" column to the Artist table. If you wish to, you can make allthese additions for yourself. However, to make things easier I've created a setupex4.sql filethat will do it all for you if you'd prefer. In either case, your tables should look like this:mysql SELECT * FROM Artist; ------- --------------------------------- ---------------- artID artName artNationality ------- --------------------------------- ---------------- 1 Muse British 2 Mr. Scruff British 3 DeadMau5 Canadian 4 Mark Ronson British 5 Mark Ronson & The Business Intl British 6 Animal Collective American 7 Kings of Leon American 8 Maroon 5 American ------- --------------------------------- ---------------- 8 rows in set (0.00 sec)mysql SELECT * FROM CD; ------ ------- ----------------------------- --------- --------------- ---------- -------- cdID artID cdTitle cdPrice cdGenre cdRating cdYear ------ ------- ----------------------------- --------- --------------- ---------- -------- 1 1 Black Holes and Revelations 9.99 Rock 78 2006 2 1 The Resistance 11.99 Rock 90 2009 3 2 Ninja Tuna 9.99 Electronica 55 2008 4 3 For Lack of a Better Name 9.99 Electro House 38 2009 5 4 Version 11.99 Rock 77 2007 6 5 Record Collection 12.99 Pop 22 2010 7 6 Merriweather Post Pavilion 12.99 Electronica 82 2009 8 7 Only By The Night 9.99 Rock 67 2008 9 7 Come Around Sundown 12.99 Rock 31 2010 10 8 Hands All Over 11.99 Pop 64 2010 ------ ------- ----------------------------- --------- --------------- ---------- -------- 10 rows in set (0.00 sec)
SETTING UP PHPBefore you can begin writing PHP scripts you need to create a public html folder inside yourhome directory. Begin by starting exceed, and make sure you're in your home directory.When you log onto avon.cs.nott.ac.uk, you should see the following command line:username@avon: The : tells us you're in your home directory, which is represented as a in linux. If yousee other directories, you can change to your home directory like this:username@avon: /solaris/Private cd username@avon: As you can see, we were in the directory /solaris/Private, and after we use thecommand 'cd ', we move back into our home directory.Now you are in your home directory, we need to create the necessary php and other filesready for your web page. To make this process easier, we will download a compressedwebsite from elsewhere, and extract it into your home directory. The steps we will followare shown below:1. Ensure you are logged into avon from exceed, and you are in your home directory.2. Download the required file from the school servers using the following command:wget http://cs.nott.ac.uk/ mpp/files/exercise4.tar.gzThis command downloads the file straight to your home directory. This file containsall the files we need to start a website with PHP.3. The file is a tar archive, that has also been zipped. When you extract it, it will createthe complete website you need to get started, with all the correct file permissions.Use the following command to do this:tar -pxzvf exercise4.tar.gzThis command will extract all the files we need. tar is the program we run, -pxzvf arethe optional flags. -p: Instructs the program to preserve all our file permissions. This is extremelyimportant-x: Instructs the program to extract all the files in the archive-z: Instructs the program to unzip all the files as well, because they have beencompressed
-v: Instructs the program to operate in verbose mode, which increases theinformation it shows us about the process-f: Tells the program we intend to supply a file name for the archive we areextracting, in this case it's exercise4.tar.gz4. Now we have downloaded and extracted the file, we should be able to view our testwebsite. If you open your web browser and go tohttp://avon.cs.nott.ac.uk/ username/exercise4/index.php you should see a messagetelling you your website has been setup successfully!The entire process above should look like this on your Exceed terminal (highlighted in boldare the commands you use):user@avon: wget http://www.cs.nott.ac.uk/ mpp/files/exercise4.tar.gz--2010-10-23 22:30:55-- http://www.cs.nott.ac.uk/ mpp/files/exercise4.tar.gzResolving www.cs.nott.ac.uk. 128.243.21.19, 128.243.20.9Connecting to www.cs.nott.ac.uk 128.243.21.19 :80. connected.HTTP request sent, awaiting response. 200 OKLength: 3466 (3.4K) [application/x-gzip]Saving to: exercise4.tar.gz'100%[ ] 3,466--.-K/sin 0s2010-10-23 22:30:55 (288 MB/s) - exercise4.tar.gz' saved [3466/3466]user@avon: tar -pxzvf exercise4.tar.gzpublic html/public html/exercise4/dbconnect.phppublic html/exercise4/styles.csspublic html/exercise4/images/public html/exercise4/images/blend.pngpublic html/exercise4/images/flags/public html/exercise4/images/flags/flag1.gifpublic html/exercise4/images/flags/flag2.gifpublic html/exercise4/images/flags/flag3.gifpublic html/exercise4/index.phppublic html/exercise4/functions.phpuser@avon: EDITING THE WEB PAGEIf everything has worked correctly, you should now have a working website. Next you willwant to open up your favourite text editor (I recommend Notepad which is installed on allthe lab machines). Once you have an editor running you need to open the following files: H:/public html/exercise4/index.phpH:/public html/exercise4/dbconnect.phpThere are other files in the directory, such as functions.php and styles.css. We won't beediting these in this exercise. We will be using them, so feel free to open them and have alook at how they work. functions.php is a file containing some useful functions. styles.css is a
cascading style sheet, this tells your browser how to render your website. For example,properties like background colour and font size are stored in this file.Index.php is the main page of the website. Currently it only contains the bare minimumrequired for a webpage. That is, a pair of html tags, around a pair of body tags, andfinally the text you saw in your browser. First we'll add a header to the HTML document sothat it reads the style sheet, then we'll begin adding some content.HTML HEADERSThe HTML head tag goes between the html tags, but before the body tags. We willadd a link to the styles.css file, as well as a title for our webpage. Begin by creating a pair of head /head tags before the body start tag. If you do this correctly, your index.php fileshould look like this: html head /head body Well done, you've successfully created your web page! /body /html Remember, all tags in HTML need to open and close. You can tell which tags are close tagsbecause they contain a '/' character. Between your head tags, add a title /title pair, thatalso contain the text you would like for your web page title. For example: title G64DBS Lab /title Next, add the following line inside the head tags too: link rel "stylesheet" type "text/css" href "styles.css" / This tells the browser that your HTML gets it's formatting information from styles.css. Ifyou've done this correctly, your website text will be centered, and your index.php will looklike this: html head title G64DBS Lab /title link rel "stylesheet" type "text/css" href "styles.css" / /head body Well done, you've successfully created your web page! /body /html
It's useful to indent each pair of tags that appear inside others, much like you might withSQL or PHP code. It makes it easier to read, and spot any errors.CREATING A WEBPAGEBy this point you have a working php file, that currently only contains HTML text. Rememberthat a PHP document outputs HTML, so in this case we have simply written the HTMLdirectly into the file. A PHP script will usually contain a combination of directly typed HTML,and HTML that is output by PHP code.Next we will focus on creating some content for the web page. From now onwards all PHPcode blocks and HTML text will be written between these two body tags. That is: body All web page content will go between these two tags in theindex.php file. /body Begin by deleting any text that appears between the two body tags. Then write in some textof your own. Save your file, and visit the page in your browser. When you refresh thebrowser, you should see that the text has been updated to reflect your changes. From nowon, each time you add some content you might like to revisit the page in your browser, tomake sure everything is working.Clear any text that is in your body region, and begin by adding a page Heading. This can beachieved by adding some text into the body, between two h1 /h1 tags. Below this, addyour username between h2 /h2 tags. Your body region should look like this: body h1 Database Systems /h1 h2 Username: user /h2 /body
CREATING AN HTML TABLESince a lot of the output of SQL is in the form of tables, it's helpful if we understand theHTML structure we need to create tables on the web page. An HTML table is created in thefollowing way: table /table tags around the outside of the table elementInside the table tags, tr /tr tags for each Table Row that is required.Inside the tr tags, td /td tags for each table data cell needed.Inside each td tag, optional text that will appear.For example the following code will create a 2x2 table: table tr td Cell 1 /td td Cell 2 /td /tr tr td Cell 3 /td td Cell 4 /td /tr /table Exercise: Create a table on your webpage that contains 4 rows and 3 columns per row. Putany text you like inside each cell.You'll notice that there are no borders at the top and bottom of some of the cells. This isbecause the styles.css file has been created to format the table in this way. To make outtable look better, we should tell the browser that the top row of the table is the"tablehead". By doing this, the browser will look inside the styles.css file for the tableheadclass. It will read the formatting information contained within, and make the top row of thetable look better. To add the tablehead class to the first row in the table, we add anattribute class "tablehead" to our first tr tag. Like below: table tr class "tablehead" td Cell 1 /td td Cell 2 /td /tr . /table Only make this change to the first row in your table, otherwise the reformatting will beapplied to every row.
Now that you know how to make a table, we should attempt to create one as if we wereworking with the database. At first we will create one by hand, then we will connect to thedatabase and retrieve information using code.Exercise: Create a new table using HTML that looks like the one below (Obtained usingSELECT artName, cdTitle, cdPrice, cdGenre FROM Artist NATURAL JOINCD WHERE artName LIKE 'M%'): --------------------------------- ----------------------------- --------- ------------- artName cdTitle cdPrice cdGenre --------------------------------- ----------------------------- --------- ------------- Mark Ronson Version 11.99 Rock Mark Ronson & The Business Intl Record Collection 12.99 Pop Maroon 5 Hands All Over 11.99 Pop Mr. Scruff Ninja Tuna 9.99 Electronica Muse Black Holes and Revelations 9.99 Rock Muse The Resistance 11.99 Rock --------------------------------- ----------------------------- --------- ------------- It's important to become familiar with the HTML Table element, so that we can produce itusing PHP code later in the exercise.STARTING WITH PHPNow we're familiar with the main elements of HTML we need, we can begin using PHP toaccess the database. For all remaining work in this exercise, you can either delete what youhave previous done, between body and /body or you can add onto the end. It dependson whether you'd like to keep all your previous HTML answers intact. Remember,everything must go inside your body tags.To begin writing a php script, we must open and close a php block. Like this: body h1 Database Systems h1 h2 Username h2 possibly some previous tables you've created here ?php? /body It's important to remember that when you're writing inside php tags, you're writing phpcode. Outside of php tags, you're writing HTML text. Begin by using the echo command tooutput some text using PHP. Like this: ?phpecho "This is some text!";? Exercise: Use multiple echo commands to output 5 lines of text. The text can be anythingyou like. Go to your browser and see if they appear. You might notice that they will allappear on the same line in your webpage.
One problem with HTML is that most of the time it will ignore information that isn't text,including new lines. We can tell HTML to put a new line in, by using br/ . The br tagdoesn't need anything between them, so we can use a single, self-closed tag. So to outputtext on separate lines, echo some pre tags like this:echo "some text";echo " br/ ";echo "some text
G64DBS EXERCISE 4: PHP, MYSQL AND HTML INTRODUCTION During this exercise we will cover how to use PHP to produce dynamic web pages based on our database. SQL is great for declarative queries using a DBMS, but for outputting useable, formatted documents, it falls short. Instead of trying to adapt SQL to improve the output, we can use PHP to retrieve our database results, and convert them into .
MySQL PHP Syntax MySQL works very well in combination of various programming languages like PERL, C, C , JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities. PHP provides various functions to access MySQL database and to manipulate data records inside MySQL database.
MySQL is no longer enabled by default, so the php_mysql.dllDLL must be enabled inside of php.ini. Also, PHP needs access to the MySQL client library. A file named libmysql.dllis included in the Windows PHP distribution and in order for PHP to talk to MySQL this file needs to be available to the Windows systems PATH. See the FAQ titled "How do I add my PHP directory to the PATHon Windows" for .
Section 4: PHP and MySQL - The Structured Repository 4.1 PHP MySQL Connectivity 4.2 Integrating Web Forms and Database 4.3 Using PHP’s MySQL Extension 4.4 Using PHP’s PDO Extension Section 5: Learn More Advanced Techniques in PHP 5.1 Introduction to Object Oriented Programming 5.2 Classes and Objects
PHP is FREE to download from the official PHP resource: www.php.net PHP is easy to learn and runs efficiently on the server side Where to Start? To get access to a web server with PHP support, you can: Install Apache (or IIS) on your own server, install PHP, and MySQL Or find a web hosting plan with PHP and
MySQL for Excel is a 32-bit add-in for Microsoft Excel, which you can install and run on Microsoft Windows. MySQL for Excel is not compatible with Linux or macOS. MySQL for Excel can interact with MySQL Workbench to simplify the management of MySQL connections when both MySQL client tools are installed.
Lifetime Support Oracle Premier Support Oracle Product Certifications MySQL Enterprise High Availability MySQL Enterprise Security MySQL Enterprise Scalability MySQL Enterprise Backup MySQL Enterprise Monitor/Query Analyzer MySQL Workbench MySQL Enterprise Edition. 11 MySQL Database
uqc103s/UFCE47-20-1 PHP-mySQL 7 Why PHP and mySQL „ MySQL is a key part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python), a fast growing open source enterprise software stack. More and more companies are using LAMP as an alternative to expensive proprietary software stacks because of its lower cost and freedom from lock-in.
Division and 3-505 Parachute Infantry Regiment on 4 August 1990. My company, Charlie 3-505, had been conducting night live-fire exercises at Fort Bragg, North Carolina. Around 2230 hours on the night of 4 August, I received a Warning Order from my commander, Captain Charles Dydasco, to prepare for movement to the Battalion Area. Shortly after midnight, in a torrential downpour, we began .
