Page 1 of 1

problem comparing two queries

Posted: Fri Sep 17, 2004 12:45 am
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? :?:

Posted: Fri Sep 17, 2004 12:48 am
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.

Posted: Fri Sep 17, 2004 2:18 am
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"; 
   }
}

Posted: Fri Sep 17, 2004 3:12 am
by feyd
I was suggesting saving all the actual results of the second query into an array. Not running the query 6 times.

Posted: Fri Sep 17, 2004 3:36 am
by nacho_c
could you explain it with code?

Posted: Fri Sep 17, 2004 3:51 am
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.

Posted: Fri Sep 17, 2004 5:24 am
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";

}