Guide to PHP and MySQL (Computer Science 4 All Version)
Chapter 9: Working With PHP and a MySQL Database
PHP and MySQL
Before you can work with a MySQL database using PHP you need to create a connection to your database. First, create a file called dbvars.php containing variables that store the server name, database name, your username for that database and your password for that database:
Using mysqli() to Connect to a MySQL Database
After you have your database connections variables defined you need to use the PHP require_once() function to have the variables available for your connection to your MySQL database. You use the mysqli() extension to connect to your database.
Using PDO() to Connect to a MySQL Database
After you have your database connections variables defined you might also use the PHP Data Objects (PDO()) interface to connect to your database. PDO is handy because it provides drivers which can be used with other databases such as DB2, MS SQL, Oracle, or PostgreSQL
The MySQL SELECT Statement using mysqli()
Yuo would use the SELECT statement to retrieve a data record (or records) from your database. You need to create a variable to store your SQL query. Then you need to execute that query, walk through the results set to display the database data and, finally, close the database connetion.
The MySQL INSERT Statement
You would use the INSERT statement to add a data record (row) to your table.
The MySQL DELETE Statement
You would use the DELETE statement to remove a data record (row) from your table.
Working With Multiple MySQL Tables
In most applications you will need to work with more than one table.
The MySQL join Keyword
You can use the join keyword to relate two or more tables together.
Rows: 3
Employee
Department
Location
Dylan, Bob
HR
New York, NY
Cocker, Joe
HR
Paris, FR
Prine, John
HR
London, UK
Figure 9-9: MySQL JOIN Output
The prepare and bind Methods
You can use the prepare method to setup a query that would use parameters. You then use bind method to provide variable values for those parameters.
Task - Create a PHP Table Page From a MySQL Table
For this Assignment you will create a page, php_mysql_table.php on your local web server and the BSC web server. This file should be based on your lfa.php file. This web page should:
Use PHP to create a function that reads from the MySQL monthsTable table and displays an HTML table page.
This function should use a PHP conditional (if) test to determine whether the row is odd or even and use appropriate CSS to make the even rows a different background/foreground color than the odd rows.
Create a PHP function that:
Connects to your MySQL server.
Requests (SELECT) all records in (FROM) the monthsTable.
Generates HTML5/CSS to display the results set as an HTML table.
Use appropriate PHP comments throughout your code.
Appropriate HTML5, CSS-styled tags that identify this as your web page including your name and a brief paragraph describing how you approached solving this problem.
You should use appropriately identified and styled <div> tags for the various areas of your page layout.
Modify your JavaScript in your index.html file that uses an array of the Assignments for this course to display an ordered list of these assignments to convert the entry for this assignment to a web link that allows me download and save your index.php, php_mysql_table.php, vars.php, and functions.php files so I can download, save, and grade your work.
You *MUST* use the W3C Unicorn Validator to validate your HTML5/CSS3 code.
You will modify the PHP function for this item in your index.php page to add a link to your php_mysql_table.php that allows me to view your work.