mysql insert escape

Question

I currently have this code

$main_cat = "Antiques-collectables";
$mcat = "0187-1443";
$sub_cat = "toys";


   mysql_query("
INSERT INTO categories
(id, main_cat, sub_cat, mcat)
VALUES
('', '$main_cat', '$sub_cat', '$mcat')
");

For some reasons the $mcat value is not stored properly . When I check it in the database it appears as "1" , "347" values etc ... only 1 or 3 digits value . I think that the "-" is interpreted by the sql engine as operator . Is there any way to escape it ? I also tried

$mcat = str_replace("-", "\-", $mcat);

but still doesn't work .

Answer 1

When you are inserting strings into the database, you need to make sure to use mysql_real_escape_string() on them. This prevents any unintentional problems, and also SQL injections which can really cause a lot of problems for your site.

You can, also, look at using prepared statements, which effectively eliminate this problem.

Answer 2

No, '-' is not a SQL operator when within single quotes (as you have it there).

The mcat column probably has the wrong data type. Is it a VARCHAR? To store what you've got there, it should be.

Answer 3

You didn't mention your data type of column in your table. I think datatype of mcat is numeric type.

Probably by changing mcat data type to string solve your problem.