Page 3 of 10
Posted: Thu May 17, 2007 2:52 pm
by RobertGonzalez
OK, I think it is time to step back and write some HTML then put your PHP code into it. What you are trying to do at the moment is some pretty basic HTML coding and should be something that you are comfortable with before moving in to PHP.
Clean up your HTML and make that work. When it is done, integrate your PHP code into it. If you are still having problems, post your entire code back here.
Posted: Thu May 17, 2007 3:01 pm
by phpflixnewbie
Ok this is my full code including a complete form, but I dont know how to integrate the PHP into it.
Code: Select all
<form action="main.php" method="post">
<select name="gid">
<option selected value="">Genre</option>
<option value="">Action</option>
<option value="">Animation</option>
<option value="">Childrens</option>
<option value="">Comedy</option>
</select>
</form>
Code: Select all
<?php
$Host = 'localhost';
$User = 'username';
$Password = 'password';
$Database = 'db_name';
if (!$Link_ID = mysql_connect($Host, $User, $Password)) {
echo 'Failed to connect to Server=' . $Host;
exit;
}
if (!mysql_select_db($Database)) {
echo '<br>Cannot use database=' . $Database;
exit;
}
$genres = array('Action', 'Animation', 'Childrens', 'Comedy');
echo '<select name="genre">';
foreach ($genres as $k => $v) {
echo '<option value="' . $k . '">' . $v. '</option>';
}
echo '</select>';
//Handle posts of the select from above
if (isset($_POST['genre'])) {
$genre = null;
$gid = intval($_POST['genre']);
if ($gid < count($genres)) {
$genre = $gid;
}
if (!is_null($genre)) {
// Handle the selection of the users choice here
}
}
$genre = $_POST['genre'];
$sql = 'select genre_id, genre from genre_ids';
if (!$result = mysql_query($sql)) {
exit('<p>unable to obtain genre list from database using SQL: ' . $sql . '.</p>');
}
$genre = $_POST['genre'];
// Performing SQL query
$sql = "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
, genre
, genre_id
, dvd_genre
FROM dvd_titles
, dvd_genres
, critics_ratings
, genre_ids
WHERE dvd_genres.dvd_id=dvd_titles.dvd_id
AND dvd_titles.dvd_id=critics_ratings.dvd_id
AND genre_ids.genre = '$genre'
GROUP BY dvd_titles.dvd_id
ORDER BY dvd_titles.dvd_title";
$result = mysql_query($sql) or die("Query ($sql) failed: " . mysql_error());
// Printing results in HTML
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)) {
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>';
// Closing connection
mysql_close($Link_ID);
?>
Posted: Sat Jun 02, 2007 12:25 pm
by phpflixnewbie
Any further assistance with this would be greatly appreciated!
Posted: Sat Jun 02, 2007 1:22 pm
by RobertGonzalez
How is the code you posted not working?
Posted: Sun Jun 03, 2007 9:25 am
by phpflixnewbie
With this current code no results are been displayed and im getting two identical drop-down menus, and if i select any option from either menu I also get no results displayed.
Posted: Mon Jun 04, 2007 10:31 am
by RobertGonzalez
Things to check...
Is the database be contacted correctly?
Is the query working?
Are the request vars (POST or GET) being captured and assigned properly?
Posted: Mon Jun 04, 2007 12:15 pm
by phpflixnewbie
OK if I take out the line:
AND genre_ids.genre = '$genre'
from the main query all results are displayed fine, but still the form options do not alter the displayed results at all.
Posted: Mon Jun 04, 2007 12:24 pm
by RobertGonzalez
It doesn't look like you have a form field named 'genre'.
Posted: Mon Jun 04, 2007 1:02 pm
by phpflixnewbie
genre is the name of the field in my database table genre_ids.
Posted: Mon Jun 04, 2007 1:04 pm
by RobertGonzalez
Right, but you are asking the database to give you all records where genre_id is equal to a PHP variable called $genre, which happens to be set to a $_POST (or form field) value, which there doesn't appear to be. So in essence you are telling the database to give you all records where genre_id is NULL, which there probably aren't any which would lead me to believe you are getting an empty result set.
Posted: Mon Jun 04, 2007 1:15 pm
by phpflixnewbie
I thought $genre was the variable which matched $gid and therefore a genre value from my database table, the code in question below was written by yourself and I dont fully understand it:
//Handle posts of the select from above
Code: Select all
if (isset($_POST['genre'])) {
$genre = null;
$gid = intval($_POST['genre']);
if ($gid < count($genres)) {
$genre = $gid;
}
if (!is_null($genre)) {
// Handle the selection of the users choice here
}
}
Posted: Mon Jun 04, 2007 1:19 pm
by RobertGonzalez
Right, in the code I posted I assumed there was a field in the HTML form called genre, which would be the value of the genre_id. When passed to the page, the code looks to see if there is a form field that was passed called 'genre'. If it is set, then the integer value of that field is then assigned to the $genre variable as long as the genre integer value is less that the total number of members of the $genres array.
The code I gave was meant as a guide, not to be taken as-is and placed in your app.
Posted: Mon Jun 04, 2007 1:24 pm
by phpflixnewbie
I give up, this is too difficult for my tiny brain

Posted: Mon Jun 04, 2007 1:31 pm
by RobertGonzalez
Lets start with this. Post the complete HTML form that you want to use to achieve what you want. Lets move from there.
Posted: Mon Jun 04, 2007 1:39 pm
by phpflixnewbie
Appreciate the help, heres the form i'd like to use:
Code: Select all
<form action="main.php" method="post">
<select name="rlsdate">
<option value=""selected="selected">Released</option>
<option value="last30">in the last 30 days</option>
<option value="last60">in the last 60 days</option>
<option value="last90">in the last 90 days</option>
<option value="last6mth">in the last 6 months</option>
<option value="lastyr">in the last year</option>
<option value="overayr">over a year ago</option>
</select>
<select name="avgrating">
<option value="" selected="selected">Avg.Rating</option>
<option value="4ormore">> 4 </option>
<option value="3ormore">> 3 </option>
<option value="2ormore">> 2 </option>
<option value="1ormore">> 1 </option>
</select>
<select name="genre">
<option value="" selected="selected">Genre</option>
<option value="action">Action</option>
<option value="animation">Animation</option>
<option value="kids">Childrens</option>
<option value="comedy">Comedy</option>
<option value="docu">Documentary</option>
<option value="drama">Drama</option>
<option value="horror">Horror</option>
<option value="musical">Musical</option>
<option value="scifi">Science-Fiction</option>
<option value="thriller">Thriller</option>
</select>
</form>