1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Buying Need php program to count matching records in mysql

Discussion in 'Programming' started by cerno, Nov 18, 2017.

  1. #1
    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.
    SEMrush
    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
     
    cerno, Nov 18, 2017 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,626
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #2
    So... you have a relational database with no relation between the columns in a given row? You should rethink your db-design...
     
    PoPSiCLe, Nov 18, 2017 IP
    sarahk likes this.
  3. cLogik

    cLogik Active Member

    Messages:
    157
    Likes Received:
    28
    Best Answers:
    0
    Trophy Points:
    90
    As Seller:
    100% - 0
    As Buyer:
    100% - 1
    #3
    @PoPSiCLe And you should re-think your reading-skills. Men du er Norsk, og fra Bergen, regner med det forklarer ALT! :D

    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 :)
     
    Last edited: Nov 18, 2017
    cLogik, Nov 18, 2017 IP
    Nigel Lew and StaSen like this.
  4. Einheijar

    Einheijar Well-Known Member

    Messages:
    537
    Likes Received:
    13
    Best Answers:
    3
    Trophy Points:
    115
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #4
    That almost works, sam will also locate instances of samuel and samson
     
    Einheijar, Nov 20, 2017 IP
  5. cerno

    cerno Active Member

    Messages:
    207
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    58
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #5
    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?
     
    Last edited: Nov 28, 2017
    cerno, Nov 28, 2017 IP
  6. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    5
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #6
    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.
     
    Benanamen, Nov 28, 2017 IP
  7. Sugavanas

    Sugavanas Active Member

    Messages:
    608
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    95
    As Seller:
    100% - 6
    As Buyer:
    75.0% - 3
    #7
    Hi, I have sent you a PM with the quotation for a php script.
     
    Sugavanas, Nov 28, 2017 IP