Syntax error changing SQL select in index.php

[soulman99]

Hi,

 

I'm modifying my oscommerce site and have fallen foul of a syntax error for the last 24 hours that I can't resolve. I expect it is something very obvious but I have been staring at the code for so long I have become word blind :rolleyes:

 

The changes being made are to delete the products_description table and add some of the fields into the products table and also to delete some fields from the products table.

 

Then to change the code in index.php to reflect the deleted sql tables and fields, so far so good.

 

The original code line looks like this:

        $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 . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

And my hacked line like this:

          $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, from " . TABLE_PRODUCTS . " p, " . TABLE_MANUFACTURERS . " m and p.manufacturers_id = m.manufacturers_id " . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

The result of my brilliance is 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 'and p.manufacturers_id = m.manufacturers_id 11'' at line 1

 

select count(p.products_id) as total from products p, manufacturers m and p.manufacturers_id = m.manufacturers_id 11'

 

Any help in pointing out the fumbling mistakes to me would be oh so gratefully received as I have lost the will to live and shall now take up drinking.

Thank you,

Rand

[Guest]

Rand,

 

 

Ok, this should work (I Hope):

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_MANUFACTURERS . " and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id= '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

 

Chris

[soulman99]

Hi Chris,

 

Really appreciate you coming to help me out again :thumbsup:

 

Made the code changes as you suggested ( copy and paste ;) ) yet still my code throws a syntax error. I hate to think what I have messed up!

 

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 'and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id= '11'' at line 1

 

select count(p.products_id) as total from products p, manufacturers and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id= '11'

 

eof:

 

Added the 'table identifier' "m" to the code changes you made but still no joy.

 

Do you know of any useful utilities that are a 'php syntax checker' and are free , that might help me :lol:

 

Cheers,

Rand

[Guest]

See if this works

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_MANUFACTURERS . " m where p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

[soulman99]

Hi Brian,

 

That did the trick :D

 

If you have a moment could you explain (simply) what I was getting so wrong?

 

As you have probably guessed I'm not very experienced with either php or sql.

 

Many thanks,

Rand

[Guest]

Hi Brian,

 

That did the trick :D

 

If you have a moment could you explain (simply) what I was getting so wrong?

 

As you have probably guessed I'm not very experienced with either php or sql.

 

Many thanks,

Rand

In your original code you had 3 errors.

p.manufacturers_id, from

You do not need a " , " after the last select part before the from statement. So the above became

p.manufacturers_id from

 

This part

" . TABLE_MANUFACTURERS . " m and p.manufacturers_id = m.manufacturers_id

You needed to start the where part here. You had " and " instead. So that became

" . TABLE_MANUFACTURERS . " m where p.manufacturers_id = m.manufacturers_id

And this part

" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'

needed to be

and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'

[soulman99]

Thank you :thumbsup:

[Juto]

Hello Brian, It seems to me that you know sql syntax very well. So I wonder if you could help me with this:

 

$query = amDB::query("select * from ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS,TABLE_PRODUCTS_OPTIONS_VALUES,TABLE_PRODUCTS_OPTIONS

 

"where products_options_values_to_products_options.PRODUCTS_OPTIONS_VALUES_ID = products_options_values.PRODUCTS_OPTIONS_VALUES_ID

 

and products_options_values_to_products_options.PRODUCTS_OPTIONS_ID = products_options.PRODUCTS_OPTIONS_ID");

 

 

 

Which gave:

1) In the browser: There was an error while retrieving the URL: Internal Server Error

2)In the error_log: PHP Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

 

As you can se I have three tables which I need to query. I haven't got the slightest idea on how to do it.

 

Kind regards

Sara

[Guest]

Hello Brian, It seems to me that you know sql syntax very well.

Not at all. I fake it.

 

$query = amDB::query("select * from ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS,TABLE_PRODUCTS_OPTIONS_VALUES,TABLE_PRODUCTS_OPTIONS

 

"where products_options_values_to_products_options.PRODUCTS_OPTIONS_VALUES_ID = products_options_values.PRODUCTS_OPTIONS_VALUES_ID

 

and products_options_values_to_products_options.PRODUCTS_OPTIONS_ID = products_options.PRODUCTS_OPTIONS_ID");

I have no idea what you have going on there. Are you using osC 2.3 or less?

 

If so you could try something like

$query = tep_db_query("select * from ". TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " povtpo, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov, " . TABLE_PRODUCTS_OPTIONS . " po where povtpo.products_options_values_id = pov.products_options_values_id and povtpo.products_options_id = po.products_options_id");

I don't know if it is what or will give the results you are looking for.