Select dropdown with two Sql query

PHP
1

Hi,

I have some difficulty with my ‘select’

In my select/menu, I display all the options come from a table (table_db_email) in my database

In this table (tb_code_prmtn11_email) I have two field :
fld_email_id
fld_name_email

It works here is a code

<select name="email_adress_menu" id="email_adress_menu"  class="valid"  onchange="submit()">
        <?php
			
            echo "<option selected=\\"selected\\" value=''>Choose your name</option>"; 
           

			$req_email_adress_menu =   " select DISTINCT id_email, fld_name_email, fld_adresse_email FROM $table_db_email ORDER BY fld_name_email ";
			          
			$rep_email_adress_menu =  mysql_query($req_email_adress_menu, $cnx) or die( mysql_error() ) ;
			
			
			
            while($show_email_adress_menu = mysql_fetch_assoc($rep_email_adress_menu)) {
               
				
				echo '<option value="'.$show_email_adress_menu['id_email'].'"';
									//if($primes==$show_email_adress_menu['fld_name_email']){echo " selected";} //display to select an option!!!!!!!!!!!!!!!!
									echo '>'.$show_email_adress_menu['fld_name_email'].'  - '.$show_email_adress_menu['fld_adresse_email'].'</option>';
									
									
							
				

            }
        ?>
      </select>

I have some other information come from another table : tb_code_prmtn11 (containing information about people)
I have a few fields :
id_resultat
fld_name
fld_email_id ( FOREIGN KEY (fld_email_id) REFERENCES tb_code_prmtn11_email (id_email) ON DELETE NO ACTION ON UPDATE CASCADE;)
…

I want to put this menu on another Web page and this select must display all of the options as below( with) BY SELECTING THE OPTION THAT MATCHES THE INFORMATION FOUND IN THE SECOND TABLE : tb_code_prmtn11

How can I do this ?

I know how I can make to my request :

here is a code MySql :

ELECT td.id_resultat,td.fld_email_id,email.fld_name_email
FROM $table_db td 
									   
INNER JOIN $table_db_email email
ON td.fld_email_id = email.id_email 
			
WHERE td.id_resultat=$id

This code works also but I don’t know how I can include this second query in my menu… Can you help me please

2

Leave inner joins and use nested mysql_query() calls… Feed the data from the resultset of first query to create the second query and execute it while the resultset of first query loops…
I think this is performance-savvy way!!!

3

Hi techbongo,
Many Thanks for your idea…:wink:

nested mysql_query() ? what it means ? I must use two " while" loops ? :rolleyes:

How can I ? :confused:

I tried twice with the following codes and I was always the same error message and My select doesn’t work:

first one :

<select name="email_adress_menu" id="email_adress_menu"  class="valid"  onchange="submit()">
<?php
  echo "<option selected=\\"selected\\" value=''>Choose your name</option>"; 
  $req_email_adress_menu =   "SELECT DISTINCT id_email, fld_name_email, fld_adresse_email FROM $table_db_email ORDER BY fld_name_email ";
  $rep_email_adress_menu =  mysql_query($req_email_adress_menu, $cnx) or die( mysql_error() ) ;

  while($show_email_adress_menu = mysql_fetch_assoc($rep_email_adress_menu)) {
    echo '<option value="'.$show_email_adress_menu['id_email'].'"';


   $req_email_adress_menu_for_selected =   "SELECT fld_email_id FROM $table_db ";
   $rep_email_adress_menu_for_selected =  mysql_query($req_email_adress_menu_for_selected, $cnx) or die( mysql_error() ) ;
	   while($show_email_adress_menu_for_selected = mysql_fetch_assoc($rep_email_adress_menu_for_selected)) 
	   {
		if($emailselected==$show_email_adress_menu['fld_name_email']){echo " selected";} //display to select an option!!!!!!!!!!!!!!!!
		}
    echo '>'.$show_email_adress_menu['fld_name_email'].'  - '.$show_email_adress_menu['fld_adresse_email'].'</option>';
  }
?>
</select>

and here is its error message : Unknown column ‘fld_name_email’ in ‘field list’

and second one :

<select name="email_adress_menu" id="email_adress_menu"  class="valid"  onchange="submit()">
<?php
  echo "<option selected=\\"selected\\" value=''>Choose your name</option>"; 
  $req_email_adress_menu =   "SELECT DISTINCT id_email, fld_name_email, fld_adresse_email FROM $table_db_email ORDER BY fld_name_email ";
  $rep_email_adress_menu =  mysql_query($req_email_adress_menu, $cnx) or die( mysql_error() ) ;

  while($show_email_adress_menu = mysql_fetch_assoc($rep_email_adress_menu)) {
    echo '<option value="'.$show_email_adress_menu['id_email'].'"';


   $req_email_adress_menu_for_selected =   "SELECT td.id_resultat,td.fld_email_id,email.fld_name_email 
  FROM $table_db td
  JOIN $table_db_email email ON td.fld_email_id = email.id_email 
 WHERE td.id_resultat = $id  ";
   $rep_email_adress_menu_for_selected =  mysql_query($req_email_adress_menu_for_selected, $cnx) or die( mysql_error() ) ;
	   while($show_email_adress_menu_for_selected = mysql_fetch_assoc($rep_email_adress_menu_for_selected)) 
	   {
		if($emailselected==$show_email_adress_menu['fld_name_email']){echo " selected";} //display to select an option!!!!!!!!!!!!!!!!
		}
    echo '>'.$show_email_adress_menu['fld_name_email'].'  - '.$show_email_adress_menu['fld_adresse_email'].'</option>';
  }
?>
</select>

and same error message :[I] <select name=“email_adress_menu” id=“email_adress_menu” class=“valid” onchange=“submit()”>

<option selected=“selected” value=‘’>Choose your name</option>Unknown column ‘fld_name_email’ in ‘field list’[/I]:fire:

So What I must do ?:confused:

4

In the query you’re using a variable called $table_db_email echo the content of
 $req_email_adress_menu which contains the query to make sure that your using the value that your’re expecting to use. Also if $table_db_email is a string you should place ’ around it.

5

Ok thanks SpacePhoenix,

I made some mistake…

I made some mistake… Now, my menu works : It display all of the option : with this following code :

<select name="email_menu" id="email_menu" onchange="submit()">
        <?php
			
            echo "<option selected=\\"selected\\" value=''>Choose your name</option>"; 
           

			$req_email_menu =   " select DISTINCT id_email, fld_nom_email, fld_adresse_email FROM $table_db_email ORDER BY fld_nom_email ";
			          
			$rep_email_menu =  mysql_query($req_email_menu, $cnx) or die( mysql_error() ) ;
			
			
			
            while($show_contenu_email_menu = mysql_fetch_assoc($rep_email_menu)) {
               
				
				echo '<option value="'.$show_contenu_email_menu['id_email'].'"';
				
				
				

						
						//if($primes==$show_contenu_email_menu['fld_nom_email']){echo " selected";} // pour afficher la selectionne
									echo '>'.$show_contenu_email_menu['fld_nom_email'].'  - '.$show_contenu_email_menu['fld_adresse_email'].'</option>';
									
									
							
				

            }
        ?>
      </select>

I get the following code in FireFox

<select name="email_menu" id="email_menu" onchange="submit()">

        <option selected="selected" value=''>Choose your name</option><option value="tomo">TOTO MONO  - toto.mono@test.com</option><option value="kito">KIKI TOTO - kiki.toto@test.com</option></select>

Now I want display option selected with other option

Here is my new code :

<select name="email_menu" id="email_menu" onchange="submit()">
        <?php
			
            echo "<option selected=\\"selected\\" value=''>Choose your name</option>"; 
           

			$req_email_menu =   " select DISTINCT id_email, fld_nom_email, fld_adresse_email FROM $table_db_email ORDER BY fld_nom_email ";
			          
			$rep_email_menu =  mysql_query($req_email_menu, $cnx) or die( mysql_error() ) ;
			
			
			
            while($show_contenu_email_menu = mysql_fetch_assoc($rep_email_menu)) {
               
				
				echo '<option value="'.$show_contenu_email_menu['id_email'].'"';
				
				
				
////////***************************************** to display/show item selected
				
				$req_email_adress_menu_for_selected =   "SELECT td.id_resultat,td.fld_email_id,email.fld_nom_email
									   FROM $table_db td 
									   
									   INNER JOIN $table_db_email email
									   ON td.fld_email_id = email.id_email 
			
			                           WHERE td.id_resultat=$id ";
			   $rep_email_adress_menu_for_selected =  mysql_query($req_email_adress_menu_for_selected, $cnx) or die( mysql_error() ) ;
				   while($show_email_adress_menu_for_selected = mysql_fetch_assoc($rep_email_adress_menu_for_selected)) 
				   {
					if($emailselected==$show_email_adress_menu['fld_email_id']){echo " selected";} //display to select an option!!!!!!!!!!!!!!!!
					}
				
/////--------------------------------
						//if($primes==$show_contenu_email_menu['fld_nom_email']){echo " selected";} // pour afficher la selectionne
									echo '>'.$show_contenu_email_menu['fld_nom_email'].'  - '.$show_contenu_email_menu['fld_adresse_email'].'</option>';
									
									
							
				

            }
        ?>
      </select>

My menu works but all options are selected, however, in reality, there is an option that is selected because in my database, there is one in each but all options are selected, however, in reality, there is an option that is selected because in my database, there is one in each recording

I get the following code in FireFox

<select name="email_menu" id="email_menu" onchange="submit()">

        <option selected="selected" value=''>Choose your name</option><option value="tomo"  selected>TOTO MONO  - toto.mono@test.com</option><option value="kito"  selected>KIKI TOTO - kiki.toto@test.com</option></select>

How can I display the correct option selected