Databases & MySQL

1. Databases & MySQL

2. Database Overview • Database: • A collection of related Tables • A database server could house many databases • Table: • A collection of records that describe items in an entity • Some sytems call this a “file” • E.g. Employee Table contains Employee Records Wendi Jollymore, ACES

3. Database Overview • Record: • Describes a single item • Collection of Fields or Columns • Also called a Row • Field: • A single data element • Also called a column • Describes a piece of an item Wendi Jollymore, ACES

4. Database Overview Wendi Jollymore, ACES

5. Database Overview Wendi Jollymore, ACES

6. Database Overview • Primary Key • A special field that acts as a unique identifier for a record • Must be unique for each record • Each table must have one • Examples: • Student ID, Social Insurance #, Product ID Wendi Jollymore, ACES

7. Database Overview - Exercises • Besides Students, what other tables do you think would exist in a database for Sheridan College? • Define a structure for a table called Mp3s that you would use to keep track of all your MP3 files. • Make up three examples of records with field values for the Mp3s table in #2. Wendi Jollymore, ACES

8. Using MySQL • In order to create database-driven pages, you need a database server • We will use MySQL • You will need to go in and create databases and tables that your pages can use • We’ll use the PHPMyAdmin tool! Wendi Jollymore, ACES

9. Using MySQL • Go to http://localhost • Log in using the user name and password you set up when you installed XAMPP • In the left-hand menu, select phpMyAdmin • Log in using the root password you set up for your Sql server when you installed XAMPP Wendi Jollymore, ACES

10. Using MySQL • Click on the Databases tab and find “Create Database” • Name: Media • Collation: latin1_general_ci (optional) • Click Create • Create the Table: • Name: Cds • Number of fields: 4 • Click Go Wendi Jollymore, ACES

11. Using MySQL • Fill in the information for all four fields • See notes online for field details • Add a description • Click Save Wendi Jollymore, ACES

12. Using MySQL • We can’t use the root account for web pages • Too powerful for a regular site visitor • Add a Guest user: • On main admin page, Users tab • Click Add new user • Fill in the user name and password • Select Host: Local • For Global Privileges, check SELECT, INSERT, UPDATE, DELETE • Click Add User button bottom-right Wendi Jollymore, ACES

13. Accessing the DB • Create the form in the notes • Use this to get user data to store • PHP file will be used to retrieve the data and save it to the database table • Open a new PHP file • Add variables for user name, host, password • Use the guest name and password you created Wendi Jollymore, ACES

14. Accessing the DB • To perform any task with your table data: • Connect to the database server • Select the database you want to work with • Perform the commands you want Wendi Jollymore, ACES

15. Accessing the DB • Connecting to the server: • mysql_connect(host, user, passwd) • Connects to a database server with a specific user name and password • Returns a reference to the database connection object $dblink = mysql_connect($hostname, $user, $passwd) or die ("Error: No connection to MySQL server\n"); Wendi Jollymore, ACES

16. Accessing the DB • Selecting the database: • mysql_select_db(db, conn) • Selects a specific database using a connection that has already been created mysql_select_db($dbname, $dblink) or die ("Error: MySQL database not selected\n"); Wendi Jollymore, ACES

17. Accessing the DB • Executing an SQL statement: • mysql_query(cmd, conn) • Executes a specific command or query using a database connection • If cmd is a SELECT statement, function returns a set of records • If cmd is INSERT, DELETE, UPDATE, $result true if successful, false if not $result = mysql_query($sql, $dblink) or die ("SQL query failed: $sql<br />".mysql_error()); Wendi Jollymore, ACES

18. Accessing the DB • In your PHP file: • At the top, add variables for host, user, password • Normally these are stored somewhere else • In the <body> tag: • Connect to the database • Select the Media table Wendi Jollymore, ACES

19. Retrieving Form Data • You sent your form data using method=“post” • To access in PHP file: • $_POST[“fieldname”] • Fieldname is the value in the input element’s name=“” attribute • isset($_POST[“fieldname”]) function • Returns true if the field has a value $cdTitle = (isset($_POST["title"])) ? $_POST["title"] : ""; Wendi Jollymore, ACES

20. Inserting Records • The SQL INSERT statement allows you to add records: • INSERT INTO tableName (f1, f2, f3, ...) VALUES (v1, v2, v3…); • Inserts the values v1 into field f1, the value v2 into field f2, etc… • You can build an SQL statement using the form data Wendi Jollymore, ACES

21. Inserting Records • Add the code to • build the SQL INSERT query • Execute the query • If the result returned is greater than 0, display a confirmation message • If the result is 0 or less, display an error message • Check the notes! Wendi Jollymore, ACES

22. Inserting Records • Use phpMyAdmin to check your table and see if the record was added! • Check the notes online for complete code solution Wendi Jollymore, ACES

23. Displaying Records • A SELECT query will select a specific set of records • SELECT fields FROM table WHERE condition • Fields = comma delimited list of fields • If you want all fields, use * instead • WHERE clause is optional • SELECT id, lastName, firstName FROM Students • SELECT * FROM Wine WHERE price > 50.0 • SELECT * FROM Wine WHERE estate LIKE ‘%Jackson%’ Wendi Jollymore, ACES

24. Displaying Records • Once you run a query, you’ll want to get the results • Determine how many records are in the result set • $rows = mysql_num_rows($result); • Access a row in the result set • $myRecord = mysql_fetch_array($result); • Access fields in a fetched row • echo “Name: “.$myRecord[“lastName”].”, “.$myRecord[“firstName”]; Wendi Jollymore, ACES

25. Displaying Records • Try the demos in the notes: • Display all records in a table • Display only records between a user-specified min and max price • Do the search exercise Wendi Jollymore, ACES