I have a database with two columns, first column is individual name, second column contains hundreds of names separated by commas. The database has thousands of rows I need to populate a third new column with the total number of appearances of the first column name in the second column array of names in any row Here is simple example Count Calculated joe | jim, nancy, mary, peter | 2 peter | joe, nancy, mary | 1 mary | nancy, jim, joe, susie | 3 danny | mary, nick, john | 0 I normally do this with a spreadsheet countif statement but the file is large now and too slow. If you can create a php program I can run to calculate the count and add to database send me a pm with a price. Thanks
So... you have a relational database with no relation between the columns in a given row? You should rethink your db-design...
@PoPSiCLe And you should re-think your reading-skills. Men du er Norsk, og fra Bergen, regner med det forklarer ALT! The OP; this is a simple MySQL query. SELECT field1, field2, ROUND ( ( LENGTH(field2) - LENGTH( REPLACE ( field2, field1, ", ") ) ) / LENGTH(field1) ) AS count FROM tblname Code (markup): Enter that in phpmyadmin and it should instantly give you your results
Einheijar I am having the partial match problem you describe (not with above code) Short names are getting many false matches I need a solution to work with 100,000 records and each record may have 1000 names Countif works fine in spreadsheet, I need solution that will count in a reasonable time, prefer php and mysql but open to almost any suggestion Is there a solution?
Your database design is wrong. You do not store multiple pieces of data in the same column. Look up and learn Database Normalization and then fix your database accordingly.