您的位置: 首页  >  技术问答  >  MYSQL，PHP：将记录从一个数据库插入另一个数据库

MYSQL，PHP：将记录从一个数据库插入另一个数据库

分类: 技术问答 • 2022-03-01 12:10:05

MYSQL，PHP：将记录从一个数据库插入另一个数据库

问题描述：

I have a necessity to insert some record from one table1 in database1 to another table2 in database2. So far I have this..

    $records_r = mysqli_fetch_assoc(mysqli_query($conn_r, "SELECT * FROM `export` WHERE ID < 100"));        

    $columns_r = implode(",",array_keys($records_r));
    $values_r = implode(",",array_values($records_r));          

    $import = mysqli_query($conn_i,"INSERT INTO NOTimport ($columns_r) values ($values_r)");
    if (!$import) {
    printf("Error: %s
", mysqli_error($conn_i));
    exit();}

It gives me the error:

Error: You have an error in your SQL syntax;

This is how the syntax looks:

INSERT INTO `NOTimport` ('xx,xx,xx,xx,xx,xx,xx,xx') values ('11,'11,E,2079,1931,xx,xx,x')

I am 99% sure that single quotes are causing the error, but why are there?

答

Without Using PHP you can use MySql Query Which Will Perform Insert Operation As:-

$columns_r='`name`,`class`';

mysqli_query($conn_i,"INSERT INTO `import` ({$columns_r}) select {$columns_r} from `export`");

答

import is a reserved word in MYSQL. So, you need to use backticks (``) around it in your query.

So rewrite as follows:

 $import = mysqli_query($conn_i,"INSERT INTO `import` ($columns_r) values ($values_r)");

答

As per your original post https://stackoverflow.com/revisions/31116693/1 and completely overwriting your original post without marking it as an edit:

You're using the MySQL import reserved word
https://dev.mysql.com/doc/refman/5.5/en/keywords.html

It needs to be wrapped in ticks

INSERT INTO `import` ($columns_r) values ($values_r)

or rename that table to something other than a reserved word.

Plus, $values_r may require to be quoted and depending on what's being passed through $columns_r, you may need to use ticks around that.

I.e.:

INSERT INTO `import` (`$columns_r`) values ('".$values_r."')

Even then, that is open to SQL injection.

So, as per your edit with these values values ('11,'11,E,2079,1931,xx,xx,x'), just quote the values since you have some strings in there. MySQL will differentiate between those values.

Escape your values:

$values_r = implode(",",array_values($records_r));
$values_r = mysqli_real_escape_string($conn_r, $values_r);

or $conn_i I'm getting confused as to which variable is which here. Be consistent if you're using the same db.

Edit:

As stated in comments by chris85, use prepared statements and be done with it.

相关推荐