MySQL remove spaces before insert

I'm brand new to MySQL. What I'm trying to do doesn't seem all too difficult, but I can't figure it out.

I want to do 2 things, currently only one is working. First, I want to duplicate a new entry's value into another column on the same table. This works. Second, I want to look at this new entry and remove all spaces from this new, copied value, and replace them with _

This is what I have currently:

<?php

$con = mysql_connect("localhost","username","password");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}
mysql_select_db("testdb", $con);

$sql="INSERT INTO testtable (name, type, rating, name_copy) 
VALUES ('$_POST[name]','$_POST[type]','$_POST[rating]','$_POST[name]')";
mysql_query("UPDATE 'testt开发者_开发技巧able' SET 'name_copy' = REPLACE( 'name_copy', ' ', '_' )");
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
header( 'Location: http://dev.test.com/records.php' ) ;
mysql_close($con)

?>

Notice that to copy the name value, I'm posting it twice. Once to the name column, and once to the name_copy column. Not sure if this is the best way to copy the value. Essentially, the reason I'm trying to do this is so that I can then generate urls from these values by removing the spaces.

Any help is appreciated!

Thanks.

---

You are applying the UPDATE before the INSERT. Hence, your newly inserted row is never updated to strip out the spaces. Also, you should remove the single quotes around the column names.

---

What you should do is:

1. Enable proper PHP error handling. At least set error_handling(E_ALL) and write an error handler which spits the error out, in great detail, to a log file and causes the page to stop. Currently you are "Charging headlong into disaster" by completely ignoring the possibility of an error. Personally I would use PDO and set the PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION.

2. Understand SQL injection and how your page is vulnerable to it (see comments by others). When you understand this, you will probably want to use parameterised queries (PDO can do this - see my first point).

3. Try to decide, in your head, what you are really trying to do.

I think that it is sufficient to do the string replacements on variables in the page, then use those as parameters to the insert. It is not necessary to insert a row, then UPDATE it (or indeed, as you have tried to do, UPDATE every row in the table!)

---

mysql_query("UPDATE 'testtable' SET 'name_copy' = REPLACE( 'name_copy', ' ', '_' )");

Remove the single quotes from around the column name or change them to backticks:

mysql_query("UPDATE 'testtable' SET `name_copy` = REPLACE( 'name_copy', ' ', '_' )");

However I would suggest doing both during the insert or at least limiting the UPDATE query (in your solution it goes through all the rows on every insert).

$sql = "INSERT INTO testtable (name, type, rating, name_copy) VALUES ".
       "('$name','$type','$rating','".str_replace(' ', '_', trim($name))."')";

By the way, DON'T forget to escape the variables before inserting into the database! Search for it if you don't know how, it's a frequently asked question.

---

EDIT:

<?php

$con = mysql_connect("localhost","username","password");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}
mysql_select_db("testdb", $con);

$sql = "INSERT INTO testtable (name, type, rating, name_copy) VALUES ".
       "('$name','$type','$rating','".str_replace(' ', '_', trim($name))."')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
header( 'Location: http://dev.test.com/records.php' ) ;
mysql_close($con);

?>