Page 9 of 10

Posted: Sun Jun 17, 2007 3:36 pm
by RobertGonzalez
Does it show any results when you run the query in an admin tool?

Posted: Sun Jun 17, 2007 3:44 pm
by phpflixnewbie
Yes the main query works fine in sql admin. The query also works when you first load the page, but after you have selected a form value and then DESELECTED a form value it displays nothing.

Posted: Sun Jun 17, 2007 4:07 pm
by RobertGonzalez
That means there is something happening wrong in the capturing of values that are being sent into the query in your code.

Posted: Sun Jun 17, 2007 4:32 pm
by phpflixnewbie
This is my current full code, I've tried adding a Show All value to the array so if selected everything will be displayed. I've added it to the OR conditions but now it displays all results whatever form value you choose.
I would like to keep the Show All option though.
You mentioned there could be a problem with capturing of the values, any ideas whats causing it?

Code: Select all

<?php

include 'dbcn.inc.php';

$rlsdate = array('Show All','Last Quarter 2006', 'First Quarter 2007', 'Second Quarter 2007', 'Third Quarter 2007', 'Fourth Quarter 2007');
$genre =  array('Show All','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'];
}


$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="selected"' : '';
  echo "\t" . '<option value="' . $date . '"' . $selected . '>' .$date.'</option>' . "\n";
}
echo '</select>' . "\n";


echo '<select name="genre">' . "\n";
foreach($genre as $genreItem) {
  $selected = $genreItem == $dvdgenre ? ' selected="selected"' : '';
    echo "\t" . '<option value="' . $genreItem . '"' . $selected . '>' . $genreItem . '</option>' . "\n";
}
echo '</select>' . "\n";
echo '<input type="submit" name="submit" value="Apply Filter" >' . "\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
			        , form_display_all
			     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' OR form_display_all='Show All')
			    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
			        , form_display_all
			     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: Sun Jun 17, 2007 5:12 pm
by RobertGonzalez
Am I missing something or does the else not have an opening brace?

Posted: Sun Jun 17, 2007 5:19 pm
by phpflixnewbie
Added opening brace, but still no results :cry:

Posted: Mon Jun 18, 2007 1:43 pm
by phpflixnewbie
After trying numerous different queries it seems that the 'Show All' value data is definitely not being captured. I feel I am so close to completing this application - Please Help! :cry:

Posted: Mon Jun 18, 2007 2:04 pm
by RobertGonzalez
Show All is being passed as the $_POST var, so when you are using:

Code: Select all

AND (rls_quarter='$releasedate' OR dvd_genre='$dvdgenre' OR form_display_all='Show All')
in your query, if show all was selected, the query gets transformed into this:

Code: Select all

AND (rls_quarter='Show All' OR dvd_genre='Show All' OR form_display_all='Show All')

Posted: Mon Jun 18, 2007 2:10 pm
by phpflixnewbie
OMG!!!!! Just tried one more query and it works!!!!!!. I had added a show_all table and the field containing the Show All values is called form_value.

Code: Select all

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
			        , form_value
			    FROM dvd_titles
			        , dvd_genres
			        , critics_ratings
			        , rls_quarters
			        , show_all
			    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 dvd_titles.dvd_id=show_all.dvd_id
			      AND (rls_quarter='$releasedate' OR form_value='$releasedate')
			      AND (dvd_genre='$dvdgenre' OR form_value='$dvdgenre')
			      GROUP BY dvd_titles.dvd_id
		          ORDER BY dvd_title";

}
I think the OR operator will only compare against two values at a time, so it will ignore the third. By putting them in seperate conditions they work seperately, one condition for the Releasedate form and one for the Genre form!!!!! 8O

Everah just ONE last thing PLEASE mate. Is it possible to define a default selected value, so that when the page is loaded the DVD Release Date form always has for example 'Second Quarter 2007' value selected??
I tried adding this value to the selected value where the "produce some forms" code is but nothing happened.

Posted: Mon Jun 18, 2007 2:16 pm
by RobertGonzalez
In this bit of code:

Code: Select all

<?php
$releasedate = '';
if (!empty($_POST['releasedate']) && in_array($_POST['releasedate'], $rlsdate)) {
  // The posted form field is valid
  $releasedate = $_POST['releasedate'];
} 
?>
change $releasedate = '' to:

Code: Select all

<?php
$releasedate = 'Second Quarter 2007';
?>
so that it now looks like:

Code: Select all

<?php
$releasedate = 'Second Quarter 2007';
if (!empty($_POST['releasedate']) && in_array($_POST['releasedate'], $rlsdate)) {
  // The posted form field is valid
  $releasedate = $_POST['releasedate'];
} 
?>
That should do it for a default value.

Posted: Mon Jun 18, 2007 2:34 pm
by phpflixnewbie
OK, that selects it, but is there a way for it also to post that default value on page load?

Posted: Mon Jun 18, 2007 2:55 pm
by RobertGonzalez
When the form posts, it will have that value in that form field $_POST value.

Posted: Mon Jun 18, 2007 3:03 pm
by phpflixnewbie
Yeh but it only posts the value when a user clicks the submit button, can it post the value automatically when the page is opened?

Posted: Mon Jun 18, 2007 3:09 pm
by phpflixnewbie
I'm so dumb sometimes (Everah thinks "only SOMETIMES?!!!" :rofl: )
All I need to do is change my main query to rls_quarter='Second Quarter 2007' and it will display the required results on page load, with the corresponding form value chosen using the ammended code from your last post. :D

Posted: Mon Jun 18, 2007 3:17 pm
by RobertGonzalez
So are you good now?