• Howdy! Welcome to our community of more than 130.000 members devoted to web hosting. This is a great place to get special offers from web hosts and post your own requests or ads. To start posting sign up here. Cheers! /Peo, FreeWebSpace.net
managed wordpress hosting

Dynamic array

aussiewarrior

New Member
Hello
I have an array that looks like this:

$cities = array(
1 => array('Delhi','Mumbai','Kolkata','Bangalore','Hyderabad','Pune','Chennai','Jaipur','Goa'),
2 => array('Beijing','Chengdu','Lhasa','Macau','Shanghai'),
3 => array('Los Angeles','New York','Dallas','Boston','Seattle','Washington','Las Vegas'),
4 => array('Birmingham','Bradford','Cambridge','Derby','Lincoln','Liverpool','Manchester')
);

I want to know if somebody can help me so these details are pulled out of a mysql database.
the table in the database has to fields. Country value and State Name

I want it to read from the database so everytime i update the database i don't want to have to update the array manually.

Many Thanks in advance



Your help would be much appreciated.
 
Depends on if you want to pull all the data at once, or if you want to pull it for just for a country.

Ideally you'd have 2 tables:

countries
  • ID
  • name

cities
  • ID
  • name
  • countryID (foreign key, referencing ID from the countries table)

Yours is pretty close to the second table, minus the ID field, which isn't required, since you could use a primary key of (name, countryID) instead if you wanted to.

I'm assuming you already know how to connect to a MySQL database (mysql_connect and mysql_select_db for the basics, though keep in mind these "mysql_*" functions are essentially deprecated at this point; they're good for learning, but not so great for production sites):


To pull out all the cities from one country, given a country's ID:
PHP:
function getCities($countryID){
  $cities = Array();
  $result = mysql_query("SELECT name FROM cities WHERE countryID = '$countryID';");
  while($row = mysql_fetch_assoc($result)){
    $cities[] = $row['name'];
  }
  return $country;
}

To pull out all the countries in your database, you'd do:
PHP:
function getCountries(){
  $countries = Array();
  $result = mysql_query("SELECT ID, name FROM countries");
  while($row = mysql_fetch_assoc($result)){
    $counties[$row['ID']]['name'] = $row['name']; //This line pulls out the country name
    $countries[$row['ID']['cities'] = getCities($row['ID']);  //This line pulls out all the cities in that country
  }
  return $countries;
}

Then you'd just do:

PHP:
$countries = getCountries();

And you'll have your array filled with country IDs, names, and cities for each country. The array is a little different from yours, but you can modify it easily enough to return what you want.

It's not the most efficient way to do it, but it's a pretty generic way that demonstrates some basic mysql functions. If this is too complicated let me know, and I can simplify it a little to just fit your problem.
 
Given a 2-field table called statesCities, with fields stateID and cityName

PHP:
//This function will give back an array of cities that are in the given stateID
function getCities($stateID){ 
  $cities = Array(); 
  $result = mysql_query("SELECT cityName FROM statesCities WHERE stateID = '$stateID';"); 
  while($row = mysql_fetch_assoc($result)){ 
    $cities[] = $row['name']; 
  } 
  return $cities; 
} 

//This function will get each stateID and put them into an array, then use the previous function to get the array of cities in each stateID
function getCountries(){ 
  $states = Array(); 
  $result = mysql_query("SELECT stateID FROM statesCities"); //This gets the list of all the stateIDs
  while($row = mysql_fetch_assoc($result)){ //This loops through each stateID in the list you just got
    $states[$row[state'ID'] = getCities($row['stateID']);  //This line pulls out all the cities in that country 
  } 
  return $states; 
}  



//Connect to MySQL
$con = mysql_connect("host", "admin", "password");
//Pick which database you're using
mysql_select_db("yourDB");

//Then, to have a usable array with each stateID and all the cities for each stateID:
$cities = getCountries();

//Close the MySQL connection
mysql_close($con);

I'm assuming you know what you're doing with the array once you have it.
 
Back
Top