Dynamic Content Display Using Loops and Conditionals in PHP and MySQL

1. MS3304: Week 9 Loops & Conditionals for Dynamic Content Display S Pogoda

2. 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 S Pogoda

3. Connecting to a database Make the connection DB server DB name User name User password Compose/send query SQL statement Web server PHP script query MySQL Database PHP processor result May be on same machine or spread across different machines S Pogoda

4. 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 = ""; S Pogoda

5. 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); S Pogoda

6. 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); } S Pogoda

7. 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); … S Pogoda

8. 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); … S Pogoda

9. 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); } S Pogoda

10. 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 S Pogoda

11. A simple query SELECT * FROM students WHERE firstName LIKE “Richard" S Pogoda

12. 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) S Pogoda

13. 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 display echo "<b>First Name:</b> $row[firstName]<br>\n"; echo "<b>Surname:</b> $row[surname]<br>\n"; echo "<b>Student Number:</b> $row[studentNumber]<br>\n"; S Pogoda

14. Sample query display First Name: RichardSurname: StrockleStudent Number: u0307214 S Pogoda

15. A dealing with multiple results SELECT * FROM students WHERE (group =2) AND (team = libSMS) S Pogoda

16. Displaying multiple results • We know how to display the first record: echo"<b>First Name:</b> $row[firstName]<br>\n"; echo"<b>Surname:</b> $row[surname]<br>\n"; echo"<b>Student No: </b>$row[studentNumber] <br>\n"; Would display: First Name: Richard Surname: Strockle Student No: u0307214 S Pogoda

17. Displaying multiple results • What if we repeat the statements? echo "<b>First Name:</b>$row[firstName] <br>\n"; echo "<b>Surname:</b> $row[surname]<br>\n"; echo "<b>Student No: </b>$row[studentNumber] <br>\n"; echo "<b>First Name:</b>$row[firstName] <br>\n"; echo "<b>Surname:</b> $row[surname]<br>\n"; echo "<b>Student No: </b>$row[studentNumber] <br>\n"; S Pogoda

18. Results First Name: Richard Surname: Strockle Student No: u0307214 First Name: Richard Surname: Strockle Student No: u0307214 S Pogoda

19. 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 } S Pogoda

20. A simple example Given the following code, what would be displayed? $x = 0; while($x<5){ print ($x . "<br>"); $x = $x+1; } S Pogoda

21. 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 } S Pogoda

22. Displaying multiple results What will this display for the second query? while($row = mysql_fetch_array($results)){ echo("<b>First Name:</b> $row[firstName] <br>\n"; echo "<b>Surname:</b> $row[surname]<br>\n"; echo "<b>Student No: </b>$row[studentNumber] <br>\n"; } S Pogoda

23. Displaying multiple results First Name: Richard Surname: Strockle Student No: u0307214 First Name: Ben Surname: Willaims Student No: u0301575 First Name: Michael Surname: Raneses Student No: u0303886 First Name: Adetayo Surname: Isikalu Student No: u0208882 S Pogoda

24. Layout considerations What will this display for the second query? while($row = mysql_fetch_array($results)){ echo "<b>First Name:</b> $row[firstName] <br>\n"; echo "<b>Surname:</b> $row[surname]<br>\n"; echo "<b>Student No: </b>$row[studentNumber] <br>\n <hr>"; } Addition of an <hr> at the end of the last echo statement S Pogoda

25. Formatting multiple results First Name: Richard Surname: Strockle Student No: u0307214 First Name: Ben Surname: Willaims Student No: u0301575 First Name: Michael Surname: Raneses Student No: u0303886 First Name: Adetayo Surname: Isikalu Student No: u0208882 The <hr> displays In between each record. Any code that is inside the loop will be repeated S Pogoda

26. Alternate formatting What if we wanted to format the results to display in a table? S Pogoda

27. Alternate formatting – creating your display • Write the HTML code that you want to display first using dummy data and check it • Decide what parts need to go inside your loop and write the conditional statement around it • Put the HTML code into print statements and replace dummy data with display variables S Pogoda

28. 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> S Pogoda

29. Alternate formatting – step 2 <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> S Pogoda

30. Alternate formatting – step 3 echo"<table border='1' cellpadding='5'>\n <tr>\n\t<th>Student No</th>\n\t<th>First Name</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>"; S Pogoda

31. Overview of steps to create dynamic content • Write code to set the connection variables • Test the conditional statements by printing out the $SQLcmd string to the screen to make sure conditional statements are working properly • Add the connection statements S Pogoda

32. Overview of steps to create dynamic content • Write print statements to display data from just the first record to test DB connection and SQL command • In a separate file, write the HTML code for the display you want, using dummy data, and test to see it displays correctly • Copy this code into your php page S Pogoda

33. Overview of steps to create dynamic content • Decide which parts of your HTML code need to go inside the loop • Write the while loop around the code that needs to be repeated • Put all of the HTML code into print statements and replace the dummy data with display variables S Pogoda