Problem with dropdown filter

[MargateSteve]

I have set up a dynamic dropdown to filter records on a page but it is not working and I feel it is down to the way I am using the GET. My guess is that you cannot use dates as a filter in this way but I cannot find any evidence of that through searching, mainly as I do not know what to search for!

The url generated is 'games.php?date=2005-09-03&submit=Filter' so it is passing the data from the dropdown correctly but I am clearly missing something as it just returns that no records have been found.

I am probably missing something simple and am sure that someone on here will spot it within seconds but I am lost. The actual testing page for this, in case you want to have a look at it not working, is http://www.margate-fc.com/content/test/games.php.

 

As always, any advice would be greatly appreciated!

Steve

The Query Part

// The 'WHERE' bit
if (isset($_GET['date'])) {
    if ($_GET['date'] == "")
    {
        $filter = "";
    } 
    else 
    {
        $filter = "WHERE date = {$_GET['date']}";
    }
}

// Main Query
$limit = 5;
$all_games = mysql_query("SELECT *, HT.team_name as HomeTeam, VT.team_name as AwayTeam, COMP.comp_short_name as CP
FROM all_games
JOIN teams as HT ON all_games.home_team = HT.team_id
JOIN teams as VT ON all_games.away_team = VT.team_id 
JOIN competitions as COMP ON all_games.comp = COMP.comp_id 
$filter ORDER BY all_games_id ASC
");
if (!$all_games) {
    die("Query to show fields from table failed");
}

// Filter query
$dates = mysql_query("SELECT DISTINCT date FROM all_games
ORDER BY date ASC
"); 

The form

<!--Filter Form-->		
<form method='get' enctype='text/plain'>
<select size='1' name='date'>
                <?php while ($row_dates = mysql_fetch_assoc($dates)){ 
	    
	 echo "<option value='".$row_dates['date']."'>".$row_dates['date']."</a></option>";
       }
    ?>

 </select>
        <input type='submit' name='submit' value='Filter'> 
</form>

 

The table part (excluding header row)

  <?php if(!mysql_num_rows($all_games)) {
  echo "<tr><td style=\"text-align:left\" COLSPAN=\"8\">No records returned</td></tr>";
	} else {
	while ($row_all_games = mysql_fetch_assoc($all_games)){ 
	    
echo '<tr>
   <td style="text-align:left" width="10PX">'.$row_all_games['all_games_id'].'</td>';
if ($row_all_games['date']=="")
echo  '<td>TBA</td>';
  else		
echo '<td style="text-align:left" width="100PX">'.date('d M y',strtotime($row_all_games['date'])).'</td>
   <td style="text-align:left">'.$row_all_games['CP'].'</td>
   <td style="text-align:left"> '.$row_all_games['HomeTeam'].'</td>
   <td style="text-align:left"> '.$row_all_games['home_goals']. '</td>
   <td style="text-align:left"> '.$row_all_games['AwayTeam']. '</td>
   <td style="text-align:left"> '.$row_all_games['away_goals']. '</td>
   <td style="text-align:left"> '.$row_all_games['attendance']. '</td>
	                  </tr>';
       }}
    ?>

[Skylight_lady]

I haven't had time to check your code fully. Sounds like a problem in your query. If Ur using dates make sure it's exactly the same as shown in your db.

[MargateSteve]

The dropdown is pulling the date directly from the database and passing it on without any changing or formatting so I would not imagine it is the date format. I could be wrong though!

Steve

[Skylight_lady]

Check your sql query and use as you could have errors in it as i was previously told:

 

 mysql_query($sql) or die(mysql_error() . " In $sql");

 

I found one error but there could be more:

 

"SELECT *,"

 

is not allowed to have a "," after "SELECT *".

[MargateSteve]

is not allowed to have a "," after "SELECT *".

Are you sure? I must use that in 95% of my queries, albeit not ones that are trying to filter by date, ones

 

mysql_query($sql) or die(mysql_error() . " In $sql");

I tried that, but amended it to reflect my query and it returned

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #3' at line 1 In Resource id #3

Steve

[BlueSkyIS]

you can have a comma after any value in the field list.

 

SELECT A.*, B.*, C.someotherfield, D.*, E.somethingelse FROM ....

 

The problem appears to be that $sql is the result of a query and not SQL.

[Skylight_lady]

He has values after the select statement. Still i have never seen:

 

SELECT *,

 

with the values following after it. I don't believe you can use both "SELECT *" and values added onto it. I haven't tested it and have never seen it before. Is there a tutorial or something you can point me to where this is displayed? There is always something new we learn every day.

 

Please tell me that you did not use the $sql in your code. That was an example. echo out your sql statement e.g

 

echo $all_games;

 

after your sql statement and see where it stops or what values are not entered.

[MargateSteve]

I now think that I am more confused than when I started. I know that there is something wrong but do not know what is wrong otherwise I would try to solve it.

 

However I do not think there is anything in the query structure itself. It is a similar sort of setup to what I have used, and seen used, successfully in other queries.

 

On top of that, the basis of the code came from this very forum (http://www.phpfreaks.com/forums/php-coding-help/(solved)-clever-way-to-make-a-dropdown-menu-filter/msg747338/#msg747338) so I personally trust the syntax.

There could, of course, be a typo or I may have changed something incorrectly but I have double checked and cannot see anything.

 

Whatever code anomalies there may be in the query it does seem to be doing it's job.

 

When the page first loads, or you hit submit with no date selected, it sets $filter = ""; and all records load correctly. So the query in these instances picks up $filter perfectly.

 

When a date is picked and you hit submit, no records are returned. However

http://www.margate-fc.com/content/test/games.php

changes to

http://www.margate-fc.com/content/test/games.php?date=2005-08-13&submit=Filter

 

So the form is collecting the date correctly, passing the date on correctly, the page is getting the date correctly, as shown in the URL. There is just something wrong or missing that is stopping it from displaying the records.

 

Any suggestions on what I am missing??

Steve

[BlueSkyIS]

He has values after the select statement. Still i have never seen:

 

SELECT *,

 

with the values following after it. I don't believe you can use both "SELECT *" and values added onto it. I haven't tested it and have never seen it before. Is there a tutorial or something you can point me to where this is displayed? There is always something new we learn every day.

 

This is a very simple, valid SQL statement assuming a table called locations that has a field called id:

 

SELECT * , id FROM `locations`

 

perhaps pointless in this simple example, as you get all fields with * anyway. but it is valid syntax and executes with the expected results (all columns in `locations` with id repeated on the end).

[Skylight_lady]

Thanks for that BlueSkyIs. I never use it because "*" alone gets all the data itself from all the tables displayed in any sql statement.

 

MargateSteve, it sounds like you are displaying the data on the link's FORM without any trouble BUT it's not displaying the data on the FORM. This correct?

 

I'm not sure if this will make a difference but the following code:

 <td style="text-align:left">'.$row_all_games['CP'].'</td>

 

Should more or less be like:

 <td style="text-align:left">"'.$row_all_games['CP'].'"</td>

 

As it is missing the " . Thats the same case for everyone of them as shown in your code. This is the way i do it.

 

[Skylight_lady]

I had the quotes the wrong way.

 

I use:

 

 echo "<td style=\"text-align:left\">'".$row_all_games['CP']."'</td>";

 

when using double quotes for the echo. Maybe bluesky can tell u if the the code:

 

 echo '<td style="text-align:left">"'.$row_all_games['CP'].'"</td>';

 

can be used when using single quotes with echo.

[MargateSteve]

Wouldn't I only need to do that if I was echoing each <td> individually? All of the <td>'s are combined in one echo. The code to echo is enclosed in single quotation marks and any data is concatenated with '.' . When I tried your code it just returned the echoed data enclosed in double quotation marks.

Once again, the records displayed when there is no filter requested are correct so the html side of the table works fine. I just need to know why it is not showing any records when a date is selected.

Steve

[Pikachu2000]

Is the code still the same as in the OP? If not, please post the revised code.

[Pikachu2000]

Nevermind. I think I may have spotted the problem, so try this before reposting the code. When you assign the filter to the $filter variable, the date should be in quotes:

$filter = "WHERE date = '{$_GET['date']}'";

[MargateSteve]

Perfect!!!! Pikachu, you are a star!

 

I knew it was something simple but just could not see it!

 

Thanks for the help

Steve