PHP/MySQL 将相同的数据插入到两个表中

Question

我有两个表单共享一些相同的列:NAME、EMAIL、NOMINEE、DEPT、RANK 和 TIMESTAMP我想将这些相同的列存储在一个名为:TEACHING_AWARD_ALL_NOMINATIONS 的新表中。

我想出了将每个数据存储到不同表中的代码，然后应该有一些代码将共享列存储到 TEACHING_AWARD_ALL_NOMINATIONS 表中。

我还没想出代码，求助!!!

$srr = array_map('mysql_escape_string', $_REQUEST);

if ($srr['NOMINATIONTYPE'] == 'STUDENT')
    $fields = Array('NAME', 'EMAIL', 'NOMINEE', 'DEPT', 'COURSE', 'YEARTERM', 'REQUIRED_FOR_MAJOR', 'MAJOR_LEARNING_OBJECTIVES', 'WHAT_EXTENT_INSTRUCTOR_HELP', 'RANK', 'RANK_COMMENT', 'TEXTBOX_1', 'TEXTBOX_2', 'TEXTBOX_3', 'TEXTBOX_4', 'TEXTBOX_5');
else if ($srr['NOMINATIONTYPE'] == 'FACULTY')
    $fields = Array('NAME', 'EMAIL', 'NOMINEE', 'DEPT', 'RANK', 'TEXTBOX_1', 'TEXTBOX_2', 'TEXTBOX_3');
else die('error: no nomination type');

foreach ($fields as $f)
    $$f = $srr[$f];

$qry = "INSERT INTO TEACHING_AWARD_".$srr['NOMINATIONTYPE']."_NOMINATIONS (";
    foreach ($fields as $f) $qry .= $f . ", ";
    $qry = substr($qry, 0, -2);
    $qry .= ") VALUES (";
    foreach ($fields as $f) $qry .= "'" . $$f . "', ";
    $qry = substr($qry, 0, -2);
    $qry .= ")";

$result = mysql_query($qry) or die('An error ocurred: '.mysql_error());

echo 'Success! Thank you for submitting your nomination.';

Answer 1

WARNING! As noted in comments your original code is using deprecated functions and suffers from potential security issues. Check out the refactored solution that is using PDO, and yes you should use PDO too (instead of your current approach).

查看下面的代码，未经测试但应该可以完成您的工作。如上所述，它使用 PDO - check the docs here

    // obviously, first set your connection parameters $DbHost, $DbName etc.

    //connect to mysql
    $dbh = new PDO("mysql:host=".$DbHost.";dbname=".$DbName, $DbUser, $DbPass, array(PDO::ATTR_PERSISTENT => true));
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbh->query('SET NAMES UTF8'); //assuming you use utf-8 encoding

    //provide values array
    $values = $YourValuesArray;

    //set field types
    $fields = array();
    $fieldsStudent = Array('NAME', 'EMAIL', 'NOMINEE', 'DEPT', 'COURSE', 'YEARTERM', 'REQUIRED_FOR_MAJOR', 'MAJOR_LEARNING_OBJECTIVES', 'WHAT_EXTENT_INSTRUCTOR_HELP', 'RANK', 'RANK_COMMENT', 'TEXTBOX_1', 'TEXTBOX_2', 'TEXTBOX_3', 'TEXTBOX_4', 'TEXTBOX_5');
    $fieldsFaculty = Array('NAME', 'EMAIL', 'NOMINEE', 'DEPT', 'RANK', 'TEXTBOX_1', 'TEXTBOX_2', 'TEXTBOX_3');
    $fieldsAll = array_intersect($fieldsStudent, $fieldsFaculty);

    //pick the proper field set or die
    $fields = ($srr['NOMINATIONTYPE'] == 'STUDENT') ? $fieldsStudent : $fieldsFaculty;
    if(empty($fields)) die('error: no nomination type');

    //set tables
    $table = 'TEACHING_AWARD_'.$srr['NOMINATIONTYPE'].'_NOMINATIONS';
    $tableAll = 'TEACHING_AWARD_ALL_NOMINATIONS';

    //construct fields string
    $strFields = implode(",", $fields);
    $strFieldsAll = implode(",", $fieldsAll);

    //construct the placeholders string
    $strIns = implode(",", array_map(function($item){ return ":".$item; }, $fields)); 
    $strInsAll = implode(",", array_map(function($item){ return ":".$item; }, $fieldsAll)); 

    //insert specific data 
    $sql = "INSERT INTO $table ($strFields) VALUES ($strIns)";
    $sth = $dbh->prepare($sql);

    //bind values to placeholders
    foreach ($fields as $f)
    {
        $sth->bindValue(':' . $f, $values[$f]);
    }

    $sth->execute();

    //insert all data
    $sql = "INSERT INTO $tableAll ($strFieldsAll) VALUES ($strInsAll)";
    $sth = $dbh->prepare($sql);

    //bind values to placeholders
    foreach ($fieldsAll as $f)
    {
        $sth->bindValue(':' . $f, $values[$f]);
    }

    $sth->execute();

编辑:

根据您的评论，要使用 PDO 选择一组 UNIONed 结果，您可以使用以下(未经测试的)代码:

    //connect to mysql
    $dbh = new PDO("mysql:host=".$DbHost.";dbname=".$DbName, $DbUser, $DbPass, array(PDO::ATTR_PERSISTENT => true));
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbh->query('SET NAMES UTF8'); //assuming you use utf-8 encoding

    $sql = "SELECT NAME, EMAIL, NOMINEE, DEPT, RANK, TIMESTAMP, 'STUDENT' AS TYPE FROM TEACHING_AWARD_STUDENT_NOMINATIONS 
    UNION 
    SELECT NAME, EMAIL, NOMINEE, DEPT, RANK, TIMESTAMP, 'FACULTY' AS TYPE FROM TEACHING_AWARD_FACULTY_NOMINATIONS";

    $sth = $dbh->prepare($sql);
    $sth->execute();

    //init result array
    $results = array();

    //fetch the results into an array
    while($row = $sth->fetch(PDO::FETCH_ASSOC))
        $results[] = $row;

    //show results or do whatever else you need
    print_r($results);