0

I have a script that has a list of country ID codes as $item_details[countries] which are coma delimited.

In order to display the names of the countries (stored in mySQL), it needs to currently run each loop to get the country name and then display it.

To prevent the script from connecting to the mySQL for each and every single name, could there be an easier way of getting all the names at once and then displaying them in a loop afterwards, so that there is only 1 mySQL query?

Such as:

$all_country_names = $db->get_sql_field("SELECT name,id FROM db_countries");

Here is what I have now:

// $item_details['countries'] are the coma delimited country IDs

// Changing the coma delimited IDs into an array
  $array = explode(',', $item_details['countries']);

// Running the loop
  foreach($array as $key=>$value){

// Getting each country name from the database based on the ID
  $country_name = $db->get_sql_field("SELECT name FROM db_countries WHERE id='".$value."'");    

// Displaying each country name
  echo $country_name;
  echo ", ";
}

Thank you in advance for any help :)

user1227914
  • 3,446
  • 10
  • 42
  • 76
  • Coul dyou use the foreach loop to create one long mysql query and then run that query once? – Mark Feb 05 '14 at 11:26
  • Maybe the answer to this question will help you http://stackoverflow.com/questions/1307618/multiple-mysql-insert-statements-in-one-query-php – Mark Feb 05 '14 at 11:28
  • 1
    They're delimited by extended periods of unconsciousness? – Strawberry Feb 05 '14 at 11:29

4 Answers4

0

First of all: Your script doesn't reconnect on each statement.. it just makes a new query! You can do it like this:

$country_names = $db->get_sql_field("SELECT name FROM db_countries WHERE id IN(" .explode(',', $item_details['countries']) .")";

Reference for the IN Clause: http://www.tutorialspoint.com/mysql/mysql-in-clause.htm

Yami
  • 1,405
  • 1
  • 11
  • 16
0

yes you can use IN clause like this

$in_text = implode(",", $array)
 $country_name = $db->get_sql_field("SELECT name FROM db_countries WHERE id IN($in_text)");
Satish Sharma
  • 9,547
  • 6
  • 29
  • 51
0

You can put your IDs on an array and later do:

$all_country_names = $db->get_sql_field("SELECT name,id FROM db_countries WHERE id IN(" . join(",", $idArray) . ")");
Henrique Barcelos
  • 7,670
  • 1
  • 41
  • 66
0

updated sql here

 $sql = "SELECT name FROM db_countries WHERE id IN(" .$item_details['countries']. ")";
Jignesh Patel
  • 1,028
  • 6
  • 10