How to get (n) fields with one ID ?

web_designer_iq

hi,
for example:
I have 2 table with the following information:
table1
1) name
2) countries

table2
1) country_id
2) country_name

You can select your favorites country from table2 and save the countries by the following format:
1,2,3,4

for example:
1 = iraq
2 = usa
3 = germany
4 = canada

I want to get (all) your favorites countries by one query.
i use the following code and i don't have any problem, but it's running (n) query.

$db_connect->start_mysql_connection();
$DB->exe_query("SELECT countries FROM favorites_countries WHERE my_favorites_countries = 1");
$s1 = mysql_result($DB->query_result, 0,0);
$s2 = array($s1);
$i = 0;

foreach ($s2 as $c){
   $aa = explode(',', $c);

   for ($i == 0; $i <= count($aa); $i++){
      $DB->exe_query("SELECT * FROM countries_table WHERE country_id = $aa[$i]");
      while($row = @mysql_fetch_array($DB->query_result)){
         echo $row['country_name']." - ";
      } // while

   }
}

thank you!

web_designer_iq

Note: all countries saved in countries_table

Roger_Ramjet

I don't really follow your question but I can guess what you are after.

JOINS

jdorsch

This is why you should never store a list of keys in a comma delimited list in one field. You should go back and redesign your database structure, its going to save you a lot of headaches down the road.

You might be able to use JOIN tbl1 ON tbl2 WHERE tbl2.id IN (tbl1.idlist) but I have never tried that.

gmac63

web.designer.iq wrote:
hi,
for example:
I have 2 table with the following information:
table1
1) name
2) countries

table2
1) country_id
2) country_name

You can select your favorites country from table2 and save the countries by the following format:
1,2,3,4

for example:
1 = iraq
2 = usa
3 = germany
4 = canada

I want to get (all) your favorites countries by one query.
i use the following code and i don't have any problem, but it's running (n) query.

thank you!

Dude, I see what you want, but the easiest way is to insert each name/country individually into table 1 along with the name:

Then do a query:

select name, countries where name = "bob";

should return:
name | countries
bob | germany
bob | usa
bob | canada

This would be the easiest. Not the most correct, but each record would be unique.

gmac63

You would probably want to use the country_id:

table1
name | countries
bob | 3
sam | 2
bob | 2
bob | 4
sam | 1

Then do a join. Something like:

select table1.name, table2.country_name from table1,table2 where table1.country_id = table2.country_id and table1.name = "bob";

Tried it, it works well.

Piranha

You can use the foreach loop to add things in your sql simular to the way shown below. Note that I haven't tested it, so it might not work without debugging.

$db_connect->start_mysql_connection();
$DB->exe_query("SELECT countries FROM favorites_countries WHERE my_favorites_countries = 1");
$s1 = mysql_result($DB->query_result, 0,0);
$s2 = array($s1);
$i = 0;

foreach ($s2 as $c){
   $aa = explode(',', $c);

   for ($i == 0; $i <= count($aa); $i++){
      if ($i == 0) { // If it is the first result you should start the sql-statement
          $sql = "SELECT * FROM countries_table WHERE country_id = $aa[$i]";
      }
      else // if it is another result you should add the new result in the sql-statement
      {
          $sql = sql . "OR country_id = $aa[$i]";
      }

  $DB->exe_query($sql);
  while($row = @mysql_fetch_array($DB->query_result)){
     echo $row['country_name']." - ";
  } // while

   }
}

But I don't understand why you use both a foreach-loop and a for-loop. One of them should be enough, and the easiest way would probably be to use the foreach loop on $s1.

Edit: With this way of doing things you get 2 sql-statements. It would be better, as stated in posts above, to join tables and get only one statement. In some cases however you need to do it the way shown above, but that is normally when you get things from the website with POST or GET methods.