Posted on

Login and Registration Options

Readings

PHP6 and MySQL5 for Dynamic Web Sites
Larry Ullman. Visual QuickPro Guide. Peachpit Press. 2008.

Collecting and storing user data from a web site has many purposes:

  • Track the kinds of visitors and their needs
  • Target content to specific vistors
  • Restrict content to adults
  • Secure transactions, such as forum conversations, product purchases, and download copyrighted material
  • Reduce spam in unrestricted areas of a site

While user data can be stored in a file on the server, it is more secure when stored in a database. Many web hosts provide PHP and MySQL connectivity with their accounts. These accounts often provide one or more databases with multiple users who have their own login credentials for accessing the database. Management of the database is either one manually by the hosts’ staff or within an Account Management tool like Plesk or CPanel. OSU Provides one database per student/staff, which can be activated by logging into onid.orst.edu, clicking Web Database, and the Create Database button. Use the database name, userid, and password in conjunction with a connection script.

Chapter 13 of PHP and MySQL for Dynamic Web Sites provides user registration code which you may download at no cost. Refer to the printed book for implementation.

Create a Database Table for Chapter 13

The Chapter 13 code requires a database table called users, and the following SQL query can be used along with the a connection script to create the table via a browser:

// Create the table. Only needed this one time.
$blog = 'CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
active BOOLEAN NULL,
first_name VARCHAR(40) NOT NULL,
last_name VARCHAR(40) NOT NULL,
password1 VARCHAR NULL,
password2 VARCHAR NULL,
pass VARCHAR NOT NULL
email VARCHAR(40) NOT NULL,
registration_date DATETIME NOT NULL
)';

// Run the query.
if (@mysql_query ($blog)) {
print '<p>The table has been created.</p>';
} else {
die ('<p>Could not create the table because: <b>' . mysql_error() . '</b>.</p><p>The query being run was: ' . $query . '</p>');
}

This set of scripts, like many authentication systems provides the following components.

  • Standard file structure
  • Include files to build the page layout
  • Error handling in a config.inc file
  • Data validation
  • Database connectivity with mysql_connect
  • Registration
  • Authentication
  • Login
  • Logout
  • Change password
  • Forgot password

Another component that is useful for user authentication management is an administrative view of each user’s data. Such a tool would provide the ability to insert, edit, delete, and encript passwords, as well as backup the data. phpMyAdmin is a tool provided with OSU’s ONID accounts that allows staff, students, and faculty to manage the their databases, including user authentication tables.

Posted on

Displaying database content with MySQL queries

Examples

Download the code you see in the column at the right, prepared by Pam Van Londen and James Holladay, OSU.

Readings

PHP and MySQL for Dynamic Web Sites
Larry Ullman. Visual QuickPro Guide. Peachpit Press. 2003. Read and try chapters 4 to 10.
PHP.net
Type in any php function name after the domain name to read what it means and how to use it.

A standard set of actions between PHP and MySQL is often structured like this in a PHP file:

1. Start the script.

<?php
/* This form allows the user to add a descriptive link to a database after preview and validation
and display it on the same page.
Written by Pam Van Londen and James Holladay in 2009.
*/

// 1. Set variables for the script.
$pagetitle = "Daily Painters ";
$subtitle = "Famous Artists";
//Set the default background color for each input field.
$firstnamecolor = '#fff';
$lastnamecolor = '#fff';
$websitenamecolor = '#fff';
$imagenamecolor = '#fff';
$bionamecolor = '#fff';
//Define global variables so their values can be accessed by other PHP tags.
global $valid;
global $submitted;
global $validFirstName;
global $validLastName;
global $validWebsite;
global $validImage;
global $validBio;

//initialize variable values
if(!isset($_POST['submit'])){//if the submit button was not yet pressed
$submitted = 0;
$valid = 0;//to stop the preview from showing when the page first loads with no content.
}
if(isset($_GET['submitted'])){//lets the preview division show that the entry has been added to the database
$submitted = 1;
}

2. Include site-wide files.

// 2. Display the template header.
include '../header.htm' ;
Open a connection to the database.
// Connect to the database.
require_once ('../../connect.php');

3. Verify the form data.

// 3. Set up the preview validation.
//if the preview button is clicked, set $valid to 1, we will make it 0 if any part of validation fails. Set all other validation-related variables to 1 or true.
if(isset($_POST['preview'])){//if the preview button is clicked
  $valid = 1;
  $validFirstName = true;
  $validLastName = true;
  $validWebsite = true;
  $validImage = true;
  $validBio = true;
}

//Validate each field.
if(isset($_POST['first_name'])){
  if(!ctype_alpha($_POST['first_name']) || empty($_POST['first_name'])){//must not be empty, must be alphabetic.
    //if not valid, change background color of input element, set pertinant validation variables to 0 or false.
    $valid = 0;
	$firstnamecolor = '#FFCC99';
	$validFirstName = false;
  }
}
if(isset($_POST['last_name'])){
  if(!ctype_alpha($_POST['last_name']) || empty($_POST['last_name'])){//must not be empty, must be alphabetic.
	//if not valid, change background color of input element, set pertinant validation variables to 0 or false.
    $valid = 0;
	$lastnamecolor = '#FFCC99';
	$validLastName = false;
  }
}
if(isset($_POST['website'])){
  $webURL = $_POST['website'];
  if(@fopen($webURL,'r') === false || empty($_POST['website'])){//must not be empty, URL must exist.
	//if not valid, change background color of input element, set pertinant validation variables to 0 or false.
    $valid = 0;
	$websitecolor = '#FFCC99';
	$validWebsite = false;
  }
}
if(isset($_POST['image'])){
  $imgURL = $_POST['image'];
  if(@GetImageSize("$imgURL") === false || empty($_POST['image'])){//must not be empty, an image must exist at the specified URL.
    //if not valid, change background color of input element, set pertinant validation variables to 0 or false.
    $valid = 0;
	$imagecolor = '#FFCC99';
	$validImage = false;
  }
}
if(isset($_POST['bio'])){
  if(empty($_POST['bio'])){//must not be empty.
    //if not valid, change background color of input element, set pertinant validation variables to 0 or false.
    $valid = 0;
	$biocolor = '#FFCC99';
	$validBio = false;
  }
}
3.1 Handle the data given by the visitor.
global $submitted;
// Handle the form when submit is clicked.
if(isset($_POST['submit'])){
  if(!$submitted){//change the $submitted variable to 1, after clicking the submit button
   $submitted = 1;

    // Insert new data from the form into the database

    // 3.1 Grab field data from the form and protect against SQL injections (stop any code inserted into the form field from running, which could erase our database tables).
	$cleanFirstName = mysql_real_escape_string($_POST['first_name']);
	$cleanLastName = mysql_real_escape_string($_POST['last_name']);
	$cleanWebsite = mysql_real_escape_string($_POST['website']);
	$cleanImage = mysql_real_escape_string($_POST['bio']);
	$cleanBio = mysql_real_escape_string($_POST['image']);
3.2 Construct SQL INSERT query.
// 3.2 Construct the query.
    $insert = "INSERT INTO artists (user_id, first_name, last_name, website, bio, image, registration_date) VALUES (
	   'NULL',
	   '{$cleanFirstName}',
	   '{$cleanLastName}',
	   '{$cleanWebsite}',
	   '{$cleanImage}',
       '{$cleanBio}',
	   NOW()
	)";
3.3 Execute SQL query, saving visitor data to the database.
// 3.3 Send the data to the database
	if(@mysql_query($insert)){
	  print '<p class="alert">Entry added.</p>';
	}
	else{
	  die("<p>Could not add the entry because: it was probably a duplicate, or <b>" . mysql_error() . "</b>. The query was $query.</p>");
	}

	//this works to keep data from resubmitting on refresh, but no 'submitted' message will appear, and it requires javascript to be turned on.
	echo "<script>window.location.replace('redirect.php?name=".$_POST['first_name']."');</script>";

  }//end if for insert
}

4. Add content from the database.

4.1 Query the database.
// 4. Display selected files from the table and order them.

// 4.1 Display the list of artists in ascending order by last name.
$show = 'SELECT * FROM artists ORDER BY last_name ASC';
4.2 Check for errors.
// 4.2 Run the query; if the table has rows fo data...
if($r = mysql_query($show)){
4.3 Begin the <table> element.
// 4.3 Start a division, list, and/or table before the rows are displayed.
  echo '<div class="rightbox"><dl> ';
4.4 Loop through all records, adding a <tr> for each
// 4.4 Retrieve and print every record or row along with the list or table tags each needs.
  while($row = mysql_fetch_array($r)){
	print "<dt><a href='{$row['website']}' >{$row['last_name']}, {$row['first_name']}  

			<dd><img src='{$row['image']}' alt='{$row['bio']}' height='30' width='175' />

			\n";
  }
4.5 Close the <table> element
// 4.5 End the list, table, and/or division.
  echo '</dl></div>';
}

5. If an error occured while inserting the form data into the database, add an error message.

// 5. Error - Query didn't run.
else{
  die ('<p>Could not retrieve the data because: <b>' . mysql_error() . "</b>. The query was $show.</p>");
}// End of query IF.

6. Close the database connection.

// 6. Close the database connection.
mysql_close($dbc); 

// Close the php script.
?>

7. Add the form.

<!-- 7. display a form with validadtion, preview, and submit to database options. -->
<form name="addfamous"  id="addfamous" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>"  method="post"> 

<!--7.1 preview is hidden until preview button is clicked -->
  <div id="previewFormData">
  <?php
    global $valid;
	global $submitted;
	if($submitted){//clear the form if it was submitted.
	  echo '<fieldset class="preview"><legend style="margin-top:30px;">Preview</legend><dl>';
	  print "<dt><p>Your Information has been submitted, ".$_GET['submitted'].".</p></dt>";
	  echo '</dl></fieldset>';
	  $valid = 0;
	}
	else if($valid == 1){//if the form is valid, but not yet submitted, write the content to the preview division.
	  echo '<fieldset class="preview"><legend style="margin-top:30px;">Preview</legend><dl>';
	  print "<dt><a href='{$_POST['website']}' style='color:#000;' >{$_POST['last_name']}, {$_POST['first_name']}</a></dt>
			 <dd><img src='{$_POST['image']}' alt='{$_POST['bio']}' height='30' width='175' /></dd>
			 \n";
	  echo '</dl>
	        <label for="submit"><input name="submit" id="submit" class="confirm" type="submit"  value="Add to List" tabindex="y" accesskey="a"  /></label>
			</fieldset>';
	}
  ?>
  </div>

<!--7.2 form instructions-->
  <p>Please use this form to add a famous artist, a link to their web site, and an image of their work. </p>
  <p><span class="example">Required fields are marked with an orange  <span class="alert">*</span>
  and accesskeys are labeled with an <u>underline</u> and can be used with the Alt-Shift (Windows) or Control (Mac) keys.</span> </p>
<!-- 7.3 form fields-->
  <fieldset>
    <legend>Who do you consider famous?</legend>
    <p><label for="first_name"><u>F</u>irst name  <span class="alert"> *</span></label><br />
      <input  style="background-color:<?php echo $firstnamecolor?>" name="first_name" id="first_name" type="text" tabindex="1" accesskey="f" value="<?php echo $_POST['first_name']; ?>"  size="20" maxlength="30"   /><?php global $validFirstName; if (isset ($_POST['preview'])) { if(!$validFirstName){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['first_name'])){echo ' valid';}echo ' first name.</p>';}} ?> </p>

    <p><label for="last_name">Last <u>n</u>ame  <span class="alert"> *</span></label><br />
      <input  style="background-color:<?php echo $lastnamecolor?>" name="last_name" id="last_name" type="text" tabindex="2" accesskey="n" value="<?php echo $_POST['last_name']; ?>"  size="20" maxlength="30"   /><?php global $validLastName; if (isset ($_POST['preview'])) { if(!$validLastName){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['last_name'])){echo ' valid';}echo ' last name.</p>';}} ?> </p>

    <p><label for="website"><u>W</u>ebsite address <span class="alert"> *</span> </label><br />
      <input style="background-color:<?php echo $websitecolor?>"  name="website" id="website" type="text" tabindex="3" accesskey="w" value="<?php echo $_POST['website']; ?>" size="20" maxlength="100"  /><?php global $validWebsite; if (isset ($_POST['preview'])) { if(!$validWebsite){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['website'])){echo ' valid';}echo ' website address.</p>';}} ?> <br />
      <span class="example">Example: http://www.site.com</span></p>

    <p><label for="image"><u>I</u>mage of their work or portrait <span class="alert"> *</span> </label><br />
      <input  style="background-color:<?php echo $imagecolor?>" name="image" id="image" type="text" tabindex="4" accesskey="i" value="<?php echo $_POST['image']; ?>" size="20" maxlength="100"  /><?php global $validImage; if (isset ($_POST['preview'])) { if(!$validImage){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['image'])){echo ' valid';}else{echo 'n';}echo ' image URL.</p>';}} ?> <br />
      <span class="example">Example: http://www.site.com/picture.jpg</span></p>

    <p><label for="bio"><u>B</u>iography (short) <span class="alert"> *</span> </label><br />
      <textarea style="background-color:<?php echo $biocolor?>" name="bio" id="bio" tabindex="5" accesskey="b" cols="20" rows="6" ><?php echo $_POST['bio']; ?></textarea><?php global $validBio; if (isset ($_POST['preview'])) { if(!$validBio){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['bio'])){echo ' valid';}echo ' bio.</p>';}} ?> <br />
      <span class="example">No html allowed; just plain text.</span>
<!-- note that textarea size attributes are rows and columns, not size -->
    </p>
  </fieldset>
<!--7.4 submit if preview is ok and all fields are valid -->
  <fieldset>
    <legend>Preview will show at the top of the page.</legend>
    <label for="submit"><input name="preview" id="preview" class="validator"  type="submit"   value="Preview" tabindex="6" accesskey="v"  />
    to <u>v</u>iew your submission.</label>
  </fieldset>
</form>

8. Include the site template.

<!--8. end the template items -->
<?php
include ('../footer.htm');
?>

Posted on

Database Design

Readings

SQLCourse.com
Free interactive course online, for learning MySQL.
MySQL tutorial
MySQL Reference manual, chapter 3.
Database Enabled Websites
Tutorial recommended by OSU PHP documentation.

A database is a collection of related data. It can also be viewed as a collection of related tables.

Tables have sets of fields. Field types define the kind of data the field can contain, including:

String
Stores text and numerical characters.
Numeric
Stores numbers that can be calculated.
Date and time
Stores dates and times.
Boolean, or logical
Accepts one of only two specified values.

It is possible to create tables; define fields; and enter, query, and display data using scripts in web pages. For this course, we will use three “tools” to do this:

MySQL
An open-source database residing on the ONID server; each student gets one.
phpMyAdmin
The interface provided to create tables, define fields, enter, browse, test, and query data.
PHP scripts
In web pages, to enter, query, and display data from database tables.

MySQL database via your web server account

MySQL is an open-source database. Most web server accounts include the use of at least one MySQL database. To design a database, think about data you would like collected from your visitors. What do you want to know about them?

Start with pen and paper to list the ideas about like sets of data you want to collect. Organize the sets into:

  • Tables (or entities)
  • Fields
  • Relationships (how one table uses data from another table)

For instance, if you just want to track inquiries, a single table might meet your needs. This database uses one table (contact) and that table uses 9 fields:

Inquiry database structure
Table NameFields needed
ContactCustomerNumber
FirstName
LastName
Address
City
State
Zip
Phone
Email

If you have more complicated data to collect, group related fields in separate tables. For instance, if you want to track statistics of your favorite sports teams you may end up with 4 tables, several fields per table, and relationships between fields/tables:

Team standings database structure
Table NameFieldsType
Teams Index
Rank
Name
Location
Sport		 Int
Int
Varchar
Varchar
Varchar
Varchar
Players & Coaches Index
TeamIndex
Rank
Name
Location
Age
Position
Sport
Comments		 Int
Int
Int
Varchar
Int
Varchar
Varchar
Varchar
Text
Sport SportType
SeasonStart
SeasonEnd		Varchar
Date
Date
Matches Index
Location
DateStart
DateEnd
Rank		Int
Varchar
Date
Date
Int

Once you decide the number of tables you need, and what you want to call each field, define the field types.  Basic types include:

Integer (int)
Numerals that will be calculated.
Float
A number with decimals.
Variable Character (varchar)
Numerals and text in short strings; usually 100 characters or less.
Text (text)
Numerals and text for long passages.
Date (date)
Years months, and days. The default is year-month-day.

Some attributes to note include:

Index
Provides a unique identifier for the row/record in the table. Can be the primary key field. Use “idx” rather than “index” for the field name; it is sometimes dangerous to use a field name that is already a standard database term/variable/type, etc.
Null
Means the field can remain empty; the visitor doesn’t have to fill this field in.
Not Null
Means the field cannot remain empty; the visitor must fill in the field.

When you know what fields you need, you can either define the tables and fields using:

  • phpMyAdmin
    • Link to it from your database set up page accessible when you login to your web server account.
    • Requires your database login and password.
  • PHP Scripts
    • Find or write a script that creates a tables and fields.

Once your fields are defined, build a web page form to collect the data. Use the same field names in your form as used in your database.

You’ll need snippets of code to:

  • Login/connect to the database
  • Add a row to the database
  • Add data to the row
  • Display the new row and/or a set of records/rows
  • Close the connection to the database

Larry Ullman, author of the Visual Quickstart Guides for PHP and MySQL, says “One of the best ways to determine what information should be stored in a database is to clarify what questions will be asked of it and what data would be included in the answers.”

See Displaying database content with MySQL queries for example code.