come in! friend who is familiar with XCART

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lucent918
Forum Newbie
Posts: 1
Joined: Sat Jan 28, 2006 1:48 am

come in! friend who is familiar with XCART

Post by lucent918 »

i tested XCART this software on local machine, there is a problem which i had not been solved,

INVALID SQL: 1054 : Unknown column 'xcart_products.productid' in 'on clause'

SQL QUERY FAILURE:

Code: Select all

SELECT 
   COUNT(xcart_products.productid), 
   MIN(xcart_pricing.price) as price , 
   xcart_products_lng.product as product_lng, 
   xcart_products_lng.descr as descr_lng, 
   xcart_products_lng.full_descr as fulldescr_lng, 
   IF(xcart_variants.variantid IS NOT NULL,'Y','') as is_variant, 
   IF(xcart_classes.classid IS NOT NULL,'Y','') as is_product_options, 
   MIN(v_pricing.price) as v_price 
FROM 
   xcart_products, 
   xcart_pricing ,
   xcart_featured_products, 
   xcart_products_categories, 
   xcart_categories 
LEFT JOIN xcart_products_lng ON 
   xcart_products_lng.productid = 
   xcart_products.productid AND 
   xcart_products_lng.code = 'US' 
LEFT JOIN xcart_classes ON 
   xcart_classes.productid = xcart_products.productid 
LEFT JOIN xcart_variants ON 
   xcart_variants.productid = xcart_products.productid 
LEFT JOIN xcart_pricing as v_pricing ON 
   v_pricing.variantid = xcart_variants.variantid AND 
   v_pricing.quantity = 1 AND 
   v_pricing.membership IN ('','')
WHERE 
   xcart_products.productid=xcart_featured_products.productid AND 
   xcart_featured_products.avail='Y' AND 
   xcart_featured_products.categoryid='0 AND 
   xcart_pricing.productid=xcart_products.productid AND 
   xcart_pricing.quantity=1 AND 
   xcart_pricing.membership IN ('','') AND 
   xcart_products.product_type <> 'C' AND 
   xcart_products.product_type <> 'B' AND 
   xcart_pricing.variantid = 0 AND 
   xcart_products_categories.productid=xcart_products.productid AND 
   xcart_products_categories.categoryid = xcart_categories.categoryid AND
   xcart_categories.membership IN ('','') AND 
   xcart_products.forsale='Y' 
GROUP BY 
   xcart_products.productid

I can not find out the location of this sql sentence so that i can not repair it, dear friend! can you help me? thanks so much!

Jcart | I formatted for you so people could actually read it.. I suggest you do the same next time.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

That sql error means that the table does not have that column, now I have no idea what xcart does (I'll go out on a limb here and say it's a shopping cart), did it come with an installer or instructions for setting up the mysql tables?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I don't think this is the problem, but your missing a quote on

Code: Select all

WHERE 
   xcart_products.productid=xcart_featured_products.productid AND 
   xcart_featured_products.avail='Y' AND 
   xcart_featured_products.categoryid='0 AND
... ts.categoryid='0 AND ...
tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

Post by tristanlee85 »

I keep getting this error as well. I can't figure it out. The column does exist in the database though.

It isn't all from the same file though. That's what is getting me. Maybe some of you might have more of an idea.

Code: Select all

[29-Apr-2006 02:29:23] SQL error: /xcart/home.php
SELECT COUNT(DISTINCT(xcart_products.productid)) FROM xcart_products, xcart_pricing , xcart_products_categories  LEFT JOIN xcart_products_lng ON xcart_products_lng.productid = xcart_products.productid AND xcart_products_lng.code = 'US'  WHERE xcart_pricing.productid=xcart_products.productid AND xcart_pricing.quantity=1 AND xcart_pricing.variantid = 0 AND xcart_pricing.membership IN ('','') AND xcart_products_categories.productid=xcart_products.productid  AND  xcart_products_categories.categoryid='2' AND (xcart_products_categories.main='Y' OR xcart_products_categories.main!='Y') AND xcart_products.forsale='Y'
1054 : Unknown column 'xcart_products.productid' in 'on clause'
-------------------------------------------------
[29-Apr-2006 02:58:58] SQL error: /xcart/product.php
SELECT xcart_classes.*, xcart_class_options.*, xcart_class_lng.class as class_lng, xcart_class_lng.classtext as classtext_lng, xcart_product_options_lng.option_name as option_name_lng FROM xcart_class_options, xcart_classes LEFT JOIN xcart_product_options_lng ON xcart_class_options.optionid = xcart_product_options_lng.optionid AND xcart_product_options_lng.code = 'US' LEFT JOIN xcart_class_lng ON xcart_classes.classid = xcart_class_lng.classid AND xcart_class_lng.code = 'US' WHERE xcart_classes.productid = '34' AND xcart_classes.classid = xcart_class_options.classid AND xcart_class_options.avail = 'Y' AND xcart_classes.avail = 'Y'
1054 : Unknown column 'xcart_class_options.optionid' in 'on clause'
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Would you mind taking the time to format your query so we can read it?
choppsta
Forum Contributor
Posts: 114
Joined: Thu Jul 03, 2003 11:11 am

Post by choppsta »

I have come across this when using MySQL 5.0 on my local machine. It's to do with how they've changed the precedence of table joins or something I didn't really understand, but the solution is to put brackets around your straight joins like so:

Code: Select all

FROM
   (xcart_products,
   xcart_pricing ,
   xcart_featured_products,
   xcart_products_categories,
   xcart_categories)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

SQL Questions :arrow: Databases.
tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

Post by tristanlee85 »

choppsta wrote:I have come across this when using MySQL 5.0 on my local machine. It's to do with how they've changed the precedence of table joins or something I didn't really understand, but the solution is to put brackets around your straight joins like so:

Code: Select all

FROM
   (xcart_products,
   xcart_pricing ,
   xcart_featured_products,
   xcart_products_categories,
   xcart_categories)
So I have to search through every file to change that?
matt1019
Forum Contributor
Posts: 172
Joined: Thu Jul 06, 2006 6:41 pm

Post by matt1019 »

tristanlee85 wrote:
choppsta wrote:I have come across this when using MySQL 5.0 on my local machine. It's to do with how they've changed the precedence of table joins or something I didn't really understand, but the solution is to put brackets around your straight joins like so:

Code: Select all

FROM
   (xcart_products,
   xcart_pricing ,
   xcart_featured_products,
   xcart_products_categories,
   xcart_categories)
So I have to search through every file to change that?
Yes, and they are telling you to do this because it will make YOUR life a lot easier if you have a nice and tidy formatting/style

TRUST ME ;)

-Matt
Post Reply