1 / 25

Databases & MySQL

Databases & MySQL. 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. Database Overview.

ggraciela
Download Presentation

Databases & MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related