MySQL2 Question

This might not be the right place to put this, if it's not I do
apologize, but I hope maybe someone here can at least point me in the
right direction.

I'm currently creating a module to download sports box scores from
websites (in HTML) that will insert the data into databases using regex
and loops so that the data can be further analyzed.

I ran into a problem with the names of some players having non letter
punctuation (commas, periods, apostrophes, dashes, etc...), which I
hadn't fully expected. I solved the issue of 'getting' the names with
the regex, but mysql rejects the inserts into a varchar field with this
non text characters. Right now my solution is to remove the non text
characters on the insert of the athletes name, but then the name is
stored improperly. I know PHP has an 'escape string' function that
handles these things automatically. Is there something built in to ruby
to handle something like this. If there isn't is there a way to write a
gsub that will replace these non text characters with a \ before them,
all at once?

i.e if it's a ",", ".", "-" replace it with \, , \. , \- or do i have to
do each one individually?

Hi,

This sounds like a MySQL problem. What happens when you execute the query directly from the command line?

I haven't really tried that yet, when typing things out i realized that since my query is written in ruby with the text inserts in 'single quotes' ('), the ' in a name like o'neal might throw it off.

Tonight I'll rewrite it with " instead of ' and see if it works, that might be it.

The bigger part was making sure I got all the information (and the regex for that required some tweaking)

This is my first major undertaking in ruby, and it's a 'side project' in that I work 50 hours a week already at smething else.

If you're just looking to replace characters for the MySQL query, use the "escape_string" method as described in the manual.

But you cannot "escape" all non-alphanumeric characters like the dot or the comma, because those don't have a special meaning in SQL. If that's actually your problem, it's definitely a problem of the database.

I know how to escape them in ruby but how do i escape them when i don't know for sure they are there.

Is there a way to have a gsub written that says if you find a . or a , or a ' that you replace it with \. or \, or \'. I thought maybe with a backreference, but that wouldn't work if there are multiple characters in the name, which is possible

Right now what I'm doing is just stripping out the 'non text' characters using the gsub ,but that' not ideal. I'd prefer to keep the last name as O'neal, as opposed to oneal.

I will try the double quote " instead of single quote ' tonight, if that's the solution I'll be a bit bummed.

As I already said, you cannot escape "." or ",". You just can't. There seems to be a misunderstanding of what escaping even is. The purpose of escaping is to turn special characters that have a certain meaning in the particular language into literal characters. You can escape quotes (which would otherwise be interpreted as string delimiters) or the backslash. But neither the dot nor the comma have a special meaning in Ruby or SQL.

So it might be a good idea to forget this approach and instead look for the actual problem. Not being able to insert a comma in a database is not normal. So either there's a general problem in how you do the queries in Ruby, or your MySQL/database is messed up.

But like I said: Forget the "gsub". That doesn't work and won't solve any problem.