Page 8 of 10

Posted: Tue Jun 12, 2007 6:12 pm
by RobertGonzalez
Try it and see...

Posted: Fri Jun 15, 2007 12:45 pm
by phpflixnewbie
OK tried the OR operator route and it crashed my browser and had to restart PC :s

Posted: Fri Jun 15, 2007 12:50 pm
by RobertGonzalez
Remember, OR queries typically return much larger result sets than straight queries and AND queries.

Posted: Fri Jun 15, 2007 1:15 pm
by phpflixnewbie
This is the query im trying:

Code: Select all

$query =  "SELECT dvd_titles.dvd_id
         	        , dvd_title
         	        , ROUND((coalesce(sum(totalfilm),0)
         	          +coalesce(sum(empire),0)
         	          +coalesce(sum(radiotimes),0)
			          +coalesce(sum(independent),0)
			          +coalesce(sum(mirror),0)
			          +coalesce(sum(guardian),0)
			         ) /
			         coalesce( count(totalfilm)
			                    +count(empire)
			                    +count(radiotimes)
			                    +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
			        , independent
			        , mirror
			        , guardian
			        , rls_quarter
			     FROM dvd_titles
			        , dvd_genres
			        , critics_ratings
			        , rls_quarters
			    WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
			      AND dvd_titles.dvd_id=critics_ratings.dvd_id
			      AND dvd_titles.dvd_id=rls_quarters.dvd_id
			      AND rls_quarter='$releasedate' OR dvd_genre='$dvdgenre'
			    GROUP BY dvd_titles.dvd_id
		        ORDER BY dvd_title";

I've also tried:

Code: Select all

$query =  "SELECT dvd_titles.dvd_id
         	        , dvd_title
         	        , ROUND((coalesce(sum(totalfilm),0)
         	          +coalesce(sum(empire),0)
         	          +coalesce(sum(radiotimes),0)
			          +coalesce(sum(independent),0)
			          +coalesce(sum(mirror),0)
			          +coalesce(sum(guardian),0)
			         ) /
			         coalesce( count(totalfilm)
			                    +count(empire)
			                    +count(radiotimes)
			                    +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
			        , independent
			        , mirror
			        , guardian
			        , rls_quarter
			     FROM dvd_titles LEFT JOIN
			        , dvd_genres
			        , critics_ratings
			        , rls_quarters
			       ON dvd_genres.dvd_id=dvd_titles.dvd_id
			      AND dvd_titles.dvd_id=critics_ratings.dvd_id
			      AND dvd_titles.dvd_id=rls_quarters.dvd_id
			      AND rls_quarter='$releasedate' OR dvd_genre='$dvdgenre'
			    GROUP BY dvd_titles.dvd_id
		        ORDER BY dvd_title";
First query crashes my PC (even though theres only 162 rows in my tables), second query gives mysql syntax error near: ", dvd_genres, critics_ratings, rls_quarters"
Please help :s

Posted: Fri Jun 15, 2007 1:40 pm
by RobertGonzalez
Are you running the query in PHP or in your administrator?

Posted: Fri Jun 15, 2007 1:43 pm
by phpflixnewbie
Testing it in the full PHP code, yes PHP.

Posted: Fri Jun 15, 2007 1:46 pm
by RobertGonzalez
Try running it in a database administrator and see of the results are handled differently.

Posted: Fri Jun 15, 2007 1:46 pm
by phpflixnewbie
Each variable works individually as their own AND conditions but as soon as I add them both using the OR operator my CPU usage goes upto 100% and the problems start.

Posted: Fri Jun 15, 2007 1:47 pm
by phpflixnewbie
Ok that will require moving the variables and actually assigning values to the Or conditions, will try it.

Posted: Sat Jun 16, 2007 11:16 am
by phpflixnewbie
OK made some progress. Using the following query either $releasedate or $dvdgenre values selected filters the displayed results:

Code: Select all

$query =  "SELECT dvd_titles.dvd_id
         	        , dvd_title
         	        , ROUND((coalesce(sum(totalfilm),0)
         	          +coalesce(sum(empire),0)
         	          +coalesce(sum(radiotimes),0)
			          +coalesce(sum(independent),0)
			          +coalesce(sum(mirror),0)
			          +coalesce(sum(guardian),0)
			         ) /
			         coalesce( count(totalfilm)
			                    +count(empire)
			                    +count(radiotimes)
			                    +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
			        , independent
			        , mirror
			        , guardian
			        , rls_quarter
			     FROM dvd_titles
			        , dvd_genres
			        , critics_ratings
			        , rls_quarters
			    WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
			      AND dvd_titles.dvd_id=critics_ratings.dvd_id
			      AND dvd_titles.dvd_id=rls_quarters.dvd_id
			      AND (rls_quarter='$releasedate' OR dvd_genre='$dvdgenre')
			    GROUP BY dvd_titles.dvd_id
		        ORDER BY dvd_title";
However, I now have a bigger problem to get the $avgrating values working. The average ratings are calculated within the query involving an alias field rounded_rating. Therefore this field doesnt actually exist in the database but is created on the fly, so I can't add matching form values to the database for the query to refer to.
UNLESS, I create yet another table with just average ratings obtained from running this query.
Any other suggestions appreciated.

Posted: Sat Jun 16, 2007 11:27 am
by phpflixnewbie
I've just discovered if I select 'Second Quarter 2007' from the form menu I'm getting no results even though there are definitely Second Quarter 2007 values in the database. Last Quarter 2006 and First Quarter 2007 form values work perfectly though, HELP! :cry

Code: Select all

<?php

include 'dbcn.inc.php';

$rlsdate = array('Released', 'Last Quarter 2006', 'First Quarter 2007', 'Second Quarter 2007', 'Third Quarter 2007', 'Fourth Quarter 2007');
$avgrating = array('Avg.Rating', 'Less Than 1', 'More Than 1', 'More Than 2', 'More Than 3','More Than 4');
$genre =  array('Genre', 'Action', 'Animation', 'Childrens', 'Comedy', 'Documentary', 'Drama', 'Horror', 'Musical', 'Science Fiction', 'Thriller');

/***********************************************
If there are form posts, assign and handle here
***********************************************/
$releasedate = '';
if (!empty($_POST['releasedate']) && in_array($_POST['releasedate'], $rlsdate)) {
  // The posted form field is valid
  $releasedate = $_POST['releasedate'];
}

$averagegrating = '';
if (!empty($_POST['avgrating']) && in_array($_POST['avgrating'], $avgrating)) {
  // The posted form field is valid
  $averagegrating = $_POST['avgrating'];
}

$dvdgenre = '';
if (!empty($_POST['genre']) && in_array($_POST['genre'], $genre)) {
  // The posted form field is valid
  $dvdgenre = $_POST['genre'];
}

/***********************************************
Produce some forms
***********************************************/
echo '<form method="post" action="' . basename(__FILE__) . '">' . "\n";
echo '<select name="releasedate">' . "\n";
foreach($rlsdate as $date) {
  $selected = $date == $releasedate ? ' selected="Released"' : '';
  echo "\t" . '<option value="' . $date . '"' . $selected . '>' .$date.'</option>' . "\n";
}
echo '</select>' . "\n";

echo '<select name="avgrating">' . "\n";
foreach($avgrating as $arating) {
  $selected = $arating == $averagegrating ? ' selected="Avg.Rating"' : '';
  echo "\t" . '<option value="' . $arating . '"' . $selected . '>' . $arating . '</option>' . "\n";
}
echo '</select>' . "\n";

echo '<select name="genre">' . "\n";
foreach($genre as $genreItem) {
  $selected = $genreItem == $dvdgenre ? ' selected="Genre"' : '';
    echo "\t" . '<option value="' . $genreItem . '"' . $selected . '>' . $genreItem . '</option>' . "\n";
}
echo '</select>' . "\n";
echo '<input type="submit" name="submit" value="Send the form" >' . "\n";
echo '</form>' . "\n";


if(!empty($dvdgenre)) {

$query =  "SELECT dvd_titles.dvd_id
         	        , dvd_title
         	        , ROUND((coalesce(sum(totalfilm),0)
         	          +coalesce(sum(empire),0)
         	          +coalesce(sum(radiotimes),0)
			          +coalesce(sum(independent),0)
			          +coalesce(sum(mirror),0)
			          +coalesce(sum(guardian),0)
			         ) /
			         coalesce( count(totalfilm)
			                    +count(empire)
			                    +count(radiotimes)
			                    +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
			        , independent
			        , mirror
			        , guardian
			        , rls_quarter
			     FROM dvd_titles
			        , dvd_genres
			        , critics_ratings
			        , rls_quarters
			    WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
			      AND dvd_titles.dvd_id=critics_ratings.dvd_id
			      AND dvd_titles.dvd_id=rls_quarters.dvd_id
			      AND (rls_quarter='$releasedate' OR dvd_genre='$dvdgenre')
			    GROUP BY dvd_titles.dvd_id
		        ORDER BY dvd_title";

}

else




// Performing Main 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(independent),0)
			          +coalesce(sum(mirror),0)
			          +coalesce(sum(guardian),0)
			         ) /
			         coalesce( count(totalfilm)
			                    +count(empire)
			                    +count(radiotimes)
			                    +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
			        , 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 '<table id="maintable">';




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>ID</th><th>MR</th><th>GN</th></tr>";  // Setting Column Names
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['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 Jun 16, 2007 11:30 am
by phpflixnewbie
Ignore my last post, I was using the wrong database :P

Posted: Sat Jun 16, 2007 11:56 am
by phpflixnewbie
I've decided to remove the $avgrating array and form from the page. However, my small problem now is, when I value is not selected in the form and I refesh the page or click send the form it not displaying all the results, its as if the else statement is not working.

Please help, once I have this sorted the page is virtually complete.

Posted: Sat Jun 16, 2007 2:20 pm
by RobertGonzalez
How many results are showing and how many are you expecting? Have you counted at all using mysql_num_rows()?

Posted: Sun Jun 17, 2007 7:52 am
by phpflixnewbie
It doesnt display any results m8, just the table header is shown.