PHP Web Hosting, Tomcat, JSP, J2EE, Servlets, Struts - PHP4, PHP5 Programming Blog

review flag has been set, then you ll see the items that make up the reviews (review table headings and the reviews). You ve made quite a few changes in this section. But as you can see, the changes have been well worth it. Now you know how to use MySQL to create relationships between tables. You successfully retrieved all the reviews from the review table depending on the movie_id variable. You also looked at using the $_GET super global variable to pass values from one page to another. Summary You ve learned how to work with HTML tables to display your data, how to pull data from more than one database table and have it displayed seamlessly with data from another table, and how to create dynamic pages that display detailed information about the rows in your database. You should also be able to include those nifty little images to graphically display data to your Web site visitors. So far, you ve hard-coded all the additions to the database yourself, which isn t very dynamic. We ll teach you how to let the user add items to the database and edit them later in Chapter 6, but first, you need to know how to use forms with PHP, which is the subject of Chapter 5. Exercises 1. Add a column in the top table of your movie_details.php file that shows the average rating given by reviewers. 2. Change each column heading of the review table in your movie_details.php file to a link that allows the user to sort by that column (i.e., the user would click on Date of Review to sort all the reviews by date). 3. Alternate the background colors of each row in the review table of your movie_details.php file to make them easier to read. Hint: Odd-numbered rows would have a background of one color, even-numbered rows would have a background of another color; check each row number to see if it is divisible by 2 to determine if it is even or odd. 134 Chapter 4
Note: In case you are looking for affordable and reliable webhost to host and run your business application check Vision ftp web hosting services

Figure 4-7 While the script is collecting rows of reviews, if there are any reviews for the movie, you set a flag indicating this using the $review_flag variable. The code creates arrays to hold the values that will be returned. Why are you putting them into arrays and not just ordinary variables? This allows the variables to hold data for more than one review for the movie. After all, you expect that there ll be many, many reviews for each film. If you didn t create the review variables as arrays, then you d return only the last review for the movie. In the previous discussion, we looked at why we preferred to put the field values into a variable rather than echo out the field values. Take a look at the line reviewer_name. You ll notice that we have placed the line $review_row[ review_name ] inside the PHP function ucwords. This allows you to automatically perform the ucwords function (which capitalizes the first letter of each word) on the value returned from that field. The code then loops through the array and assigns values to each of the fields that you are going to display to the user for the review. You use the PHP sizeof function to calculate how many records have been returned. Finally, you ve broken the $movie_details variable up into several smaller chunks and added them through the use of .=<<<. Just as you have done before, you used an already-defined variable (in this case, $review_table_headings and $review_details) and just slotted it into the correct place. If the 133 Using Tables to Display Data
Note: If you are looking for best quality webspace to host and run your tomcat application check Vision personal web hosting services

$i = 0; $review_details = ; while ($i $review_date[$i] $review_title[$i] $reviewer_name[$i] $review[$i] $review_rating[$i]

EOD; $i++; } 5. Make the changes as shown here. Go slowly, and ensure that you make all the changes correctly. $movie_health

EOD; if ($review_flag) { $movie_details .=<<

EOD; } 6. Save the file as movie_details.php (overwriting the existing one we hope you have made a backup copy as suggested). 7. Upload to your Web server, load table3.php, and click a movie. You ll see something similar to Figure 4-7. How It Works The generate_ratings function is fairly straightforward. You send it the value that is in the ratings field for a movie and it creates a rating image for that movie and returns it. Notice that you are using .= (which is similar to .=<<<). This ensures that movies with a rating of more than 1 will get additional images added to the single rating image. The $review_table_headings variable contains the table headings for the reviews that you have just pulled out via the previous SQL query. This uses exactly the same concept as the movie table headings in the previous example. So now you have all the review table headings in a nice, neat variable. 132 Chapter 4
Note: In case you are looking for affordable webhost to host and run your web application check Vision http web server services

A fundamental mistake that a lot of beginners make is to simply use the same variable names when creating SQL queries (for example, $sql = SELECT …). Assume that you simply copied and pasted, and then modified the movie query and movie result when it was called query. You d have two SQL queries called query and two results sets called $result. When the first result ran it would produce the expected results, as would the second one. However, if you ever wanted to refer to the results set that was returned from the first SQL, you d have a big problem. Why? The first results set would have been overwritten by the results of the second SQL query. For this reason, always ensure that you use different names for additional SQL queries and results sets returned from the query. Try It Out Displaying the Reviews The next chunk of code is the function that allows you to display a cool little graphic for the rating that each film received from the reviewer. We ve used one of our images (thumbsup.gif) but you can use anything on your local machine. Just make sure to use your own filename. 1. Add this code to movie_details.php: function generate_ratings($review_rating) { $movie_rating = ; for($i=0; $i<$review_rating; $i++) { $movie_rating .=   ; } return $movie_rating; } 2. Now add the code in the following lines immediately below the $movie_table_headings variable: $review_table_headings=<< Date of Review Review Title Reviewer Name Movie Review Comments Rating

EOD; 3. You need to add the next few lines after the review table headings section: while($review_row = mysql_fetch_array($review_result)) { $review_flag =1; $review_title[] = $review_row[ review_name ]; $reviewer_name[] = ucwords($review_row[ review_reviewer_name ]); $review[] = $review_row[ review_comment ]; $review_date[] = $review_row[ review_date ]; $review_rating[] = generate_ratings($review_row[ review_rating ]); } 4. Next, you need to add the following lines to the page: 131 Using Tables to Display Data
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check Vision professional web hosting services

( 3 , 2003-08-01 , An awesome time , George B. , I liked this movie, even though I thought it was an informational video from our travel agent. , 3 ) ; $insert_results = mysql_query($insert) or die(mysql_error()); ?> 2. Save this file as createreviews.php, upload it to your server, and open it in your browser. Your reviews table has now been created and filled! How It Works By now you should be familiar with creating tables using MySQL and PHP, and this should be pretty self-explanatory. If you re having trouble, you might try going back to Chapter 3. Try It Out Querying for the Reviews In this example, you re going to link two tables (movies and reviews) to show the reviews for a particular movie. This requires a lot of changes to the movie_details.php page, so you d best make a copy of the file (can t ever be too careful). Then follow these steps: 1. Open movie_details.php in your favorite text/HTML editor. 2. Make the following changes to the code (changes are highlighted): $movie_query = SELECT * FROM movie . WHERE movie_id = . $_GET[ movie_id ] . ; $movie_result = mysql_query($movie_query, $link) or die(mysql_error()); And later in the code, change the following: while ($row = mysql_fetch_array($movie_result)) { $movie_name = $row[ movie_name ]; $movie_director = $row[ movie_director ]; 3. Add the following lines after the closing bracket of your while statement: $review_query = SELECT * FROM reviews . WHERE review_movie_id = . $_GET[ movie_id ] . . ORDER BY review_date DESC ; $review_result = mysql_query($review_query, $link) or die(mysql_error()); How It Works You ve changed the name of $query to $movie_query, and also changed $result to $movie_result. This was done to ensure that you do not confuse yourself when accessing the relevant results set returned from a SQL query. There is also an order by clause, which ensures that the most recent reviews are at the top of the page. 130 Chapter 4
Note: If you are looking for cheap and reliable webhost to host and run your web application check Vision coldfusion web hosting services

It s time to answer the question, what s a relationship? A relationship is a way of joining tables so that you can access the data in all those tables. The benefit of MySQL is that it is a relational database and, as such, supports the creation of relationships between tables. When used correctly (this can take a bit of time to get your head around) relationships can be very, very powerful and can be used to retrieve data from many, many tables in one SQL query. The best way to demonstrate this is to build upon what you have done so far, so let s do it. Try It Out Creating and Filling a Movie Review Table Before you can access movie reviews in your movie review table, you need to create the table and then fill it with data. 1. Open your text editor and type the following code: Note: If you are looking for cheap webhost to host and run your apache application check Vision apache web hosting services

Figure 4-6 Next, you define the $movie_details variable. This should be fairly self-explanatory. Remember the $movie_table_headings variable you created previously? All you ve done is slot it into place within the $movie_details variable and, hey presto, it appears. Finally, you define the $page_end variable and bring it all together in the closing lines. Phew! That was a lot of code there! Now is a good time to take a break and reward yourself (mine s coffee with milk and two sugars, thanks). A Lasting Relationship What if you wanted to find all the reviews for a particular movie? As it stands, you d need to create a new SQL query in the movies_details.php page and execute it when the page loads, which would make a total of two SQL queries in one page. It would work, but it would not be very efficient. (We re all efficient coders, aren t we?) This also results in unnecessary code. 128 Chapter 4
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision j2ee hosting services

EOD; $movie_details =<<

$movie_table_headings

EOD; $page_end =<< EOD; $detailed_movie_info =<<Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision j2ee hosting services

$movie_cost = $row[ movie_cost ]; //get director s name from people table get_director(); //get lead actor s name from people table get_leadactor(); } How It Works Because you ve already written the functions to get the director s and lead actor s names, you borrowed this code from the table2.php file. Then you ve changed the query to return everything in each record, as opposed to only a few fields. It does mean that you are returning one field that you are not actually using. The query now contains a WHERE clause. This determines which record you are going to retrieve the data from. Take a look at the WHERE clause (it s not as daunting as it first seems): . You have used $_GET[ movie_id ] in the WHERE clause. This is the ID of the movie that was passed from the hyperlink in table3.php. . You ve also created the variable $movie_table_headings to contain the headings for the fields that you ll be using. . The rest of the code is very similar to the code in table3.php. You ve added four extra fields to the while control loop. Didn t we say previously that returning fields that you don t need is not good practice? Yes, we did. However, in this case you are returning only one more field than you need, as opposed to returning many redundant fields. So are we going against our own advice? To be 100 percent truthful, yes. However, because you are using the vast majority of fields in each record, PHP will not suffer from this tradeoff, and it is worth it. You would not want to do this when, for example, you want the values of (say) 5 fields and the record structure contains 50 fields. If you did this in that instance, PHP would be wasting resources to return the other 45 fields. So now that you ve arranged to return information from records, what next? Now you put this extra information to work. Try It Out Displaying Movie Details In this exercise, you ll enhance the movie_details page with your new data. 1. Add the following lines of code to the end of movie_details.php: $movie_health = calculate_differences($movie_takings, $movie_cost); $page_start =<< 126 Chapter 4
Note: In case you are looking for affordable webhost to host and run your servlet application check Vision ecommerce web hosting services

/* Function to get the director s name from the people table */ function get_director() { global $movie_director; global $director; $query_d = SELECT people_fullname . FROM people . WHERE people_id= $movie_director ; $results_d = mysql_query($query_d) or die(mysql_error()); $row_d = mysql_fetch_array($results_d); extract($row_d); $director = $people_fullname; } /* Function to get the lead actor s name from the people table */ function get_leadactor() { global $movie_leadactor; global $leadactor; $query_a = SELECT people_fullname . FROM people . WHERE people_id= $movie_leadactor ; $results_a = mysql_query($query_a) or die(mysql_error()); $row_a = mysql_fetch_array($results_a); extract($row_a); $leadactor = $people_fullname; } $query = SELECT * FROM movie . WHERE movie_id = . $_GET[ movie_id ] . ; $result = mysql_query($query, $link) or die(mysql_error()); $movie_table_headings=<< Movie Title Year of Release Movie Director Movie Lead Actor Movie Running Time Movie Health

EOD; while ($row = mysql_fetch_array($result)) { $movie_name = $row[ movie_name ]; $movie_director = $row[ movie_director ]; $movie_leadactor = $row[ movie_leadactor ]; $movie_year = $row[ movie_year ]; $movie_running_time = $row[ movie_running_time ]. mins ; $movie_takings = $row[ movie_takings ]; 125 Using Tables to Display Data
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision j2ee hosting services