white list the countries list and LL's experience

[blackhorse]

In my thread discussing the zencart countries list. Laserlight had this response.

---

Solution:
Use the zencart countries, but only show the countries we select (white list the countries).

If someone specifically requests we ship to a country that's not on the standard list, we take it on a case by case basis.

LL's experience:

The surprise for me is that I once made a purchase from ThinkGeek.com, and my goods were delivered. Well and good. Then sometime later, I tried making another purchase, only to be told that ThinkGeek no longer shipped to Singapore because of excessive fraudulent orders.

---

This brings another question:

Saying we did business with singapore before, singapore was on the white list. When LL registered frist time, LL created an account. Next time, when LL logged in, I will use 1 sql to get LL's account information

table accounts as a field called countries_id use the countries_id of table countries as the foreign key.

So the sql will be

sql="select accounts.*, countries.name as countries_name from accounts, countries where accounts.accounts_id=$LL_id and accounts.countries_id=countries.countries_id and accounts.valid=1 and countries.vaild=1";

This will show LL's profile.

But if later on, we "no longer shipped to Singapore because of excessive fraudulent orders.", then we set countries.vaild=0 for singapore.

Then when LL logged in again, next time, this sql will not return any result

sql="select accounts.*, countries.name as countries_name from accounts, countries where accounts.accounts_id=$LL_id and accounts.countries_id=countries.countries_id and accounts.valid=1 and countries.vaild=1";

due to singapore's valid value was set as 0 now. LL will not see his profile, the system may tell LL we cannot find his profile. But actually the right message should be LL's profile is still in our database, but LL's country is not on the valid list any more.

So in order to get the right message, do we have to abandon the relationship table sql search like this

sql="select accounts.*, countries.name as countries_name from accounts, countries where accounts.accounts_id=$LL_id and accounts.countries_id=countries.countries_id and accounts.valid=1 and countries.vaild=1";

And use two sqls instead

sql1="select * from accounts where accounts_id==$LL_id and valid=1";

and from the values returned by sql1 we will have the LL_countries_id

sql2="select name from countries where countries_id=$LL_countries_id";

And then from the sql2 results, we can tell if the countries is valid or not and show the right countries name and also tell LL the changes if it is not a valid country to do business any more.

Usually, we will tend to use the 1 sql approach, but with the situations I explained, it brought some confustion about when we should use 1 sql approach, and when we should use 2 sql approach.

Do you run into this situation before, how do you handle it?

Thanks!

[bpat1434]

Just remove the countries.valid=1 part of your query. Then make sure you also select the countries.valid column in the SELECT part. Then, use PHP to do the question of: Is this country valid?

$query = "SELECT a.*, c.name as country_name, c.valid as country_valid
FROM accounts as a
INNER JOIN countries AS c
    ON a.country_id=c.id
WHERE a.id={$LL_ID}
  AND a.valid=1";
$result = mysql_query($query) or die(mysql_error());
$info = mysql_fetch_assoc($result);

if($info['country_valid'] == 0) {
  // The country is not white-listed...
  // Perhaps show a warning message to the user.
}

Something like that would work. And you'd still get the profile and stuff. You'd just have put in checks in the PHP code for whether the country is valid or not.

[blackhorse]

Thanks! that will do!!!