Re: [PHP] Search/Replace in entire database?
At 9:17 PM -0700 6/14/09, Chris Payne wrote: Hi everyone, I am in the middle of creating an editor where you can search and replace on an individual column in a single table then I came across something I need to be able to do but not sure how. Is it posible (And if so please how :-) to search an entire database and all tables within a database and do a find/replace on keywords without having to specify each table/column within that table? The people I am working for have made some big changes and one of them is changing the names of one of their products, but this product name appears EVERYWHERE in many tables and in lots of different column names, and it would save so much time if I could do a single query that would just search EVERYTHING within the database. Thanks for any advice you can give me. Regards Chris Payne Chris: The problem you cite is exactly why one should use relational databases. If the people you are working for had done that, then you would have only to edit one table. The process works like this -- you have a product table that has an unique id with other information about each product, such as its name. However, every table that references that product does so via the product's id and not the product's name. As such, each look-up for the product name requires first to know the product's id and then pull out the name associated with that id. In any event, a little more work at the start saves tons later. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Search/Replace in entire database?
Hi Chris, MySQL introduced full - text indexing and searching capabilities back in version 3.23.23. The implementation is straightforward and easy to use — define a FULLTEXT index and use MATCH / AGAINST in the query. Consider this example: CREATE TABLE SOCIAL_EVENT ( EVENT_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, USER_ID INTEGER UNSIGNED NOT NULL, HEADLINE TEXT NOT NULL, EVENT_TEXT TEXT NOT NULL, EVENT_DATE TIMESTAMP NOT NULL, PRIMARY KEY (EVENT_ID), FOREIGN KEY (USER_ID) REFERENCES SOCIAL_USER(USER_ID), FULLTEXT INDEX (HEADLINE, EVENT_TEXT) ) ENGINE=MyISAM DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs AUTO_INCREMENT=0; Thanks. Caner 2009/6/15 Chris Payne > Hi everyone, > > I am in the middle of creating an editor where you can search and > replace on an individual column in a single table then I came across > something I need to be able to do but not sure how. > > Is it posible (And if so please how :-) to search an entire database > and all tables within a database and do a find/replace on keywords > without having to specify each table/column within that table? > > The people I am working for have made some big changes and one of them > is changing the names of one of their products, but this product name > appears EVERYWHERE in many tables and in lots of different column > names, and it would save so much time if I could do a single query > that would just search EVERYTHING within the database. > > Thanks for any advice you can give me. > > Regards > > Chris Payne > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
[PHP] Search/Replace in entire database?
Hi everyone, I am in the middle of creating an editor where you can search and replace on an individual column in a single table then I came across something I need to be able to do but not sure how. Is it posible (And if so please how :-) to search an entire database and all tables within a database and do a find/replace on keywords without having to specify each table/column within that table? The people I am working for have made some big changes and one of them is changing the names of one of their products, but this product name appears EVERYWHERE in many tables and in lots of different column names, and it would save so much time if I could do a single query that would just search EVERYTHING within the database. Thanks for any advice you can give me. Regards Chris Payne -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php