Dynamic array

[aussiewarrior]

Hello
I have an array that looks like this:

$cities = array(
1 => array('Delhi','Mumbai','Kolkata','Bangalore','Hyderabad','Pu ne','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.

[TehGuy]

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:

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:

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:

$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.

[aussiewarrior]

can you please simplfy it

thanks

[TehGuy]

Given a 2-field table called statesCities, with fields stateID and cityName

//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.