Drop-down filter menu

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Drop-down filter menu

Post by phpflixnewbie »

Im trying to add a form which filters the content of a table displayed when the visitor selects a category from the drop-down menu.
At the moment I get a parse error unexpected ;, Ive highlighted the bracket in question with a comment. Im still very much a PHP beginner, so please let me know if im going in the right direction and where to go next.

Code: Select all

//Basic Form

<form action="main.php" method="post">
<select name="gid">
<option selected value="">Genre</option>
</form>

Code: Select all

//Connecting to database

$Host = "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= ";
      }



//Setting variables and looping through selection

$genres = @mysql_query ('select genre_id, genres from genre_ids');
if (!$genres)  {
   exit('<p>unable to obtain genre list from database.</p>';
} // <<<<<<parse error here


while ($genre = mysql_fetch_array($genres)) {
  $gid = $genre['genre_id'];
  $gname = htmlspecialchars($genre['genres']);
  echo "<option value='$gid'>$gname</option>\n;
}


// Performing SQL query when no category has been selected


$query =  "SELECT dvd_titles.dvd_id      // <<<<<<parse error here
         	        , 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 class="sortable" preserve_style="cell" id="maintable">';




echo "<thead><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></thead>";  // Setting Column Names
while( $row=mysql_fetch_array($result, MYSQL_ASSOC) ) {

           echo '<tbody><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></tbody>';

}


echo '</table>';




// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($Link_ID);
?>
Last edited by phpflixnewbie on Thu May 10, 2007 7:56 am, edited 1 time in total.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

you're exit() is missing it's closing parenthesis.

It's always good practice to look at the line (or structure) before the error, as this happens quite routinely. You'll get used to it in no time!
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Thanks Kieran, I added the closing parenthesis. Ive edited my previous post, and commented another parse error further down.
Please help me diagnose my error and let me know if im on the right track to creating a drop-down filter menu.

tia
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

look at the highlighting in your post. you will clearly see that you are missing a " after some echo.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Thanks shiznatix I now have no parse errors. The page is just displaying a Genre drop-down form, but there are no genre categories listed and no table displayed, I guess I need to add another sql query, but im not sure, please advise me.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Would really appreciate further assistance with this. :cry:

tia
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

what does your code look like now?
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Its as above but without the previous errors. For now I am going to limit the results displayed on the page using the mysql LIMIT function. But obviously I would really like to have a filter menu for visitors to be able to filter the results themselves. :(
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Post your new code please.
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Heres my current code, which just produces a one field (would like to add more categories once this one works) form drop-down menu labelled 'genre', with no options listed below it:

Code: Select all

<form action="main.php" method="post">
<select name="gid">
<option selected value="">Genre</option>
</form>

Code: Select all

<?php

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

$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= ";
      }


//Setting variables and looping through selection

$genres = @mysql_query ('select genre_id, genres from genre_ids');
if (!$genres)  {
   exit('<p>unable to obtain genre list from database.</p>');
}


while ($genre = mysql_fetch_array($genres)) {
  $gid = $genre['genre_id'];
  $gname = htmlspecialchars($genre['genres']);
  echo "<option value='$gid'>$gname</option>\n";
}


// 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(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);
?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

This is a little bit cleaner rendition of your code. What do you want it to do again?

Code: Select all

<?php
$Host = 'localhost';
$User = 'username';
$Password = 'password';
$Database = 'database';

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;
}

$sql = 'select genre_id, genres from genre_ids';
if (!$result = mysql_query($sql)) {
   exit('<p>unable to obtain genre list from database using SQL: ' . $sql . '.</p>');
}

while ($row = mysql_fetch_array($result)) {
    echo '<option value="' . $row['genre_id'] . '">' . htmlspecialshars($row['genres']) . '</option>' . "\n";
}

// 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
        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($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);
?>
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Thanks for the tidy-up Everah. Basically I want the visitor to be able to choose an option from the Genre form, so for example if they choose 'Drama' only titles which have Drama as their genres will be displayed.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Simple enough. You use the form field name which they use to select their choice in the $_POST array to pass into the query for selecting that genre.

Code: Select all

<?php
$genres = array('Old', 'New', 'Black', 'Blue');
?>
<select name="genre">
<?php foreach ($genres as $k => $v): ?>
    <option value="<?php echo $k; ?>"><?php echo $v; ?></option>
<?php endforeach; ?>
</select>
<?php
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
    }
}
?>
Without the spaghetti:

Code: Select all

<?php
$genres = array('Old', 'New', 'Black', 'Blue');
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
    }
}
?>
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Thanx Everah. I'll have a go at using your code tomorow, need sleep. I will want all the other fields that currently display in the results as well, but only titles that match the genre. So i'm guessing I will just need to add a query similar to the full query I already have, but the where statement will need to include the $genre or $genres variable in it.

Many Thanks again, will be in touch tomorow
phpflixnewbie
Forum Contributor
Posts: 132
Joined: Fri Nov 17, 2006 11:46 am

Post by phpflixnewbie »

Back - this is my current code. I have a form which lists the genres nicely. But how do I pass the users choice onto the query? At the moment im getting a message displaying my main sql query and then "failed: FUNCTION coalesce does not exist".

Please help, I feel im am almost there!

Code: Select all

<?php

$Host = 'localhost';
$User = 'username';
$Password = 'password';
$Database = 'database_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
    }
}

$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>');
}


// 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
        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($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);
?>
Post Reply