Clearence Items Mod

Online Michael · #1 07-07-2005, 09:46 AM

Can someone please help me with a mod I'm trying to put together regarding Product Clearance items.

I have created a category called "Clearance" and have added several clearance products to it. What I would like to do is display some of those products on the home page and show them in random order.

So this is what I need... I need the following code in my clearance.php file to query the database for all products in the "Clearance" category and select 5 of those products in random order (oh, and btw, I have already completed the clearance.tpl file so I don't need any help there... just the php).

Here is the code I need to modify in my clearance.php file:

Code:

<? 

// the database query 
$query = "SELECT * FROM $sql_tbl[products],$sql_tbl[categories] WHERE forsale='Y' AND avail>0 AND $sql_tbl[categories].productid=$sql_tbl[products].productid AND categoryid='264' ORDER BY RAND() LIMIT 5 "; 

// give the product array to smarty to make it available sitewide. 
$specials = func_query($query); 
$smarty->assign("clearance",$clearance); 
?>

If I can get this mod to work, I'll post all code and instructions here for everyone to see and use.

Online Michael · #2 07-12-2005, 04:44 AM

Anyone?

ETInteractive.com · #3 07-12-2005, 06:51 AM

select * from xcart_products a, xcart_products_categories b
where a.forsale = 'Y'
and a.avail > 0
and a.productid = b.productid
and b.categoryid = '123'
order by Rand() limit 5

you need to use "xcart_product_categories" table, not categories.

Online Michael · #4 07-12-2005, 08:19 AM

Thank you ETInteractive.com

Well, IБ─≥ve done it. Finally! I said I would share, so here it is.

What does it do I hear you ask? ThisБ─╕

This mod searches a particular category and displays the contents at random on the home page. In my case, I have a Б─°ClearanceБ─² category that I wanted to highlight. This mod works great on x-cart 4.1.13.

I should also note that this mod is based on the Б─°SpecialsБ─² mod posted by funkydunk but is, by large, different because it only focuses on one single category instead of the entire database. The mod can be configured to display any number of products (currently set to 5) and you can nominate any category you like by simply modifying the product id (currently set to Б─°278Б─²).

I should also mention that this mod will display the following product information:

Product thumbnail (25 pixels wide)
Product name
Product list price (with a line running through it)
Product price

(Note: You will have to edit the clearance.tpl file (below) if you want to add, modify, or remove any of the above information)

Here is a an image of what it looks like in my store (currently under construction and closed... sorry).

[SCREENSHOT WAS REMOVED FROM HERE]

So without further delay, this is what you need to do:

Create a file called clearance.php in the xcart root directory and paste the following code into it. Save the file (check if you have write permissions first). Keep in mind that you will need to either nominate and existing category, by replacing 278 with your own, or create a new one. You don't have to call it "Clearance"... any name will do. Also, don't forget to add some products to it too. If you want a higher or lower limit than 5 just replace the 5 with the figure you want.

Code:

<? 

// the database query 
$query = "SELECT * FROM $sql_tbl[products],$sql_tbl[products_categories],$sql_tbl[pricing] WHERE forsale='Y' AND avail>0 AND categoryid=278 AND $sql_tbl[products_categories].productid=$sql_tbl[products].productid AND $sql_tbl[products].productid=$sql_tbl[pricing].productid ORDER BY RAND() LIMIT 5 "; 

// give the product array to smarty to make it available sitewide. 
$clearance = func_query($query); 
$smarty->assign("clearance",$clearance); 
?>

Now add the following line to your home.php file (once again, check you write permissions):

Code:

require $xcart_dir."/clearance.php";

Just after this:

Code:

define('OFFERS_DONT_SHOW_NEW',1);
require "./auth.php";

Now, in you welcome.tpl (or where ever else you want this mod to show) add the following code:

Code:

{include file="customer/main/clearance.tpl"}

Now, somewhere in the middle, add the following code to the skin1/customer/home_main.tpl file:

Code:

{elseif $main eq "clearance"}
{include file="customer/main/welcome.tpl"}

Now create a file called clearance.tpl in skin1/customer/main directory and paste the following code into it:

Code:

{* $Id: clearance.tpl,v 1.3.2.1 2004/11/16 11:15:01 max Exp $ *}
{if $clearance ne ""}
{capture name=clearance_list}
<TABLE cellpadding="0" cellspacing="2" border="0">
{section name=num loop=$clearance}
<TR>
<TD width="30">
{include file="product_thumbnail.tpl" productid=$clearance[num].productid image_x=25 product=$clearance[num].product}
</TD>
<TD>
{$clearance[num].product}

<S>{include file="currency.tpl" value=$clearance[num].list_price}</S>
Now:{include file="currency.tpl" value=$clearance[num].price}

</TD>
</TR>
{/section}
</TABLE>
{/capture}
{include file="dialog.tpl" title= "Clearance Items" content=$smarty.capture.clearance_list extra="width=100%"}
{/if}

That's it!

Enjoy!

Online Michael · #5 07-12-2005, 09:30 AM

Just one more thing, and it's optional too. If you want to include a link so the customer can see all the products in the Clearance category just add this line to the clearance.tpl file (remember to change the categoryid number to your own):

Code:

<Div align="center">See more...</Div>

Just below:

Code:

</TD>
</TR>
{/section}
</TABLE>

Now it's complete! \

shan · #6 07-12-2005, 10:29 AM

moved to custom mods

markwhoo · #7 07-17-2005, 03:05 PM

I am getting this error when I try to implement this mod.

Looked pretty easy and straight forward. I changed a small snippet in the code to get to my xcart_categories, but other than that, the only thing changed was the category Id.

Here is the error:

Quote:

INVALID SQL: 1052 : Column: 'categoryid' in where clause is ambiguous
SQL QUERY FAILURE: SELECT * FROM xcart_products,xcart_categories,xcart_pricing WHERE forsale='Y' AND categoryid=335 AND xcart_categories.productid=xcart_products.producti d AND xcart_products.productid=xcart_pricing.productid ORDER BY RAND() LIMIT 5

Here is the code in clearance.php:

Code:

<?

// the database query
$query = "SELECT * FROM 
$sql_tbl[products],$sql_tbl[categories],$sql_tbl[pricing] WHERE 
forsale='Y' AND avail>0 AND categoryid=335 AND 
$sql_tbl[categories].productid=$sql_tbl[products].productid AND 
$sql_tbl[products].productid=$sql_tbl[pricing].productid ORDER BY RAND() 
LIMIT 5 ";
 
// give the product array to smarty to make it available sitewide. 
$clearance = func_query($query); 
$smarty->assign("clearance",$clearance); 
?>

Any idea of what may be happening to me here?

I am trying to use this in an older 3.4.x branch version

Online Michael · #8 07-18-2005, 03:24 AM

Why did you change xcart_products_categories to xcart_categories?

You are having this problem because xcart_categories does not hold productid information... xcart_products_categories does. Look at your xcart_categories table and see if there is a column named productid in it (try using phpMyAdmin in C/Panel to do this). If you canБ─≥t find it, then the whole thing won't work. If you don't have an xcart_products_categories table, what you probably need to do is search for a similar table with a different name but it must have the productid info in it.

Unfortunately I don't have your version of x-cart so I am unable to be of great assistance. Perhaps someone else here in this forum that is more experience with your x-cart version, can suggest a solution. It definitely has something to do with what I suggested above so it won't take much to fix it.

Sorry I can't be of further assistance. Let me know how you went.

markwhoo · #9 07-18-2005, 10:30 PM

Thanks for the reply so soon.

I changed this to xcart_categories due to my not having the xcart_products_categories.

However, something you said about the dategory id AND product id gave me an idea and I looked at the xcart_categories and it does not have the product id, so I went to my xcart_products and it has the product id and category id.

So, saying that, I will upload the files once again and try this using xcart_products.

I bet this does it, I will just need to tweak a bit and try it out.

I will post if this does it or not to share.

If it does not, I hope to hear from someone else that knows. Looks like all of the info is there though, so I will give it a shot.

Thanks again

eaglemobiles · #10 07-22-2005, 07:28 PM

Thanks Online Michael for sharing this great mod, how display this code side menu.

Thanks