Dynamic Menu - using PHP, Javascript and MySQL

[mrman23]

Hi,

 

I am working on a menu that is generated from our MySQL database, it has four drop-down menus which all call different sections of data based upon the selection before. This all works fine and is reflective of the first choice, then second, then third - the problem that I would like it to work if the user selects the second or third option without choosing the first.

 

My code is as below:

 

<SCRIPT language=JavaScript> 
function reload(form) 
{ 
var val1=form.locationS.options[form.locationS.options.selectedIndex].value; 
var val2=form.typeS.options[form.typeS.options.selectedIndex].value; 
var val3=form.genreS.options[form.genreS.options.selectedIndex].value; 
var val4=form.statusS.options[form.statusS.options.selectedIndex].value; 
self.location='search.php?locationS=' + val1 + '&typeS=' + val2 + '&genreS=' + val3 + '&statusS=' + val4; 
} 
</script> 

<?php 
include 'db.php'; 
include 'dbopen.php'; 
include 'alltitle.php'; 
include 'allstyle.php'; 

$selection1=$_GET['locationS']; 
$selection2=$_GET['typeS']; 
$selection3=$_GET['genreS']; 
$selection4=$_GET['statusS']; 

//CITY 
$quer1=mysql_query("SELECT DISTINCT city FROM members ORDER BY city"); 

//TYPE 
if(isset($selection1) and strlen($selection1) > 0) 
{$quer2=mysql_query("SELECT DISTINCT type FROM members WHERE city='$selection1' ORDER BY type");} 
else 
{$quer2=mysql_query("SELECT DISTINCT type FROM members ORDER BY type");} 

//GENRE 
if(isset($selection2) and strlen($selection2) > 0) 
{$quer3=mysql_query("SELECT DISTINCT genre FROM members WHERE city='$selection1' AND type='$selection2' ORDER BY genre");} 
else if(isset($selection1) and strlen($selection1) > 0) 
{$quer3=mysql_query("SELECT DISTINCT genre FROM members WHERE city='$selection1' ORDER BY genre");} 
else 
{$quer3=mysql_query("SELECT DISTINCT genre FROM members ORDER BY genre");} 

//STATUS 
if(isset($selection3) and strlen($selection3) > 0) 
{$quer4=mysql_query("SELECT DISTINCT status FROM members WHERE city='$selection1' AND type='$selection2'  AND genre='$selection3' ORDER BY status");} 
else if(isset($selection2) and strlen($selection2) > 0) 
{$quer4=mysql_query("SELECT DISTINCT status FROM members WHERE city='$selection1' AND type='$selection2' ORDER BY status");} 
else if(isset($selection1) and strlen($selection1) > 0) 
{$quer4=mysql_query("SELECT DISTINCT status FROM members WHERE city='$selection1' ORDER BY status");} 
else 
{$quer4=mysql_query("SELECT DISTINCT status FROM members ORDER BY status");} 


//FORM 
echo "<form method=post name=f1 action=''>"; 

//CITY 
echo "<select name='locationS' onchange=\"reload(this.form)\"><option value=''>Select one</option>"; 
while($noticia1 = mysql_fetch_array($quer1)) 
{if($sect1['city']==@$selection1) 
{echo "<option selected value='$sect1[city]'>$sect1[city]</option>"."<BR>";} 
else 
{echo "<option value='$sect1[city]'>$sect1[city]</option>";}}echo "</select>"; 

//TYPE 
echo "<select name='typeS' onchange=\"reload(this.form)\"><option value=''>Select one</option>"; 
while($sect2 = mysql_fetch_array($quer2)) 
{if($sect2['type']==@$selection2) 
{echo "<option selected value='$sect2[type]'>$sect2[type]</option>"."<BR>";} 
else 
{echo "<option value='$sect2[type]'>$sect2[type]</option>";}} 
echo "</select>"; 

//GENRE 
echo "<select name='genreS' onchange=\"reload(this.form)\"><option value=''>Select one</option>"; 
while($sect3 = mysql_fetch_array($quer3)) 
{if($sect3['genre']==@$selection3) 
{echo "<option selected value='$sect3[genre]'>$sect3[genre]</option>"."<BR>";} 
else 
{echo "<option value='$sect3[genre]'>$sect3[genre]</option>";}} 
echo "</select>"; 

//STATUS 
echo "<select name='statusS' onchange=\"reload(this.form)\"><option value=''>Select one</option>"; 
while($sect4 = mysql_fetch_array($quer4)) 
{if($sect4['status']==@$selection4) 
{echo "<option selected value='$sect4[status]'>$sect4[status]</option>"."<BR>";} 
else 
{echo "<option value='$sect4[status]'>$sect4[status]</option>";}} 
echo "</select>"; 

// add your other form fields here //// 
echo "<input type=submit value=Submit>"; 
echo"<input type=button onClick=\"parent.location='search.php'\" value=Reset>"; 
echo "</form>"; 

?>

 

I just wondered if there is a way to get this to work the way I want, or if anyone had a better solution to getting around this.

[mrman23]

Just in case I didn't explain what I couldn't achieve well enough...

 

Each row within my database has a different City, Type, Genre and Status combination - thus picking the first City (generated from the database) presents you with all the Types within the database that have the City listed as well as all the Genres and Status's from within that group, e.g.

 

London - Band - Rock - Gigging

Birmingham - Band - Pop - Gigging

Manchester - Solo - Rock - Recording

Birmingham - Solo - Metal - Giggins

 

Thus if you select Birmingham from the list it brings up Band and Solo within Type, Pop and Metal within Genre and just Gigging within Status - then you select Band from the Type menu and it shows Pop within the Genre menu and Gigging in Status.

 

This all works fine at present, but what I want is for when someone visits the site for the first time and the menu system is fresh with no selected input that they can - instead of starting from the City selection, choose to start from the Genre or Type field first, i.e.

 

CITY - TYPE - GENRE - STATUS

 

----- - BAND:

 

- the rest (GENRE & STATUS) display all the bands and not the Solo artists within the database that match the 'Band' criteria. That way a search can be done using any criteria and no rely on a user selecting all 4 fields.

 

I hope the way I've explained it makes sense!

[Barand]

Just start off with all menus containing all options

[pre]

city                    type                genre            status

  London                  Band                Rock              Gigging

  Birmingham              Solo                Pop              Recording

  Manchester                                  Metal

[mrman23]

All menus do start with all data listed within them - all based upon DISTINCT data. The problem is that when you select any options, aside from starting from the first category CITY you cannot start selecting from another category, e.g.

 

Upon loading the menus are as such:

 

CITYTYPEGENRESTATUS

BirminghamBandRockGigging

BirminghamSoloMetalGigging

LondonBandMetalRecording

 

So when you select Birmingham in the first menu it displays:

 

CITYTYPEGENRESTATUS

BirminghamBandRockGigging

[/td]SoloMetalGigging

 

What I also want it to be able to do is that if you select the TYPE option first that it changes what is displayed in the GENRE and STATUS sections:

 

CITYTYPEGENRESTATUS

BandRockGigging

MetalRecording

 

I hope that 'sort of' explains what I want!?

[mrman23]

From a different angle... has anyone got an alternative method for generating an SQL driven PHP menu system that will allow you to search the SQL database as well?

[Barand]

does the ajax link in my sig help?