MargateSteve Posted January 14, 2011 Share Posted January 14, 2011 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>'; }} ?> Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted January 14, 2011 Share Posted January 14, 2011 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. Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 14, 2011 Author Share Posted January 14, 2011 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 Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted January 14, 2011 Share Posted January 14, 2011 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 *". Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 14, 2011 Author Share Posted January 14, 2011 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 Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 14, 2011 Share Posted January 14, 2011 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. Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted January 14, 2011 Share Posted January 14, 2011 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. Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 14, 2011 Author Share Posted January 14, 2011 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 Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted January 14, 2011 Share Posted January 14, 2011 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). Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted January 14, 2011 Share Posted January 14, 2011 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. Quote Link to comment Share on other sites More sharing options...
Skylight_lady Posted January 14, 2011 Share Posted January 14, 2011 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. Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 15, 2011 Author Share Posted January 15, 2011 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 15, 2011 Share Posted January 15, 2011 Is the code still the same as in the OP? If not, please post the revised code. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 15, 2011 Share Posted January 15, 2011 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']}'"; Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 15, 2011 Author Share Posted January 15, 2011 Perfect!!!! Pikachu, you are a star! I knew it was something simple but just could not see it! Thanks for the help Steve Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.