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)

Thanks
joomlaAsked:
Who is Participating?
 
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
0
 
johanntagleCommented:
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.

0
 
johanntagleCommented:
I think there's an "Accept multiple solutions" option where you can spread the points however you like.  Thanks.
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.