7 replies to this topic

[Angelissa]

Hi,

I've just returned from holidays and having problems with my site, which is making me very stressed again I'm also not a technical person which isn't helping..

When I log in and go to Categories/Products, I get the following error:

[indent]1054 - Unknown column 'c.categories_id' in 'field list'

select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '0' order by c.sort_order, cd.categories_name

[TEP STOP][/indent]

I have spent the last 2 hours reading forum threads but can't seem to find someone with the exact same issue (although lots of similar ones). Given that the site was working find the past few months and I haven't changed anything, I'm wondering if my server has changed something. These are my server versions:

MySQL version 4.1.21-standard
PHP version 4.4.4

I also found my way to phpMyAdmin and can see that categories_description has a categories_id field.

I am at a loss for what to change without worsening the issue, but cannot access any categories or products on my site. I have spent countless hours on the site updating products and cannot afford to re-install. The site also appears to be missing the customers from the previous months and I'm not sure if this is related.

Please help as I'm getting quite desperate!

Thanks

[davidinottawa]

Angelissa, on Mar 6 2007, 05:58 AM, said:

When I log in and go to Categories/Products, I get the following error:
1054 - Unknown column 'c.categories_id' in 'field list'
select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '0' order by c.sort_order, cd.categories_name

Yikes.
Not good.

What happens when you log into phpMyAdmin and kick this statement off through your SQL window ?
select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '0' order by c.sort_order, cd.categories_name

Can you do this please ? Just to make sure you get the same error.

Quote

I also found my way to phpMyAdmin and can see that categories_description has a categories_id field.

Well - it's not complaining about the categories_description table - it's complaining about the categories table.
Go to phpMyAdmin and select your database, then select your categories table - you should see the structure of the table - and categories_id should be the first field.
If you're missing this - you'll have much larger issues to be dealing with!

Quote

I have spent countless hours on the site updating products and cannot afford to re-install. The site also appears to be missing the customers from the previous months and I'm not sure if this is related.

Database content "doesn't just go missing" - so something must've happened.
Did you install a new contrib that modifed the database ? Anything like that ?

Do you have a backup ? I know you don't want to hear my "back up speech" right now - but in all honesty this is Web 101 - you must ALWAYS backup your database, and your files. You backup schedules should be consistent with the number of times the products change within your store.

david

[Angelissa]

Hi David,

Thanks I really appreciate your help..

davidinottawa, on Mar 7 2007, 01:01 AM, said:

What happens when you log into phpMyAdmin and kick this statement off through your SQL window ?
select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '0' order by c.sort_order, cd.categories_name

Can you do this please ? Just to make sure you get the same error.

Yep - same error.

davidinottawa, on Mar 7 2007, 01:01 AM, said:

Well - it's not complaining about the categories_description table - it's complaining about the categories table.
Go to phpMyAdmin and select your database, then select your categories table - you should see the structure of the table - and categories_id should be the first field.
If you're missing this - you'll have much larger issues to be dealing with!

Eek

This is all that's in my categories table:

[indent]categories_image
sort_order
date_added
last_modified[/indent]

So it looks like I'm somehow missing all the fields that its looking for?

davidinottawa, on Mar 7 2007, 01:01 AM, said:

Database content "doesn't just go missing" - so something must've happened.
Did you install a new contrib that modifed the database ? Anything like that ?

It seems that the info is stored in the database, just not showing through the oscommerce frontend. I haven't installed anything or modified anything in the last few weeks. What could explain this? And how do I go about fixing it?

davidinottawa, on Mar 7 2007, 01:01 AM, said:

Do you have a backup ? I know you don't want to hear my "back up speech" right now - but in all honesty this is Web 101 - you must ALWAYS backup your database, and your files. You backup schedules should be consistent with the number of times the products change within your store.

Sadly, I don't know how to back up my database, which I know is a shameful excuse, the site's just come together without a lot of knowledge or advice.

[davidinottawa]

Angelissa, on Mar 6 2007, 10:42 PM, said:

This is all that's in my categories table:
[indent]
categories_image
sort_order
date_added
last_modified
[/indent]

Well - I don't even know what to say really.
Your category_id field is missing from that table - and OSC certainly cannot function without it.
What I don't get is what apps are accessing and using the category field.
If you go to your shop, and type in a URL with just the product_id - the prodcut is displayed - whether or not the category id is in the URL or not.

ie. this :
domain.com/shop/product_info.php?products_id=3
is ths same as this :
domain.com/shop/product_info.php?cPath=1_9&products_id=3

Sooooooooooooooooo - I'm not sure what to tell you.
First of all - BACK UP YOUR DATABASE.
Login to phpMyAdmin
Hit the Export hyperlink at the top of the page
Depending on your version you'll need to checkbox or radio button these 4 options :
Save as SQL
Save Date
Save Structure
Save As File
Once you've done this, hit Go and your file will start to be saved locally.

Now click the categories table - and click 'Add 1 Field' --> At Beginning of Table --> Go
Field name : categories_id
Type : int
Length : 11
Null : Not null
Default : auto_increment

Save.

It'll get rid of your error - but this doesn't mean that your cart will work!!

Quote

So it looks like I'm somehow missing all the fields that its looking for?

Well - there's only one missing from the categories table. I dunno about your categories_description table.

Quote

What could explain this? And how do I go about fixing it?

I really don't know how this would happen. Fixing would be difficult. HOw many products do you have stored ?
Ask your host if they have database backups ? and if so, if they can execute it into your database.

david

[Angelissa]

Ok I added in categories_id and it also needed parent_id and seems to have fixed the current issue, I also needed to add in all the categories again as they had disappeared too. Thank god everything else was still there.

Thankyou David for your help - the site seems to be back on track again.

Now, how to prevent this from happening again...

May seem like a silly question, but how do I change the password in OSC? It was set up for me and I haven't needed to change it..

[davidinottawa]

Angelissa, on Mar 7 2007, 06:38 PM, said:

May seem like a silly question, but how do I change the password in OSC? It was set up for me and I haven't needed to change it..

Downlaod the .htaccess file under the admin directory.
Open the file to see where the .htpasswd file is kept - then go download it.
Generate a new set of credentials using this : http://home.flash.net/cgi-bin/pw.pl
and dump the output into the .htpasswd file
then re-uplaod the .htpasswd file.
Be sure you are doing this with a *good* text editor! Like : http://editplus.com

Or, your control panel with your host should be able to provide a 'Password Protected Directory' link

[Angelissa]

Great - worked first go!

Thanks so much for the help David.

[kozak]

every time a work in the admin a make a databasebackup ! Now i got the same problem, i re-installed & restored (less than 5min) and everything is working properly