dongliao9233 2017-09-08 06:24
浏览 149

如何将ID添加到另一个表?

I'm new here and not very handy with PHP an MySql. Though I've managed to get something working (with a little help of some friends ;-) and it is possibly it's a bit messy), but I'm stuck now.

This is the situation; I've got three tables; participants, items and interview.

the participant table is filled at the start of the interview with the details of the person I'm interviewing.

The items table consist out of a set of subjects I'm using during the interview.

And the interview table will be filled with data during the interview.

What's working is that I've got the system to show the items in a popup page next to a set of checkboxes and a text field and a selectbox. And data entered into the form fields are added to the interview table.

What I still need is that the ID of the participant and the item and itemID is added to the interview table. Can somebody help me out?

This is the code of the call for the popup page;

 <div class="communication">
        <a href="popups/DeveloperCommunication.php" onclick="window.open('popups/DeveloperCommunication.php','communication', 'width=800,height=350,scrollbars=no,toolbar=no,location=no'); return false">          
        <?php
        // get the records from the database
        if ($result = $conn->query('SELECT Categories FROM categories WHERE ID="2"'))
        {
        // display records if there are records to display
        if ($result->num_rows > 0)
        {

        while ($row = $result->fetch_object())
        {
        // set up a row for each record
        echo $row->Categories;
        }
        }
        // if there are no records in the database, display an alert message
        else
        {
        echo "No results to display!";
        }
        }
        // show an error if there is an issue with the database query
        else
        {
        echo "Error: " . $conn->error;
        }
        ?>
        </a></a>
    </div>

This is the code for the popup page;

<?php
    include '../../include/dbh.inc.php';
    date_default_timezone_set('Europe/Amsterdam');
?>

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Developer Communication</title>
<link href="../../css/chapterDeveloper.css" rel="stylesheet" type="text/css" media="screen">
</head>

<body>
<div class="popupTitle">
    <p>Developer Communication</p>
</div>

<?php 

    $comment = "";

// connect to the database  
$result = $conn->query('SELECT participant.ID, items.items, participant.yes, Participant.no, Participant.question, Participant.comment 
FROM participant 
RIGHT JOIN items ON participant.itemID=items.ID
WHERE items.role="DE" AND items.categorie="Communication"
ORDER BY items.items')

?> 

<form method="post" action="../../include/func.participant.inc.php" onsubmit="refreshAndClose()"> 

<table>
    <tr>
        <!--<th></th>--><th>Items</th><th>Y</th><th>N</th><th>?</th><th>Comment</th><th>levels</th>
         <?php 
         while ($row = mysqli_fetch_assoc($result)) {?>
    </tr>
    <tr>
         <td>
                <?php echo $row['items'] ?>
         </td>
         <td>
                <input name="items[<?php echo $row['ID']; ?>][yes]" type="checkbox" value="yes" <?php if ($row['yes'] == "yes") echo "checked"; ?>/>
         </td>
         <td>
                <input name="items[<?php echo $row['ID']; ?>][no]" type="checkbox" value="no" <?php if ($row['no'] == "yes") echo "checked"; ?>/>
        </td>
         <td>
                <input name="items[<?php echo $row['ID']; ?>][question]" type="checkbox" value="question" <?php if ($row['question'] == "yes") echo "checked"; ?>/>
        </td>
         <td>
                <textarea name="items[<?php echo $row['ID']; ?>][comment]" rows="1" cols="25" placeholder="comment"><?php echo $comment;?></textarea>
        </td>       
         <td>
            <select name="items[<?php echo $row['ID']; ?>][level]">
                <option value="">Select...</option>
                <option value="1">1. Starter</option>
                <option value="2">2. Junior</option>
                <option value="3">3. Intermediate</option>
                <option value="4">4. Senior</option>
                <option value="5">5. Expert</option>
                <option value="6">6. Un Known</option>
                <option value="7">7. Future</option>
                <option value="8">8. Not relefant</option>
            </select>
        </td>
    </tr>
        <?php } ?>
</table>
<input type="submit" name="submit" value="Submit">  

</form>

<?php 
function refreshAndClose() {
            window.opener.location.reload(true);
            window.close();
} ?> 
</body>
</html>

And this is the function used in the popup page;

<?php include 'dbh.inc.php'; ?>

<?php

var_dump($_POST); //met $_POST wordt de array met de verschillende items getoond

/*
 * Je lust nu door deze array met items, en ieder item sla je op in de database obv de id
 */
foreach($_POST['items'] as $item) {
    $query = "INSERT INTO interview (participantID, itemID, item, role, yes, no, question, comment, level)
              VALUES ('DE', '11', '12', '13', '".mysqli_real_escape_string($conn, $item['yes'])."', 
                      '".mysqli_real_escape_string($conn, $item['no'])."',
                      '".mysqli_real_escape_string($conn, $item['question'])."',
                      '".mysqli_real_escape_string($conn, $item['comment'])."',
                      '".mysqli_real_escape_string($conn, $item['level'])."', 
              )";
    mysqli_query($conn, $query);

    echo "<br>Record toegevoegd! ($query)<hr>";
}
#

Position 11 needs to be the participantID and position 12 needs to be the itemID and position 13 needs to be the item it self.

I hope someone can help me out.

Greetz.

  • 写回答

2条回答 默认 最新

  • ds20021205 2017-09-08 07:37
    关注

    FWIW, I find this easier to read and comprehend, but it would be useful to see CREATE and INSERT statements for these tables, together with a desired result.

    SELECT p.ID
         , i.items
         , p.yes
         , p.no
         , p.question
         , p.comment
      FROM items i
      LEFT
      JOIN participant p
         ON p.itemID = i.ID
     WHERE i.role = "DE" 
       AND i.categorie = "Communication"
     ORDER 
        BY i.items
    

    Incidentally, having both a yes and a no column is odd

    评论

报告相同问题?

悬赏问题

  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?