Jump to content
Larry Ullman's Book Forums

Multilevel Dropdown With Dynamically Filled Data


Recommended Posts

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

 


  •  
  • 2003
    • 2002-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

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

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

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

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

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

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

 Share

×
×
  • Create New...