Page 1 of 10

Drop-down filter menu

Posted: Wed May 09, 2007 2:58 pm
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);
?>

Posted: Wed May 09, 2007 5:28 pm
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!

Posted: Thu May 10, 2007 7:59 am
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

Posted: Thu May 10, 2007 8:37 am
by shiznatix
look at the highlighting in your post. you will clearly see that you are missing a " after some echo.

Posted: Thu May 10, 2007 10:08 am
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.

Posted: Fri May 11, 2007 9:08 am
by phpflixnewbie
Would really appreciate further assistance with this. :cry:

tia

Posted: Mon May 14, 2007 5:30 am
by shiznatix
what does your code look like now?

Posted: Tue May 15, 2007 1:27 pm
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. :(

Posted: Tue May 15, 2007 1:34 pm
by RobertGonzalez
Post your new code please.

Posted: Tue May 15, 2007 2:01 pm
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);
?>

Posted: Tue May 15, 2007 2:32 pm
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);
?>

Posted: Tue May 15, 2007 2:40 pm
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.

Posted: Tue May 15, 2007 3:34 pm
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
    }
}
?>

Posted: Tue May 15, 2007 3:42 pm
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

Posted: Wed May 16, 2007 3:37 pm
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);
?>