Nick20 Posted August 26, 2011 Share Posted August 26, 2011 Hi guys, I hope someone can help! I've been experimenting with some concepts from Larry's book and wanted to create a multilevel dropdown nav style menu which is populated with information gathered from the db. Basically gonna be two columns for selecting minutes of meeting dates. the first column will have all the years from the starting year of meetings up to the current year. THEN (and this is the bit I'm stuck on), the second level will come off each year and show all the meeting dates that occurred in that year. So far I'm pretty much there, all of my queries work in mySQL except for specifying one string in particular, which is the string that specifies which year to use in menu level two. the code I have so far is: $q2 = "SELECT YEAR(CURDATE())"; // select current year $r2 = @mysqli_query ($dbc, $q2); $f = mysqli_fetch_row($r2); echo '<p>Select meeting date:</p>'; echo "<ul>"; for ($year = 2002; $year <= $f[0]; $year++) { echo "<li><a href=\"#\">$year</a><ul>"; $q3 = 'SELECT meeting_date FROM btc_meetings WHERE YEAR(meeting_date) = $year'; $r3 = @mysqli_query ($dbc, $q3); while ($row = mysqli_fetch_array($r3, MYSQLI_NUM)) {echo "<li><a href=\"#\">$row[0]</a></li>";} echo "</li></ul></p>"; } NOW... if I run this script with "2002" in place of $year, i get this: Select meeting date: 2002 2002-01-01 2002-01-01 2002-05-01 2002-05-01 2002-03-01 20032002-01-01 2002-01-01 2002-05-01 2002-05-01 2002-03-01 2002-03-01 2002-12-01 2002-04-01 Which seems promising... however, I need to know how to specify that string so that under 2002, all the 2002 dates come up... under 2003, all the 2003 dates come up etc. I hope that makes sense!! Hope someone can help! Cheers, Nick Link to comment Share on other sites More sharing options...
HartleySan Posted August 26, 2011 Share Posted August 26, 2011 You'll only get one year because your query is testing for WHERE YEAR(meeting_date) = $year. You need to use a less than or equals to operator. See the following for more information: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html Cool? Link to comment Share on other sites More sharing options...
Antonio Conte Posted August 26, 2011 Share Posted August 26, 2011 To sort by date, use ORDER BY: (GROUP BY does also work) ORDER BY meeting_date ASC Hartley is probably right about that WHERE-clause. Your syntax for using variables in i query is not right. That is why it seems right. $q3 = 'SELECT meeting_date FROM btc_meetings WHERE ( '.$year.' < YEAR(meeting_date) ) ORDER BY meeting_date ASC'; mysqli_real_escape_string($q3); // some security at least Link to comment Share on other sites More sharing options...
Nick20 Posted August 26, 2011 Author Share Posted August 26, 2011 Hi HartleySan, Thanks for your reply. I forgot to post that when I run the query with the line "... = $year", or also I just checked with what you suggested <=... I get this: Select meeting date: 2002 2003 2004 2005 2006 2007 - no second level and only the first level of the menu comes up. It doesn't seem to recognise the variable $year as the year from the previous level. When I change the value $year to a constant value for the year, the query works, but of course only brings up that constant year for the second level of the menu. Link to comment Share on other sites More sharing options...
Nick20 Posted August 26, 2011 Author Share Posted August 26, 2011 YAYYYY!!!! it's working!! Thanks guys, you've really made my day! The answer was similar to what Antonio suggested but with = rather than >: my original: $q3 = 'SELECT meeting_date FROM btc_meetings WHERE YEAR(meeting_date) = '$year'; working line: $q3 = 'SELECT meeting_date FROM btc_meetings WHERE ( '.$year.' = YEAR(meeting_date) ) ORDER BY meeting_date ASC'; I think it was just a syntax error in specifying the $year string. Now I get: 2002[ *]2002-01-01 2002-01-01 2002-01-01 2002-01-01 2002-01-01 [*]2003 [*]2004 [*]2005 [*]2006 [*]2007 [*]2008 2008-02-02 (crazy data, but matches the db). Thanks tons again! Link to comment Share on other sites More sharing options...
HartleySan Posted August 26, 2011 Share Posted August 26, 2011 I honestly don't know how you're getting those results with that query. The query should specifically only pull data of one year from the DB. I hope you aren't actually performing multiple queries via a loop are you? If you are, please don't do that. Anyway, I don't know how your DB is organized, but I'm very confused how you got multiple years out of that query. All the same, it works, which is good. Nice! Link to comment Share on other sites More sharing options...
Nick20 Posted August 27, 2011 Author Share Posted August 27, 2011 I hope you aren't actually performing multiple queries via a loop are you? If you are, please don't do that. I don't think I am, but I'm pretty new to this, so just making it up as I go along! The database is really simple - this query just references a bunch of dates of meetings over multiple years. To set up a method of selecting a meeting date, I wanted two dropdown menu levels, the first to select the years of the meetings which is in the first query, $q2, $r2 & $f - this generated the array to list a dropdown from the first year of meetings to whatever the current year is. The second level of dropdown was to list all the meetings in each of those years, so I had to reference the row of the first array (the selected year - which I was having the trouble with) and pull out of the database all the meeting dates that occurred in that year. I'm guessing that there is a simpler way of doing this! Probably with java/ajax or something! Link to comment Share on other sites More sharing options...
HartleySan Posted August 27, 2011 Share Posted August 27, 2011 Java will accomplish the same thing that PHP will, but I think PHP is easier. Ajax is a combination of PHP (or some other server-side language) and JavaScript to make calls to a DB, etc. without having to reload the page. To be honest though, it seems somewhat illogical to me to have the user select a year, and then display all the meetings up to that year. Well, I don't know how many meetings there are a year, but a (possibly) more logical solution would be to skip the first step of selecting a year, and just displaying all the meetings in a big table from the start. If there are a lot of meetings, you can easily paginate the table. Also, you could easily set up the table to be sortable on any column (i.e., by date, meeting name, etc.) that way. Anyway, just my personal opinion, but whatever works for you. I still don't honestly understand how saying $year = YEAR(meeting_date) in your query pulled all the meetings up to that year. It should have been just the meetings of that year. All the same, glad it works. Link to comment Share on other sites More sharing options...
Recommended Posts