Hi David,
I'm looking to give a total price including postage to visitors of my site. I have added a field for postage and can display the postage charge for each product in prices.php.
Code for displaying both items in prices.php is
<?php
print $config_currencyHTML.$product["price"];
?>
<?php
print $config_currencyHTML.$product["postage"];
?>
How would I add price and postage to have
<?php
print $config_currencyHTML.$product["totalprice"];
?>
Hope you can help.
Thanks
Paddyman
Hi David,
Thanks for your quick response :)
Have inserted the last line of your code above but it isn't working as my postage values aren't being imported into the database leaving the default value of 0.00 for all products. I have the field for postage set in the products table as decimal(10,2) which is the same as price but still getting 0.00 for all products.
Any ideas?
Also, where would I include the 1st line of your code above in prices.php?
<?php
$totalPrice = sprintf("%.2f",($product["price"]+$product["postage"]));
print $totalPrice;
?>
Thanks
Paddyman
Hi,
I'm extremely familiar with the modifications required to add a new field, so the easiest thing to do is if you can email me your modified admin/feeds_register_step2.php and includes/admin.php i'll check your changes for you (reply to your registration code or forum reg email)
You don't need to add anything else to prices.php - I included all the code in the single line - the first block was just to demonstrate how to add the fields.
Cheers,
David.
Hi David,
Got the total to work, there was a problem with my feed. Thanks for that.
One more thing if you don't mind!!
I have the total price displayed using
<?php
print $config_currencyHTML.sprintf("%.2f",($product["price"]+$product["postage"]));
?>
How can I sort the products in the prices table by Total Price with the lowest Total Price showing first.
Do I insert the above code anywhere and use $totalprice to sort?
<?php
$totalPrice = sprintf("%.2f",($product["price"]+$product["postage"]));
print $totalPrice;
?>
Thanks
Paddyman
Hi,
To do the sort, the changes need to be made in products.php. The existing SQL to select the products, ordered by price (which results in how they are ordered in the price table) is constructed by the following code on line 12:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;To change this to sort by the total price, try this:
$sql = "SELECT *,(price+postage) AS totalprice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY totalprice LIMIT ".$config_resultsPerPage;Hope this helps!
Cheers,
David.
David You're a genius.
Works perfectly. Great feature to have on the site.
Many thanks as always :)
Hi David,
One more thing on this. So far each product I have had has more than one merchant, so search displays compare prices and displays the minPrice function from search.php. I had
$sql = "SELECT * , MIN( price+postage ) AS minPrice, MAX( price+postage ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";
to display the price including postage in search results.
Now I have a product with only one merchant, so if I understand rightly it goes to searchresults.php and uses
<?php
print $config_currencyHTML.$product["price"];
?>
<?php
print translate("More Information");
?>
How do I declare totalprice=price+postage in search.php and have ["totalprice"] instead of ["price'] in the above code ?
In products.php it is done using
$sql = "SELECT *,(price+postage) AS totalprice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY totalprice LIMIT ".$config_resultsPerPage;
But I can't change the select statement in search.php as it is setting (price+postage) as minPrice.
Thanks
Adrian
Hi Adrian,
You should just be able to change:
print $config_currencyHTML.$product["price"];...to:
print $config_currencyHTML.$product["minPrice"];minPrice will still have been calculated even though there is only one merchant so that should do the trick...
Cheers,
David.
Doh !! Should have thought of that.
Many Thanks
Adrian
Hi David,
Another related question on this :)
Have added a stock availability field to the database, where Yes is "In stock" and No is "Out of Stock". Am now sorting products in the prices table, first by availability descending so out of stock items appear at the bottom of the prices table and then sorting by price.
$sql = "SELECT *,(price+delivery) AS totalprice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY availability DESC,totalprice LIMIT ".$config_resultsPerPage;
Best Price is displaying correctly in products.php as the first product In Stock using
$mainProduct["totalprice"]
However, search results are still displaying the cheapest price even though it may be out of stock. I would like it to display the cheapest in stock product.
Thought it would be a matter of changing product in the following line in searchresults.php and defining $mainproduct, but this hasn't worked
<?php print $config_currencyHTML.$product["minPrice"];Any ideas?
Thanks
Adrian
Hi Adrian,
Do you mean that you want the actual search results (so nothing to do with the product pages) to order in-Stock (cheap > most expensive) followed by out-of-stock (cheap > most expensive) - or is it just the prices table and that is not sorted as you're expecting...
Cheers,
David.
Hi David,
Sorry for dragging up an old thread.
I have used the 4 individual lines of code you posted in the 2nd post above into my prices.php which works great thanks!
However I wish to order by totalprice.
I know what to change in the products.php (or pricesExternal.php in my case) but I see that it relies on $totalprice to know what to order by.
Where do I need to add the below code so that it knows what the $totalprice is? I have not used the below code, only the other lines of code directly into my table code to work out the 'Total price'
<?php
$totalPrice = sprintf("%.2f",($product["price"]+$product["postage"]));
print $totalPrice;
?>
Many thanks,
Craig
Hi Craig,
In priceExternal.php, the SQL to select the products is current the following code on line 54:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;This is where you need to add price+postate to create a virtual "totalprice" field that the query can then sort by. To do this, REPLACE the above line with:
$sql = "SELECT *,(price+postage) AS totalprice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY totalprice LIMIT ".$config_resultsPerPage;...and that should do the trick!
Cheers,
David.
That has done the trick David, thanks again for your excellent support!
hi david
was wondering what do i change this
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' ORDER BY price";
to in the new release to enable the ordering to go by total price?
regards
Phil Stone
www.buy24-7.net
Hi Phil,
$sql = "SELECT *,(price+postage) AS totalprice FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."' ORDER BY totalprice";Cheers,
David.
--
PriceTapestry.com
hi david
thanks for your help once again
i have made a change to the script but still the prices are not in the right order, could you advise me on this
thanks
Hi Phil,
Ah - in the beta version the results are re-sorted after voucher codes are applied - and in the distribution this is against the price field, so you'll need to change this to totalprice.
Instead of the replacement SQL as described above, instead revert back to the original:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE normalised_name = '".database_safe($q)."'";Next, look for the following code beginning at line 23:
foreach($rows as $k => $row)
{...and REPLACE with:
foreach($rows as $k => $row)
{
$rows[$k]["totalprice"] = $row["price"] + $row["postage"];Finally, look for the cmp() function beginning at line 49:
function cmp($a, $b)
{
if ($a["totalprice"] == $b["totalprice"])
{
return 0;
}
return ($a["totalprice"] < $b["totalprice"]) ? -1 : 1;
}If you're not sure, email me your products.php and I'll check it out for you...
Cheers,
David.
--
PriceTapestry.com
Hi,
I'd using sprintf to do the caluation as it will handle the rounding properly:
<?php$totalPrice = sprintf("%.2f",($product["price"]+$product["postage"]));
print $totalPrice;
?>
To incorporate this into html/prices.php, look for the following line:
<th align='left'><?php print translate("Price"); ?></th>...and add the following on the next line:
<th align='left'><?php print translate("Total Price"); ?></th>Then, after the following code:
<td><strong><?php print $config_currencyHTML.$product["price"]; ?></strong></td>... add this to calculate and display the total price:
<td><strong><?php print $config_currencyHTML.sprintf("%.2f",($product["price"]+$product["postage"])); ?></strong></td>Cheers,
David.