error: 1054 - Unknown column 'products_seo_title' in 'field list'

[rob_illinois]

   I am getting this error message on a store when I click on any product details. I can add a product to the cart but can not see any details for any product. Can someone please help on how to fix this?

[♥raiwa]

Hello Rob @@Rob_illinois,

 

It seems you updated the file product_info.php and missed to update the database.

The database query of the product info file searches for the field "products_seo_title" and it doesn't exist in your database.

The easy fix would be to add trhis missing field to your database.

Run this sql script in your database:

ALTER TABLE `products_description` ADD `products_seo_title` VARCHAR(128) NULL DEFAULT NULL AFTER `products_seo_description`;

If you get also errors for missing fields: "products_seo_keywords" and "products_seo_description" add aslo these:

ALTER TABLE `products_description` ADD `products_seo_description` TEXT NULL;

and

ALTER TABLE `products_description` ADD `products_seo_keywords` VARCHAR(128) NULL;

However it would be interesting to know what exact you changed when the error appeared.

If you only updated product_info.php, then you would need to update also Admin/categories.php in order to be able to fill in something in these SEO fields.

 

regards

Rainer

[♥raiwa]

@@Rob_illinois,

 

sorry the first one should be only this:

ALTER TABLE `products_description` ADD `products_seo_title` VARCHAR(128) NULL;

[MrPhil]

What have you recently done to your store? Did you just add some sort of SEO add-on? If so, as @@raiwa suggested, you mostly likely forgot to update the database structure. Go back over the instructions very carefully, and make the required changes.

 

If you didn't install an add-on lately, there are several things that could have happened. Did you use to have some sort of SEO add-on, and removed it? If so, did you (or your host) just restore an old code backup that makes use of this add-on's code, but the database no longer supports it? Alternately, if you recently installed an add-on, and the store was working OK, did you (or your host) restore an old database backup that does not include the necessary changes? If that happened, you will need to manually make the changes to the database to bring it in sync with the code.

[rob_illinois]

   Thank you for the replies on this error.

 

       So here is what was done before this error showed up. I had some updates done to the website "on the home page and admin area, all was working 100% fine after updates were completed. These updates were done to an offline version of the site with database data that is now 6 months old. So in the 6 months the live site had a lot of new product adds and price changes. I needed to move these updates / changes that were made on the live site to the site offline so it was current. So I exported out tables with the data needed from the live site to do this, and then imported the tables from the live site into the offline site and it looked fine except for this error above when clicking on a product description.

 

 

Thanks

[MrPhil]

Adding products and changing prices will not change the structure (layout, schema) of the database. Your updates added new database fields, which apparently you have in one database and not the other. If I follow you correctly, your live site is older code (and database definitions), and it's an "offline" site that has all the updates. It sounds like when you exported data from the live site to the updated offline site, that you overwrote the newer tables with older tables. If you don't have a backup of the working offline store to restore, you'll need to compare database layouts (prior to the database export/import) and patch up the offline store. Then be careful about how you export old data and try to read it in (import it) into a newer database -- it sounds like you transferred not only the live site's data, but also its layout (schema). You ended up with old fashioned data and code that's expecting new data fields. Did the live site backup include commands to delete tables and redefine them? That would do it!

[rob_illinois]

   I deleted the following tables from the offline site and replaced them "Imported" with the exported table of the data from the live site. I did this table by table.

 

 

action_recorder
address_book
customers
customers_basket
customers_info
Manufactures
Manufactures_info
Orders
orders_products
order_status_history
Orders_total
Products
products_description
products_images
products_to_categories
Specials
usu_cache

[♥raiwa]

@@Rob_illinois,

 

Just run the sql I told you. It should fix the error.

[♥raiwa]

@@Rob_illinois,

 

Some additional information:

 

As I mentioned in the first answer you have  the database field "products_seo_title" missing in your database ( and maybe other fields too).

This happened because you added this field when you updated/added SEO feature to your local test store. Now when you deleted the table "products_description" from your test store, you removed the fields you added during your updates and didn't recover them because they were not yet added to the database of your live store. THis produced the problem explained by Phil.

 

Now we have the problem that you explained very nice what you did, but you still didn't tell us which exact updates you applied to your test store.

 

I guess there are 2 possibilities:

1. You are using osc 2.3.4 bootstrap and updated from the Gold or a previous EDGE version to the actual Edge version which includes all the SEO stuff.

2. You are using a previous version, any 2.3x standard version or bootstrap gold or older edge version and added the SEO Header tags reloaded add-on or an older version of that add-on.

 

In any above described options you added the now missing and maybe more fields to the database.

Probably at least the 3 changes I posted in my first answer. The best and most secure way is to apply again the database changes you did during the updates following the instructions of the add-on you installed.

 

Resume of recomendations how to better proceed for your scenario:

- Do not delete the whole table of your test store database.

- If you wish to update the data on the test store, you should only empty the data in the test store keeeping the structure. Then import the actual data from the live store.

 

However I would recomend you the following:

A.- Update your test store like you did (do ONLY one update/add-on installation at a time)

- Once you have it running ok, do a backup of all test store (files and database)

B.- Update your live store in the following order:

0. backup your live store files and database

1. apply sql database changes to the live store database (following the add-on instructions like you did in the test store)

2. upload new files to your live store

3. upload and replace modified files to your live store

4. install and configure new modules on your live store

5. check that all works like it should

6. backup files and database of your live store

 

Then you can update all database of your test store with that livestore backup to have all new products etc up to date on your test store.

 

BETTER DO 2 BACKUPS MORE THAN 1 LESS :rolleyes:

 

Recomendations for posting hep request here in the forum:

- tell us your exact oscommerce store version

- tell us the error like you did in the first post/title

- explain what you changed and what you did when the error appeared (like you did in the second and third post)

- be more specific: instead to say "I had some updates done to the website" say (example):

I installed the add-on "SEO HEader Tags reloaded":

http://addons.oscommerce.com/info/8864

 

Like this we all spent much less time to find out what happened and how to help you and you would get the exact solution probably in the first answer.

Nevermind, try it in the next post. :)

 

Best regards

Rainer

[rob_illinois]

Raiwa:  Thank you for the help. Looks like it fixed the issues with the product links. Still getting one when I click on the Manufactures though "see below".

 

 

1054 - Unknown column 'm.manufacturers_seo_title' in 'field list'

 

select m.manufacturers_seo_title, mi.manufacturers_seo_description, mi.manufacturers_seo_keywords from manufacturers m, manufacturers_info mi where m.manufacturers_id = '14' and m.manufacturers_id = mi.manufacturers_id

 

How do I fix this one?

[♥raiwa]

@@Rob_illinois,

ALTER TABLE `manufacturers_info` ADD `manufacturers_seo_title` VARCHAR(128) NULL;

[rob_illinois]

  Nope did not work. :(

[Dan Cole]

@@Rob_illinois

 

Looks like the wrong table was altered Rob but you might want to wait for Rainer to confirm and be sure to backup your database first...based on the query you posted I think he meant...

 

ALTER TABLE `manufacturers` ADD `manufacturers_seo_title` VARCHAR(128) NULL;

 

Dan

[rob_illinois]

Ok, I do backup before any update or change :)

 

  I reverted back to the back prior to the change Rainer gave, then tried yours above and still no change. Still getting the same 1054 error message.

[♥raiwa]

@@Rob_illinois, please post the error message you get.

[rob_illinois]

1054 - Unknown column 'mi.manufacturers_seo_description' in 'field list'

select m.manufacturers_seo_title, mi.manufacturers_seo_description, mi.manufacturers_seo_keywords from manufacturers m, manufacturers_info mi where m.manufacturers_id = '58' and m.manufacturers_id = mi.manufacturers_id

[♥raiwa]

@@Rob_illinois,

 

that's not the same message like the first, It searches now for mi.manufacturers_seo_description not m.manufacturers_seo_title.

It's strange that m.manufacturers_seo_title seems to be in the manufacturers table in your store and not in manufacurers_info table like in a standard 2.3.4 BS EDGE installation.

However try this now:

ALTER TABLE `manufacturers_info` ADD `manufacturers_seo_description` VARCHAR(128) NULL;
ALTER TABLE `manufacturers_info` ADD `manufacturers_seo_keywords` VARCHAR(128) NULL;

If there is still another error message, please post it.

[rob_illinois]

Rainer, Thanks that seems to have fixed it. No more errors on this now.

 

 

   I have another time wonder if you could help on.It is for the search feature on the site.

 

  This is the issue - On the front end the search is working fine and does a search by name and also by Products Model #. On the back end admin area the by name way works fine, but not by Products Model #. Can you please let me know where the code for this function is at for both the front end and admin area so I can do a compare and troubleshoot it?

 

 

Thank you

[♥raiwa]

@@Rob_illinois,

 

I checked and it works on my newest 2.3.4 edge version if I search in the filed on the categories/products page in Admin. If it doesn't work for you, you may altered something. As I already mentioned in a before post, it would be very helpful to tell us more details.On which page you searched, the exact model and add maybe a screenshot. If it really doesn't work it's not possible to say how to fix it without a deeper look on your store files and database, at least I have no idea.

[rob_illinois]

     Ok, I am not using the edge version now. I was running version 2.3.3 and upgraded it to 2.3.4. I thought there maybe a few php files I could look at. I was thinking some code could have been commented out by mistake during upgrade. :)

[♥raiwa]

@@Rob_illinois,

 

Just checked on 2.3.4 standard and it doesn't work for models in my test store. So I believe search by model is not implemented in that version for admin site.

[rob_illinois]

Ok, do you know where the code is located for the search feature for both the front end and admin area? I would like to take a look still to see the differences.

[♥raiwa]

@@Rob_illinois,

 

For admin its: admin/categories.php.

Compare with the version included in 2.3.4 Bootstrap Edge.

The front end search is not related to the admin search.

[♥raiwa]

@@Rob_illinois.

 

I was curious and checked the difference.

You need to add product_model to the search query like this:

 

In admin/categories.php find:

      $products_query = tep_db_query("select p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p2c.categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and pd.products_name like '%" . tep_db_input($search) . "%' order by pd.products_name");

and change to:

      $products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_quantity, p.products_image, p.products_price, p.products_ws_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p2c.categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and((pd.products_name like '%" . tep_db_input($search) . "%') || (p.products_model like '%" . tep_db_input($search) . "%')) order by pd.products_name");

[rob_illinois]

  Ok, I applied this update and did the search and it gave me this error -

 

1054 - Unknown column 'p.products_ws_price' in 'field list'

select p.products_id, pd.products_name, p.products_model, p.products_quantity, p.products_image, p.products_price, p.products_ws_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p2c.categories_id from products p, products_description pd, products_to_categories p2c where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and((pd.products_name like '%856538001022%') || (p.products_model like '%856538001022%')) order by pd.products_name