Storing users details in mysql

[graham23s]

Hi Guys,

 

i have just started a new site, and it requires a lot of information from the user: country,state,post/zip code etc

 

i normally store all the information in mysql as: Scotland,Lanatrkshire,ML9 1BJ

 

would i be better using 2d arrays and doing: array(""1","Scotland""); for example and storing just the numbers in mysql as oppose to the actual word? just before i go ahead lol

 

cheers

 

Graham

[revraz]

Then you would need a table linking 1 to Scotland right?

 

Do you want to search on their location?

[graham23s]

Hi R,

 

yeah that was the plan, then eventually search by zip location i did this for  aprevious site:

 

     $countries_list = array(
                       54 => 'Afganistan',
                       65 => 'Albania',
                       35 => 'Algeria',
                       68 => 'Andorra',
                       36 => 'Angola',
                       35 => 'Algeria',
                       89 => 'Antigua Barbuda',
                       20 => 'Australia',
                       37 => 'Austria',
                       82 => 'Bahamas',
                       86 => 'Bangladesh',
                       85 => 'Barbados',
                       16 => 'Belgium',
                       34 => 'Belize',
                       67 => 'Bosnia Herzegovina',
                       18 => 'Brazil',
                      104 => 'Bulgaria',
                       60 => 'Burkina Faso',
                       82 => 'Bahamas',
                        5 => 'Canada',
                       51 => 'Chile',
                        8 => 'China',
                       99 => 'Colombia',
                      102 => 'Costa Rica',
                       53 => 'Congo',
                       97 => 'Croatia',
                       52 => 'Cuba',
                       46 => 'Czech Republic',
                       10 => 'Denmark',
                       41 => 'Dominican Republic',
                      103 => 'Egypt',
                       98 => 'Estonia',
                       14 => 'Finland',
                        6 => 'France',
                        7 => 'Germany',
                       42 => 'Greece',
                       43 => 'Guatemala',
                       33 => 'Hong Kong',
                       79 => 'Honduras',
                       74 => 'Hungary',
                       62 => 'Iceland',
                       70 => 'India',
                       44 => 'Israel',
                        9 => 'Italy',
                       13 => 'Ireland',
                       31 => 'Jamaica',
                       17 => 'Japan',
                       58 => 'Kiribati',
                       87 => 'Laos',
                      101 => 'Latvia',
                      100 => 'Lebanon',
                       69 => 'Lithuania',
                       32 => 'Luxembourg',
                       40 => 'Malaysia',
                       25 => 'Mexico',
                       63 => 'Nauru',
                       15 => 'Netherlands',
                       71 => 'Netherlands Antilles',
                       21 => 'New Zealand',
                       61 => 'Nigeria',
                       96 => 'North Korea',
                       11 => 'Norway',
                       45 => 'Pakistan',
                       90 => 'Paraguay',
                       83 => 'Peru',
                       59 => 'Philippines',
                       14 => 'Poland',
                       24 => 'Portugal',
                       50 => 'Puerto Rico',
                       75 => 'Romania',
                        3 => 'Russia',
                       47 => 'Serbia',
                       48 => 'Seychelles',
                      200 => 'Scotland',
                       94 => 'Senegal',
                       26 => 'Singapore',
                       64 => 'Slovenia',
                       29 => 'South Africa',
                       30 => 'South Korea',
                       23 => 'Spain',
                        1 => 'Sweden',
                       57 => 'Switzerland',
                       49 => 'Taiwan',
                       93 => 'Thailand',
                       50 => 'Togo',
                       78 => 'Trinidad & Tobago',
                       55 => 'Turkey',
                       55 => 'Ukraine',
                       12 => 'United Kingdom',
                        2 => 'United States of America',
                       88 => 'Uruguay',
                       56 => 'Uzbekistan',
                       73 => 'Venezuela',
                       77 => 'Vietnam',
                       39 => 'Western Samoa',
                       38 => 'Yugoslavia',
                       );

 

would this be a good way to go do you think?

 

cheers

 

Graham

[lemmin]

I think he means that he would have a lookup table in the php code, like he showed: test = array("Scotland", "Ireland") so when he queries the data base and the result for location is 1, he can put it into the array test[1] and return Ireland.

 

Doing it with the array will put more load on the parsing and execution, but it will take less space to store the data. If you are trying to maximize efficiency of the code, keep all the information in the database. In general, I wouldn't sacrifice execution efficiency for disk space. Having "Scotland" stored in the database will make the code run faster.

[revraz]

I think any hard coded data in your Code like that would be a bad idea.

[graham23s]

Hi Guys,

 

yeah basically 1 -> Scotland, 2 => Ireland etc, so it would be better storing the words as oppose to the numbers, i'm on that part right now i don't know what to do lol

 

cheers

 

Graham

[revraz]

Storing where?  If you are talking about storing a number in the MySQL DB as a location, then that's fine, as long as you have another table that links the number to the location.

[graham23s]

yeah thats it, just a number associated to a particular country i'll go for that as i have made the rest 2d arrays.

 

cheers guys

 

Graham

[lemmin]

Remember that your array is going to instantiated into memory every time that code loads. That is a pretty big list to load at run-time. Plus, after it is loaded, you still need to run extra code to link the location to part of the array. If it were in a separate table, it wouldn't have to be loaded at run-time, but you would have to make a second query or subquery for every location. Before you commit to that array, remember that the only thing you are saving is disk space at the cost of an increase in execution time. I don't know how much space you would be saving, but I doubt it is worth the efficiency.