dreamweaver/PHP : 

authoring system for writing php code to access a database

download files for exercise

Dreamweaver (and GoLive) offer high level authoring tools which allow you to generate PHP code for accessing a database. This means you can write PHP code in the same way you write HTML in Dreamweaver. I.e. Dreamweaver does it for you. Of course, as with HTML, you can have more control if you learn a little PHP.

From the official PHP website FAQ:

PHP stands for PHP: Hypertext Preprocessor . This confuses many people because the first word of the acronym is the acronym. This type of acronym is called a recursive acronym. The curious can visit Free On-Line Dictionary of Computing for more information on recursive acronyms.

PHP is a programming language designed to be embedded directly in the HTML of web pages. When PHP pages are served to the end-user, the PHP code is processed by the web server and only the resulting HTML (or sometimes JavaScript) is sent out. So when a PHP page is viewed in the client browser, no PHP code is visible.

This section has the following topics:

• set up a database site - set up a Dreamweaver Site definition that works with PHP and mySQL, establishing where you files will be located
• define a connection - tell Dreamweaver the database server address, database account, and password
• create a recordset - set up a specific database query that retrieves the content needed
• replace fixed content with dynamic code - Use the authoring features of Dreamweaver to insert PHP code that accesses the database and displays the results - possibly modify this code to your needs
• upload the page and the associated support files - Moving the page and extra PHP files to your account on the MPDX server
• design and build the backend pages that update the content - use a similar process to the above to create pages that are used to administer the content in the database (update, create, delete)

When building php/mySQL pages in Dreamweaver, you must set up the Site definition specially for this kind of development. For your accounts on the server, do the following:

Set up both Remote Info and Testing Server with the following:

• Access: FTP
• FTP host: artcenter.philvanallen.com
• Host Directory: artcenter.philvanallen.com/your last name/
• Login: accd_test
• Password: ask your instructor

In the Testing Server dialog, set these additional options:

• Server Model: PHP MySQL
• URL Prefix: http://artcenter.philvanallen.com/your last name/

Your site will be accessible at this URL Prefix.

In order to access information in a database, Dreamweaver needs to know:

• the address of the database server (which is sometimes different from the website server)
• database login and password (again, not necessarily the same as your ftp login)
• the specific database you will be using

For this class, use the following:

• Connection name: your last name (or whatever you want - has no functional effect)
• MySQL Server: mysql.philvanallen.com
• User Name: your last name (for those with 2 letter names, add your first initial)
• Password: student id #
• Database: your last name or click on SELECT (no first initial needed)
  

To get specific information for a page out of a database, you need to define the table and search criteria. This is called a Recordset in Dreamweaver:

• name of the connection defined above
• name of the table used
• the fields being used for this query
• criteria for retrieving content from the database
  • search rules
  • sorting rules

Here is a simple Recordset where information is being retrieved from a table where there is only one record, as with the current assignment:

The following is an example of a recordset that has multiple records, where we only want records where the "link_active" field is set to "Y", and the records are sorted by the name of the link.

Dreamweaver will write the PHP code needed to retrieve information from the database. There are two approaches to doing this:

Single record

In the simple case where you need individual values from a recordset, do the following:

• in your webpage, remove the placeholder content (but leaving any formatting)
• go to the bindings palette and flip down the triangle for the recordset you want to use
• drag the desired field to the location in the webpage where you want the content to be displayed
• perform any additional formatting needed.

Multiple Records

In the more complex case, you can display a series of records from the database inserted into your page:

• in your webpage, remove the placeholder content, leaving the cursor in that position
• select the Application tab in the Insert palette, and click on the Dynamic Table icon
• in the dialog, select the desired recordset, and determine if you want all records, and the table format
  
• select "SAVE", and the table with PHP code should be inserted into your page
  
  
  
• optionally modify the display of the fields: in this example, we want to use the field link_name as the name for a link, and the link_url field for the url in the link. To accomplish this, do the following:
  • select the "{links.link_name}" object in the page, and make it a link by using a # for the url
  • delete the top header row of the table by selecting both cells and hitting the delete key
  • then select the link_name table and view its HTML
    
    
    
  • modify the HTML to move the php code that shows the link_url into the position where the # is in the <a href> code. Some example code:
    
    <a href="<?php echo $row_links['link_url']; ?>"><?php echo $row_links['link_name']; ?></a>
    
    
    
  • then delete the old table cell that used to hold the link_url

Dreamweaver has a feature that enables you to view your page with content from the database, while viewing the page inside Dreamweaver. Use the Live Data View button at the top left of the document window. If the planets are not aligned right, Dreamweaver may have trouble with this. If so, just skip to the next step.

To make view your pages on the server, upload the following:

• the page itself
• related image & css files
• the Connections directory Dreamweaver created for the database access

If you have trouble with FTP in Dreamweaver, a better free FTP client is RBrowserLite, available here:

www.rbrowser.com/RBrowserLite/DownLoad.html

To make a page that updates a record or records, do the following:

• create a new PHP page
• create a recordset (see above) for the table you want to update
• click on the RECORD UPDATE FORM button in the application palette
  • select the connection, table, and recordset
  • select the Unique Key Column, which should be a field that you made a unique index when you set up the database in phpMySQL
  • set the "AFTER UPDATING, GO TO" url. This can be:
    • go to this same update page
    • go back to a web page that displays all the records
  • all of the fields in the table will be listed initially in the FORM FIELDS section. Delete any fields you don't want the user to be able to change (or set the DISPLAY AS to only TEXT). For example, you should not allow users to modify the unique index, link_red_id in this case.
  • clicking on each form field allows you to control the display of the field:
    • set the Label that identifies the field in the page table
    • set DISPLAY AS to the appropriate format. In this example, the link_active field is set to CHECK BOX, because it was defined in phpMyAdmin as an ENUM with values of Y or N. When the record is displayed, the checkbox will be checked if the current value is "Y".

If your table has multiple records in it, you may want to add navigation links to move from record to record:

• in the APPLICATIONS insert palette, click on the RECORDSET NAVIGATION BAR icon and select whether you want text or graphic navigation links. Select OK. It will tell you that it needs a REPEAT REGION, but you can ignore this.
• alternatively, you can add the prev, next, etc. behaviors individually. Select the text or graphic for a link and in the SERVER BEHAVIORS palette, select the plus icon, and navigate to RECORDSET PAGING>MOVE TO NEXT PAGE (or other navigation).
• you can also insert code to display the current record number out of the total number of records. In the APPLICATIONS insert palette, click on the RECORDSET NAVIGATION STATUS icon . This can also be inserted manually from the SERVER BEHAVIORS palette.

To add new records to the table, the process is similar to making an UPDATE page. In fact you can add an INSERT FORM to the same page where you update records.

• create a new PHP page, and add a recordset for the table you want to insert records into
• OR, use an existing page such as the update page we created above
• place the cursor where you want the insert form to be placed
• click on the RECORD INSERTION FORM icon
  • the options in this form are similar to the RECORD UPDATE FORM
  • in most cases, you will want to delete the unique key from the insert form (link_rec_id in this example), because the AUTO_INCREMENT mySQL option will create the number for the record id

Setting up a system to delete a record in a table is a bit more complicated than the previous exercises. You need to have a page that displays records, and has links for each record that point to a delete page. The delete page displays the record, and if the user clicks on the form button Delete Record, the record is deleted, and the user is returned to the page that lists records.

• create a page that displays multiple records using the DYNAMIC TABLE icon in the APPLICATIONS palette.
• add a column at the end of this table for the delete link (MODIFY>TABLE>INSERT ROW OR COLUMN), This column will contain a link that will open a page for deleting the corresponding record. We do this by turning the link_rec_id into a special kind of link which tells the delete page which record to delete. This link will pass what's called a URL Parameter to the delete page.
  • insert the unique index field in this cell - link_rec_id in this example - by dragging it from the BINDINGS palette
  • to the right of the link_rec_id, insert the words "delete record" into this cell
  • select this dynamic code and open the HTML view

• modify the HTML code to make a link so that the record_id is passed as a URL parameter to the delete.php page. some example HTML is:
  
  <a href="delete.php?link_rec_id=<?php echo $row_Recordset1['link_rec_id']; ?>">delete record</a></td>
  
  when the php is processed, the actual link will be something like this: delete.php?link_rec_id=3 (where the 3 is the record number).

• now create the delete.php page
• add a recordset to the page, where the FILTER is set accept the URL PARAMETER we are sending from the viewlinks.php page, in this example link_rec_id:
  
  
  
• use the BINDINGS palette, and drag some fields into the page so the user can see which record they are deleting (you don't need all of the fields)
• add a form, and insert only a Submit button, changing the label to "Delete Record"
  
  
  
• now add the DELETE RECORD behavior from the SERVER BEHAVIORS palette:
  • set the first option is set to FORM VARIABLE with an option of the button name in the form "Submit". Note that matching the letter case is important.
  • select the connection and table
  • use your unique key as the PRIMARY KEY COLUMN
  • set the PRIMARY KEY VALUE to URL PARAMETER with the name of the URL parameter passed from the viewlinks.php page, in this example link_rec_id.
  • set the AFTER DELETING, GO TO to the page that displays multiple records, viewlinks.php in this example
    
    

It is possible to make a page where the user can enter a text string that will be used to search one of the fields in the database table. As usual, a Recordset is created, but this time the dialog is set so that the Filter is set to use a FORM VARIABLE. A form is then inserted into the page and a text field is named with the same name used in the Filter setting.

• create a new page and make a Recordset with the Filter set to FORM VARIABLE. E.g.
  
  
• insert a dymanic table that uses the above Recordset
• insert a form, put a text field inside the form, and name this field the same as that used for the Form Variable in the Recordset
• put a Submit button in the form
• select the red border of the form so the property inspector shows the Action and Method for the form (you may need to VIEW>VISUAL AIDS>SHOW INVISIBLES to see the red form border.
• set the Action of the form to the name of the current php file. E.g.
  
  

all materials on this web site © copyright 2005, Philip van Allen