Hi,
I am having problems updating a DB using a prepared pdo statement.
I have been looking through this code for two days now and can not see what I am doing wrong.
I would be very grateful if anyone could have a look and would share their thoughts.
Here is an excerpt from the index.php file which populates the form. Note the form is use to update information and to add new information. But the code for adding information is not shown here. It is almost the same. Uses INSERT instead of UPDATE. So if it workis to INSERT, why not UPDATE.
if (isset($_POST['action']) and $_POST['action'] == 'Edit')
{
include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';
try
{
$sql = 'SELECT id, unit, year, exam, question, part, subpart, topic, subtopic, tp, questiontype FROM teachingpts WHERE id = :id';
$s = $pdo->prepare($sql);
$s->bindParam(':id', $_POST['id']);
$s->execute();
}
catch (PDOException $e)
{
$error = 'Error fetching teachingpoints row details.';
include 'error.html.php';
exit();
}
$row = $s->fetch();
$pageTitle = 'Edit teaching points';
$action = 'editform';
$id = $row['id'];
$unit = $row['unit'];
$year = $row['year'];
$exam = $row['exam'];
$question = $row['question'];
$part = $row['part'];
$subpart = $row['subpart'];
$topic = $row['topic'];
$subtopic = $row['subtopic'];
$tp = $row['tp'];
$questiontype = $row['questiontype'];
$button = 'Update teaching points';
// Build the list of teaching point rows
try
{
$result = $pdo->query('SELECT id, unit, year, exam, question, part, subpart, topic, subtopic, tp, questiontype FROM teachingpts');
}
catch (PDOException $e)
{
$error = 'Error fetching list of teaching points.';
include 'error.html.php';
exit();
}
foreach ($result as $row)
{
$teachingpts[] = array('id' => $row['id'], 'unit' => $row['unit'], 'year' => $row['year'], 'exam' => $row['exam'], 'question' => $row['question'], 'part' => $row['part'], 'subpart' => $row['subpart'], 'topic' => $row['topic'], 'subtopic' => $row['subtopic'], 'tp' => $row['tp'], 'questiontype' => $row['questiontype']);
}
include 'form.html.php';
exit();
}
The HTML form is as follows.
<?php include_once $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/helpers.inc.php';
include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/buildtopics.inc.php';
include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/buildsubtopics.inc.php'; ?>
<!DOCTYPE html>
<html lang="en">
<head>
<script>
var newTopics = <?php echo json_encode($topics, JSON_PRETTY_PRINT) ?>;//converts php arrays to JS object
var newSubTopics = <?php echo json_encode($subtopics, JSON_PRETTY_PRINT) ?>;
function populate(s1, s2){
var s1 = document.getElementById(s1);
var s2 = document.getElementById(s2);
var optionArray = [];
s2.innerHTML = "";
for (i = 0; i < newTopics.length; i++) {
for (j = 0; j < newSubTopics.length; j++) {
if(newTopics[i].id == newSubTopics[j].topicid){
if(s1.value == newTopics[i].topic){
optionArray.push(newSubTopics[j].subtopic);
}
}
}
}
for(var option in optionArray){
var newOption = document.createElement("option");
newOption.value = optionArray[option];
newOption.innerHTML = optionArray[option];
s2.options.add(newOption);
}
}
</script>
<meta charset="utf-8">
<title><?php htmlout($pageTitle); ?></title>
<style>
table,th,td{ border-collapse:collapse;}
th, td{ padding:6px;}
td { vertical-align:top; text-align: center; width: 40px; overflow: hidden; white-space: nowrap;}
th{ background-color:#ccc;}
</style>
</head>
<body background="/artgibney/includes/retro_intro_@2X.png">
<p><a href="..">physCMS home</a> ⇒ <a href="/artgibney/admin/teachingpoints/">Teaching Points</a></p>
<h1><?php htmlout($pageTitle); ?></h1>
<form action="?<?php htmlout($action); ?>" method="post">
<table>
<tr>
<th>Unit</th>
<th>Year</th>
<th>Exam</th>
<th>Question</th>
<th>Part</th>
<th>Sub-part</th>
<th>Question type</th>
</tr>
<tr>
<?php $units = ['1' => '1', '2' => '2', '3' => '3', '4' => '4', '5' => '5', '6' => '6'];
?>
<td>
<select name="unit" id="unit">
<?php foreach($units as $key=>$option):
$selected = ($unit == $key) ? 'selected' : '';
echo "<option value='$key' $selected>$option</option>";
endforeach; ?>
</select>
</td>
<?php $years = ['2009' => '2009', '2010' => '2010', '2011' => '2011', '2012' => '2012', '2013' => '2013', '2014' => '2014', '2015' => '2015'];
?>
<td>
<select name="year" id="year">
<?php foreach($years as $key=>$option):
$selected = ($year == $key) ? 'selected' : '';
echo "<option value='$key' $selected>$option</option>";
echo($key);
endforeach; ?>
</select>
</td>
<?php $exams = ['January' => 'January', 'June' => 'June']; ?>
<td>
<select name="exam" id="exam">
<?php foreach($exams as $key=>$option):
$selected = ($exam == $key) ? 'selected' : '';
echo "<option value='$key' $selected>$option</option>";
endforeach; ?>
</select>
</td>
<td>
<input type="number" name="question" min="1" max="25" value="<?php htmlout($question);?>">
</td>
<td>
<input type="text" name="part" value="<?php htmlout($part);?>">
</td>
<td>
<input type="text" name="subpart" value="<?php htmlout($subpart);?>">
</td>
<?php
$questions = ['Multiple Choice' => 'Multiple Choice', 'Calculation' => 'Calculation', 'Short answer' => 'Short answer', 'Long answer' => 'Long answer'];
?>
<td>
<select name="questiontype" id="questiontype">
<?php foreach($questions as $key=>$option):
$selected = ($questiontype == $key) ? 'selected' : '';
echo "<option value='$key' $selected>$option</option>";
endforeach; ?>
</select>
</td>
</tr>
</table>
<table>
<th>Topic</th>
<th>Sub-topic</th>
<th>Teaching point</th>
<tr>
<td>
<select name="topic" id="topic" onchange="populate('topic', 'subtopic')">
<option value=""></option>
<?php foreach($topics as $topical):
$value = $topical['topic'];
$selected = ($topic == $value) ? 'selected' : '';
echo "<option value='$value' $selected>$value</option>";
endforeach; ?>
</select>
</td>
<td>
<select name="subtopic" id="subtopic">
<option value=""></option>
<?php foreach($subtopics as $subtopical):
$value = $subtopical['subtopic'];
$selected = ($subtopic == $value) ? 'selected' : '';
echo "<option value='$value' $selected>$value</option>";
endforeach; ?>
</select>
</td>
<td>
<textarea id="tp" name="tp" rows="3" cols="40"><?php htmlout($tp); ?></textarea>
</td>
</tr>
</table>
<input type="hidden" name="id" value"<?php htmlout($id); ?>">
<input type="submit" value="<?php htmlout($button); ?>">
</form>
</body>
</html>
Everything to here is working fine. The next excerpt from the index.php file should insert the information into the database but there are no changes occurring in the database.
if (isset($_GET['editform']))
{
include $_SERVER['DOCUMENT_ROOT'] . '/artgibney/includes/db.inc.php';
try
{
$sql = 'UPDATE teachingpts SET
unit = :unit,
year = :year,
exam = :exam,
question = :question,
part = :part,
subpart = :subpart,
topic = :topic,
subtopic = :subtopic,
tp = :tp,
questiontype = :questiontype
WHERE id = :id';
$s = $pdo->prepare($sql);
$s->bindValue(':id', $_POST['id']);
$s->bindValue(':unit', $_POST['unit']);
$s->bindValue(':year', $_POST['year']);
$s->bindValue(':exam', $_POST['exam']);
$s->bindValue(':question', $_POST['question']);
$s->bindValue(':part', $_POST['part']);
$s->bindValue(':subpart', $_POST['subpart']);
$s->bindValue(':topic', $_POST['topic']);
$s->bindValue(':subtopic', $_POST['subtopic']);
$s->bindValue(':tp', $_POST['tp']);
$s->bindValue(':questiontype', $_POST['questiontype']);
$s->execute();
}
catch (PDOException $e)
{
$error = 'Error updating submitted teaching points';
include 'error.html.php';
echo $e;
exit();
}
header('Location: .');
exit();
}
Thanks,
Shane
