Creative COW SIGN IN :: SPONSORS :: ADVERTISING :: ABOUT US :: CONTACT US :: FAQ
Creative COW's LinkedIn GroupCreative COW's Facebook PageCreative COW on TwitterCreative COW's Google+ PageCreative COW on YouTube
LIBRARY:TutorialsVideo TutorialsReviewsInterviewsEditorialsFeaturesBusinessAuthorsRSS Feed

Using PHP To Obtain Data from a MySQL Database

COW Library : Adobe Dreamweaver Tutorials : Demetri Tashie : Using PHP To Obtain Data from a MySQL Database
Share on Facebook
CreativeCOW presents Using PHP To Obtain Data from a MySQL Database -- Adobe Dreamweaver Tutorial


New York New York United States

©2011 CreativeCOW.net. All rights reserved.


Showing how to use PHP to access a MySQL database. Building a real world application, this tutorial is interesting in that it shows how to narrow a search based on 2 different criteria (2 different MySQL fields). Finally, this tutorial ends with giving complete code for an HTML page as well as the PHP files.



Using PHP to obtain data from a MySQL database

by Demetri Tashie Dec 2011

Level: Intermediate

Required: Server running PHP and MySQL. If you are not using or testing via the web, you will need to install WAMP on your PC, enable Apache on your mac, or install Apache on your Linux machine.

Skills needed:

  • Creating a MySQL database and table
  • basic HTML:
    • creating forms and form elements
  • basic PHP:
    • creating variables
    • echo statements

We will explore this topic by creating a real world application. Our goal will be to access a MySQL database of vehicles (cars), dynamically choosing them by their make and/or their for sale/lease availability.

Create your MySQL database

You should already be familiar with creating a MySQL databse on your server. Please refer to other tutorials and references for specific help. A good source of info is w3schools.com.

Name your database 'myDatabase' or whatever else you choose to name it. Keep track of the username used to set this databse up, as well as the password used. You will also need the server name which is often localhost, but that vaires depending on your server/webhost. Check with their documentation if you are uncertain.

Create a new table

Create a new table inside this database. Name it vehicles or whatever else you choose to name it. For now, create a table with 5 fields and 7 rows. I created the 5 fields like this:

  1. field: increment, Type: int(1), Extra: auto_increment, Primary Key
  2. field: make, Type: varchar(15), Index
  3. field: model, Type: varchar(15)
  4. field: year, Type: year(4)
  5. field: availability, Type: varchar(5), Index

The 'make' and 'availability' fields are Indexed, to make searching them more efficient. For a table this size, it doesn't matter much, but for a large table it can help the performance.

Insert data into the fields. For this example, my table looks like this:

increment make model year availability
1 ford fiesta 2011 sale
2 ford mustang 2011 sale
3 ford mustang 2010 lease
4 ford fiesta 2010 lease
5 chevy volt 2011 sale
6 chevy volt 2011 lease
7 chevy volt 2010 lease

Create your web page

Create, name and save a web page as .html or .php, whichever you prefer. This page will contain a form with radio buttons which will allow the user to narrow down their search of cars, based on either the make (ford or chevy, in this example) and/or the availability (whether vehicle is for sale or lease).

Create a form with radio buttons

This will be a simple form, with 2 radio button groups - 1 to choose vehicle make, and the other to choose vehicle availability. The form will have a submit button which will have as its action to post to a php page 'vehicles.php'. You can style it as you want with CSS. Here is the code:

<form name="input" action="vehicles.php" method="post">

<fieldset>
<legend>Make you are looking for:</legend>
	<input type="radio" name="make" value="ford" /> Ford
	<input type="radio" name="make" value="chevy" /> Chevy
    <input type="radio" name="make" value="all" checked /> All
  </fieldset>
 <fieldset>   
<legend>Looking to:</legend>    
<input type="radio" name="availability" value="sale" /> Buy
<input type="radio" name="availability" value="lease" /> Rent 
<input type="radio" name="availability" value="all" checked /> All 
</fieldset>

<input type="submit" value="Submit" />   
</form>

Let's look at the form

  • This submitted form will send values to and trigger the file 'vehicles.php'. This is the file which will process the items clicked on this form, and will query the MySQL database to return the requested information.
  • The first radio button group is named 'make' to correspond to the same field in the database table, with the possible the values to choose from being the same as the data in the 'make' field of the database table. In other words, since we have the data as 'ford' and 'chevy' in the database, we then use those same case sensitive values for the buttons. The 'All' button has the value 'all' and is a special case, as we'll see later on.
  • The second radio button group is named 'availability' to correspond to the same filed in the database table, and the values the same as the data in the 'availability' field of the database table ( 'sale', 'lease', and the special case 'all').

Since this form will post to 'vehicles.php', let's create that next.

The PHP file

Create a new empty php file and name it 'vehicles.php'. This page/file needs to accomplish several things:

  • receive the values from the radio buttons
  • connect to the database
  • query the database based on the values passed from the radio buttons
  • print out the correct information

Writing the PHP

In order to access the database, we need the username, password, database name, server name, and database table name. Let's start out by placing the username, password, and database name into php variables:

$username="myUserName";
$password="myPassWord";
$database="myDatabaseName";

We can make this a lit bit more secure by placing this information in it's own separate php file, named for instance 'config.php'. We then access it by including or requiring it in the main php file. If placed outside your root folder, it adds another layer of security.

ob_start();
require("config.php");
ob_end_clean();

Wrapped inside commands to start, and then end and erase(clean) the output buffering (ob), we also increase some security issues. Please consult PHP resources to best address your security needs.

Create some variables for requests

I would next create some variables to use as requests. These will be the values that are sent from the form's 2 radio button groups. So the first one, '$req' will be equal to the value we get from the 'make' button group. The second one, $req2' will be equal to the value obtained from the 'availability' radio button group.

 $req=$_REQUEST['make'];
 $req2=$_REQUEST['availability'];

Conenct to the database

We can next connect to the database. Any connection that is opened must at some point be closed! If you need to brush up on this or other specifics of PHP, please consult other PHP documentation and manuals.

mysql_connect("localhost",$username,$password);
mysql_select_db($database) or die( "Unable to select database");

"localhost" is the name of the server where the database is located. This will vary depending on your server. Many servers have it located as 'localhost'. Others will be a specific address such as 'sql28e8d34f.carrierzone.com' or something else. Please consult your web host or server administrator for your specific settings.

If there is a problem connecting, you will see the message "Unable to select database".

Choose which table to work from

In a simple scenario, you would just state which database table to work in. In our case, we are working from the 'vehicles' table. The simple code to select ( go through every row) of this table would look like this:

$query="SELECT * FROM vehicles";

What we want to do is a little more complicated than that. Let's review. We want to first see which 'make' was chosen. If 'ford' was chosen, we want to only consider the rows which have the 'make' field filled in as 'ford'. Likewise if 'chevy' was chosen, we only want to choose 'make' fields with a value of 'chevy'. But if 'all' is chosen, then we DO want to consider all rows.

Similarly, if the 'buy' or 'lease' button was chosen we only want to be shown rows which are either 'buy' or lease', and if 'all' was chosen, then we want to consider all rows.

Choosing from just one field is fairly simple too. For instance, to consider only rows of 'ford, we can can ask our query to SELECT all rows FROM the vehicles table WHERE the field 'make' is 'ford'. That would look like this:

$query="SELECT * FROM vehicles WHERE make='ford'";

Since we don't know which 'make' will be chosen, we can't hard code the value. We want to use the value that was passed from the button chosen in the form. Remember that we stored this value as '$req', so now we can make this query as:

$query="SELECT * FROM vehicles WHERE make='$req'";

Not so bad. But since we want to check both the make AND the availability, we can use a query which uses '$req' to look for 'make' and '$req2' to look for availability:

$query="SELECT * FROM vehicles WHERE make='$req' AND availability='$req2'";

OK. Still not so bad. But now what happens if one or both of the buttons were chosen for 'all'? Basically, with these 2 radio button groups there are 4 different scenarios that could happen:

  1. a particular make was chosen and a particular availability was chosen
  2. 'all' was chosen for 'make', and a particular availability was chosen
  3. a particular make was chosen, and 'all' was chosen for 'availability'
  4. 'all' was chosen for both 'make' and 'availability'

There are probably other ways to handle this. I choose to do this as a series of 'if/else' conditionals. Here is the code to handle the 4 conditions:

if ($req!="all" && $req2!="all")  $query="SELECT * FROM vehicles WHERE make='$req' AND availability='$req2'";
else if($req=="all" && $req2!="all" ) $query="SELECT * FROM vehicles WHERE availability='$req2'";
else if($req!="all" && $req2=="all" ) $query="SELECT * FROM vehicles WHERE make='$req'";
else if($req=="all" || $req2=="all" ) $query="SELECT * FROM vehicles";

You might notice that all of these conditions are really just checking whether a value was set as 'all':

if(&req=='all') 

or whether it was set as something else(and therefore it does NOT equal 'all'):

if(&req!='all') 

Finish the query and close the connection

These following 4 lines of standard code are used to finish the query and to close the connection. From it, we learn both which rows contain the information we asked for which is stored in the variable '$result', as well as the number of rows this information is in, which is stored in the variable '$num'. Also, note that the connection is now closed. Consult PHP manuals for further discussion.

$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_query($result);
mysql_close();

Using this information

We can now actually use this information! What we need to do is to iterate(loop through) the rows that contained the requested data, and to print this out ('echo' it, in php lingo). We will use a 'for' loop to accomplish this. You will see others using 'while' loops to do this. Depending on the case at hand, the differences between the 2 can be subtle or profound. In this case it doesn't matter, so either would work.

$i=0;
for ($i; $i < $num; $i++){
	$f12=mysql_result($result,$i,"model");
	$f13=mysql_result($result,$i,"year");
	$f14=mysql_result($result,$i,"availability");

Note that in the opening curly bracket in line 2 is not closed yet. It will be closed shortly.

Explanation

  1. a variable '$i' is created and it's initial value is set to the integer zero.
  2. the 'for' loop begins at $i equaling zero, and continues while $i is less than the value of '$num', which is the number of correct rows from the query. After each iteration, $i is increased by 1($i++).
  3. variable $f12 is set at the value of 'model' in the row of the query with the value $i.
  4. variable $f13 is set at the value of 'year' in the row of the query with the value $i.
  5. variable $f14 is set at the value of 'availability' in the row of the query with the value $i.

Print this to the page

All that is left to do is to print this info to the page. We'll do this by concatenating (combining) the 3 variables, including spaces between each, and adding a line break after the 3rd variable. The second line of code closes the 'for' loop with a closing curly bracket.

echo $f12." ".$f13." ".$f14." ";
}

Here is the complete html code:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Untitled Document</title>
</head>

<body>
<form name="input" action="vehicles.php" method="post">
<fieldset>
<legend>Make you are looking for:</legend>
	<input type="radio" name="make" value="ford" /> Ford
	<input type="radio" name="make" value="chevy" /> Chevy
    <input type="radio" name="make" value="all" checked /> All
</fieldset>
<fieldset>   
<legend>Looking to:</legend>    
	<input type="radio" name="availability" value="sale" /> Buy
	<input type="radio" name="availability" value="lease" /> Lease 
	<input type="radio" name="availability" value="all" checked /> All 
</fieldset>
<input type="submit" value="Submit" />   
</form>
</body>
</html>

Here is the complete code for 'vehicle.php'

<?php
ob_start();
require("config.php");
ob_end_clean();

$req=$_REQUEST['make'];
$req2=$_REQUEST['availability'];

mysql_connect("localhost",$username,$password);
mysql_select_db($database) or die( "Unable to select database");

if ($req!="all" && $req2!="all")  $query="SELECT * FROM vehicles WHERE make='$req' AND availability='$req2'";
else if($req=="all" && $req2!="all" ) $query="SELECT * FROM vehicles WHERE availability='$req2'";
else if($req!="all" && $req2=="all" ) $query="SELECT * FROM vehicles WHERE make='$req'";
else if($req=="all" || $req2=="all" ) $query="SELECT * FROM vehicles";

$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_query($result);
mysql_close();

$i=0;

for ($i; $i < $num; $i++){
	$f12=mysql_result($result,$i,"model");
	$f13=mysql_result($result,$i,"year");
	$f14=mysql_result($result,$i,"availability");
	
echo $f12." ".$f13." ".$f14."<br />";
}
?>

Here is the complete code for 'config.php' which contains your database password etc:

<?php
 
$username="myUserName";
$password="myPassWord";
$database="myDatabaseName";
 

?>

I hope this helps you get on your way with PHP and MySQL. This was written in response to an Adobe Dreamweaver Thread: http://forums.creativecow.net/readpost/191/866799


  Adobe Dreamweaver Tutorials   •   Adobe Dreamweaver Forum
Reply   Like  
+1
Share on Facebook


Related Articles / Tutorials:
Adobe Dreamweaver
Working with Photoshop Slices in Dreamweaver - 4

Working with Photoshop Slices in Dreamweaver - 4
  Play Video
In part 4 of this video tutorial series, Richard finalizes the web page inside of Dreamweaver. Topics covered in this tutorial are repeating background images, background color, site load speed up and expandable site construction.

Tutorial, Video Tutorial
Adobe Dreamweaver
Working with Photoshop Slices in Dreamweaver - 1

Working with Photoshop Slices in Dreamweaver - 1
  Play Video
In part one of this video tutorial series, Richard Williams shows you the process of designing a website in Photoshop, exporting it as image slices and then importing the slices into Dreamweaver.

Tutorial, Video Tutorial
Adobe Dreamweaver
Working with Photoshop Slices in Dreamweaver - 2

Working with Photoshop Slices in Dreamweaver - 2
  Play Video
In part two of this video tutorial series, Richard Williams explains how to export the rollover state of buttons from Photoshop and import them into Dreamweaver to then be applied to the site being designed from part one.

Tutorial, Video Tutorial
Adobe Dreamweaver
Working with Photoshop Slices in Dreamweaver - 3

Working with Photoshop Slices in Dreamweaver - 3
  Play Video
In part three of this video tutorial series, Richard Williams shows how to convert images to background images and put text over the top of them inside of Dreamweaver. Richard then applies CSS styling to the HTML text to match it with the design created in Photoshop.

Tutorial, Video Tutorial
Adobe Dreamweaver
Spry Menu Explained

Spry Menu Explained

In this tutorial, CreativeCOW leader Richard Williams explains the principles of the Spry Menu and answers the most commonly asked questions.

Tutorial
Adobe Dreamweaver
Rollovers in Adobe Dreamweaver CS3Rollovers in Adobe Dreamweaver CS3

In this Dreamweaver CS3 tutorial, Abraham explores the capabilities of Dreamweaver to create dynamic rollover functions on a web page. What before might have taken special scripting is easily accomplished through a few slight alterations of the rollover behaviors built into Dreamweaver. Part two of this tutorial shows how to embed pop up videos into your web page.

Tutorial
Adobe Dreamweaver
CSS Basics in Adobe DreamweaverCSS Basics in Adobe Dreamweaver

This tutorial is designed to introduce the concepts of using CSS in Adobe Dreamweaver and HTML for web designers who are not familiar with CSS in web design. CSS stands for Cascading Style Sheet and is a system for adding styling to your web pages and web page content.

Tutorial
Recent Articles / Tutorials:
WalterBiscardi.com
We'd Like to Introduce You to WalterBiscardi.com Training

We'd Like to Introduce You to WalterBiscardi.com Training
  Play Video
Welcome to WalterBiscardi.com and the home of the craft and business of media production brought to you not by professional trainers, but by working Creative Professionals.

Tutorial, Video Tutorial
Art of the Edit
Post Production: Renaming Audio and Video Tracks

Post Production: Renaming Audio and Video Tracks
  Play Video
In this Post Production Quick Tip: Walter shows how taking just a few moments to rename your editing audio and video tracks in your NLE can pay big dividends for organization, collaboration, finish and revisions later in your production cycle.

Tutorial, Video Tutorial
Adobe After Effects Techniques
After Effects: Animated Guitar Strings with Plexus

After Effects: Animated Guitar Strings with Plexus
  Play Video
Using Rowbyte Plexus (the 3D particle system plugin for Adobe After Effects) and Trapcode SoundKeys in After Effects, Gardy Raymond demonstrates how to create animated guitar strings that distort in time with the music.

Tutorial, Video Tutorial
MORE


FORUMSTUTORIALSFEATURESVIDEOSPODCASTSEVENTSSERVICESNEWSLETTERNEWSBLOGS

Creative COW LinkedIn Group Creative COW Facebook Page Creative COW on Twitter
© 2014 CreativeCOW.net All rights are reserved. - Privacy Policy

[Top]