ms3304: week 9

21 November 2006 S Pogoda

MS3304: Week 9

Loops & Conditionals for Dynamic Content Display


Overview

• Connecting to a database• Accessing the results array• Basic loops for displaying the all

results returned• Review of steps for creating

dynamic content templates


Connecting to a database

Web server

PHP processor MySQLDatabase

PHP script

query

result

May be on same machine or spread across different machines

Make the connection DB server DB name User name User password

Compose/send query SQL statement


Database connection details

First we set variables to hold the data that we need to pass to the server

$server = "161.76.10.12"; $user = "studread"; $pass = "ms3304"; $myDB = "test";$SQLcmd = "";


Database connection statement

• Next send this information to the server to set up a connection

• We assign this connection to a variable

$connect = mysql_connect($server, $user, $pass);


Checking the connection

• Once we send the statement, we check to see if the connection was made

• As we assigned the connection to a variable we can do this by checking the variable

if(!$connect){ die (cannot connect to $server using $user);

}


Specifying the database to connect to

• If the connection has been made successfully we tell the connection the name of the database we want to connect to

…}else{ mysql_select_db($myDB);

…


Sending the SQL statement

• Next we send the SQL statement to the database

• We assign the results of this command statement to a variable

…$result =($SQLcmd, $connect);

…


The connection statements

$connect = mysql_connect($server, $user, $pass);

if (!$connect){ die ("cannot connect to $server using $user");

} else{ mysql_select_db($myDB); $result = mysql_query($SQLcmd, $connect);

}


Accessing the results

• Once the SQL command statement is executed, all the results are stored in the $results variable as an associative array

• It is multi-dimensional because it may contain multiple records

• Each of these records may contain multiple fields


A simple query

SELECT * FROM students WHERE firstName LIKE “Richard"firstName Richard

surname Strockle

studentNumber u0307214

group 2

cw1 null

cw2 null

cwAverage null

team null

IT_prog 0

MM_prog 1


Storing the results for access

• First we need to load the first record of the results into an an accessible associative array using the field names as keys

• We do this using the mysql_fetch_array() function

$row = mysql_fetch_array ( results location)

In our case$row = mysql_fetch_array ($results)

Accessing and displaying the results

• To access the data we use the $row[ ] array we have loaded the results into and use the field names in the database as the identifier for the fields we wish to displayecho "First Name: $row[firstName] \n"; echo "Surname: $row[surname] \n"; echo "Student Number: $row[studentNumber] \n";


Sample query display

First Name: RichardSurname: StrockleStudent Number: u0307214


SELECT * FROM students WHERE (group =2) AND (team = libSMS)

firstName Richard Ben Michael Adetayo

surname Strockle Williams Raneses Isikalu

studentNumber

u0307214 u0301575 u0303886 u0208882

group 2 2 2 2

cw1 null null null null

cw2 null null null null

cwAverage null null null null

team libSMS libSMS libSMS libSMS

IT_prog 0 0 0 1

MM_prog 1 1 1 0

A dealing with multiple results

Displaying multiple results

• We know how to display the first record:echo "First Name: $row[firstName] \n";

echo "Surname: $row[surname] \n";

echo "Student No:

$row[studentNumber] \n";

Would display:

First Name: Richard

Surname: Strockle

Student No: u0307214

• What if we repeat the statements?echo "First Name:$row[firstName] \n"; echo "Surname: $row[surname] \n"; echo "Student No: $row[studentNumber] \n";

echo "First Name:$row[firstName] \n"; echo "Surname: $row[surname] \n"; echo "Student No: $row[studentNumber] \n";


Results

First Name: Richard

Surname: Strockle


First Name: Richard

Surname: Strockle



A basic while loop

• While() loops are the simplest type of loop

• All statements nested within the loop are executed until the condition is met

while (condition){

statements

}

A simple example

Given the following code, what would be displayed?

$x = 0;

while($x<5){print ($x . " ");$x = $x+1;

}


Creating the while() condition

• This condition will loop through each record in the $result array

• On each iteration the $row array will reference the fields in the current row

• When there are no results left the loop will be exited

while($row = mysql_fetch_array($results)){ //statements}

What will this display for the second query?

while($row = mysql_fetch_array($results)){ echo("First Name:

$row[firstName] \n"; echo "Surname: $row[surname] \n"; echo "Student No: $row[studentNumber] \n";

}



First Name: RichardSurname: StrockleStudent No: u0307214First Name: BenSurname: WillaimsStudent No: u0301575First Name: MichaelSurname: RanesesStudent No: u0303886First Name: AdetayoSurname: IsikaluStudent No: u0208882

Layout considerations

What will this display for the second query?

while($row = mysql_fetch_array($results)){ echo "First Name:

$row[firstName] \n"; echo "Surname: $row[surname] \n"; echo "Student No: $row[studentNumber] \n <hr>";

}

Addition of an <hr> at the end of the last echo statement

Formatting multiple results

The <hr> displaysIn between each record.

Any code that is inside the loopwill be repeated

First Name: RichardSurname: StrockleStudent No: u0307214

First Name: BenSurname: WillaimsStudent No: u0301575

First Name: MichaelSurname: RanesesStudent No: u0303886

First Name: AdetayoSurname: IsikaluStudent No: u0208882


Alternate formatting

What if we wanted to format the results to display in a table?


Alternate formatting – creating your display

1. Write the HTML code that you want to display first using dummy data and check it

2. Decide what parts need to go inside your loop and write the conditional statement around it

3. Put the HTML code into print statements and replace dummy data with display variables

Alternate formatting – step 1

<table border="1" cellpadding="5"> <tr> <th>Student No</th> <th>First Name</th> <th>Surname</th> </tr> <tr> <td>u0307214</td> <td>Richard</td> <td>Strockl</td> </tr></table>

<table border="1" cellpadding="5"> <tr> <th>Student No</th> <th>First Name</th> <th>Surname</th> </tr>while($row = mysql_fetch_array($results)){ <tr> <td>u0307214</td> <td>Richard</td> <td>Strockl</td> </tr>}</table>

echo "<table border='1' cellpadding='5'>\n<tr>\n\t<th>Student No</th>\n\t<th>FirstName</th>\n\t <th>Surname</th></tr>";

while($row = mysql_fetch_array($results)){echo "<tr>\n\t<td>$row[studentNumber]</td>";

echo "\n\t<td>$row[firstName]</td>"; echo "\n\t<td>$row[surname]<\td>\n</tr> \

n";}echo "</table>";


Overview of steps to create dynamic content

1. Write code to set the connection variables

2. Test the conditional statements by printing out the $SQLcmd string to the screen to make sure conditional statements are working properly

3. Add the connection statements



4. Write print statements to display data from just the first record to test DB connection and SQL command

5. In a separate file, write the HTML code for the display you want, using dummy data, and test to see it displays correctly

6. Copy this code into your php page



7. Decide which parts of your HTML code need to go inside the loop

8. Write the while loop around the code that needs to be repeated

9. Put all of the HTML code into print statements and replace the dummy data with display variables

ms3304: week 9

Documents