跨数据库表行数据复制失败：新行空白无数据问题求助

尝试将旧数据库contacts表的客户数据复制到表结构相同的新数据库时，点击客户详情页按钮后，新数据库中会创建对应行，但该行所有字段为空。脚本运行无报错，var_dump显示客户ID传递正常，但$name等其他字段值为null。当前URL为.php?id=<?=$_GET['id']?>，若通过.php?name=<?=$contact['name']?>传递数据并改用$_GET['name']接收，可正常写入数据但存在严重安全风险。已开启PHP错误报告和PDO的ERRMODE_EXCEPTION模式，仍无报错信息。

现有代码如下：

<?php
session_start();
define('db_host','localhost');
// database username
define('db_user','user');
// database password
define('db_pass','');
// database name
define('db_name','newdatabase');
// database charset - change this only if utf8 is unsupported by your language
define('db_charset','utf8');

// var_dump($data->status);
// exit();

    $pdo = new PDO('mysql:host=' . db_host . ';dbname=' . db_name . ';charset=' . db_charset, db_user, db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $data = json_decode($_POST['array'], TRUE);


//$id = isset($_GET['id']) && !empty($_GET['id']) && $_GET['id'] != 'auto' ? $_GET['id'] : NULL;
//$name             =       $data->name;
$name             =       $_GET['name'];
$name       =     $data->name;
$email      =     $data->email;
$phone      =     $data->phone;
$title      =     $data->title;
$created        =     $data->created;
$status         =     $data->status;
$riskdv         =     $data->riskdv;
$nin            =     $data->nin;
$dob            =     $data->dob;
$nextofkin      =     $data->nextofkin;
$address        =     $data->address;
$refname        =     $data->refname;
$refagency      =     $data->refagency;
$refadd         =     $data->refadd;
$refemail       =     $data->refemail;
$refnum         =     $data->refnum;
$riskvoi        =     $data->riskvoi;
$riskar         =     $data->riskar;
$risksat        =     $data->risksat;
$riskpph        =     $data->riskpph;
$riskpmh        =     $data->riskpmh;
$refpos         =     $data->riskpos;
$mentalhealth   =     $data->mentalheath;
$hisdrug        =     $data->hisdrug;
$exoff      =     $data->exoff;
$learndif       =     $data->learndif;
$hisalc         =     $data->hisalc;
$fled       =     $data->fled;
$rousl      =     $data->rousl;
$trave      =     $data->trave;
$yplc       =     $data->yplc;
$psdpsd     =     $data->psd; 
$hivaids        =     $data->hivadids;
$hep            =     $data->hep;
$phyneeds       =     $data->phyneeds;
$mentneeds      =     $data->mentneeds;
$meds       =     $data->meds;
$subdose        =     $data->subdose;
$methmil        =     $data->methmil;
$alcdepend      =     $data->alcdepend;
$ivdrug         =     $data->ivdrug;
$onmeth         =     $data->onmeth;
$compdetox      =     $data->compdetox;
$drugdepend     =     $data->drugdepend;
$dip            =     $data->dip;
$attcdp     =     $data->attcdp;
$heroin         =     $data->heroin;
$canna      =     $data->canna;
$meth       =     $data->meth;
$coc            =     $data->coc;
$crack      =     $data->crack;
$solvents       =     $data->solvents;
$amph       =     $data->amph;
$spice      =     $data->spice;
$premed         =     $data->premed;
$halluc         =     $data->halluc;
$subu       =     $data->subu;
$alclevel       =     $data->alchevel;
$otherinfo      =     $data->otherinfo;
$btype      =     $data->btype;
$bamount        =     $data->bamount;
$bstartdate     =     $data->bstartdate;
$bnextpay       =     $data->bnextpay;
$padd1      =     $data->padd1;
$padd2      =     $data->padd2;
$preoff         =     $data->preoff;
$pendcase       =     $data->pendcase; 
$precon     =     $data->precon; 
$onprob         =     $data->onprob;
$shop       =     $data->shop;
$cook       =     $data->cook;
$money      =     $data->money;
$cleaning       =     $data->cleaning;
$perh       =     $data->perh;
$housing_benefit_claim_date         =     $data->housing_benefit_claim_date;
$housing_benefit_reference      =     $data->housing_benefit_reference; 
$housing_benefit_cancelled      =     $data->housing_benefit_cancelled; 
$date_left_left         =     $data->date_left_left;
$number_of_times_at_reflections         =     $data->number_of_times_at_reflections;
$amount_of_service_charge_owed      =     $data->amount_of_service_charge_owed;
$reason_for_leaving         =     $data->reason_for_leaving;
$home_town      =     $data->home_town;
$town_moved_to  =     $data->town_moved_to;
$image      =     $data->image; 
$is_moved_in    =     $data->is_moved_in; 
$datemovedin    =     $data->datemovedin;
$gp_surgery     =     $data->gpsurgery; 
$rflfull        =     $data->rflfull;
$last_date_left     =     $data->last_date_left;
$med_cons       =     $data->med_cons;
$addinfo        =     $data->addinfo;
$compldetox     =     $data->compldetox;
$complprog      =     $data->complprog; 
$dietaryreq     =     $data->dietaryreq;


    // Update the record

    $stmt = $pdo->prepare('INSERT INTO contacts VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

$result = $stmt->execute([null, $name, $email, $phone, $title, $created, $status, $riskdv, $nin, $dob, $nextofkin, $address, $refname, $refagency, $refadd, $refemail, $refnum, $riskvoi, $riskar, $risksat, $riskpph, $riskpmh, $refpos, $mentalhealth, $hisdrug, $exoff, $learndif, $hisalc, $fled, $rousl, $trave, $yplc, $psd, $hivaids, $hep, $phyneeds, $mentneeds, $meds, $subdose, $methmil, $alcdepend, $ivdrug, $onmeth, $compdetox, $drugdepend, $dip, $attcdp, $heroin, $canna, $meth, $coc, $crack, $solvents, $amph, $spice, $premed, $halluc, $subu, $alclevel, $otherinfo, $btype, $bamount, $bstartdate, $bnextpay, $padd1, $padd2, $preoff, $pendcase, $precon, $onprob, $shop, $cook, $money, $cleaning, $perh, $housing_benefit_claim_date, $housing_benefit_reference, $housing_benefit_cancelled, $date_left_left, $number_of_times_at_reflections, $amount_of_service_charge_owed, $reason_for_leaving, $home_town, $town_moved_to, $image, $is_moved_in, $datemovedin, $gp_surgery, $rflfull, $last_date_left, $med_cons, $addinfo, $compldetox, $complprog, $dietaryreq]);

     echo "Entry added into Vader!";
var_dump($name);
     
?>

1. JSON解码访问方式错误：代码中$data = json_decode($_POST['array'], TRUE);第二个参数为TRUE，将JSON解码为关联数组，但后续用$data->name的对象属性方式访问，导致所有字段值为null。
2. 变量覆盖冗余：先给$name = $_GET['name'];赋值，随后又用$data->name;覆盖，进一步导致值丢失。
3. 未利用ID拉取旧库数据：当前代码没有用$_GET['id']从旧数据库查询完整客户数据，而是依赖前端可能未正确传递的$_POST['array']参数。
4. INSERT语句不规范：未指定字段列表的INSERT语句，表结构变动时极易出错，且维护难度高。

步骤1：修正数据访问语法 #

如果前端确实传递了$_POST['array']，将对象访问改为数组访问：

// 替换所有$data->xxx为$data['xxx']，同时添加空值处理
$name = $data['name'] ?? null;
$email = $data['email'] ?? null;
// ... 其他字段同理

步骤2：通过ID从旧库拉取数据（推荐安全方案） #

直接用URL传递的id从旧数据库查询完整数据，避免前端传递敏感信息：

// 连接旧数据库
$old_pdo = new PDO('mysql:host=' . db_host . ';dbname=olddatabase;charset=' . db_charset, db_user, db_pass);
$old_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$id = $_GET['id'] ?? null;
if (!$id) {
    die('客户ID缺失');
}

// 查询旧库对应客户数据
$stmt = $old_pdo->prepare('SELECT * FROM contacts WHERE id = ?');
$stmt->execute([$id]);
$contact = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$contact) {
    die('未找到对应客户数据');
}

步骤3：重构INSERT语句，指定字段列表 #

// 列出contacts表所有字段（按表结构顺序）
$fields = [
    'name', 'email', 'phone', 'title', 'created', 'status', 'riskdv', 'nin', 'dob', 
    'nextofkin', 'address', 'refname', 'refagency', 'refadd', 'refemail', 'refnum', 
    'riskvoi', 'riskar', 'risksat', 'riskpph', 'riskpmh', 'refpos', 'mentalhealth', 
    'hisdrug', 'exoff', 'learndif', 'hisalc', 'fled', 'rousl', 'trave', 'yplc', 'psd', 
    'hivaids', 'hep', 'phyneeds', 'mentneeds', 'meds', 'subdose', 'methmil', 'alcdepend', 
    'ivdrug', 'onmeth', 'compdetox', 'drugdepend', 'dip', 'attcdp', 'heroin', 'canna', 
    'meth', 'coc', 'crack', 'solvents', 'amph', 'spice', 'premed', 'halluc', 'subu', 
    'alclevel', 'otherinfo', 'btype', 'bamount', 'bstartdate', 'bnextpay', 'padd1', 
    'padd2', 'preoff', 'pendcase', 'precon', 'onprob', 'shop', 'cook', 'money', 'cleaning', 
    'perh', 'housing_benefit_claim_date', 'housing_benefit_reference', 'housing_benefit_cancelled', 
    'date_left_left', 'number_of_times_at_reflections', 'amount_of_service_charge_owed', 
    'reason_for_leaving', 'home_town', 'town_moved_to', 'image', 'is_moved_in', 'datemovedin', 
    'gp_surgery', 'rflfull', 'last_date_left', 'med_cons', 'addinfo', 'compldetox', 
    'complprog', 'dietaryreq'
];

// 构建INSERT语句
$placeholders = implode(', ', array_fill(0, count($fields), '?'));
$stmt = $pdo->prepare("INSERT INTO contacts (" . implode(', ', $fields) . ") VALUES ($placeholders)");

// 提取对应字段值
$values = array_map(function($field) use ($contact) {
    return $contact[$field] ?? null;
}, $fields);

$result = $stmt->execute($values);

步骤4：清理冗余代码 #

删除$name = $_GET['name'];等无用赋值语句，确保变量来源唯一。

步骤5：调试辅助 #

添加调试代码确认数据来源：

// 调试POST/GET数据
var_dump($_POST, $_GET);
// 调试旧库查询结果
var_dump($contact);
exit;

<?php
session_start();
define('db_host','localhost');
define('db_user','user');
define('db_pass','');
define('db_name','newdatabase');
define('db_charset','utf8');

// 连接新数据库
$pdo = new PDO('mysql:host=' . db_host . ';dbname=' . db_name . ';charset=' . db_charset, db_user, db_pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 连接旧数据库
$old_pdo = new PDO('mysql:host=' . db_host . ';dbname=olddatabase;charset=' . db_charset, db_user, db_pass);
$old_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 获取客户ID
$id = $_GET['id'] ?? null;
if (!$id) {
    die('客户ID缺失');
}

// 查询旧库客户数据
$stmt = $old_pdo->prepare('SELECT * FROM contacts WHERE id = ?');
$stmt->execute([$id]);
$contact = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$contact) {
    die('未找到对应客户数据');
}

// 定义表字段列表
$fields = [
    'name', 'email', 'phone', 'title', 'created', 'status', 'riskdv', 'nin', 'dob', 
    'nextofkin', 'address', 'refname', 'refagency', 'refadd', 'refemail', 'refnum', 
    'riskvoi', 'riskar', 'risksat', 'riskpph', 'riskpmh', 'refpos', 'mentalhealth', 
    'hisdrug', 'exoff', 'learndif', 'hisalc', 'fled', 'rousl', 'trave', 'yplc', 'psd', 
    'hivaids', 'hep', 'phyneeds', 'mentneeds', 'meds', 'subdose', 'methmil', 'alcdepend', 
    'ivdrug', 'onmeth', 'compdetox', 'drugdepend', 'dip', 'attcdp', 'heroin', 'canna', 
    'meth', 'coc', 'crack', 'solvents', 'amph', 'spice', 'premed', 'halluc', 'subu', 
    'alclevel', 'otherinfo', 'btype', 'bamount', 'bstartdate', 'bnextpay', 'padd1', 
    'padd2', 'preoff', 'pendcase', 'precon', 'onprob', 'shop', 'cook', 'money', 'cleaning', 
    'perh', 'housing_benefit_claim_date', 'housing_benefit_reference', 'housing_benefit_cancelled', 
    'date_left_left', 'number_of_times_at_reflections', 'amount_of_service_