Web Development Using LAMP Essay

Web Development Using LAMP Essay

Order Number	45789632101
Type of Project	ESSAY
Writer Level	PHD VERIFIED
Format	APA
Academic Sources	10
Page Count	3-12 PAGES

Web Development Using LAMP Essay

Instructions/Descriptions

[Order Now]

 

Module 03 Lab Worksheet

Web Development Using LAMP

Lab Activities:

• Create a database in MySQL
• Import data into MySQL
• Access a MySQL database using SQL
• Connect to a MySQL database using PHP
• Integrate SQL query results into a Web page
• Run an SQL query from a Web form

[Order Now]

 

Introduction

Download the sample data file TestData.csv.zip and unzip it.

Note that the file is in plain ASCII, with Unix line endings and uses the pipe symbol (|) as a field delimiter. You’ll need to know this when you import it into your database. Over the course of this worksheet I’ll be asking you questions about MySQL commands. You can find documentation at http://dev.mysql.com . I encourage you to use this to answer the worksheet questions. You can also get some of your answers from PHPMyAdmin itself as we work through the exercise but this will help you get more familiar with the SQL language.

Evaluate Your Data

[Order Now]

 

Open the sample data file in a spreadsheet program and examine the data. In the following table, list the names, description and data types for each field.

Field Name (from file)	Description	Data Type

Create Database

Before you import unformatted data into MySQL, you have to have a place to put it.

You can import data in three ways:

• Use a compatible table of an existing database
• Create a new table in an existing database either manually or by importing.
• Create a new database with a table that fits the incoming data.

Log into PHPMyAdmin.

Create a new database called dbtest. What SQL command would you use?

Since it’s not a good idea to use the MySQL administrator account root for everything, we’ll create a new MySQL user that will be managing this new database.

Create a new user with a user name consisting of your first initial followed by your last name. For example, Ellie Palka would create a user named epalka.

This will be a user for localhost only and has no access to any databases. The password will be the same as the username (we can change it later).

In other words, the full user name for Edith Palka would look like epalka@localhost with a password of epalka. (Remember to substitute your own first initial/last name for the user ID.)

What is your user name?

What was the SQL command you would use to create this user?

A user with an easily-guessed password is insecure but this is only for testing purposes and we can change the password later.

Now give the user you just created administrative access to the database dbtest. That is, they should have full control over the database dbtest and no others. What SQL command would do that?

Confirm that your user has full access to dbtest. How would you show this with an SQL command without logging in as that user?

Run that command, if you haven’t already. What was the output?

Log out and log back in as your new user to confirm that they can administer dbtest and nothing else. If this works, continue with this lab as your new user.

Importing Data

Sometimes the data file you are importing is formatted in a way that isn’t quite right for your project. This happens quite frequently when you are getting your data from a third party. The process of fixing it is called data cleansing.

Data cleansing can be a complex job and there are commercial applications designed for just that purpose. In fact, there are entire companies that provide data cleansing services.

In our case we have to

• replace the pipe (|) delimiters in our file with commas
• enclose our data fields with quotes
• add a column to use as our primary key.

This can all be done by hand, of course but we’d prefer to have the computer do the grunt work for us.

Our cleaning job is pretty simple so we might be able to do it with the tools we already have.

Cleaning Our Data

Upload TestData.csv to your workspace.

Using Find->Replace, replace the pipe symbols (|) with a comma enclosed in double quotes. (“,”)

That’s most of the work. Now we just have to put a double quote (“) on the beginning and end of each line and add a field for our data key. You can do it by hand but it’s better to automate the job.

The text editor isn’t flexible enough to let us do this but we have other tools at our disposal.

At the bottom of your workspace is a terminal window with a command prompt. This lets you access many powerful text-processing tools that will make your work easier.

Every modern operating system has a set of command line utilities that allow you to do things that are difficult or even impossible to do in the GUI. If you are an IT professional, or plan to become one, you should familiarize yourself with these tools.

Click your mouse into the window to make it active.

If the window is not available, right-click on your DevBox and select SSH Terminal, then click your mouse inside the window.

Type ls at the prompt and press <Enter> to see a list of the files in your workspace.

To add a double quote to the beginning and end of each line, we will use a utility called sed, which is short for ‘stream editor’. It’s very useful for performing monotonous tasks like this.

At the command prompt, type:

sed ‘s/^.*$/”&”/g’ TestData.csv > TestData01.csv

and press <Enter>.

Normally sed will print its output to the screen. To save it to a file instead (like TestData01.csv) we need to use the > symbol to redirect the output.

Right-click on your DevBox and select Refresh.

A new file, TestData01.csv should now be in your File Explorer. Note that the original file is still available so if we mess up we can easily go back and re-do it.

Click on TestData01.csv to open it and make sure that it’s properly formatted before continuing.

Adding a Primary Key Field

Remember, a primary key has two major properties: 1. It must be unique 2. It cannot be null (it should always have a value). When choosing a primary key, you should consider: · It should be simple and familiar. · The value shouldn’t change. · For efficiency, an integer value is preferable. · It is usually a single column but you can use multiple columns. In any case, use as few columns as possible. A popular strategy is to create a new column with an unrelated, unique number and auto-increment it with each new record. That’s what we are going to do.

We’re going to create a column labelled CUST_ID that consists of 1, 2, 3 and so on for each row.

.

First we need to figure out how many records we actually have. At the command line, enter:

wc -l TestData01.csv

The wc command is normally used to get a word count. The -l option tells wc to count lines instead.

(We can also count them in our text editor by scrolling down and looking at the last line number.)

What number is returned by this command?

Given that the first line consists of our field labels, how many records do we actually have?

We’re going to do this in two steps:

1. Create a text file with the column data we need.
2. Join this file to TestData01.csv.

Create the Column Data

In your workspace, create a new file called ids.txt.

Select the file to open it in your text editor.

In the file add the single line at the top:

“CUST_ID”,

Don’t forget the end comma and press <Enter> at the end of the line.

Save your changes before continuing.

Adding our Number Data

Now we need to add the numbers. We know that we will need 1 through 100 and they will have to be double-quoted with a comma on the end. In other words, each line will look something like this:

“2”,

A good way to get a computer to do something repetitive is to use a loop. Normally these are found in program code but we can also run them at the command line.

At the terminal, run the following command:

for ((a=1;a <= 100;a++)); do echo \”$a\”, ; done >> ids.txt

This looks complicated but let’s break it down. a is the label for the number that we’re going to print and we want it to start at 1: a=1 and end at 100. So we test to see if a is still less than or equal to 100 each time we run our loop: a <= 100 Each time we run the loop, we add 1 to a: a++ During our loop we run one command: echo \”$a\”, The $ tells echo that we don’t want the letter ‘a’, we want the current number with the label a. A double-quote usually means something special to the echo command and they’re not normally printed out. The backslashes (\) tell echo to treat the double-quotes like any other character and just print them. Normally echo prints to the screen but we want to add the output to our file ids.txt instead. >> ids.txt The double-arrows (>>) tell our terminal to append the output to the end of our file instead of replacing the contents of the file.

Close and reopen the ids.txt file in your text editor and you should see the changes.

Putting It All Together

The last thing we need to do is to append ids.txt to TestData01.csv.

This is actually pretty easy. In the terminal, run the command:

paste -d ” ids.txt TestData01.csv > TestData02.csv

Now when you refresh your workspace, you’ll see the TestData02.csv file.

Open it in your editor to make sure that all of the changes we need have been completed.

Download TestData02.csv and rename it accounts.csv.

NOTE: You could do all of this by hand or in a spreadsheet program.

BUT:

Sometimes the spreadsheet will modify your data when you open the file because it wrongly guesses the datatype. For example, it might convert a credit card number into scientific notation. MS Excel in particular is notorious for this. This can corrupt your data so make sure to double-check before importing into your database! I’ve experimented with several spreadsheet programs and so far have found that LibreOffice (free for Windows, Linux and Mac OS X) and Numbers (free for Mac OS X) preserve the formatting of the text fields. Since this test data is just 100 records, it can be edited by hand. However, the project data file is 1000 records so it’s worth the effort to figure out what tools work to automate the process.

Now we can finally import our data.

Log into PHPMyAdmin and click on the dbtest database you created.

Click on the Import tab.

In File to Import, click on the Browse button and select accounts.csv.

Since we want the first line (the header columns) to be the names of our fields, click on the check box labelled “The first line of the file contains the table column names (if this is unchecked, the first line will become part of the data)”.

Click on Go.

If you get an error, read it carefully to see what part of the data file was a problem. Once you fix it, try to import it again.

If everything goes well, you should receive a success message.

How many records were added?

What SQL command would you use to import your data file?

Click on the new table name and examine it to make sure it has all of the data we need.

We need to tell MySQL that CUST_ID is our primary key.

In the table view, select the Structure tab.

In the row containing CUST_ID, click on Primary (the golden key icon).

Confirm that you want to make CUST_ID your primary key. (The key icon should change from gold to grey.)

Note that the table has the default name of TABLE 1. We should rename it to make it less generic.

With the table still selected, click on the More tab and select Operations.

In the Table operations page, go to the Table options section and rename the table to accounts. Click the Go button to submit your change.

Access Database Using SQL and PHP

Now we have our database and have run some SQL statements against it in PHPMyAdmin. But our real goal is to integrate our database with our Web page. We can do this with any programming language understood by our Web server but since we have PHP installed we’ll use that.

Let’s break this task down into pieces:

• Crafting the SQL query.
• Passing the query via PHP and sending the output to a Web page.
• Create a Web page with a button linked to our query.

Crafting our Query

It’s important to make sure your query works as expected before you plug it into a PHP statement. This minimizes the work required to debug your code. PHPMyAdmin allows you to enter SQL directly and will advise you if you make a mistake. Let’s keep things simple for now with a query that will search for the first and last names of all of the customers in our table.

SELECT FNAME,LNAME FROM `accounts`;

Go ahead and run this statement in the SQL tab of PHPMyAdmin and make sure it works as expected. If it does, we can move on to the next phase.

Passing Our Query to PHP

First we need to tell PHP how to open a connection to our database before it can send a query. Create a new folder in your workspace called test to hold your test scripts. Add a file to this folder called php_dbtest.php with the following content in the body:

<!doctype html>

<html lang=”en”>

<head>

<title>PHP Data Connection Test</title>

</head>

<body>

<?php

$servername = “localhost”;

$username = “username”;

$password = “password”;

// Create connection

‘$conn = new mysqli($servername, $username, $password);

// Check connection

if (!$conn) {

die(“Connection failed: ” . mysqli_connect_error());

}

echo “Connected successfully”;

?>

</body>

</html>

(Don’t forget to save your changes before continuing.)

Remember to change username and password to the user and password you created for your dbtest database before saving.

Open a new page in your Web browser and point it to your DevBox home page. Click on the link for the file you just created.

You should get the message ‘Connected successfully’. If you didn’t, check your code and try again until you do before continuing.

Your connection to the database will remain open as long as the script is running. If you want to close it beforehand, simply use the expression:

$conn->close();

So now we know how to connect to our server, let’s connect specifically to our database dbtest. Edit your PHP test page and add the following after the code that opens the database connection:

In the section where we set the server and user information, add the line:

$dbname = “dbtest”;

In the section where we create our connection, modify the statement

$conn = new mysqli($servername, $username, $password);

to read

$conn = new mysqli($servername, $username, $password, $dbname);

The original connection was to the database server. The new connection is to a specific database on that server.

Save the changes, reload the Web page and make sure you get the success message before continuing.

Now that we can connect to a specific database on our server, it’s time to send a query.

Edit your Web page to add the following to the section after we create our connection:

$sql = “SELECT FNAME,LNAME FROM `accounts`”;

$result = $conn->query($sql);

This loads our query into the variable sql which then is sent to the server via the connection we just opened. The output of our query is stored in the object named $result.

We’re not done yet. Now that we have a result, let’s do something with it. First let’s just see how many records were returned.

if ($result->num_rows > 0) {

echo “<br>$result->num_rows results returned.”;

} else {

echo “0 results”;

}

This if statement will be very, very useful for us. Basically it says “If more than one record matched the query, do something with it, otherwise tell me that nothing happened.” All we have to do is figure out what that “something” is. The <br> tag just puts in a line break between the successful connection message and our output.

Remember any printed output from this script will be interpreted as HTML so remember to include appropriate tags to format your text.

Save your changes and refresh the Web page.

How many results were returned?

Take a few moments to experiment with other HTML tags on your output before continuing, like changing the size and style.

Now that we know our query was passed and executed successfully, let’s print out the results. This time instead of echo, we’ll use the printf (formatted print) function. In addition, we’d like to print out the name in standard <last name>,<first name> format.

Modify your if statement to read:

if ($result->num_rows > 0) {

//output data of each row

while ($row = $result->fetch_assoc()) {

printf (“%s, %s<br>”, $row[“LNAME”], $row[“FNAME”]);

}

if ($result->num_rows = 0) {

echo “0 results <br”;

}

}

Let’s break down what’s happening here, line-by-line.

while ($row = $result->fetch_assoc()) {

Remember, result is an object that contains the…well, results of our query. The method fetch_assoc() dumps the records into an associative array called row. The while loop goes through each record until there are no more records to process.

Unlike a traditional array, which uses integers as indexes (ex. a[0],a[1]…), an associative array uses strings. In this case the indexes are the field names (row[FNAME], row[LNAME], row[ZIP], etc.) Be aware that field names are case-sensitive.

printf (“%s, %s<br>”, $row[“LNAME”], $row[“NAME”]);

The printf function has two parts – the first describes the desired print format and what data types are going to be printed and the second part lists the variables to be printed. Some common data type notations you can use:

%s	string
%c	character
%d	decimal (base 10)
%o	octal (base 8)
%x	hexadecimal (base 16
%f	floating point

Think of it this way — you’re sending printf a string of 1’s and 0’s and the data type tells it how to interpret them.

So now we can use PHP to run a query on a database and output the results to a Web page. For our last step, let’s set up a button on our Web page to run that query when clicked. In other words, we’re going to create an HTML form.

For what we need to do, creating a page with a form is pretty simple. All we need is a page with a single button which, when pressed, runs our query and prints the results.

Create a new HTML page called dbform_test.html with the following content and save it in your test folder:

<html>

<body>

<form action=”php_dbtest.php”>

<button type=”submit”>Run SQL Query</button>

</form>

</body>

</html>

Button, Button, Where’s that Darn Button?

Go back to your DevBox home page and click the link for dbform_test.html. (You may have to refresh the page.)

What do you see?

Click the button. What happens?

Final Task: Create a Web Search Page

By now you should know enough to create a simple search form to run a query on a database.

• Create a form that will let the user input a surname and when the search runs, it will output all users with that surname from the accounts table.
• Submit your HTML and PHP code files with this worksheet.
• Export the accounts table as a PDF, name it <userid>_accounts.pdf and submit it with this worksheet. For example, if your username is epalka, then you would submit a PDF named epalka_accounts.pdf.

You can submit all of these files as a single Zip file if you prefer.

Web Development Using LAMP Essay

 

PLACE THE ORDER WITH US TODAY AND GET A PERFECT SCORE!!!