Freelancer

Description

I need mods on 3 dropdown menus to sort product by category & sub-category
1. Sort (Specials)by category & sub-category
2. Sort (What's new?)by category & sub-category
3. Sort (keyword Advance search)by category & sub-category

I have all 3 scrip for these menus that prsently work perfectly
on a 6 year old version of oscommerce. For some reason these are not
functioning properly with the newer versions.
I need some one to modify the scrips to correct this.

I will supply the old script .
I will show you the old scripts in action on my old site
And any other tech help.

Thanks, Ronnie


Additional information submitted:

07/23/2008 at 16:03 EDT:
See drop down menu script in action (old site)
1.youregiftbasket.com/specials.php
2.youregiftbasket.com/products_new.php
3.youregiftbasket.com/advanced_search_result_set.php?keywords=Set&categories_id=&inc_subcat=1&manufacturers_id
(See script for Specials.)

<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;
$chkSub1 = 1;
}
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, " . TABLE_SPECIALS . " s, 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 s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '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=".$cat_id1);
$result = mysql_query("select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, 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 s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1'");
$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, " . TABLE_SPECIALS . " s, 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 s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1'");
}
//print("<br><br>select distinct CD.categories_id, CD.categories_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, 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 s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1'");
//$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="specials.php?categories_id='.$row->categories_id.'&inc_subcat=1&manufacturers_id">'.$row->categories_name);
}
//mysql_free_result($result);
?>
<option value="specials.php?categories_id=&inc_subcat=1&manufacturers_id">View All
</SELECT NAME></form>
</tr>
</table></td>
</tr>





<?php
if (isset($categories_id)){
if (empty($categories_id) && $categories_id==""){
$specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";
}
else{
$specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, " . 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 s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";
}
}
else{
$specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";
}
$specials_split = new splitPageResults($specials_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS);

if (($specials_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3'))) {
?>

The scripts for specials & what's new are very similar
--------------------------------------------------
(Script for keyword Advance search)

<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="advanced_search_result_set.php?keywords=Set&categories_id='.$row->categories_id.'&inc_subcat=1&manufacturers_id">'.$row->categories_name);
}
mysql_free_result($result);
?>

<option value="advanced_search_result_set.php?keywords=Set&categories_id=&inc_subcat=1&manufacturers_id">View All
</SELECT NAME></form>

07/23/2008 at 16:10 EDT:
On this particular (Script for keyword Advance search) the Keword is "Set" and runs on a advanced_search_result.php that has been renamed to advanced_search_result_set.php to work properly.