I've written a php page to load data from mysql table based on dropdown selection which is also populated from table, I then want some of the data to be able to be changed and then when submitted written back to the database.
Here's what I have so far, I can get the turnout and percentage to update using the $sql update command at the bottom, however the other data is a list of names which I cannot get to update, see $sql1 command. I've tried lots of methods including for($i=0;$i<$count;$i++){ etc.... however I can only ever get it to update one of the rows. Really hope someone can help, as I'm so close now
<?php
require 'config.php';
?>
<!doctype html public "-//w3c//dtd html 3.2//en">
<html>
<head>
<title>Election Results</title>
<SCRIPT language=JavaScript>
function reload(form)
{
var val=form.cat.options[form.cat.options.selectedIndex].value;
self.location='index.php?cat=' + val ;
}
</script>
</head>
<body>
<?Php
@$cat=$_GET['cat'];
if(strlen($cat) > 0 and !is_numeric($cat)){
echo "Data Error";
exit;
}
$quer2="SELECT DISTINCT * FROM ward order by ward";
echo "<form method='post' action='$_PHP_SELF'>";
echo "<select name='cat' style='width:220px' onchange=\"reload(this.form)\"><option value=''>Select Ward</option>";
foreach ($dbo->query($quer2) as $noticia2) {
if($noticia2['ward_id']==@$cat){echo "<option selected value='$noticia2[ward_id]'>$noticia2[ward]</option>"."<BR>";
}
else{echo "<option value='$noticia2[ward_id]'>$noticia2[ward]</option>";
}
}
echo "</select><br><br>";
if(isset($cat) and strlen($cat) > 0){
$quer="SELECT DISTINCT * FROM candidate where ward_id=$cat order by candidate_id";
}else{ }
foreach ($dbo->query($quer2) as $noticia2) {
if($noticia2['ward_id']==@$cat){
echo "Turnout: <input type='text' id='result' name='turnout' style='width:35px' value='$noticia2[turnout]'> Percentage: <input type='text' id='result' name='percentage' style='width:35px' value='$noticia2[percentage]'>%<br><br>";
echo "Candidates: <br>";
$conn = mysql_connect($dbhost_name, $username, $password);
mysql_select_db('raynerco_election');
function mysql_query_or_die($query) {
$result = mysql_query($query);
if ($result)
return $result;
else {
$err = mysql_error();
die("<br>{$query}<br>*** {$err} ***<br>");
}
}
$query="SELECT DISTINCT * FROM candidate where ward_id=$cat order by candidate_id";
$result = mysql_query_or_die($query);
echo("<table>");
echo '<tr>';
echo "<th></th>";
echo "<th>Candidate:</th>";
echo "<th>Results:</th>";
echo '</tr>';
while ($rows = mysql_fetch_assoc($result)) {
echo '<tr>';
echo "<td><input type='text' name='candidate_id' value='$rows[candidate_id]'></td>";
echo "<td><input type='text' name='candidate' value='$rows[candidate]'></td>";
echo "<td><input type='text' name='result' value='$rows[result]'></td>";
// }
echo '</tr>';
}
echo("</table>");
echo "<br><input name='update' type='submit' value='Update'>";
echo "</form>";
}
else{
}
}
?>
<?php
$ward_id = $_POST['ward_id'];
$turnout = $_POST['turnout'];
$percentage = $_POST['percentage'];
$candidate_id = $_POST['candidate_id'];
$candidate = $_POST['candidate'];
$result = $_POST['result'];
if(isset($_POST['update']))
{
$conn = mysql_connect($dbhost_name, $username, $password);
$sql = "UPDATE ward SET turnout = '$turnout', percentage = '$percentage' WHERE ward_id = '$cat'";
$sql1 = "UPDATE candidate SET candidate = '$candidate', result = '$result' WHERE candidate_id = 'candidate_id' ";
mysql_select_db('raynerco_election');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ' . mysql_error());
}
header( "Location: index.php?cat=$cat&updated" ); die;
}
if (isset($_GET['updated'])) {
echo "Data updated successfuly";
}
?>
<br><br>
<a href=index.php>Start again</a>
<br><br>
</body>
</html>