Talking to Databases: SQL for Designers
Sean Hedenskog Ideas in motion. Biography Sean Hedenskog Agent Instructor Macromedia Certified Master Instructor Macromedia Certified Developer ColdFusion / Dreamweaver Reside in Naperville, IL Since 1998 has successfully taught hundreds of classes for Allaire, Macromedia and their partners Overview Static pages v. Dynamic pages What is SQL? Anatomy of a SQL statement Connecting to a database Recordset dialog box – simple mode SQL in Code View Modifying an existing query Recordset dialog box – advanced mode Static Web Pages § Static pages are processed like this: Web Browser http http://www.macromedia.com/software/index.htm Web Server <html> ColdFusion Dreamweaver Flash </html> Dynamic Web Pages § Dynamic pages are processed like this: Web Server http http://www.macromedia.com/software/index.cfm Web Browser <html> ColdFusion Dreamweaver Flash </html> Application Server Database Benefits of Building Dynamic Pages § Automatically reflect changes in data § Content management by nontechnical business users § Maintaining site is easierDatabases Explained § Collection of data stored in some organized fashion – Tables – Column and Datatypes – Rows – Primary Keys § For this session we will be using two tables – Products – Categories What is SQL? § Structured Query Language § Standard language for getting information from and updating database data § Some relational databases that use SQL are Oracle, Microsoft SQL Server, Access, Sybase, Informix and DB2 § All contain the commands to Select, Insert, Update, Delete, Create and Drop SQL Code Example SELECT ProductName, Price, Description FROM Products ORDER BY Price DESC Anatomy of a SQL Statement § SELECT {Field(s)} § FROM {Table(s)} § WHERE {Join Condition and Filter(s)} § ORDER BY {Field(s)} Basic SQL Code Examples SELECT * FROM Products SELECT ProductID, ProductName, Price FROM Products ORDER BY ProductName SELECT ProductName, Description FROM Products WHERE ProductID = 2 Understanding Relational Tables § Imagine you have a table containing product information – Product name, product description, price and vendor information § Multiple catalog items created by the same vendor – Where would you store the information?
Understanding Relational Tables § You wouldn’t want to store that data along with the products – Because vendor information is same for each product, you repeat the information and waste time and storage space – If vendor changes you will have to update all the vendor information – When data is repeated you increase the chances that the data will be entered differently Why Use Joins § More efficient data storage, easier manipulation and greater scalability § If data is stored in multiple tables how can you retrieve the data? § The answer is to use a join. It associates tables within a SELECT statement. – Onthefly Joining 2 or More Tables § If you are using more than one table you MUST join the tables § If you forget you will return a cross join or Cartesian product § Two options are available to join tables: – Option 1 – in the WHERE clause – Option 2 – in the FROM clause Join Types Example Data § You have two tables Products Categories 19 records 5 records Don’t Forget to Join the Tables SELECT CategoryName, ProductName, Price FROM Products, Categories ORDER BY CategoryName Products Categories 19 records 5 records Recordset 95 records Tables Joined in the WHERE Clause SELECT CategoryName, ProductName, Price FROM Products, Categories WHERE Products.CategoryID = Categories.CategoryID ORDER BY CategoryName Products Categories
Tables Joined in the FROM Clause § You have three options: – Option 1 – INNER JOIN • Returns all rows from both tables where there is a match. If there are rows in one table that do not have matches in the other, those rows will not be listed. – Option 2 – LEFT OUTER JOIN • Returns all the rows from the first table, even if there are no matches in the second table. – Option 3 – RIGHT OUTER JOIN • Returns all the rows from the second table, even if there are no matches in the first table. INNER JOIN SELECT CategoryName, ProductName, Price FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID ORDER BY CategoryName Products Categories
19 records 5 records Recordset 16 records
LEFT OUTER JOIN SELECT CategoryName, ProductName, Price FROM Products LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID ORDER BY CategoryName Products Categories
19 records 5 records Recordset 19 records
RIGHT OUTER JOIN SELECT CategoryName, ProductName, Price FROM Products RIGHT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID ORDER BY CategoryName Products Categories
19 records 5 records Recordset 17 records
Filtering Data § The previous examples would retrieve all rows from the tables § Retrieving just the data you want involves specifying a search criteria, also known as a filter § Data is filtered by specifying the search criteria in the WHERE clause § In addition, you can use AND, OR and NOT Filtering Data SELECT ProductName, Price FROM Products WHERE Price < 500 SELECT ProductName, Price FROM Products WHERE ProductName = ‘ColdFusion MX Standard’ OR ProductName = ‘Dreamweaver 8’
Wildcard Filtering SELECT ProductName, Price FROM Products WHERE ProductName LIKE ‘C%’ § Filtering against unknown values § Use the LIKE operator, with wildcards – % (percent sign) 0 or more characters – _ (underscore) character Review So Far § Dynamic pages are good § SQL will help create dynamic pages § SQL is easy to learn and not so scary anymore Resources § Teach Yourself SQL in 10 minutes by Ben Forta http://www.forta.com/books/0672325675/ § SQL: Visual QuickStart Guide, 2 nd edition by Chris Fehily http://www.peachpit.com/title/0321334175 § Interactive Online SQL Training for Beginners http://sqlcourse.com/ SQL for Designers § Best part – Dreamweaver will generate the SQL code for you § Could be easily accomplished for ASP, ColdFusion, JSP and PHP applications § In this session, we will use ColdFusion Connecting to a Database § To create a dynamic page we first need to connect to a database § We connect to the database by creating a datasource § Datasource will then be available to us via the Databases panel in Dreamweaver Connecting to a Database § Dreamweaver enables you to create connections – On the Database tab in the Application panel, click the + (plus) button and select the type of database
Connecting to a Database § Microsoft Access Connection Dialog Connecting to a Database § Once you have created the datasource you can view its contents in the Databases tab Creating a Recordset § To begin, click the plus sign on the Bindings tab and select Recordset(Query) Introducing the Recordset Dialog Box § Using the form fields construct your SQL statement Code View <cfquery name=“qGetProducts” datasource=“sa106w”> SELECT ProductName, Price, Description FROM Products ORDER BY ProductName ASC </cfquery> § Dreamweaver automatically generates the SQL code Modifying an Existing Query § To modify an existing query simply doubleclick the Recordset in the Bindings tab
Introducing the Recordset Dialog Box Advanced § This dialog allows for multitable queries Summary § Dynamic web pages are good § SQL is the translation language between our applications and the database data § SQL is easy to learn § Dreamweaver generates SQL for ASP, ColdFusion, JSP and PHP applications Resources § Macromedia Dreamweaver MX 2004 with ASP, ColdFusion, and PHP: Training from the Source by Jeff Bardzell http://www.peachpit.com/title/0321241576/ § Macromedia HandsOn Training – Macromedia Dreamweaver MX 2004: Dynamic Application Development – Fast Track to ColdFusion MX 7 – http://www.macromedia.com/training/
