generating drop down menu's based on database

Question

blahbla 1 Newbie Poster

12 Years Ago

hi i want to be able to generate drop down menu's based on the database information

these are the table information :

+--------+------------------+------------+
|  tid   | tname            | sport      |
+--------+------------------+------------+
|  10000 | Boston Celtics   | Basketball |
|  10001 | New Jersey Nets  | Basketball |
+--------+------------------+------------+

+-----------+-----------------+--------+
|    aid    | aname           |  tid   |
+-----------+-----------------+--------+
|   20000   | Ray Allen       |  10000 |
|   20001   | Carlos Arroyo   |  10000 |
|   20002   | Jordan Farmar   |  10001 |
|   20003   | Brook Lopez     |  10001 |
+-----------+-----------------+--------+

i need to build the drop down menu's from the database so if i add,delete, or update something in the tables the drop down menu's will still populate.

the teams will be one separate menu and the athlete will be another group
so i will have to build 3 menus from these tables
one for team , and 2 for athlete right?

and of course when i select boston celtics, only the athletes from boston celtics will show up
so i will need to hide and disable all other menus or something

and i have another set of radio buttons to select which sport

i'm stuck on how to combine? these codes to make it work
thats what i think i need to do

<html>
....
Select Team:
<select id='select_team1' name='team1' onchange='teams1()' style='display:none'>
	<option selected='selected' value='0'>Select a Team</option>
	<?php
	$query = mysql_query("SELECT * FROM teamdb where sports='Basketball'");
	while($row = mysql_fetch_array($query))
	{
	echo '<option value='.$row['teamid'].'>'.$row['teamname'].'</option>';
	}
	?>
</select>
....
</html>

<html>
....
Select Player:
<select id='select_player' name='player1' style='display:none'>
	<option selected='selected' value='0'>Select a Player</option>
	<?php
	$query = mysql_query("SELECT * FROM athletedb where teamid='10000'");
	while($row = mysql_fetch_array($query))
	{
	echo '<option value='.$row['athleteid'].'>'.$row['athletename'].'</option>';
	}
	?>
</select>
....
</html>

thanks in advance for any help.
an additional question:
this will be used in a form so how will it affect the php thats receiving these values?

php

4 Contributors
10 Replies
158 Views
2 Days Discussion Span
Latest Post 12 Years Ago Latest Post by blahbla

simplypixie 123 Posting Pro in Training

12 Years Ago

You column references in your queries and your result rows don't match the database structure you are showing.

According to your table structure, it should be

$query = mysql_query("SELECT * FROM teamdb where sport='Basketball'");
	while($row = mysql_fetch_array($query))
	{
	echo '<option value='.$row['tid'].'>'.$row['tname'].'</option>';
	}

and

$query = mysql_query("SELECT * FROM athletedb where tid='10000'");
	while($row = mysql_fetch_array($query))
	{
	echo '<option value='.$row['aid'].'>'.$row['aname'].'</option>';
	}

You don't actually say if you are having a problem displaying your dat and if so what the problem is so further information would be useful and also your javascript for your onclick event.

simplypixie 123 Posting Pro in Training

12 Years Ago

Sorry I can't quite work out what you are trying to do here - if it is a javascript issue then you may need to repost in that forum.

diafol

12 Years Ago

Ok I get you I think. You never duplicate code, ever.

So you've created the option dropdown in the form:

<form name="playerform" action="somepage.php" method="post">
 <select name="player">
   <option value="10005">John Geronimo</option>
   ...
 </select>
</form>

Your js code will submit the form when you choose the value from the dropdown.

The code in the somepage.php file should look like this:

if(isset($_POST['player'])){
  $player = intval($_POST['player']); 
  $result = mysql_query("SELECT ... FROM players WHERE player_id='$player'");
  ...
}

Edited 12 Years Ago by diafol because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

blahbla 1 Newbie Poster · Answer 1 · 2011-11-29T00:30:38+00:00

ah sorry i changed the names of the tables on here just so its shorter to show or whatever.

theres nothing wrong with displaying the data with those codes. its displayed correctly.

the problem is lets say i delete one of the athletes or team
with the current code i have
i will have to go back and change the code and update it, in order to represent the data properly.

i want help with writing the code (or just pointing me in the right direction) so that, once i have the code no matter what i do to the tables (add, delete, update stuff), i will not have to go back to update the code that generates the menus.

$query = mysql_query("SELECT * FROM teamdb where sport='Basketball'");
while($row = mysql_fetch_array($query))
{
echo '<option value='.$row['tid'].'>'.$row['tname'].'</option>';
}

because in the code now, i am specifying where to in the tables to look for the data.
right now in order for me to create the drop down menus for all the athletes, i am duplicating this code with the only difference is the where tid = ' ' field.

$query = mysql_query("SELECT * FROM athletedb where tid='10000'");
while($row = mysql_fetch_array($query))
{
echo '<option value='.$row['aid'].'>'.$row['aname'].'</option>';
}

the code right now, if i delete team 10000, then the code will be useless and takes up space.

major thing i want help in is how can i, i dunno, combine? them so i wont have duplicates and possibly useless code laying around.

umm do you understand what i'm trying to do?

umm i dont really remember the javascript code and i cant access it now
but its something like

team1() /// this name might be misleading, maybe it should be sport1 instead cause its for all basketball teams
{
  if(tid == 10000){   ////then this would be if team1 for basketball is selected
     enable player1 menu   /////then player1 for team1 will show up
     show player1 menu
////and all other team players will be hidden and disabled so it wont transfer data to the .php thats receiving the information in this form
     disable player2 menu  
     hide player2 menu
  }
  if(tid == 10001)
  {
     .....
     enable - show player2
     disable - hide player1
  }
}
//i have another sport baseball then it will duplicate the above code with some parts changed.

//i'm not really familiar with javascript so it became like that

sorry about that mess

thanks again for any help.

blahbla 1 Newbie Poster · Answer 2 · 2011-11-29T03:41:19+00:00

ok umm forget the javascript code

right now if the tid 10000 doesnt exist then this code is useless right?

$query = mysql_query("SELECT * FROM athletedb where tid='10000'");
    while($row = mysql_fetch_array($query))
    {
    echo '<option value='.$row['aid'].'>'.$row['aname'].'</option>';
    }

you can say i want to be able to generate the drop down menus but not know what is in the tables.
i just know what columns are in the tables.
in the team table there are 3 columns , tid, tname, and sport
in the athlete table there are 3 columns , aid, aname, and tid

how do i write the code so that i will be able to generate the drop down menus?
does this help understanding what i want to do?

diafol · Answer 3 · 2011-11-29T03:46:27+00:00

the whole point is that you never hardcode the tid - it will be taken from another form control. ARe you looking to link two dropdowns via ajax? so as you choose a team from one dd the next dd will show all the athletes in that team?

moneeshot 0 Newbie Poster · Answer 4 · 2011-11-30T09:52:02+00:00

ardav's first code snip is what you're looking for

if(isset($_POST['tid'])){
  $tid = intval($_POST['tid']); 
  $result = mysql_query("SELECT * FROM players WHERE tid='$tid'");

  echo '<select name="players">';
  
  while($row = mysql_fetch_array($result)) {
         echo '<option value='.$row['aid'].'>'.$row['aname'].'</option>';
  }

  echo '</select>';

}

if the tid isset then the select box will show or print, if not then it will not show up until the user has selected a team; and no players will be in the select box if they were removed or deleted, updated...
I hope you're code is a little cleaner then what we are looking at here. all your PHP should be in one block and instead of echo save the results as a variable then echo the variable where you want the select box to print to the screen.

$players = NULL;

if ($result) {
 $players = '<select name="players">';
  
  while($row = mysql_fetch_array($result)) {
         $players .= '<option value='.$row['aid'].'>'.$row['aname'].'</option>';
  }

 $players .= '</select>';
}

// in your html
<?php
if (isset($players) && $players !== '') {
   print $players;
}
?>

blahbla 1 Newbie Poster · Answer 5 · 2011-11-30T10:46:50+00:00

no, i dont even know if you guys are answering my questions anymore.

i am trying to create the form, the form is not created already.
i am not having a problem displaying the stuff.

i cant use this

isset($_POST['tid']

because i am creating the form

i dunno i could be wrong where does this isset($_POST get the tid from if i am creating the form

moneeshot 0 Newbie Poster · Answer 6 · 2011-11-30T11:47:32+00:00

excuse me, I thought it was just db values you where trying to display based on a certain action from the user, not that an entire form was needed. if so I could get a simple form together for, if you still need it

blahbla 1 Newbie Poster · Answer 7 · 2011-11-30T11:54:32+00:00

forget it, your saying its a simple form so you dont understand what i'm trying to do. if i'm understanding myself correctly then i'm trying to write code that will basically work for every single table out there. its driving me crazy so i'm dropping it
thanks anyway for trying to help.