Alphabetical links within categories/sub-categories

[basnyd]

Hi -

When someone clicks on a category (e.g. flag pins) 211 products are returned. The products are listed alphabetically. Currently, a person has to click page by page to find flag for Ireland. I would like to add links at the top of the page that say "A, B, C, D, E, F... Z" that would allow a person to click on "I" and go directly to the countries that begin with "I".

I've found two contributions at the oscommerce site that apply this idea to the all products page:

browse alphabetically - osCommerce Community Support Forums
Categories by letters - osCommerce Community Support Forums

I'm unsure with oscMax and BTS about where to look to make such modifcation for this page - Flag Pins : osCMax v2.0

If someone could point me in the right direction, I'd appreciate it.

Thanks,
Barb

[basnyd]

Does this query string go into catalog\allprods.php?

$firstletter=$HTTP_GET_VARS['fl'];
if (!$HTTP_GET_VARS['page']){
$where="and p.products_status=1 and pd.products_name like '$firstletter%'";
}else {
$where="and p.products_status=1";
}

I've add the navigation to allprods.tpl.php.

I think I then need to modify the query (which I found in allprods.tpl.php).

Original query:
$products_query = tep_db_query("SELECT p.products_id, pd.products_name FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd WHERE p.products_id = pd.products_id AND p.products_status = 1 AND pd.language_id = $this_language_id ORDER BY pd.products_name");

New query:
$products_new_query_raw = "select p.products_releasedate, p.products_id, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id $where order by pd.products_name";

The queries don't match exactly, what is the critical piece that I need to add to my query?

Thanks,
Barb

PS - I'm trying to get this to work with allprods.php first and then transfer my knowledge over to the product listing page.

[met00]

The code to create the selection

<table width="100%" border="0" cellspacing="2" cellpadding="1">
<tr><td class=main align=center>
<a href=allprods.php?alpha=a>A</a> |
<a href=allprods.php?alpha=b>B</a> |
<a href=allprods.php?alpha=c>C</a> |
<a href=allprods.php?alpha=d>D</a> |
<a href=allprods.php?alpha=e>E</a> |
<a href=allprods.php?alpha=f>F</a> |
<a href=allprods.php?alpha=g>G</a> |
<a href=allprods.php?alpha=h>H</a> |
<a href=allprods.php?alpha=i>I</a> |
<a href=allprods.php?alpha=j>J</a> |
<a href=allprods.php?alpha=k>K</a> |
<a href=allprods.php?alpha=l>L</a> |
<a href=allprods.php?alpha=m>M</a> |
<a href=allprods.php?alpha=n>N</a> |
<a href=allprods.php?alpha=o>O</a> |
<a href=allprods.php?alpha=p>P</a> |
<a href=allprods.php?alpha=q>Q</a> |
<a href=allprods.php?alpha=r>R</a> |
<a href=allprods.php?alpha=s>S</a> |
<a href=allprods.php?alpha=t>T</a> |
<a href=allprods.php?alpha=u>U</a> |
<a href=allprods.php?alpha=v>V</a> |
<a href=allprods.php?alpha=w>W</a> |
<a href=allprods.php?alpha=x>X</a> |
<a href=allprods.php?alpha=y>Y</a> |
<a href=allprods.php?alpha=z>Z</a> <br> 
<a href=allprods.php?alpha=1>1</a> |
<a href=allprods.php?alpha=2>2</a> |
<a href=allprods.php?alpha=3>3</a> |
<a href=allprods.php?alpha=4>4</a> |
<a href=allprods.php?alpha=5>5</a> |
<a href=allprods.php?alpha=6>6</a> |
<a href=allprods.php?alpha=7>7</a> |
<a href=allprods.php?alpha=8>8</a> |
<a href=allprods.php?alpha=9>9</a> |
<a href=allprods.php?alpha=0>0</a> |
<a href=allprods.php>LIST ALL PRODUCTS</a> 
</td></tr></table>

The query

// added alpha page selections to query
       if (!is_null($_GET['alpha'])) $addregex = "pd.products_name REGEXP '^[".strtoupper($_GET['alpha'])."!".strtolower($_GET['alpha'])."].*$' and ";

// added alpha page selections to query as well as picking up the descriptions 
// and not showing hidden categories items
       $products_query = tep_db_query("select p.products_id, p.products_model ,pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p , " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where ".$addregex." p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and c.cshow > '0' and products_status = '1' order by pd.products_name");

to see a working version

All Products : H2O Pool Products

Please note that we have made other modifications to the allprods that increase the way that the items get picked up and processed by google (these are all configurable and no, this code is not released to the public domain as we do share and release, but not our SEO stuff since it's what our clients pay for).

[basnyd]

Hi -

I've gotten it work for allprods. I'm now trying for product listing. I have the navigation showing. My questions are:

1) Where do I put the query? I only see a query for "specials" and a query for "get all product prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0"

2) How do I modify the navigation links? I'm guessing they should be something like this, but I'm not sure what variable goes to the right of cname.

<a href=index.php?cname=xxxx?alpha=a>

Thanks,
Barb

[basnyd]

Ok, I believe to have the correct navigational links:

<a href=index.php?cName=<?php echo $cName?>?alpha=a>A</a> |

Now I just need to figure out where the query that needs to be modified is. Can someone point me in the direction of the query that is used to to build the product listing page?

Thanks,
Barb

[basnyd]

Based upon another post, I believe I should be looking in the index.php for the query I need to modify that generates this page - Flag Pins : osCMax v2.0 Am I correct?

Can someone point me to the correct query? There are several on the page and so far I haven't figured out the correct one.

[basnyd]

I'm making progress. I've isolated the query in the index.php file that needs to be modified:

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

So far all modifications that I've made don't yield the right result. I've used the query for the allprod page (see the query met00 posted earlier) as my guide, but so far have been unsuccessful.

Any suggestions would be greatly appreciated.

[basnyd]

I got it working! Here it is:

In the catalog\includes\modules\product_listing.php add this where you want the navigation to be:

<table width="100%" border="0" cellspacing="2" cellpadding="1">
<tr><td class=main align=center>
<a href=index.php?cName=<?php echo $cName?>&alpha=a>A</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=b>B</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=c>C</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=d>D</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=e>E</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=f>F</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=g>G</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=h>H</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=i>I</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=j>J</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=k>K</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=l>L</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=m>M</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=n>N</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=o>O</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=p>P</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=q>Q</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=r>R</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=s>S</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=t>T</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=u>U</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=v>V</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=w>W</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=x>X</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=y>Y</a> |
<a href=index.php?cName=<?php echo $cName?>&alpha=z>Z</a><br /><br />
</td></tr></table>

In catalog\index.php modifying the existing query to read (towards end of file):

} else { // either retail or no need to get correct special prices
// LINE CHANGED: MS2 update 501112
// added alpha page selections to query
if (!is_null($_GET['alpha'])) $addregex = "pd.products_name REGEXP '^[".strtoupper($_GET['alpha'])."!".strtolower($_GET['alpha'])."].*$' and ";

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where ".$addregex." p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
} // end else { // either retail...
// EOF: MOD - Separate Pricing Per Customer
}
}

I hope this helps others.
Barb

[basnyd]

Hi -

Is it possible to place an if statement around the navigational code in the catalog\includes\modules\product_listing.php file, so that the alphabetical code is only shown for particular categories?

For example, the alphabetical code is necessary for the following categories:

Indoor flags (both sizes)
Outdoor flags (all sizes)
Mini flags
State flags (all sub categories)
Decals (both sizes)
Pins

It is not necessary for the following categories:

Bases
US flags
Poles
Individual countries under "By Country"

If a person clicks on the alphabetical links in one of these categories they are told no products are found.

Is there a way for me to test which category is being displayed and then only display the navigational links in the categories that need it?

Thanks,
Barb

[basnyd]

I'm working on the if statement to display the alphabetical links only within certain categories. I found each category has a categories_id set in the categories table. How can I reference this in an if condition?

I tried this, but it doesn't work. I'm open to suggestions. I'm guessing I'm not referencing something properly.

<?php
if ($categories_id = '1') {
?>

I would like the if statement to work for multiple categories_id. For example for, 4 , 8 & 11.

Thanks,
Barb