comparing tables based on user names

Posted on 2011-10-18
Last Modified: 2012-05-12
Working with MYSQL

TableA has a field called 'username'
contents of it is "Tyson, Mike"    (last name separated from first name by a coma)

TableB has two fields called 'firstname' & 'lastname'
content of each field  are 'Mike' & 'Tyson' respectively

I want to cycle through TableA looking for potential matches with TableB

Where the names can be matched  (Tyson, Mike in TableA     with     Mike Tyson in tableB)
I want to flag a field called Status in TableB as 2   (integer)

Where the person in TableA can't be matched
I want to flag a field called Status in TableA as 0   (integer)

Question by:joomla
    LVL 24

    Assisted Solution

    Given a "joomla" username I assume you can do some PHP coding?  If so try this:

    1.  If TableA is not MyISAM convert it or make a MyISAM copy of it.
    2.  Add a full text index to TableA

    create fulltext index ft_index_name on TableA (username);

    3.  Create a PHP or whatever your preferred script that calls "select primary_key_column, concat('+',firstname,' +', lastname) from TableB" (note the space before the second plus sign.  The primary_key_column is your identifier, replace with whatever, maybe firstname, lastname also).  The output column will look like '+Mike +Tyson).  The script will read through the rows, then call, for example:

    select count(*) from TableA where match(username) against ('+Mike +Tyson' in boolean mode);

    If the result is > 0 then you have a match.

    This should be more flexible than trying to match the exact "Tyson, Mike" format.


    Accepted Solution

    I'm afraid this was too complicated for us, so we had to get a contractor to fix.
    Can you advise how I resolve
    I'd like to award some points to johanntagle for attempting to help
    LVL 24

    Expert Comment

    I think there's an "Accept multiple solutions" option where you can spread the points however you like.  Thanks.

    Author Closing Comment

    I awarded 200 points to johanntagle for their participation.
    In the end it was too difficult for our level of experience and we got a contractor to do the work, so rather than award all points to the only particpant we awarded a portion

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now