Ok, well I thought I'd finally get this done because I got sick of manually editing my non-Zen static HTML pages, everytime I changed the price in my store!
Basically all this code does is connect to your ZenCart's database and run a query for the price. I sometimes put my items on special so I also had it run another query to grab that price and show it instead if a product was on special. If you don't have specials then you can cut-down the code, or if you have other things that affect the price you need to know that this code probably won't handle those cases!
Ok, here are the steps:
1. Say you have a file called "myStaticPage.html" and that is where you want to insert the Zen prices.
2. Copy myStaticPage.html to a file called myStaticPage.php. *Note: You can leave out this step if your webserver is set to parse HTML pages as PHP, but usually this is not the case.
3. Then all you have to do is open the myStaticPage.php file and add this bit of PHP code to the very top of the HTML code that's already there.
Code:
<?php
include("includes/configure.php"); // To grab the DB info
$dbh = mysql_connect ("localhost", DB_SERVER_USERNAME, DB_SERVER_PASSWORD) or die ('<BR> - Could not connect to the database because: '.mysql_error());
mysql_select_db (DB_DATABASE, $dbh) or die(mysql_error( ));
$query = <<<EOT
SELECT `products_id`, `products_price`
FROM `zen_products` WHERE (
`products_id` = 1 ||
`products_id` = 2 ||
`products_id` = 3)
EOT;
$result = mysql_query($query); if (!$result) { $message = "Error! Invalid Query: ".mysql_error()."\n Original Query: ".$query; die($message); }
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$productPrice[$row[0]][0] = $row[1]; // make our own array for the productPrice (I used a multi-dim array because I want to account for specials price and this is the cleanest way I could think of doing this)
}
$query = <<<EOT
SELECT `products_id`, `specials_new_products_price`
FROM `zen_specials` WHERE (
`products_id` = 1 ||
`products_id` = 2 ||
`products_id` = 3)
EOT;
$result = mysql_query($query); if (!$result) { $message = "Error! Invalid Query: ".mysql_error()."\n Original Query: ".$query; die($message); }
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$productPrice[$row[0]][1] = $row[1]; // Now if a specials price exists for a product it will record it in our productPrice array
}
mysql_close();
// Now we have gotten all our data and you would use this code at whatever location in your HTML that you want to show the item's price
// Of course you can format it however you need, and this probably isn't the cleanest way of doing this
echo "<p><B>QUERIED PRICES ARE AS FOLLOWS</B><p>";
if (isset($productPrice[1][1])) {
echo "Product Item Number 1's price is: <b><span style='text-decoration: line-through;'>".$productPrice[1][0]."</span><br><font color='red'>Sale Price: ".$productPrice[1][1]."</font></b><br>";
} else {
echo "Product Item Number 1's price is: <b>".$productPrice[1][0]."</b><br>";
}
if (isset($productPrice[2][1])) {
echo "Product Item Number 2's price is: <b><span style='text-decoration: line-through;'>".$productPrice[2][0]."</span><br><font color='red'>Sale Price: ".$productPrice[2][1]."</font></b><br>";
} else {
echo "Product Item Number 2's price is: <b>".$productPrice[2][0]."</b><br>";
}
if (isset($productPrice[3][1])) {
echo "Product Item Number 3's price is: <b><span style='text-decoration: line-through;'>".$productPrice[3][0]."</span><br><font color='red'>Sale Price: ".$productPrice[3][1]."</font></b><br>";
} else {
echo "Product Item Number 3's price is: <b>".$productPrice[3][0]."</b><br>";
}
// print_r($productPrice); // Included for test purposes only, used to show the entire array contents
?>
4. Edit my code to do what you want. Specifically, you'll want to define the product id's for the specific products on your page you want to grab prices for. Also you'll want to not echo the prices as I have done, but place those echo statements in the appropriate spot where the price is supposed to be on your page.
And that's it!
Now for some questions for the experts (That means you AJEH!!!):
Q1: Will this affect my store's performance at all since I am making external queries to the database? I don't think it should because a PHP SQL query is the same no matter where it is executed from, but just thought I should ask.
Q2: Do you see anything wrong with my code that could cause an security hole or corrupt anything?
Q3: Any ideas on how to make this more efficient? I know there is DB caching but I'm not familiar with all that.
Well, I hope this helps everyone, it's gonna make my life easier.
Bookmarks