This is a great contribution that allows users to filter the Latest Products and Product Specials by Category via a dropdown box. Many a customers have mentioned having to scroll through hundreds of products before finding what they wanted within these two pages so this contribution solves that. If the customer knows the category it will only show new or products on special for that category they chose.
Well this is a great concept except it is not working with osCommerce 2.2 MS2. I'm not a php or MySQL guru but I believe the database calls are not working.
See the contribution here: http://addons.oscommerce.com/info/5785
Here's a live site with the drop down not working properly: http://www.jehovanisi.com/products_new.php
Could you guys take a look at the code for products_new and let me know why the database is not connecting correctly?
CODE
------------------------
Install Products_new.php
------------------------
----------------
On about line 45
----------------
Find: <?php echo HEADING_TITLE; ?>
immidiately after insert:
</td>
<td class="pageHeading" align="right">
<FORM>
<?php
$cat_id1=0;
$chkSub1 = 0;
$conn1 = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD) or die ('Error connecting to mysql');
mysql_select_db(DB_DATABASE);
if (isset($categories_id)){
if (!empty($categories_id) && $categories_id!=""){
$rst = mysql_query("SELECT parent_id FROM categories WHERE categories_id = ".$categories_id);
if(mysql_num_rows($rst) > 0){
$rparent = mysql_fetch_object($rst);
if($rparent->parent_id > 0){
$cat_id1 = $rparent->parent_id;
}
else{
$cat_id1 = $categories_id;
}
}
else{
$cat_id1 = $categories_id;
}
}
else{
$cat_id1 = $categories_id;
}
}
else{
$cat_id1 = $categories_id;
}
if (isset($categories_id)){
if (empty($categories_id) && $categories_id==""){
//$result = mysql_query("SELECT CD.*, C.* FROM categories AS C, categories_description AS CD WHERE CD.language_id=1 AND C.categories_id = CD.categories_id AND C.parent_id=0");
$result = mysql_query("select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, categories AS C, categories_description AS CD, products_to_categories as pc where CD.language_id=1 AND p.products_status = '1' AND C.categories_id = CD.categories_id AND C.parent_id=0 and p.products_id = pc.products_id and pc.categories_id = C.categories_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");
}
else{
//$result = mysql_query("SELECT CD.*, C.* FROM categories AS C, categories_description AS CD WHERE CD.language_id=1 AND C.categories_id = CD.categories_id AND C.parent_id=$cat_id1");
$result = mysql_query("select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, categories AS C, categories_description AS CD, products_to_categories as pc where CD.language_id=1 AND p.products_status = '1' AND C.categories_id = CD.categories_id AND C.parent_id=$cat_id1 and p.products_id = pc.products_id and pc.categories_id = C.categories_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");
$chkSub1 = 1;
}
}
else{
//$result = mysql_query("SELECT CD.*, C.* FROM categories AS C, categories_description AS CD WHERE CD.language_id=1 AND C.categories_id = CD.categories_id AND C.parent_id=0");
$result = mysql_query("select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, categories AS C, categories_description AS CD, products_to_categories as pc where CD.language_id=1 AND p.products_status = '1' AND C.categories_id = CD.categories_id AND C.parent_id=0 and p.products_id = pc.products_id and pc.categories_id = C.categories_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");
}
//print("<br><br>select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, categories AS C, categories_description AS CD, products_to_categories as pc where CD.language_id=1 AND p.products_status = '1' AND C.categories_id = CD.categories_id AND C.parent_id=0 and p.products_id = pc.products_id and pc.categories_id = C.categories_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name");
//$result = mysql_query("SELECT CD.*, C.* FROM categories AS C, categories_description AS CD WHERE CD.language_id=1 AND C.categories_id = CD.categories_id AND C.parent_id=0");
?>
<SELECT NAME="clicklist"style="background-color:#F0F1F4" onChange="location.href=this.form.clicklist.options[this.form.clicklist.selectedIndex].value">
<?php
if ($chkSub1 == 1){
?>
<OPTION>Sub-categories
<?php
}
else{
?>
<OPTION>Select by category
<?php
}
?>
<option>----------------------
<?php
while ($row = mysql_fetch_object($result)) {
print('<option value="products_new.php?categories_id='.$row->categories_id.'&inc_subcat=1&manufacturers_id">'.$row->categories_name);
}
mysql_free_result($result);
?>
<option value="products_new.php?categories_id=&inc_subcat=1&manufacturers_id">View All
</SELECT NAME></form></div>
-----
Find:
-----
<?php
$products_new_array = array();
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);
if (($products_new_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3'))) {
?>
-----------------
And replace with:
-----------------
<?php
$products_new_array = array();
if (isset($categories_id)){
if (empty($categories_id) && $categories_id==""){
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
}
else{
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " pcat, " . TABLE_CATEGORIES . " cat where p.products_status = '1' and p.products_id = pcat.products_id and pcat.categories_id = cat.categories_id and ((cat.categories_id = " . $categories_id . " or cat.parent_id = " . $categories_id . ")) and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
}
}
else{
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
}
$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);
if (($products_new_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3'))) {
?>
Install Products_new.php
------------------------
----------------
On about line 45
----------------
Find: <?php echo HEADING_TITLE; ?>
immidiately after insert:
</td>
<td class="pageHeading" align="right">
<FORM>
<?php
$cat_id1=0;
$chkSub1 = 0;
$conn1 = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD) or die ('Error connecting to mysql');
mysql_select_db(DB_DATABASE);
if (isset($categories_id)){
if (!empty($categories_id) && $categories_id!=""){
$rst = mysql_query("SELECT parent_id FROM categories WHERE categories_id = ".$categories_id);
if(mysql_num_rows($rst) > 0){
$rparent = mysql_fetch_object($rst);
if($rparent->parent_id > 0){
$cat_id1 = $rparent->parent_id;
}
else{
$cat_id1 = $categories_id;
}
}
else{
$cat_id1 = $categories_id;
}
}
else{
$cat_id1 = $categories_id;
}
}
else{
$cat_id1 = $categories_id;
}
if (isset($categories_id)){
if (empty($categories_id) && $categories_id==""){
//$result = mysql_query("SELECT CD.*, C.* FROM categories AS C, categories_description AS CD WHERE CD.language_id=1 AND C.categories_id = CD.categories_id AND C.parent_id=0");
$result = mysql_query("select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, categories AS C, categories_description AS CD, products_to_categories as pc where CD.language_id=1 AND p.products_status = '1' AND C.categories_id = CD.categories_id AND C.parent_id=0 and p.products_id = pc.products_id and pc.categories_id = C.categories_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");
}
else{
//$result = mysql_query("SELECT CD.*, C.* FROM categories AS C, categories_description AS CD WHERE CD.language_id=1 AND C.categories_id = CD.categories_id AND C.parent_id=$cat_id1");
$result = mysql_query("select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, categories AS C, categories_description AS CD, products_to_categories as pc where CD.language_id=1 AND p.products_status = '1' AND C.categories_id = CD.categories_id AND C.parent_id=$cat_id1 and p.products_id = pc.products_id and pc.categories_id = C.categories_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");
$chkSub1 = 1;
}
}
else{
//$result = mysql_query("SELECT CD.*, C.* FROM categories AS C, categories_description AS CD WHERE CD.language_id=1 AND C.categories_id = CD.categories_id AND C.parent_id=0");
$result = mysql_query("select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, categories AS C, categories_description AS CD, products_to_categories as pc where CD.language_id=1 AND p.products_status = '1' AND C.categories_id = CD.categories_id AND C.parent_id=0 and p.products_id = pc.products_id and pc.categories_id = C.categories_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");
}
//print("<br><br>select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, categories AS C, categories_description AS CD, products_to_categories as pc where CD.language_id=1 AND p.products_status = '1' AND C.categories_id = CD.categories_id AND C.parent_id=0 and p.products_id = pc.products_id and pc.categories_id = C.categories_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name");
//$result = mysql_query("SELECT CD.*, C.* FROM categories AS C, categories_description AS CD WHERE CD.language_id=1 AND C.categories_id = CD.categories_id AND C.parent_id=0");
?>
<SELECT NAME="clicklist"style="background-color:#F0F1F4" onChange="location.href=this.form.clicklist.options[this.form.clicklist.selectedIndex].value">
<?php
if ($chkSub1 == 1){
?>
<OPTION>Sub-categories
<?php
}
else{
?>
<OPTION>Select by category
<?php
}
?>
<option>----------------------
<?php
while ($row = mysql_fetch_object($result)) {
print('<option value="products_new.php?categories_id='.$row->categories_id.'&inc_subcat=1&manufacturers_id">'.$row->categories_name);
}
mysql_free_result($result);
?>
<option value="products_new.php?categories_id=&inc_subcat=1&manufacturers_id">View All
</SELECT NAME></form></div>
-----
Find:
-----
<?php
$products_new_array = array();
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);
if (($products_new_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3'))) {
?>
-----------------
And replace with:
-----------------
<?php
$products_new_array = array();
if (isset($categories_id)){
if (empty($categories_id) && $categories_id==""){
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
}
else{
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " pcat, " . TABLE_CATEGORIES . " cat where p.products_status = '1' and p.products_id = pcat.products_id and pcat.categories_id = cat.categories_id and ((cat.categories_id = " . $categories_id . " or cat.parent_id = " . $categories_id . ")) and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
}
}
else{
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
}
$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);
if (($products_new_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3'))) {
?>