problem comparing two queries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nacho_c
Forum Newbie
Posts: 11
Joined: Tue Sep 14, 2004 2:56 am

problem comparing two queries

Post by nacho_c »

Hi guys,

I´m trying to find some help to this problem

I have two queries the first one ($query_u) gets all the languages stored in a batabase table called language.

The second ($query_u2) gets all the selected languages that are stored to a table called language_level.

So I want to print a form by which the user can edit his previously inserted language values. There are 6 lists of languages and all have different names (language_1, language_2...language_6). So what I want is that if the user has selected 3 languages of the 6, I want to pront the first 3 lists with the selected languages listed as a "SELECTED" option.

Here is the code I use:

Code: Select all

for($i = 1; $i <= 6; $i++)
  {
   $query_u = "SELECT id, name FROM language ORDER BY name ASC";
   $result_u = mysql_query($query_u) or die ("Error in query: $query_u. " .mysql_error());

   $query_u2 = "SELECT language_id FROM language_level WHERE user_id ='$row_b->id'";
   $result_u2 = mysql_query($query_u2) or die ("Error in query: $query_u2. " .mysql_error());     
   
   $selectoptions = "";
   while ($row_u = mysql_fetch_object($result_u))
   { 
      $selectoptions .= "<option value="$row_u->id" ";
	  while($selection = mysql_fetch_object($result_u2))
	  { 
      if($selection == "$row_u->id")
	  { 
         $selectoptions .= "selected"; 
      }
	  } 
      $selectoptions .=">$row_u->name</option>\n"; 
   }
}
With this code I only getting one of the 3 selected languages, which is actually the first in the language_level table. I see that the $selection variable is only containing one of the values, and it has to contain 3 actually.

What is wrong in the code? How can I fix it? :?:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

either rewrite the queries into a single query, or you need to store off all of the secondary query, so you can access it throughout the first query.
nacho_c
Forum Newbie
Posts: 11
Joined: Tue Sep 14, 2004 2:56 am

Post by nacho_c »

Do you mean this?

Code: Select all

$query_u2 = "SELECT language_id FROM language_level WHERE user_id ='$row_b->id'";
$result_u2 = mysql_query($query_u2) or die ("Error in query: $query_u2. " .mysql_error());     



for($i = 1; $i <= 6; $i++)
  {
   $query_u = "SELECT id, name FROM language ORDER BY name ASC";
   $result_u = mysql_query($query_u) or die ("Error in query: $query_u. " .mysql_error());

     
   $selectoptions = "";
   while ($row_u = mysql_fetch_object($result_u))
   { 
      $selectoptions .= "<option value="$row_u->id" ";
	  while($selection = mysql_fetch_object($result_u2))
	  { 
      if($selection == "$row_u->id")
	  { 
         $selectoptions .= "selected"; 
      }
	  } 
      $selectoptions .=">$row_u->name</option>\n"; 
   }
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I was suggesting saving all the actual results of the second query into an array. Not running the query 6 times.
nacho_c
Forum Newbie
Posts: 11
Joined: Tue Sep 14, 2004 2:56 am

Post by nacho_c »

could you explain it with code?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I will not explain it with detailed code. The two options are:

Code: Select all

run a query that uses a join to combine the original queries into one.

loop through the results, outputting user selected options along the way.
or

Code: Select all

run a query against the user's selected list.
use a loop to save all their choices into an array.
free the result.

run another query for all the languages.
loop through the languages, checking against the stored array of already selected languages so you don't waste mysql's memory and time.
nacho_c
Forum Newbie
Posts: 11
Joined: Tue Sep 14, 2004 2:56 am

Post by nacho_c »

Why this doesn´t work??




for($i = 1; $i <= 6; $i++)
{
$query_u = "SELECT id, name FROM language ORDER BY name ASC";
$result_u = mysql_query($query_u) or die ("Error in query: $query_u. " .mysql_error());

$query_u2 = "SELECT language_id FROM language_level WHERE user_id ='$row_b->id'";
$result_u2 = mysql_query($query_u2) or die ("Error in query: $query_u2. " .mysql_error());

$selectoptions = "";
while ($row_u = mysql_fetch_object($result_u))
{
$selectoptions .= "<option value=\"$row_u->id\" ";
while ($row_u2 = mysql_fetch_assoc($result_u2))
{
$selection = $row_u2['language_id'];
if($selection == "$row_u->id")
{
$selectoptions .= "selected";
}
}
$selectoptions .=">$row_u->name</option>\n";

}
Post Reply