1054 error

[thedoc420]

i searched and ill i got was some answer in another language... if someone could please tell me in english then it would greatly help. this is the error i get:

1054 - Unknown column 'p.products_id' in 'on clause'

 

SELECT s.specials_new_products_price, p.products_id, p.products_model, p.products_price, p.products_tax_class_id, pd.products_name FROM products p, products_description pd LEFT JOIN specials s ON p.products_id=s.products_id WHERE p.products_id = pd.products_id AND p.products_status = 1 AND pd.language_id = 1 ORDER BY pd.products_name

 

 

this is where i got the error http://factions.servegame.com/order/allprods.php

[kirikintha]

http://www.google.com/search?q=1054+-+Unkn...lient=firefox-a

 

Take your pic of answers!

[thedoc420]

that fixed some other issues i was having but it didnt fix the original issue i was having... my original issue was when i tried to access this link: http://factions.servegame.com/order/allprods.php

[kgt]

http://www.oscommerce.com/ext/update-20051113.html

 

Specifically, see the section on MySQL 5.0 Compatibility.

[thedoc420]

that didnt help any as i dont even have 213-233 lines in that file... oh well ill just disable it :(

[kirikintha]

Oh no you don;t have to! I can give you some code! LMK if you still need it!

[thedoc420]

i would really appreciate any suggestions :D

[kgt]

The problem with this query is the same problem described in the update.

 

SELECT s.specials_new_products_price, p.products_id, p.products_model, p.products_price, p.products_tax_class_id, pd.products_name FROM products p, products_description pd LEFT JOIN specials s ON pd.products_id=s.products_id WHERE p.products_id = pd.products_id AND p.products_status = 1 AND pd.language_id = 1 ORDER BY pd.products_name

 

allprods.php isn't a standard osCommerce file though, so there's only so much help that can be offered - we don't know what's in that code.

[kirikintha]

Ok, now I am presuming that there is a specific problem to this. Your SQL statement is valid for Mysql4, but because of the strict mode that mysql5 runs in, it will fail this otherwise valid sql.

 

BACKUP EVERYTHING 1ST!

 

In catalog/includes/configure.php

 

add this:

 

define('DB_MYSQLMODE', 'MYSQL40');

 

after:

 

define('DB_DATABASE', 'xxxxxxxxxxxx');

 

should look similar to this: (obviously the xxxx's are your variables)

 

// define our database connection
 define('DB_SERVER', '127.0.0.1'); // eg, localhost - should not be empty for productive servers
 define('DB_SERVER_USERNAME', 'xxxxxxxxxx');
 define('DB_SERVER_PASSWORD', 'xxxxxxxxxx');
 define('DB_DATABASE', 'xxxxxxxxx');
 define('DB_MYSQLMODE', 'MYSQL40');
 define('USE_PCONNECT', 'false'); // use persistent connections?
 define('STORE_SESSIONS', 'mysql'); // leave empty '' for default handler or set to 'mysql'

 

 

Now, in catalog/includes/functions/database.php

 

AND

 

catalog/admin/includes/functions/database.php

 

change this:

 

  function tep_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {
global $$link;

if (USE_PCONNECT == 'true') {
  $$link = mysql_pconnect($server, $username, $password);
} else {
  $$link = mysql_connect($server, $username, $password);
}

if ($$link) mysql_select_db($database);

return $$link;
 }

 

to this:

 

  function tep_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $sqlmode = DB_MYSQLMODE, $link = 'db_link') {
global $$link;

if (USE_PCONNECT == 'true') {
  $$link = mysql_pconnect($server, $username, $password);
  mysql_query("set sql_mode = '$sqlmode'");
} else {
  $$link = mysql_connect($server, $username, $password);
  mysql_query("set sql_mode = '$sqlmode'");
}

if ($$link) mysql_select_db($database);

return $$link;
 }

 

That will make sure that the server acts like a Mysql4 server.

 

If that does not work, we have an entirely different problem!

 

REMEMBER TO BACK UP!