mysql4u.com

This article will demonstrate an easy way to create dynamic CSS-based navigation menus that allow you to change your website navigation sitewide with one simple MySQL database update. For this example, I will be using the Chrome CSS Drop Down Menu available from Dynamic Drive. It’s a free, lightweight CSS menu that is easily customizable and only uses a small amount of JavaScript. There are other free CSS menus available such as the Suckerfish menu and other commercial menu options are available.

This article assumes the reader is somewhat familiar with Dreamweaver, PHP and MySQL. Powering your navigation menu from a database allows you to easily change your website’s navigation menu simply by updating the database fields that make up the Dreamweaver recordset. Building your site navigation using CSS also allows for quick sitewide changes via the stylesheet formatting.

The first step is to implement the CSS menu of your choice. It’s useful to include several mock menu items as you style your menu so you can see how the menu will appear with multiple navigation choices. Once your menu is in place, go back and delete all but the first menu item in the HTML unordered list.

The second step is to create your MySQL database table which will power your menu. Using the MySQL manager of your choice, create a table which includes three fields: a unique auto-numbering ID field, a field which will hold the text displayed by each menu option, and a hyperlink field, which will include the link each menu item follows when clicked. Depending on your site structure, you can use relative or absolute URLs in this field. At this point, insert data into the database table which will populate the site when loaded. For example, in the first table row, you could have the menu text “HOME” and the URL “index.php”.

Next, back to Dreamweaver to set up the dynamic menu. With your page open, establish a connection to the MySQL database and create the menu recordset. Include all database fields and don’t use any filters on the data. Back in the code, find the first menu item that you left in place earlier. Highlight the text between the quotation marks after the href portion of code. Go into your bindings panel and open the recordset you created for the menu. Click and drag the field representing the link URL field into your code where you highlighted the href link. This will create the PHP code to dynamically assign the link for each menu item. Next, highlight the code in your menu item that will display the browser text for each menu item. Grab the corresponding recordset binding and drag it into your code. This will create the required PHP code that will dynamically display each menu item.

The last step in the process is to create the repeating region that will loop for each database table row. Simply highlight all code from the opening to the closing li tags surrounding the menu item. Go to your server behaviors panel in Dreamweaver and select the “Repeated Region” option. Select the correctrecordset for the menu and click the radio button selecting all records. That completes the dynamic menu creation.

After uploading the page to your testing server, you will see each menu item you entered in your database displayed in your menu. The dynamically generated links will all point to the corresponding pages. If you view the page code, you can see it renders a clean list for the menu items. The code is short and simple and very search engine friendly. For added functionality and the creation of the drop-down menus, simply create a database table that includes the sublinks and include a table field which will hold the parent table ID value. Simply filter by ID for each main menu option, and dynamically generate the submenu the same way you did the main menu. The menu can then be included in a Dreamweaver template for inclusion on every page of your website. When a page is added to the site, simply add the menu item and link in your database and it will appear on every page that includes the navigation menu. It doesn’tget much easier than that to create dynamic database-driven CSS menus.

The author’s Kearney, Nebraska website development company specializes in creating dynamic PHP/MySQL websites for small businesses and organizations.

Article Source: http://EzineArticles.com/?expert=Ryan_Grabenstein

Most interactive websites nowadays would require a user to log in into the website’s system in order to provide a customized experience for the user. Once the user has logged in, the website will be able to provide a presentation that is tailored to the user’s preferences.

A basic login system typically contains 3 components:

1. The component that allows a user to register his preferred login id and password

2. The component that allows the system to verify and authenticate the user when he subsequently logs in

3. The component that sends the user’s password to his registered email address if the user forgets his password

Such a system can be easily created using PHP and MySQL.

================================================================

Component 1 – Registration

Component 1 is typically implemented using a simple HTML form that contains 3 fields and 2 buttons:

1. A preferred login id field
2. A preferred password field
3. A valid email address field
4. A Submit button
5. A Reset button

Assume that such a form is coded into a file named register.html. The following HTML code excerpt is a typical example. When the user has filled in all the fields, the register.php page is called when the user clicks on the Submit button.

[form name=”register” method=”post” action=”register.php”]

[input name=”login id” type=”text” value=”loginid” size=”20″/][br]

[input name=”password” type=”text” value=”password” size=”20″/][br]

[input name=”email” type=”text” value=”email” size=”50″/][br]

[input type=”submit” name=”submit” value=”submit”/]

[input type=”reset” name=”reset” value=”reset”/] [/form]

The following code excerpt can be used as part of register.php to process the registration. It connects to the MySQL database and inserts a line of data into the table used to store the registration information.

@mysql_connect(”localhost”, “mysql_login”, “mysql_pwd”) or die(”Cannot connect to DB!”); @mysql_select_db(”tbl_login”) or die(”Cannot select DB!”); $sql=”INSERT INTO login_tbl (loginid, password and email) VALUES (”.$loginid.”,”.$password.”,”.$email.”)”; $r = mysql_query($sql); if(!$r) {

$err=mysql_error();

print $err;

exit(); }

The code excerpt assumes that the MySQL table that is used to store the registration data is named tbl_login and contains 3 fields – the loginid, password and email fields. The values of the $loginid, $password and $email variables are passed in from the form in register.html using the post method.

================================================================

Component 2 – Verification and Authentication

A registered user will want to log into the system to access the functionality provided by the website. The user will have to provide his login id and password for the system to verify and authenticate.

This is typically done through a simple HTML form. This HTML form typically contains 2 fields and 2 buttons:

1. A login id field
2. A password field
3. A Submit button
4. A Reset button

Assume that such a form is coded into a file named authenticate.html. The following HTML code excerpt is a typical example. When the user has filled in all the fields, the authenticate.php page is called when the user clicks on the Submit button.

[form name=”authenticate” method=”post” action=”authenticate.php”]

[input name=”login id” type=”text” value=”loginid” size=”20″/][br]

[input name=”password” type=”text” value=”password” size=”20″/][br]

[input type=”submit” name=”submit” value=”submit”/]

[input type=”reset” name=”reset” value=”reset”/] [/form]

The following code excerpt can be used as part of authenticate.php to process the login request. It connects to the MySQL database and queries the table used to store the registration information.

@mysql_connect(”localhost”, “mysql_login”, “mysql_pwd”) or die(”Cannot connect to DB!”); @mysql_select_db(”tbl_login”) or die(”Cannot select DB!”); $sql=”SELECT loginid FROM login_tbl WHERE loginid=’”.$loginid.”’ and password=’”.$password.”’”; $r = mysql_query($sql); if(!$r) {

$err=mysql_error();

print $err;

exit(); } if(mysql_affected_rows()==0){

print “no such login in the system. please try again.”;

exit(); } else{

print “successfully logged into system.”;

//proceed to perform website’s functionality – e.g. present information to the user }

As in component 1, the code excerpt assumes that the MySQL table that is used to store the registration data is named tbl_login and contains 3 fields – the loginid, password and email fields. The values of the $loginid and $password variables are passed in from the form in authenticate.html using the post method.

================================================================

Component 3 – Forgot Password

A registered user may forget his password to log into the website’s system. In this case, the user will need to supply his loginid for the system to retrieve his password and send the password to the user’s registered email address.

This is typically done through a simple HTML form. This HTML form typically contains 1 field and 2 buttons:

1. A login id field
2. A Submit button
3. A Reset button

Assume that such a form is coded into a file named forgot.html. The following HTML code excerpt is a typical example. When the user has filled in all the fields, the forgot.php page is called when the user clicks on the Submit button.

[form name=”forgot” method=”post” action=”forgot.php”]

[input name=”login id” type=”text” value=”loginid” size=”20″/][br]

[input type=”submit” name=”submit” value=”submit”/]

[input type=”reset” name=”reset” value=”reset”/] [/form]

The following code excerpt can be used as part of forgot.php to process the login request. It connects to the MySQL database and queries the table used to store the registration information.

@mysql_connect(”localhost”, “mysql_login”, “mysql_pwd”) or die(”Cannot connect to DB!”); @mysql_select_db(”tbl_login”) or die(”Cannot select DB!”); $sql=”SELECT password, email FROM login_tbl WHERE loginid=’”.$loginid.”’”; $r = mysql_query($sql); if(!$r) {

$err=mysql_error();

print $err;

exit(); } if(mysql_affected_rows()==0){

print “no such login in the system. please try again.”;

exit(); } else {

$row=mysql_fetch_array($r);

$password=$row[”password”];

$email=$row[”email”];

$subject=”your password”;

$header=”from:you@yourdomain.com”;

$content=”your password is “.$password;

mail($email, $subject, $row, $header);

print “An email containing the password has been sent to you”;

}

As in component 1, the code excerpt assumes that the MySQL table that is used to store the registration data is named tbl_login and contains 3 fields – the loginid, password and email fields. The value of the $loginid variable is passed from the form in forgot.html using the post method.

================================================================

Conclusion

The above example is to illustrate how a very basic login system can be implemented. The example can be enhanced to include password encryption and additional functionality – e.g. to allow users to edit their login information.

Used with the author’s permission.
This article is written by John L.
John L is the Webmaster of Designer Banners (http://www.designerbanners.com)

In database-driven applications, three different players produce the final output of the web page you view with your client: the web server, the scripting language (PHP), and the database back end (MySQL). When the client browser requests a page from your web site, the following steps occur:

1.

The web server receives the request via HTTP for a particular web page and resolves and retrieves the requested file.
2.

Depending on the nature of the file (i.e., if it ends in .php), it is pre-processed using, in our case, the PHP engine.
3.

The script’s application and presentation logic executes, performing database queries as necessary.
4.

The PHP engine uses the results from the database in its application logic to construct the HTML document, returning it to the web server and, finally, the client.

We will focus on steps three and four in our discussions here. Looking at those steps in more detail, we can summarize the process of accessing and working with a database connection from within a PHP script in the following steps. (The steps in parentheses are optional, depending on circumstance.)

* Establish a connection to the database server.
* (Validate any user input.)
* Select the database on the server to use.
* Execute the desired query against the database.
* (Retrieve and process the results.)
* Create HTML or perform actions based on results.
* (Close the database connection.)

Connecting to a MySQL Database

From a development standpoint, connecting and executing queries from PHP is as simple as calling the appropriate functions. Let’s look at the basic functions used in almost every database-driven application. As I have already explained, the first step is to connect to the database — in our case, this is done via the mysql_connect() function, whose syntax follows:

mysql_connect([$server [, $username [, $password [, $new_link [, $flags]]]]])

If you have worked with MySQL’s mysql client application, most of these parameters should already make sense to you. The first parameter, $server, is the address of the MySQL server to connect to using the username and password provided by the $username and $password parameters. When I say “address,” however, I am not necessarily talking about a TCP/IP address. This parameter can take multiple forms:

// Connect to the server at hostname using the default port
$server = ‘hostname’

// Connect to the server at hostname using the specified port
$server = ‘hostname:port’

// Connect to the server on the local machine using the provided local socket
$server = ‘:/path/to/socket’

Related Reading
Web Database Applications with PHP and MySQL

Web Database Applications with PHP and MySQL
By Hugh E. Williams, David Lane
Table of Contents
Index
Sample Chapter

Read Online–Safari Search this book on Safari:

Code Fragments only

Note: when specifying a server using the hostname, it is worthy to note that the MySQL extension in PHP will attempt to connect using a local socket (or named pipe in Windows) instead of via TCP/IP, if the hostname is of the form localhost, or localhost:port is used. Although this is generally desirable (and recommended), you can also force a TCP/IP connection by using the IP address 127.0.0.1 instead.

The fourth parameter, $new_link, indicates if a new link should be established, even if one already exists for this request. This only applies if you call mysql_connect() multiple times to the same server with the same authentication information. Normally, PHP will reuse an already-opened connection. This parameter will override that behavior, creating a new connection.

The fifth and final parameter is $flags. This parameter is any combination of the following constants bitwise ORd together:

MYSQL_CLIENT_COMPRESS
Establish a connection to the database using a compressed version of the protocol.

MYSQL_CLIENT_IGNORE_SPACE
Ignore white space after function names in queries.

MYSQL_CLIENT_INTERACTIVE
Use the interactive_timeout settings of the MySQL server instead of the default wait_timeout setting when determining if the connection is inactive and should be closed by the server. (See the MySQL documentation for more information on these settings.)

When executed, the mysql_connect() function will attempt to establish a connection to the database and return a resource representing that connection. If the attempt fails for any reason, mysql_connect() will return a Boolean false.
Selecting the Database to Use

Once you have a database connection, the next step is to select the database that you will be performing queries against. (Remember the SQL USE statement?) To do this, you’ll need the mysql_select_db() function which has the following syntax:

mysql_select_db($database [, $link]);

where $database is the name of the database to use, and the optional parameter $link is the database connection resource returned from the mysql_connect() function. This function will attempt to select the specified database and return a Boolean indicating success or failure.

Note: As is the case with almost all of the MySQL extension functions, the $link parameter is optional. In every case, PHP will use the last opened connection. If no connection is open, it will attempt to open one automatically. It is strongly recommended that you provide a $link explicitly to avoid problems as your applications become more advanced.
Performing a Query Against a Database

Now that you know how to connect to the MySQL database, let’s see how to perform a query against the database from within PHP. To do this, use the appropriately named mysql_query() function, whose syntax is as follows:

mysql_query($query [, $link]);

where $query is a single SQL query to execute (without the terminating semi-colon or \g) and the optional parameter $link is the value returned from mysql_connect(). As usual, PHP will use the last opened connection if you do not provide the $link parameter.

Upon successful execution, mysql_query() will return a resource representing the result set or a Boolean false if the query failed. Note that a query is considered a success even if no results are returned; mysql_query() will only fail if the query itself was malformed or otherwise unable to execute on the server. Determining if any results were actually returned from a query requires a different method.
Retrieving a Result Set

Now that you know how to perform a query, it’s time to learn how to access the data from a result set. PHP has many different methods to accomplish this task, but they all have the same general form. For our purposes, I’ll explain things in the context of the mysql_fetch_row() function. Its syntax is as follows:

mysql_fetch_row($result);

where $result is the result resource returned from a successful query executed using the mysql_query() function. This function will return a single row from the result set as an enumerated array, where element zero represents the first column, element one represents the second, and so on. Each subsequent call will return the next row in the result set until no more rows remain. Then, mysql_fetch_row() will return a Boolean false. Generally, this function is used in conjunction with a while loop to traverse the entire array as shown in the below example snippet:

/* Connection code omitted */

$result = mysql_query("SELECT * FROM books");

if(!$result) die("Query Failed.");

while($row = mysql_fetch_row($result)) {

/*
$row[0] now contains the first column of the current row,
index 1 is the second, etc.
*/

}
?>

As I stated earlier, mysql_fetch_row() is not the only function available that allows you to access rows of a result set in this fashion. Each of the following functions has an identical syntax and use as mysql_fetch_row(), but each provides the row in a different format as described:

*

Return the current row as an associative array, where the name of each column is a key in the array.

$row = mysql_fetch_assoc($result)
$row[’column_name’]

*

Return the current row with both associative and numeric indexes where each column can either be accessed by 0, 1, 2, etc., or the column name.

$row = mysql_fetch_array($result)
$row[0]

// or

$row[’column_name’]

*

Return the current row as an object with member variables for each column in the result set.

$row = mysql_fetch_object($result)

$row->column_name

Closing a Database Connection

Although it is not strictly necessary, sometimes it is advantageous to close an open connection to the database when you no longer need it, instead of waiting for the end of the request, when PHP will do so automatically. Use the mysql_close() function with the following syntax:

mysql_close($link)

where $link is the database connection resource returned from the mysql_connect() function.