Page 2 of 2

Posted: Fri Feb 23, 2007 2:21 pm
by phpflixnewbie
This code includes an if statement, please let me know/ammend it if im on the right track.

Code: Select all

<?php>

$Host = "localhost"; //you can use IP address instead of localhost
$User = "username";
$Password = "password";
$Database = "databasename";

$Link_ID=mysql_pconnect($Host, $User, $Password);
     if (!$Link_ID)
     {
        echo "Failed to connect to Server=".$Host;
          return 0;
     }
     else
     {
#         echo "<B>Successfully to connected to Server  </B>" .$Host;
     }


     if (!@mysql_select_db($Database,$Link_ID))
     {
#         echo "<br>Cannot use database=  " .$Database;
      }
      else
     {
#         echo "<br> Successfully connected to database= ";
      }

if(isset($avgrating))
{

$query =  "SELECT dvd_titles.dvd_id
           , dvd_title
           , ROUND((coalesce(sum(totalfilm),0)
             +coalesce(sum(empire),0)
             +coalesce(sum(radiotimes),0)
          +coalesce(sum(times),0)
          +coalesce(sum(independent),0)
          +coalesce(sum(mirror),0)
          +coalesce(sum(guardian),0)
         ) /
         coalesce( count(totalfilm)
                    +count(empire)
                    +count(radiotimes)
                    +count(times)
                    +count(independent)
                    +count(mirror)
                    +count(guardian)
               ),1) as rounded_rating
        , prodn_year
        , date_format(dvd_rlsdate,'%d %b %y') as rlsdate
        , dvd_genre
        , totalfilm
        , empire
        , radiotimes
        , times
        , independent
        , mirror
        , guardian
     FROM dvd_titles
        , dvd_genres
        , critics_ratings
    WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
      AND dvd_titles.dvd_id=critics_ratings.dvd_id
      AND '$avgrating'>4
    GROUP BY dvd_titles.dvd_id
      ORDER BY dvd_title";

else


// Performing SQL query
$query =  "SELECT dvd_titles.dvd_id
         	        , dvd_title
         	        , ROUND((coalesce(sum(totalfilm),0)
         	          +coalesce(sum(empire),0)
         	          +coalesce(sum(radiotimes),0)
			          +coalesce(sum(times),0)
			          +coalesce(sum(independent),0)
			          +coalesce(sum(mirror),0)
			          +coalesce(sum(guardian),0)
			         ) /
			         coalesce( count(totalfilm)
			                    +count(empire)
			                    +count(radiotimes)
			                    +count(times)
			                    +count(independent)
			                    +count(mirror)
			                    +count(guardian)
			               ),1) as rounded_rating
			        , prodn_year
			        , date_format(dvd_rlsdate,'%d %b %y') as rlsdate
			        , dvd_genre
			        , totalfilm
			        , empire
			        , radiotimes
			        , times
			        , independent
			        , mirror
			        , guardian
			     FROM dvd_titles
			        , dvd_genres
			        , critics_ratings
			    WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
			      AND dvd_titles.dvd_id=critics_ratings.dvd_id
			    GROUP BY dvd_titles.dvd_id
		        ORDER BY dvd_title";



$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML


echo '<table>';
echo "<tr><th>Title</th><th>Avg.<br>Rating</th><th>Year</th><th>DVD<br>Release Date</th><th>Main Genre</th><th>TF</th><th>EM</th><th>RT</th><th>TI
while( $row=mysql_fetch_array($result, MYSQL_ASSOC) ) {

           echo '<tr>',
                        '<td><a href="detail.php?id=', $row['dvd_id'], '">', htmlentities($row['dvd_title']), '</a></td>',
                        '<td>', htmlentities($row['rounded_rating']), '</td>',
                        '<td>', htmlentities($row['prodn_year']), '</td>',
                        '<td>', htmlentities($row['rlsdate']), '</td>',
                        '<td>', htmlentities($row['dvd_genre']), '</td>',
                        '<td>', htmlentities($row['totalfilm']), '</td>',
                        '<td>', htmlentities($row['empire']), '</td>',
                        '<td>', htmlentities($row['radiotimes']), '</td>',
                        '<td>', htmlentities($row['times']), '</td>',
                        '<td>', htmlentities($row['independent']), '</td>',
                        '<td>', htmlentities($row['mirror']), '</td>',
                        '<td>', htmlentities($row['guardian']), '</td>',

                '</tr>';

}


echo '</table>';




// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($Link_ID);
?>

Posted: Sat Feb 24, 2007 1:17 pm
by phpflixnewbie
Am I on the right track with this or...?