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.
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
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);
}