get id by using similar_text

msnhockey

I am assuming i need to use the similar text function for what i am trying to do..

what i am tring to do is get the player id from nhl_players table where it has the fields id, name and data

now i am trying to compare the player name from nhl_stats table with the name from nhl_players table and if a close match get the id and associate with a link to that player on the nhl stats page

i have never had to use this function before so i am looking for a little guidance on what i should do?

do i make to seperate queries and then compare them and if a match over a certain percentage it grabs the player id?

thanks in advance

bradgrafelman

I guess I'm confused on what the real-world problem such a solution would solve. Why would you need to compare names to see if they're a "close match" rather than an exact match?

What good is showing Jane Smith's stats when you look up player John Smith?

msnhockey

that would be the easy way, but the delima i have is this database that i got the user did it weird

for example

on this table nhl_players

he created so it has id, name, data
but with the name he put the players position in ( ) right after the name so when i try to do an exact match i dont get one.

the nhl_stats table is set up to have name, team,position, gp, g, a, pts, pim, etc...

this is why i think i may have to use that similar text function with say 90-95% the same

bradgrafelman

It sounds like the first thing you should do is fix the database design, since the person who originally designed had no business even going near a database in the first place.

First, since the team and position seem to already be in the stats table (does this make sense? I probably would have associated those attributes with a player inside the nhl_players table), you'd want to UPDATE the table to remove the " (position_name)" value appended to the players' names.

Then, you'd want to add a player_id column to the nhl_stats table, UPDATE all rows in the table to set the player_id column equal to (subquery: SELECT the player_id from the nhl_players table WHERE nhl_stats.username = nhl_players username), and finally remove the "name" column from the nhl_stats table.

If your DB engine supports foreign keys (e.g. InnoD😎, I'd recommend adding a foreign key relationship as well. If not, you can always do a final SELECT in the nhl_players table for any names containing '(' or ')' to see if you need to manually fix some rows. Likewise, if you initially add the player_id column to the nhl_stats table as a "NULL DEFAULT NULL" column type, you could then search the nhl_stats for any rows where that column is equal to NULL (meaning the subquery didn't work and you'll again need to manually fix those rows).

Once you get the database design properly fixed up, querying it for the data you want will be a breeze.

Ashley_Sheridan

I agree with bradgrafelman, you need to try and fix the database if you can. There is a solution, but it's likely to be very slow. You could do an exact match based on a substring of the field from the nhl_players table. the SUBSTRING function in MySQL works much the same way as it does with other languages.