Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Help !!! Database Intergrity


Theodorb

Recommended Posts

Is there a tool to check the OScommerce MySQL database and tables for relational integrity? I seem to have alot of unconnected products and categories in my tables!

 

Has anyone built a MySQL scanner to pickuop all the orphins?

 

Thanks, I hope someone has an idea to help.

 

Theo.

Share your knowlege and you learn even more !

Link to comment
Share on other sites

Theo,

 

To my knowledge, MySQL doesn't support referential integrity constraints. I don't know of any external tool that exists to clean-up the orphans.

 

I did a search and didn't find anything. Perhaps someone can contribute a PERL or PHP script that does this? I don't know enough about it, or I would give it a go.

Link to comment
Share on other sites

I realize that MySQL doesn't do relational intergrity, if it did i would have the orphans.

 

I was hoping that someone had written a catalog checker that would scan the DB and identify the orphins or inconsistancies.

 

Still looking for help....

 

Thanx

Theo

Share your knowlege and you learn even more !

Link to comment
Share on other sites

SELECT 

 products.products_id, products_to_categories.categories_id 

FROM 

 products LEFT JOIN products_to_categories ON products.products_id = products_to_categories.products_id 

WHERE 

 products_to_categories.categories_id IS NULL

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Thanks Chris, now all I have to do is figure out where I can run this fine script. Being new to php and mysql could you posibibly give me a hint, how I can run it and get an onscreen display of the results?

 

thanks

 

Theo

Share your knowlege and you learn even more !

Link to comment
Share on other sites

Brian,

 

This SQL should will give you all products in your product table that do not have assigned categories.

 

I would suggest that you download phpMyAdmin (http://www.phpmyadmin.net/). Once you have it downloaded, you can browse through your tables manually, or you can use the "SQL" window to run the script I have provided. It will display the results, and you can sort it however you like.

 

phpMyAdmin is VERY easy to use. No shop owner should be without this tool.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

I agree phpMyAdmin is very useful. I've wondered if it would be possible to include it in oscommerce default install. Maybe have it as a tool in admin

Link to comment
Share on other sites

That would be somewhat redundant considering probably 99.99999% of all hosting accounts include it by default.

 

Wow! I'm in the .00001% bracket! I feel so special! :lol:

 

Seriously, guess i should look around at others.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...