PHP MYSQL 将数据插入 2 个表 [英] PHP MYSQL insert Data into 2 tables

问题描述

嘿，我的插入 php 文件目前有问题

我有一个表单……用户在其中输入详细信息……作为我的表单……事件名称、事件日期和地点基本上是工作的位是首先，我想在 2 个表中添加一个条目:事件和结果

将条目添加到事件中没有问题

但它不会在结果"中添加相同的条目

事件表包含以下列:事件 ID、事件名称、事件日期和位置

结果表包含:事件 ID、成员 ID、事件名称、分数和地点

事件 ID 自动递增

所以它会自动为其分配一个 ID

并将其应用于两个表

Event ID 自动递增

起作用的是

将条目插入事件表

但是因为事件表和结果表都有事件名称"

我希望这个 php 完全插入事件表的详细信息

但同时，只需将事件名称插入结果表

但事件中的 EventID 必须与结果中的 EventID 生成的数字相同..

以下是我的代码:非常感谢所有帮助！！！

<代码>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$query = $pdo->query('SELECT EventID, EventName, EventDate, Location from events');$rowset = array();如果($查询){while ($row = $query->fetch(PDO::FETCH_ASSOC)) {//构建行数组$rowset[] = $row;}//先输出头$headrow = $rowset[0];print("<表格边框=\"1\">\n\n");//使用 $rowset[0] 写入表格标题foreach ($headrow as $col => $val) {printf("%s\n", $col);}打印(</tr>");//然后输出表格行.//外循环遍历行foreach ($rowset 作为 $row) {打印(")；//内部循环使用 $col => 遍历列$valforeach ($row as $col => $val) {//我们不知道您的列名，但在此处将第一列(ID)替换为 FIRSTCOLprintf("<td><a href=\"adminlistresults.php?EventID=%s\">%s</a></td>\n", $row['EventID'],$val);}打印(</tr>");}}打印("</table>");?></表单>

<?$pdo = new PDO('mysql:host=localhost;dbname=clubresults', 'root', '12345678');#将错误模式设置为 ERRMODE_EXCEPTION.$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$stmt=$pdo->prepare('INSERT INTO events (EventName, EventDate, Location)值(:事件名称，:事件日期，:位置)')；$stmt->bindValue(':EventName', $_POST['EventName']);$stmt->bindValue(':EventDate', $_POST['EventDate']);$stmt->bindValue(':Location', $_POST['Location']);$stmt->execute();?><?$int_event_id = $_GET["EventID"];if((int)$int_event_id){$stmt=$pdo->prepare('INSERT INTO results (EventName, EventID)VALUES (:EventResultsName, $int_event_id)');$stmt->bindValue(':EventName', $_POST['EventResultsName']);$stmt->execute();}?>

解决方案

如果插入总是按顺序进行，我会使用 $pdo->lastInsertId()(参见:http://www.php.net/manual/en/pdo.lastinsertid.php)

所以，我认为这一行是错误的:

$int_event_id = $_GET["EventID"];

我会这样写:

<代码>prepare('INSERT INTO 结果(事件名称、事件 ID)值 (:EventResultsName, :EventId)');$stmt->bindValue(':EventName', $_POST['EventResultsName']);$stmt->bindValue(':EventId', $pdo->lastInsertId());$stmt->execute();?>

请注意，这假设在插入到事件之后立即插入到结果中.

您可以使用 MySQL 的原生 last_insert_id() 函数在没有绑定变量的情况下完成同样的事情，如下所示:

<代码>prepare('INSERT INTO 结果(事件名称、事件 ID)值 (:EventResultsName, last_insert_id())');$stmt->bindValue(':EventName', $_POST['EventResultsName']);$stmt->execute();?>

然而，这不如前一个示例可移植.但是，pdo 的 lastInsertId() 也不完全是 RDBMS 不可知的(请参阅文档)，因此如果您想针对另一个 RDBMS，无论如何您都必须修复这段代码

Hey All I currently have a problem with my insert php file

I have a form.. where the user types in details.. for my form.. event name, event date and location basically the bit that is working is well first of all i would like to add an entry into 2 tables: events and results

it's having no problems adding the entry into events

but it doesnt add the same entry into "results"

the events table had the following columns: Event ID, Event Name, Event Date and Location

The Results table has: Event ID, Member ID, Event Name, Score and Place

The Event ID is auto increment

so it auto assigns an ID to it

and its applied to both tables

the auto increment in Event ID

the bit thats working is

inserting entry into the events table

but because the events table and results table both have "Event Name

I want this php to fully insert details for the event table

BUT also at the same time, just insert the eventname into the results table

but the EventID in events has to be the same generated number as EventID in results..

Below is my code: All help really appreciated!!!

<?

        $pdo = new PDO('mysql:host=localhost;dbname=clubresults', 'root', '12345678');
    #Set Error Mode to ERRMODE_EXCEPTION.
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  

$query = $pdo->query('SELECT EventID, EventName, EventDate, Location from events');
$rowset = array();

if ($query) {
  while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    // Build array of rows
    $rowset[] = $row;
  }    

  // Output header first
  $headrow = $rowset[0];
  print("<table border=\"1\">\n<tr>\n");
  // Use $rowset[0] to write the table heading
  foreach ($headrow as $col => $val) {
    printf("<th>%s</th>\n", $col);
  }
  print("</tr>");

  // Then output table rows.
  // Outer loop iterates over row
  foreach ($rowset as $row) {
     print("<tr>");
     // Inner loop iterates over columns using $col => $val
     foreach ($row as $col => $val) {
        // We don't know your column names, but substitute the first column (the ID) for FIRSTCOL here
        printf("<td><a href=\"adminlistresults.php?EventID=%s\">%s</a></td>\n", $row['EventID'],$val);
     }
     print("</tr>");
  }
}
print("</table>");
?>
    </form>
</div>




<?

        $pdo = new PDO('mysql:host=localhost;dbname=clubresults', 'root', '12345678');
    #Set Error Mode to ERRMODE_EXCEPTION.
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  

$stmt=$pdo->prepare('INSERT INTO events (EventName, EventDate, Location)
VALUES (:EventName, :EventDate, :Location)');


  $stmt->bindValue(':EventName', $_POST['EventName']);
  $stmt->bindValue(':EventDate', $_POST['EventDate']);
  $stmt->bindValue(':Location', $_POST['Location']);

  $stmt->execute();   



    ?>
<?
    $int_event_id = $_GET["EventID"];
    if((int)$int_event_id)
    {
$stmt=$pdo->prepare('INSERT INTO results (EventName, EventID)
VALUES (:EventResultsName, $int_event_id)');
  $stmt->bindValue(':EventName', $_POST['EventResultsName']);
      $stmt->execute();    
      }
      ?>

解决方案

If the inserts are always taking place in sequence, I'd use $pdo->lastInsertId() (see: http://www.php.net/manual/en/pdo.lastinsertid.php)

So, I think this line is wrong:

$int_event_id = $_GET["EventID"];

I'd write it like this:

<?
    $stmt = $pdo->prepare('
              INSERT INTO results (EventName, EventID)
              VALUES (:EventResultsName, :EventId)
            ');
    $stmt->bindValue(':EventName', $_POST['EventResultsName']);
    $stmt->bindValue(':EventId', $pdo->lastInsertId());
    $stmt->execute();    
?>

Note that this assumes the insert into results occurs immediately after the insert into events.

You could've done the same thing without a bind variable using MySQL's native last_insert_id() function, like this:

<?
    $stmt = $pdo->prepare('
              INSERT INTO results (EventName, EventID)
              VALUES (:EventResultsName, last_insert_id())
            ');
    $stmt->bindValue(':EventName', $_POST['EventResultsName']);
    $stmt->execute();    
?>

However, this is less portable than the previous example. However, pdo's lastInsertId() isn't exactly RDBMS agnostic either (see docs) so you'd have to fix this piece of code anyway if you're thinking of targeting another RDBMS

这篇关于PHP MYSQL 将数据插入 2 个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！