mysql insert query in a php script is not working

Question

I have a php script that take data from a table and then try to insert the obtained data in a second table copy of the first one:

function copy_data($id,$mysql_conn){
 if($res=mysql_query("SELECT * from table1 WHERE id='".$id."'", $mysql_conn)){
       if($row=mysql_fetch_array($res)){
            $sql  ="INSERT INTO table2 (id, Field1, Field2) values('" . $row['id'] . "', '" . $row['Field1'] . "', '" . $row['Field2'] . "')";
            mysql_query($sql,$mysql_conn);
           }
}
}   
copy_data($id,$mysql_conn);// $id is id of the element I want to add

The insert query works fine but there is one case that makes an exception :one of the field contains a ' character, exp of a query that failed: INSERT INTO table2 (id, Field1, Field2) values ('12','Company', 'Kurt's Reifen-Shop') the exception comes from the ' character how to insert php variables that do contain this character.

The MySQL family of PHP is deprecated and support thereof will disappear. Please look into PDO or Mysqli. All answers currently fail to mention that! — chwcz7F
– chwcz7F, Commented Aug 21, 2014 at 9:09
mysql_query is an obsolete interface and should not be used in new applications and will be removed in future versions of PHP. A modern replacement like PDO is not hard to learn. If you're new to PHP, a guide like PHP The Right Way can help explain best practices. If you keep writing code like this, you will get into serious trouble. Re-writing it in PDO might be annoying, but it'll go a long way towards not having your site compromised. — tadman
– tadman, Commented Aug 21, 2014 at 9:23

Michael Wagner · Accepted Answer · 2014-08-21 09:08:49Z

1

You have to escape the data before insert them into $sql:

function copy_data($id,$mysql_conn){
 if($res=mssql_query("SELECT * from table1 WHERE id='".$id."'", $mysql_conn)){
       if($row=mysql_fetch_array($res)){
            $row['Field1'] = mysql_real_escape_string($row['Field1']);
            $row['Field2'] = mysql_real_escape_string($row['Field2']);
            $sql  ="INSERT INTO table2 (id, Field1, Field2) values('" . $row['id'] . "', '" . $row['Field1'] . "', '" . $row['Field2'] . "')";
            mysql_query($sql,$mysql_conn);
           }
}
}   
copy_data($id,$mysql_conn);// $id is id of the element I want to add

answered Aug 21, 2014 at 9:08

Michael Wagner

1,0381 gold badge8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

e-nouri Over a year ago

"This extension is deprecated as of PHP 5.5.0, and will be removed in the future." From PHP.net, Better to use PDO that provide the prepared query witch is way better secured than using mysql_real_escape_string() !

tadman Over a year ago

It's also obnoxious to have to write SQL code like this. PDO is ten times easier to write and even easier to read to check that you've done it correctly.

Naktibalda · Accepted Answer · 2014-08-21 09:10:57Z

1

You can do it with a single statement:

$id = mysql_real_escape_string($id);
INSERT INTO table2 (id, Field1, Field2) SELECT id, Field1, Field2 FROM table1 WHERE id='".$id."'"

answered Aug 21, 2014 at 9:10

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

Comments

Display name · Accepted Answer · 2014-08-21 09:11:08Z

0

i dont understand how you managed to put that ' in to the first table but you should use mysql_real_escape_string like $field1 = mysql_real_escape_string($row['Field1']); than put the $field1 as it will be safe now

answered Aug 21, 2014 at 9:11

Display name

4185 silver badges20 bronze badges

Collectives™ on Stack Overflow

mysql insert query in a php script is not working

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related