如何将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个回答



FWIW,我发现这更易于阅读和理解,但是查看这些表的CREATE和INSERT语句以及 希望的结果。</ p>

  SELECT p.ID 
,i.items
,p.yes
,p.no
,p.question
,p .comment
FROM项目i
LEFT
JOIN参与者p
ON p.itemID = i.ID
WHERE i.role =“DE”
AND i.categorie =“Communication”
ORDER
BY i.items
</ code> </ pre>

顺便说一句,同时包含yes和no列是奇数</ p>
</ div>

展开原文

原文

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



感谢您帮助我!</ p>

这读起来确实比较容易,但我不是 所以习惯了别名jet ...... </ p>

为我正在使用的三个表创建语句; </ p>

  CREATE TABLE参与者

IDint(11)NOT NULL,
in first_name文本NOT NULL,
insert文本NOT NULL,
last_name文本NOT NULL,
position文本NOT NULL,
email text NOT NULL,
datetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
obn varchar(2)NOT NULL
)ENGINE = InnoDB DEFAULT CHARSET = latin1;

CREATE TABLEitements(\ nIDint(3)NOT NULL,
itemsvarchar(75)DEFAULT NULL,
categoryvarchar(16)DEFAULT NULL,
counter varchar(2)DEFAULT NULL
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLEexview
IDint(11)NOT NULL,
constlierIDint(11)NOT NULL,
EnjectIDint(11)NOT NULL,
ite varchar(255)NOT NULL,
分类varchar(255)NOT NULL,
country varchar(2)NOT NULL,
varchar(1)NOT NULL,
novarchar(1)NOT NULL,
questionvarchar(1)NOT NULL,
commenttext NOT NULL,
levelint(11) NOT NULL,
datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE = InnoDB DEFAULT CHARSET = latin1;
</ code> </ pre>

插入语句已经在所提供的函数中 。</ p>

参与者的插入语句是; </ p>

 &lt;?php include'dbh.inc.php';  ?&gt; 

&lt;?php

//创建变量
$ first_name = $ _ POST ['first_name'];
$ insertion = $ _ POST ['insertion'];
$ last_name = $ _POST [ '姓氏'];
$的位置= $ _ POST [ '位置'];
$的角色= $ _ POST [ '角色'];
$的电子邮件= $ _ POST [ '电子邮件'];
\ n //执行查询

mysqli_query($ conn,“INSERT INTO参与者(first_name,insert,last_name,position,email,role)VALUES('$ first_name','$ insertion','$ last_name', '$ position','$ email','$ role')“);

if if(mysqli_affected_rows($ conn)&gt; 0){
echo”&lt; p&gt; Participant Added&lt; / p&gt;“;

echo“&lt; a href ='.. / indexnl.php'&gt;选择要扫描的角色&lt; / a&gt;”;
}其他{
echo“参与者未添加&lt; br /&gt;”;
echo mysqli_error($ conn);
}
</ code> </ pre>
</ div>

展开原文

原文

Thank you for helping me out!

This reads indeed easier, although I'm not so used to Aliases jet ...

Create statement for the three tables I'm using;

CREATE TABLE `participants` (
  `ID` int(11) NOT NULL,
  `first_name` text NOT NULL,
  `insertion` text NOT NULL,
  `last_name` text NOT NULL,
  `position` text NOT NULL,
  `email` text NOT NULL,
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `role` varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `items` (
  `ID` int(3) NOT NULL,
  `items` varchar(75) DEFAULT NULL,
  `categorie` varchar(16) DEFAULT NULL,
  `role` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `interview` (
  `ID` int(11) NOT NULL,
  `participantID` int(11) NOT NULL,
  `ItemID` int(11) NOT NULL,
  `item` varchar(255) NOT NULL,
  `Categorie` varchar(255) NOT NULL,
  `role` varchar(2) NOT NULL,
  `yes` varchar(1) NOT NULL,
  `no` varchar(1) NOT NULL,
  `question` varchar(1) NOT NULL,
  `comment` text NOT NULL,
  `level` int(11) NOT NULL,
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The insert statement was allready in the function presented.

The insert statement for the participant is;

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

<?php

// create a variable
$first_name=$_POST['first_name'];
$insertion=$_POST['insertion'];
$last_name=$_POST['last_name'];
$position=$_POST['position'];
$role=$_POST['role'];
$email=$_POST['email'];

//Execute the query


mysqli_query($conn,"INSERT INTO participant (first_name, insertion, last_name,position,email,role) VALUES ('$first_name','$insertion','$last_name','$position','$email','$role')");

    if(mysqli_affected_rows($conn) > 0){
    echo "<p>Participant Added</p>";
    echo "<a href='../indexnl.php'>Select role to scan</a>";
} else {
    echo "Participant NOT Added<br />";
    echo mysqli_error ($conn);
}

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐