1064 - You have an error in your SQL syntax

[native]

I am defining a lot of custom sql queries to display products meeting specific criteria (category, country of manufacture, etc.) and have been working on my first query all day. It works successfully in mysql, but when I try to run it with product_listing.php, I get the following error:

 

 

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_id, p.products_model, p.products_image, p.pro

 

select count(p.products_id) as total SELECT p.products_id, p.products_model, p.products_image, p.products_price, pd.products_name, pd.products_id, p2c.products_id, p2c.categories_id FROM products_to_categories p2c INNER JOIN products p ON p.products_id = p2c.products_id INNER JOIN products_description pd ON p2c.products_id = pd.products_id WHERE p2c.categories_id = 39

 

 

I am setting

$listing_sql="SELECT p.products_id, p.products_model, p.products_image, p.products_price, pd.products_name, pd.products_id, p2c.products_id, p2c.categories_id

FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c INNER JOIN " . TABLE_PRODUCTS . " p ON p.products_id = p2c.products_id

INNER JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON p2c.products_id = pd.products_id

WHERE p2c.categories_id = 39";

 

Then doing

<?php include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING); ?>

 

I believe the problem is related to split_page_results.php and have found the code there which creates the "select count(p.products_id) as total" statement, but am not sure why it isn't working.

 

Can anyone offer some suggestions?

[Guest]

start with the split pages fix

 

http://www.oscommerce.com/community/bugs,1605

[native]

Mark,

 

Thanks for your suggestion. I tried the fix and it didn't solve my problem.

[native]

I think the problem is in the "select count(p.products_id)" statement, however, I haven't changed anything (previous to applying the fix) in the split_page_results.php file, and it appears to work when called from other php programs so I didn't want to change anything in it. I must be doing something wrong but I don't see where my sql statement is any different than the ones in other php files.

[native]

THis problem is completely driving me crazy -- I can't figure it out! Part of it is my lack of understanding of PHP and the rest is my lack of knowing exactly how OSCommerce works . . .

 

I really need some help with this if anyone can offer it!

[native]

I am defining a lot of custom sql queries to display products meeting specific criteria (category, country of manufacture, etc.) and have been working on my first query all day. It works successfully in mysql, but when I try to run it with product_listing.php, I get the following error:

 

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_id, p.products_model, p.products_image, p.pro

 

select count(p.products_id) as total SELECT p.products_id, p.products_model, p.products_image, p.products_price, pd.products_name, pd.products_id, p2c.products_id, p2c.categories_id FROM products_to_categories p2c INNER JOIN products p ON p.products_id = p2c.products_id INNER JOIN products_description pd ON p2c.products_id = pd.products_id WHERE p2c.categories_id = 39

 

I am setting

$listing_sql="SELECT p.products_id, p.products_model, p.products_image, p.products_price, pd.products_name, pd.products_id, p2c.products_id, p2c.categories_id

FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c INNER JOIN " . TABLE_PRODUCTS . " p ON p.products_id = p2c.products_id

INNER JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON p2c.products_id = pd.products_id

WHERE p2c.categories_id = 39";

 

Then doing

<?php include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING); ?>

 

I believe the problem is related to split_page_results.php and have found the code there which creates the "select count(p.products_id) as total" statement, but am not sure why it isn't working.

 

Can anyone offer some suggestions?

 

 

 

I edited my select count statement and eliminated the second "select" statement,so that it reads:

SELECT count( p.products_id ) AS total

FROM products_to_categories p2c

INNER JOIN products p ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON p2c.products_id = pd.products_id

WHERE p2c.categories_id =39;

 

I ran it in the sql editor in phpmyadmin and it worked succesfully. So . . . I am not sure why or how the select count statement is getting messed up in split_page_results.php. Help?

[native]

I am defining a lot of custom sql queries to display products meeting specific criteria (category, country of manufacture, etc.) and have been working on my first query all day. It works successfully in mysql, but when I try to run it with product_listing.php, I get the following error:

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_id, p.products_model, p.products_image, p.pro

 

select count(p.products_id) as total SELECT p.products_id, p.products_model, p.products_image, p.products_price, pd.products_name, pd.products_id, p2c.products_id, p2c.categories_id FROM products_to_categories p2c INNER JOIN products p ON p.products_id = p2c.products_id INNER JOIN products_description pd ON p2c.products_id = pd.products_id WHERE p2c.categories_id = 39

I am setting

$listing_sql="SELECT p.products_id, p.products_model, p.products_image, p.products_price, pd.products_name, pd.products_id, p2c.products_id, p2c.categories_id

FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c INNER JOIN " . TABLE_PRODUCTS . " p ON p.products_id = p2c.products_id

INNER JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON p2c.products_id = pd.products_id

WHERE p2c.categories_id = 39";

 

Then doing

<?php include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING); ?>

 

I believe the problem is related to split_page_results.php and have found the code there which creates the "select count(p.products_id) as total" statement, but am not sure why it isn't working.

 

Can anyone offer some suggestions?

 

 

 

OK -- I am making a teeny bit of progress. It looks like the following:

 

$pos_from = strpos($this->sql_query, ' from', 0);

 

is not working. When I echo $pos_from the value is blank. I have modified my sqlstatement to make sure that it is all in lower case and I am still having the same problem.

[native]

OK -- I am making a teeny bit of progress. It looks like the following:

 

$pos_from = strpos($this->sql_query, ' from', 0);

 

is not working. When I echo $pos_from the value is blank. I have modified my sqlstatement to make sure that it is all in lower case and I am still having the same problem.

 

 

 

YIPPEE (KIND OF) . . .

 

Through trial and error, I found that the space before the word from was causing the strpos not to find it. I changed it to 'from ' and the query worked without an error and a value was returned. However, I have a different problem now -- even though the count shows 6 items were found there really are only 2 and they did not display -- but that is another problem, which I will have to research elsewhere.

 

Is the problem with the space before the word 'from' a bug?

[Guest]

depends how you compare the result of it.

 

$pos_from = strpos($this->sql_query, ' from', 0);

 

if you check the $pos_from make sure you use the === or !== operators for the false/true. I cannot see how you are using the $pos_from.

 

Is the this->sql_query basically the $listing_sql?

[native]

depends how you compare the result of it.

 

$pos_from = strpos($this->sql_query, ' from', 0);

 

if you check the $pos_from make sure you use the === or !== operators for the false/true. I cannot see how you are using the $pos_from.

 

Is the this->sql_query basically the $listing_sql?

 

 

 

 

I did not change $pos_from = strpos($this->sql_query, ' from', 0); at all in split_page_results.php -- I am trying to use the code as written if at all possible. And yes, "this->sql_query" gets set as $listing_sql in the beginning of split_page_results.php. So, basically, I did not change split_page_results.php (except for incorporating the fixes) and had a problem with the strpos as written until I moved the space from before the word ' from' to after (like 'from '). This is odd because I didn't have the problem with it not working when I did a query from search or advanced search.

 

I don't think that in this particular case, we would want to use the === or !== for false/true because we are trying to set $pos_from to the numeric (int) value of the strpos command (unless I am reading the program incorrectly).