[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

comparing tables based on user names

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)

  • 2
  • 2
2 Solutions
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.

joomlaAuthor Commented:
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
I think there's an "Accept multiple solutions" option where you can spread the points however you like.  Thanks.
joomlaAuthor Commented:
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now