Hi David! :)
I have a table called merchants and each row is the information about a specific merchant, including the merchant name and shipping prices for various countries. If the merchant doesn't ship to a specific country the field just have a "NO" in text.
You suggested a nice code a few weeks ago in order to manage several countries:
<?php
switch($_SERVER["HTTP_HOST"])
{
case "fr.example.com": // French
$country = 'fr';
// translate, etc. etc.
break;
case "es.example.com": // Spanish
$country = 'es';
break;
}
?>
What I want to do is when the user is for example in fr.example.com he will only be displayed products that ship to France. The user will search on all products (to simplify things) but he will only be shown the ones that ship to his country.
I would use something like this for the query:
<?php
$sql = "SELECT ship_".$country." FROM `".$config_databaseTablePrefix."merchants` WHERE name = '".$product["merchant"]."'";
$shipping = database_querySelect ($sql);
?>
Then check
<?php
if ($shipping != 'NO')
?>
Can you help me by giving me some clues on where should I put the condition statements and the query material?
Thank you for reading,
Pedro
Cheers, David, it sure helps!
There is just one situation in the case there are no merchants ship to that country, the script just runs normally because merchantWhere is empty and the query is run normally and shows all the products with no filter.
Could there be a solution to just stop the script and display a "We're sorry, no results were found."?
Hi,
Sure - that could be incorporated by adding a WHERE 0 clause in place of the merchant select. In the first replacement (search.php), instead use:
$sql = "SELECT name FROM `".$config_databaseTablePrefix."merchants` WHERE ship_".$country." <> 'NO'";
if (database_querySelect($sql,$merchants))
{
$ins = array();
foreach($merchants as $merchant)
{
$ins[] = "'".database_safe($merchant["name"])."'";
}
$in = implode(",",$ins);
$priceWhere .= " AND merchant IN (".$in.") ";
}
else
{
$priceWhere .= " AND 0 ";
}
if ($q)And in the second replacement (products.php), use:
$sql = "SELECT name FROM `".$config_databaseTablePrefix."merchants` WHERE ship_".$country." <> 'NO'";
if (database_querySelect($sql,$merchants))
{
$ins = array();
foreach($merchants as $merchant)
{
$ins[] = "'".database_safe($merchant["name"])."'";
}
$in = implode(",",$ins);
$merchantWhere = " AND merchant IN (".$in.") ";
}
else
{
$merchantWhere = " AND 0 ";
}
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."'".$merchantWhere." ORDER BY price";Cheers,
David.
--
PriceTapestry.com
I am very intersted to do the same with my price comparison script.
Can I follow these steps on 12/10B ?
Thanks,
Karolis
Hello Karolis,
I've added the 12/10B line number for the first modification to the original post. The second modification (in products.php) is still valid. Bear in mind that the code refers to a new table merchants containing the merchant <> country shipping mapping that the original poster had presumably created previously...
Cheers,
David.
--
PriceTapestry.com
Thanks!
I succeed using this mod. I created table merchants and have lots of subdomains nows :)
Finally, is it possible to disable main website "example.com" and leave only subdomains based on language (location).
Shortly, I want to leave main domain "example.com" only with "chose your country" function and after country is chosen, refer to any of subdomain.
Could you please suggest a solution how to disable main domain site and leave only subdomains.
Thanks,
Karolis
Hello Karolis,
Sure you can use $_SERVER["HTTP_HOST"] to detect either example.com or www.example.com and if so display your "Choose your country" form; else display the normal index.php. For example, something like this
<?php
require("includes/common.php");
if ($_POST["country"])
{
header("Location: http://".$_POST["country"]".example.com/");
exit();
}
require("html/header.php");
if (
($_SERVER["HTTP_HOST"]=="example.com")
||
($_SERVER["HTTP_HOST"]=="www.example.com")
)
{
?>
Choose your country:
France
Spain
<?php
}
else
{
// original index.php code here
}
require("html/header.php");
?>
(of course if you have site relevant stuff in your header and footer you can include separate ones instead)
Hope this points you in the right direction...
Cheers,
David.
--
PriceTapestry.com
Thanks David, I will try to do this following by your recommendations.
What is more, is it possible to filter Categories, Merchants, Brands on country subdomain also?
Now when beeing in fr.example.com I am able to see brands, merchants, categories that has no products for FR.
Karolis
Hi Karolis,
Sure - very similar code can be used in the merchant / category / brand index pages. Post a reply once you have search filtering working correctly; and include a copy of the final modifications that you made to search.php (just the replacement code) and then I'll convert that to the code required for the index pages; or if you want to try from the following example, in categories.php the existing SQL to select categories is as follows (line 5)
$sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ORDER BY category";...the similar replacement would be:
$sql = "SELECT name FROM `".$config_databaseTablePrefix."merchants` WHERE ship_".$country." <> 'NO'";
if (database_querySelect($sql,$merchants))
{
$ins = array();
foreach($merchants as $merchant)
{
$ins[] = "'".database_safe($merchant["name"])."'";
}
$in = implode(",",$ins);
$where .= " AND merchant IN (".$in.") ";
}
$sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ".$where." ORDER BY category";Cheers,
David.
--
PriceTapestry.com
Hello David,
I've created table "merchants" based on example, that's why code is the same as in your given post.
I had only one problem with the main domain "example.com", because in this case $country variable is empty. For that reason I created a column ALL in database table "merchant" and assigned default parameter before switch in translate file. Seems it works great now.
Below is mu code. I wil follow your example by myself and let you know. Thanks David!!!
$sql = "SELECT name FROM `".$config_databaseTablePrefix."merchants` WHERE ship_".$country." <> 'NO'";
if (database_querySelect($sql,$merchants))
{
$ins = array();
foreach($merchants as $merchant)
{
$ins[] = "'".database_safe($merchant["name"])."'";
}
$in = implode(",",$ins);
$priceWhere .= " AND merchant IN (".$in.") ";
}
else
{
$priceWhere .= " AND 0 ";
}Your given code dont want to work :/
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in
Warning: Cannot modify header information - headers already sent by
Soemthing wrong with SQL query?
Hi,
Although not an SQL error, I think this line:
$priceWhere .= " AND 0 ";...should actually be...
$priceWhere .= " AND 1 ";(otherwise no search queries will return anything...)
Regarding your modification, when writing code involving SQL changes you can get the actual MySQL error message by enabling database debug mode - simply change line 6 of config.advanced.php as follows;
$config_databaseDebugMode = TRUE:...view your page again and more information about the error should be displayed. If you're not sure from the debug output where the problem lies let me know what is displayed and I'll take a look...
Cheers,
David.
--
PriceTapestry.com
After modding categories.php and Debug enable, I see error:
[SELECT DISTINCT(category) as category FROM `pt_products` AND merchant IN ('Azon.lt','VistaPrint.com') ORDER BY category][You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND merchant IN ('Azon.lt','VistaPrint.com') ORDER BY category' at line 1]
can't find problem by myself...
Hi,
I see the problem, sorry this line:
$where .= " AND merchant IN (".$in.") ";...should actually be:
$where = " WHERE merchant IN (".$in.") ";(in the merchant, category and brand index, this code is creating the _only_ WHERE clause, rather than adding to one that already exists)
Cheers,
David.
--
PriceTapestry.com
Categories works great now! Thanks.
I don't really want bother you all the day, but when you will have some more free minutes it would be great to get help with brands and merchants.
Thanks again!
Karolis
Hi Karolis,
The code will be virtually identical, but simply change "category" for "merchant" (without the quotes) in merchants.php and "category" for "brand" in brands.php. If you're not sure; post your working modification to categories.php and I'll modify as required for merchant and brand...
Cheers,
David.
--
PriceTapestry.com
To continue this multi-language/multi-country topic, the next most important feature would be different currency per country/language. It's the most important thing now that website will be finished as international.
I saw several different topics about this, but mostly are very very old.
I need that specific country would have it's own currency. For example: us.example.com should have USD as main currency, uk.example.com - GB, fr.example.com - EUR and so on. (feeds I use are in different currencies too so I must find best way how to convert it).
I am thinking to add many extra fields for each currency in pt_products table, then write a price converter to fill all these fields (lots of maths) and finally edit script to check the $country and print correct currency format.
What do you think? Please advice.
Hi Karolis,
Probably the easiest approach would be to override $config_currencyHTML. Based on the above mods, your script is creating the $country variable, so just after the point where this variable is created, try using something like:
switch($country)
{
case "us":
$config_currencyHTML = "$";
break;
case "gb":
$config_currencyHTML = "£";
break;
case "fr":
case "es":
$config_currencyHTML = "€";
break;
}Cheers,
David.
--
PriceTapestry.com
Yes, your given method maybe it's the best to print the currency format. Except the format, I also need to check and print the converted price of product.
For example, in html/product.php I see: $config_currencyHTML.$mainProduct["price"]
If I add extra fields in table pt_products for example: price_euro, price_usd, price_gbp, etc. How I can add these values in array to be able to use $config_currencyHTML.$mainProduct["price_euro"] and etc.
Thanks for advice,
Karolis
Hi Karolis,
If possible the easiest way would be to call the new fields price_cc where cc is the same as the values of your $country variable. Then, within the HTML files you simply need to use, for example:
$config_currencyHTML.$mainProduct["price_".$country]Cheers,
David.
--
PriceTapestry.com
I succeed, thanks!
Works absolutely great, as expected!
I've solved with currencies and price conversion. I have many prices columns now price_usd, price_eur, price_gbp and so on.
When finishing editing /html/products.php I see that $product["minPrice"] also needs to be solved somehow.
Do you have any idea how to replace minPrice?
I changed search.php and products.php SQL statments Select price AS minPrice and I succeed!
Solved!
Hello Pedro,
Within search.php, the easiest thing to do would be to plumb this into the $priceWhere variable; which adds additional WHERE clauses onto the search SQL. In search.php, look for the following code at line 33 (49 in 12/10B) - just after $priceWhere has been constructed:
if ($q)...and REPLACE that with:
$sql = "SELECT name FROM `".$config_databaseTablePrefix."merchants` WHERE ship_".$country." <> 'NO'";if (database_querySelect($sql,$merchants))
{
$ins = array();
foreach($merchants as $merchant)
{
$ins[] = "'".database_safe($merchant["name"])."'";
}
$in = implode(",",$ins);
$priceWhere .= " AND merchant IN (".$in.") ";
}
if ($q)
Similarly, in products.php, look for the SQL construction to SELECT the products, around line 12 :
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' ORDER BY price";...and REPLACE with:
$sql = "SELECT name FROM `".$config_databaseTablePrefix."merchants` WHERE ship_".$country." <> 'NO'";if (database_querySelect($sql,$merchants))
{
$ins = array();
foreach($merchants as $merchant)
{
$ins[] = "'".database_safe($merchant["name"])."'";
}
$in = implode(",",$ins);
$merchantWhere = " AND merchant IN (".$in.") ";
}
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."'".$merchantWhere." ORDER BY price";
Hope this helps!
Cheers,
David.
--
PriceTapestry.com