Best way to retrieve data from a mysql database

hi everyone

I have a database that collects the nationality of its users. However, rather than insert the full country name, it inserts into the mysqly database a keycode for each country . (i.e the first 3 digits of the country name. )

This worked perfectly with regard to insertion. however, i am not now sure of the best way to retreive this information and convert it back to the full country name.

I am sorry if this explanation might sound confusing, it might be best to show you what i have done.




         //array function that creates the key code 

	function full_country_list() {
	     
		$selection = array('AF' => 'Afghanistan' , 'AL'  => 'Albania', 'AG'  => 'Algeria'..etc   );  
		
                 return $selection; 
		
	}


// THE FORM
     
	
    <form action= "index.php?view=reg"      method="post" name="form1" >

//returns the array of the key code to Selection
 
$selection = full_country_list();


         
     	<select name="country_loc" id ="country_loc"  	 />';  
	


	
	foreach ($selection as $key => $value){
	

	echo '<option value="' . $key .'" selected>' . $value . '</option>';
	}else{
	echo '<option value="' . $key .'">' . $value . '</option>';
	}
	
	
	

	 echo'</select>'; 
	
	     
</form>


//  the data is subsquently sent to teh database as a keycode 


//The retrival 

$q = "SELECT   country_loc,   FROM users; 





the problem now is that the information retrieved is the keycode. i however want the data to instanly convert to the full country name.

i suspect that the best away around this is to attach a function to the ‘country_loc’ so it is automoatically converted as soon as it leave the database. but i am not sure how to go about doing this. i will potentially have millions of these convertions being made at the same time. so time is of the essence when doing the convertion.

can anybody please help me with advice/code on how to accomplish this.

thank you so much for your kind attention.

warm regards

Andreea

Having your countries array in a function seems rather pointless.

This is how you’d access countries in the array if your database stores only the key:


$countries = array('AF' => 'Afghanistan' , 'AL'  => 'Albania', 'AG'  => 'Algeria'..etc   );  

$user_country = "AL";

echo 'this user lives in ' . $countries[$user_country];

If speed and simplicity are concerns – and unless you actually use the key somewhere else – why not simply store the country name in the database and do away with this key/value store?

hello Cups

thank you so much for your reply.

in responce to your comments regarding the key/value store. i am still quite new to mysql and PHP. i guess taht i adopted the system beucase i had notieced taht many other websites had adoptd the key/value store system.

i think the idea behind it is to streamline as much information in teh database as possible. i.e the less information in the database the quicker the database will be able to function and thus retreive information. i guess we are planning in advance to when the database will contain millions of users.

its it your opinion then that this will make no diffrnece to the database and that it simply better to do away with the key/value store?

I would definitely pair them in the database. In fact I’d be tempted to index the countries with a numeric primary key in a separate table if you are looking to shave effort from the database. Your country code may be useful elsewhere, so could be worth leaving in, but leaving the “countries” array to your php may not be hugely futureproof.

Say at some point in the future a country decides to change it’s name (hey, it seems to be potentially happening a lot recently!) . In order to change the country of a person, you would have to find the string value that matches the old country, in every “person” record in your db, and then update each and every one to the new value (as well as updating your php array).

If you use a separate countries table, and store just the numeric value with the person (or in a lookup table), you just need to change the country name in one table, and it is reflected elsewhere.

If you do end up with a gazillion users, the numeric method will also be easier on MySql if you ever decide to do country specific searches/groupings of users.

I would echo TheOriginalH’s comments. A database i there to store and retrieve data. If we were to avoid putting this in a database purely because there is a concern on the load your are putting on the DB server - then it kind of defeats the whole point of database in general! You’ll find that your application will be easier to expand should you wish to do so later on. This might be a good opportunity to look at other aspects of the site to see if there is any other pieces of data that could be ported the DB also.

Getting data out of your database will likely be one of the slowest operations - because of the need to make the connection.

Once the connection is made though, selecting 6 fields instead of 5 is going to make negligible difference. Generally you should prefer to keep data in your database because you can hone your tables and queries to fetch the data so that it matches how you want to finally use it as close as possible.

You could store a list of countries in a table, and then JOIN the users table to the countries table to fetch your country name.


countries
=======
code | country
============
AL | Albania
AF | Afghanistan
.... etc
US | United States


users
====
id | name | country_code
===================
34 | Bob | US

select name, country 
FROM users u 
LEFT JOIN countries c
ON u.country_code = c.code
WHERE id = 34;

// Bob, United States

So that is one way - you run 2 tables and JOIN them.

This might be great if you have the potential to re-use that countries table in other parts of your website, on sign up for example?

If you do NOT need to keep countries in their own table then you have the option of doing this:


users
====
id | name | country
===================
34 | Bob | United States

You can speed up access to table contents by creating an INDEX on the country field if you ever need to ask the question “how many users live in the United States?” of your database.

Another thing to take into consideration is how many countries will you ACTUALLY be attracting users from?

The database options are all superior to maintaining an array in PHP. There are times you need to keep arrays in PHP - but a concrete list of countries is not a good example.

so that when you look at rows in your uses table, they will look like this –


[B]users[/B]
  id  name  country_code
  34  Bob    17
  42  Todd   32

please, why numeric? that’s so ugly

wouldn’t it be better to use one of the iso standard country codes?


[B]users[/B]
  id  name  country_code
  34  Bob    UK
  42  Todd   CA

this whole business about numeric keys being fast is a sorry myth, alpha keys are just as fast

I defer to your greater experience there Rudy. However (and it really is the point of a pedant), given that the country codes you refer to themselves do change (http://en.wikipedia.org/wiki/ISO_3166-1#Changes), those updates on a gazillion users (if Fiji were to enjoy such a population, and wide membership to Andreea’s site) would be slightly more work for both the admin and db than just changing the value of that field in a numerically indexed table, no?

no :slight_smile:

ON UPDATE CASCADE can be used if needed

even if it locks the database for half an hour while it finishes

that’s better than decades of obfuscated data and needless extra joins

besides, you would have created your own country codes table, nothing says you have to change the code in your version if the iso code changes – it’s just a great starting point, isn’t it?

:smiley:

Bare with me as I’m probably misunderstanding, but I was assuming that the same number of joins would be present, and your example included a table that referenced the full country name


[B]countries[/B]
 id    name
  UK  United Kingdom
  CA  Canada


If that is the case, then where are the extra joins in the numeric index?

If it isn’t the case, then the original question has been obscured - are you suggesting that the country/code array being stored in the php (or python/whatever) itself is a better solution?

Also confused as to how locking a db for half an hour would be a good idea in any live environment.

Not trying to be contrary, but genuinely interested in the answers (and I’m sure they’ll be useful to the original poster).

Edit:

PS: now spending time avoiding proper work and learning about ON UPDATE CASCADE :smiley:

bear with me = have patience
bare with me = let’s get undressed together

:smiley:

not necessarily

in post #7, look at the two different ways a couple of rows in the users table would appear

you’re going to memorize your numeric codes? no, you’re going to do a join

whereas if i’m looking at my users table, when i see ‘CA’ i’m going to know, without a join, that it’s canada

after ON UPDATE CASCADE finishes, you go back to life as normal, unobfuscated

and like i said, you don’t necessarily have to change your table if fiji changes its code

On the whole I see what you’re saying, although having looked at the country codes, there are probably only 20 or so that I’m familiar with, just taking the b’s, there is only one (BE) that I personally was familiar with before taking a look :

BAHAMAS BS
BAHRAIN BH
BANGLADESH BD
BARBADOS BB
BELARUS BY
BELGIUM BE
BELIZE BZ
BENIN BJ
BERMUDA BM
BHUTAN BT
BOLIVIA BO
BOSNIA AND HERZEGOWINA BA
BOTSWANA BW
BOUVET ISLAND BV
BRAZIL BR

[FONT=Verdana]The others are almost as obfuscated as numbers to my eye (although I could hazard guesses without the table above, I’d still need the join to be certain).

As said, the issue is one of some pedantry. You’re right, the list of country codes could be one which is maintained just by the website itself, I suppose I’m always looking at interoperable code. Maintaining a list which adheres to current standards means that the db could happily interact/merge with any other that follows the same standard (it was actually your post that made me realise the potential).
[/FONT]
I realise that I am looking at a very unlikely situation, but (bear with me :wink: ) if:

a: There was a likelihood that this site were to expand to the size of, say, Facebook,
b: As a consequence, it is likely that there will be some communication/drawing/sharing of data with other sites
c: A country with a large population, and user base, were to have its country code changed within the standard

is changing the value of one field not preferential to updating millions of records?

fwiw, what little I do know about sql came mostly from Kevin Yank & your good self. As an aside, searching the Oracle help system to find a decent definition of CASCADE is proving almost impossible! :frowning:

it’s your facebook killer site, you decide

is running needless joins for millions of accesses day after day really preferential to temporary and highly infrequent outages?

oracle?

you have my condolences

:cool:

re: bouvet island

that’s hilarious

:smiley:

hello everyone

i want to first thank everyone for taking time to write in and explain how best to proceed. i am still quite new to PHP and SQL so the information given, although brilliant, was a bit techinical for me. i will however sit down over the weekend and read it carefully and then see how best to impliment it.

however, in the mean time, i have two questions relating to the information given.

QUESTION ONE

I have a section that asked user to list the countries that they have visited. Some of them have only visited 1 country, some have visited up to 15 countries. Accordingingly, one of the reason for putting country codes into the database rather than the full country name was to shorten the database. i mean, would it not be too cumbersome on the database to put 15 countries etc for a single user. would it not just be simplier if i put country code for each country visited. i read somewhere that this disparity in columns sizes would eventually cause the database to slow down.

i presume that this is incorrect. please advise the best way around this.

QUSTION TWO

I have actually implimented the database as above. i now however wish to retrieve this information from the database. but i dont know how . i presume taht i will have to use a loop to retrive it, but i am not sure how to go about doing this.

it might be best if i show you how i uploaded it and tehn show you how its stored.


//THIS IS THE ARRAY CONTAINING THE FULL LIST OF COUNTRIES 
 $selection= full_list_48months();


	
	echo '<option value=" "  selected ="selected" >Select One </option>';
	echo '<option value="">            </option>';

	
	
	foreach ($selection as $key => $value){
	
	if($key == $sticky) {
	echo '<option value="' . $key .'" selected>' . $value . '</option>';
	}else{
	echo '<option value="' . $key .'">' . $value . '</option>';
	}
	

	}

	 echo'</select>'; 



//The data is retrived from above and placed into a variable 


	      if(isset($_POST['country_pref']))
	      {	      
	$countries_pref = $_POST['country_pref'];
	foreach ($countries_pref as $country) 
		{$source1 .= $country.", ";}
		$country_pref = substr($source1, 0, -2);
	
	  $countries_prefs   = mysqli_real_escape_string($dbc,trim($country_pref ));
	
	      }
	
//the variable in the database containing the countrie code will look like:   145, 3, 21, 46, 3, 21, 46 . 


i subsquently retrive the data using the following 


       if (isset(Reg3['details']['0'  ] [' lang'] ))  
		  {
			 $RI =  safe_output($Reg3['details' ]['0' ] ['lang']);
			

		  }
		  
		  else  {
		  	 $RI = false;
		  }				
	    


as you can see, once the information has been retrived from the database, i placed it in a variable. however the variable look somthing like this ; 145, 3, 21, 46, 3, 21, 46 .

my question is, how do i now convert this back to the full country names .

thank you for your kind attenton.

warm regards

Andreea

no :slight_smile:

whoever wrote that, did not understand indexing

with a humoungously inefficient query in a loop as you call the countries table over and over again for each id in the list

or you can pull the entire countries table into php and do lookups that way

the better design would be to store one row per country per user, so if a user enters 15 countries as your example, there would be 15 rows for that user

that at least would enable you to use a join to the countries table in a single query

and of course if you used the country name rather than the id, you wouldn’t need a join at all

:slight_smile: