SQL Problem

[md1]

I am receiving the following error when using search or listing by category

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select p.products_image, p.products_quantity, pd.products_name,

 

select count(select p.products_image, p.products_quantity, pd.products_name, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = \'1\' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = \'1\' and p2c.categories_id = \'57\' order by pd.products_name) as total

 

It appears to be replacing ' with \'

 

Regards

Mark

[jamescalvin]

I am receiving the following error when using search or listing by category

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select p.products_image, p.products_quantity, pd.products_name,

 

select count(select p.products_image, p.products_quantity, pd.products_name, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = \'1\' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = \'1\' and p2c.categories_id = \'57\' order by pd.products_name) as total

 

It appears to be replacing ' with \'

 

Regards

Mark

<{POST_SNAPBACK}>

 

You just gotta love those SQL errors. I just got rid of a 1064 error myself. My problem was different from yours though. TRY THIS BUG ARTICLE it might point you in the right direction

[satish]

I am receiving the following error when using search or listing by category

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select p.products_image, p.products_quantity, pd.products_name,

 

select count(select p.products_image, p.products_quantity, pd.products_name, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = \'1\' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = \'1\' and p2c.categories_id = \'57\' order by pd.products_name) as total

 

It appears to be replacing ' with \'

 

Regards

Mark

<{POST_SNAPBACK}>

 

Hello ,

Check your data base.While populatring your data base all those slashes appears to have been dumped.Rectify those.

/ are treated in a different manner in PHP and Mysql and your code didnt handled it.

 

Please paste teh db details of the relate dtable to answer further.

 

 

Cheers......

Satish

[md1]

I have checked my database and still have the problem. I still have the problem if I empty Products & Products Dedcriptions etc. It also seems to be a common problem so for example is I run reviews.php, or search, or a category listing I have the same problem.

 

I am not sure what you require for teh bd details but I hope the following helps.

 

Table Action Records Type Size Overhead

PRODCSV 33,286 MyISAM 2.9 MB -

address_book 12,060 MyISAM 1.2 MB -

address_book_to_customers 0 MyISAM 1.0 KB -

address_format 5 MyISAM 2.5 KB -

affiliate_affiliate 1 MyISAM 2.4 KB 212 Bytes

affiliate_banners 7 MyISAM 2.3 KB -

affiliate_banners_history 16 MyISAM 2.4 KB -

affiliate_clickthroughs 1 MyISAM 3.2 KB -

affiliate_news 1 MyISAM 2.0 KB -

affiliate_news_contents 1 MyISAM 4.0 KB -

affiliate_newsletters 1 MyISAM 2.1 KB -

affiliate_payment 1 MyISAM 2.0 KB -

affiliate_payment_status 2 MyISAM 3.0 KB -

affiliate_payment_status_history 0 MyISAM 1.0 KB -

affiliate_sales 1 MyISAM 2.1 KB -

banners 3 MyISAM 2.4 KB -

banners_history 743 MyISAM 26.1 KB -

categories 69 MyISAM 5.7 KB 228 Bytes

categories_description 69 MyISAM 9.2 KB 472 Bytes

configuration 238 MyISAM 44.0 KB -

configuration_group 17 MyISAM 3.1 KB -

counter 1 MyISAM 1.0 KB -

counter_history 0 MyISAM 1.0 KB -

countries 24 MyISAM 3.7 KB -

currencies 2 MyISAM 2.1 KB -

customers 10,530 MyISAM 1.2 MB -

customers_basket 3,452 MyISAM 182.6 KB 80 Bytes

customers_basket_attributes 0 MyISAM 1.0 KB -

customers_info 10,527 MyISAM 465.4 KB -

geo_zones 2 MyISAM 2.1 KB -

languages 1 MyISAM 3.0 KB -

manufacturers 3 MyISAM 3.1 KB 64 Bytes

manufacturers_info 0 MyISAM 1.0 KB -

newsletters 6 MyISAM 23.8 KB -

orders 7,790 MyISAM 2.9 MB -

orders_products 10,818 MyISAM 954.0 KB -

orders_products_attributes 0 MyISAM 1.0 KB -

orders_products_download 0 MyISAM 1.0 KB -

orders_status 11 MyISAM 3.3 KB -

orders_status_history 23,626 MyISAM 1.1 MB -

orders_total 22,279 MyISAM 1.6 MB -

paypalipn_temp_data 0 MyISAM 1.0 KB -

paypalipn_txn 0 MyISAM 1.0 KB -

products 13,469 MyISAM 1.2 MB -

products_attributes 186 MyISAM 10.4 KB -

products_attributes_download 1 MyISAM 2.0 KB -

products_description 13,469 MyISAM 44.1 MB -

products_expected 0 MyISAM 1.0 KB -

products_notifications 5,534 MyISAM 173.9 KB -

products_options 1 MyISAM 2.0 KB -

products_options_values 1 MyISAM 2.0 KB -

products_options_values_to_products_options 1 MyISAM 2.0 KB -

products_to_categories 40,036 MyISAM 907.9 KB -

reviews 13 MyISAM 2.7 KB 96 Bytes

reviews_description 13 MyISAM 7.8 KB 748 Bytes

reviews_extra 0 MyISAM 1.0 KB -

seip 54,568 MyISAM 1.7 MB -

sessions 6 MyISAM 3.3 MB 3,017 KB

sources 9 MyISAM 2.2 KB -

specials 1,909 MyISAM 133.6 KB -

tax_class 1 MyISAM 2.1 KB -

tax_rates 1 MyISAM 2.1 KB -

whos_online 6 MyISAM 585.3 KB 597,360 Bytes

zones 176 MyISAM 8.8 KB -

zones_to_geo_zones 4 MyISAM 2.1 KB -

65 table(s) Sum 264,998 -- 64.8 MB 3.5 MB

Check All / Uncheck All / Check overheaded With selected: Drop Empty Print view Check table Optimize table Repair table Analyze table

 

Regards

Mark

[md1]

I have been doing some debugging with echo in database.php as follows.

 

Does this mean anything to anyone.

 

echo " result = ";

$result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error());

echo $result;

 

This gives me the following echo output:-

 

result = Resource id #7 result = Resource id #8 result = Resource id #9 result = Resource id #13

 

Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /home/looking4/public_html/catalog/includes/functions/database.php:43) in /home/looking4/public_html/catalog/includes/functions/sessions.php on line 67

 

result = Resource id #15 result = 1 result = Resource id #22 result = 1 result = Resource id #28 result = Resource id #29 result = Resource id #31 result = Resource id #33 result = Resource id #38

 

result = Resource id #56

 

 

result = 1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select p.products_image, p.products_quantity, pd.products_name,

 

select count(select p.products_image, p.products_quantity, pd.products_name, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = \'1\' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = \'1\' and p2c.categories_id = \'47\' order by pd.products_name) as total

 

[TEP STOP]

 

result = Resource id #58 result = 1

 

Regards

Mark

[md1]

I am trying more debugging but I really do need some guideance from someone with knowledge of how this works.

 

If I echo $listing_sql from product_listing.php I get the following.

 

select distinct p.products_image, p.products_quantity, m.manufacturers_id, p.products_id, pd.products_name, 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 products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%fred%' or p.products_model like '%fred%' or m.manufacturers_name like '%fred%') ) order by pd.products_name1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select distinct p.products_image, p.products_quantity, m.manuf

 

select count(select distinct p.products_image, p.products_quantity, m.manufacturers_id, p.products_id, pd.products_name, 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 products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = \'1\' and p.products_id = pd.products_id and pd.language_id = \'1\' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like \'%fred%\' or p.products_model like \'%fred%\' or m.manufacturers_name like \'%fred%\') ) order by pd.products_name) as total

 

 

Can anyone help.

 

Regards

Mark

[md1]

I don't believe this is a data problem.

 

I now have empty reviews and reviews.description files bit when I run catalog/reviews.php I get the following.

 

Something is changing the SQL select statement but I don't know where to look. Any experts that coud point me in the right direction ?

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select r.reviews_id, rd.reviews_text, r.reviews_rating, r.date_

 

select count(select r.reviews_id, rd.reviews_text, r.reviews_rating, r.date_added, p.products_id, pd.products_name, p.products_image, r.customers_name from reviews r, reviews_description rd, products p, products_description pd where p.products_status = \'1\' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and p.products_id = pd.products_id and pd.language_id = \'1\' and rd.languages_id = \'1\' order by r.reviews_id DESC) as total

 

 

Regards

Mark