I am trying to do something that a hell of a lot of people must need to do every day, yet despite this I can not find a single example online, or in books, which makes absolutely no sense!
Anyway, hopefully it will be blindingly obvious to someone what I'm doing wrong, I really hope so as I can't figure it out...
I have a mysl table as follows:
tblvenue
venue_id PRIMARY KEY (int)
venue_name (varchar 50)
station_id (int)
I have a php/html page as follows:
<html>
<head>
<title>Update & Delete Venues</title>
</head>
<body marginheight="0" topmargin="0" marginwidth="0"
leftmargin="0" style="margin:0;padding:0" bgcolor="#B0E0E6">
<h1></h1>
<form action="venue4.php" method="post">
<table border="1" style="border:solid black" cellpadding="0"
cellspacing="0" width="100%">
<tr>
<td align="center" width="33%">Action</td>
<td align="center" width="33%">Venue Name</td>
<td align="center" width="33%">Station Name</td>
</tr>
<?php
$station_name=$_POST['station_name'];
@ $db = new mysqli('XXX', 'XXX', 'XXX', 'XXX');
$query = "call sp_venue()";
$result = $db->query($query);
$num_results = $result->num_rows;
function db_result_to_array($result){
//---------------------------------------------------------------------
$res_array = array();
for ($count=0; $row = $result->fetch_assoc(); $count++) {
$res_array[$count]=$row;
}
return $res_array;
}
//---------------------------------------------------------------------
function get_dataset($server, $user, $password, $database, $query7) {
//---------------------------------------------------------------------
$db1 = new mysqli($server, $user, $password, $database);
$query1 = $query7;
$result1 = $db1->query($query1);
$num_results = $result1->num_rows;
$result8 = db_result_to_array($result1);
return $result8;}
//---------------------------------------------------------------------
echo "<tr>";
echo "<td>";
echo "<table width='100%'><td width='33%' align='center'><input type='submit' id='btnupdate' name='btnupdate' value='Update & Delete' ></td>";
echo "<td width='33%' align='center'><input type='submit' name='btncancel'id='btncancel' value='Cancel Updates' ></td>";
echo "<td width='33%' align='center'><input type='submit' value='Add New Venue' name='btnadd'></td></table>";
echo "</td>";
echo "<td><input type='text' name='tbvenue_name'/></td>";
echo "<td>";
?>
<select name="stat_id">
<?php
$stat_array=get_dataset('XXX', 'XXX', 'XXX', 'XXX',"call sp_station");
foreach ($stat_array as $thisstat){
echo "<option value=\"".$thisstat['station_id']."\"";
echo ">".$thisstat['station_name']."</option>";
}
?>
</select>
<?php
echo "</td>";
echo "</tr>";
echo "<tr>";
while($row = $result->fetch_assoc())
{
echo "<td width='33%'>Delete:<input type='Checkbox' id='cbdelete[]' name='cbdelete[]' value='".$row['venue_id'];
echo "'>Update:<input type='Checkbox' id='value' name='cbupdate[]' value='".$row['venue_id']."'><input type='hidden' name='venue_id[]' value='".$row['venue_id']."'></td>";
echo "<td width='33%'><input type='Textbox' width='100%' name='venue_names' value='" .$row['venue_name']. "'></td>";
echo "<td width='33%'>" ;
echo "<select name='station_id[]' width='100%'>";
$stat_array=get_dataset('localhost', 'root', 'snooker1', 'pool',"call sp_station");
foreach ($stat_array as $thiscat)
{
echo '<option value="' . $thiscat['station_id'] . '"';
if ($row['station_id'] == $thiscat['station_id']) {echo ' selected';}
echo '>' . $thiscat['station_name'] . '</option>';
}
echo "</td>";
echo "</tr>";
}
echo "</table>";
?>
</form>
</body>
</html>
This displays correctly and when checking the appropriate checkbox and clicking the script venue4.php is run:
<?php
//Connects to the database
$db = new mysqli('XXX', 'XXX', 'XXX', 'XXX');
//Checks to see if the update button has been clicked
if (isset($_POST['btnupdate']))
{
//Assigns checkbox array to variable
$aDel = $_POST['cbdelete'];
$aVal = $_POST['cbupdate'];
//Loops through rows to delete selected records
foreach($_POST['cbdelete'] as $delete11)
{
//Deletes record
$query = "delete from tblvenue where venue_id =".$delete11;
$result = $db->query($query);
$num_results = $result->num_rows;
}
//Loops through rows to delete selected records
foreach($_POST['cbupdate'] as $update11)
{
//Gets variable
//$Venue = $_POST['venue_names'];
//Updates record
$query = "update tblvenue set venue_name=".$venue." where venue_id =".$update11;
$result = $db->query($query);
$num_results = $result->num_rows;
}
//Checks to see if Add button was clicked
} elseif (isset($_POST['btnadd'])) {
//Looks for Venue Name & Station Name
$tbvenue_name=$_POST['tbvenue_name'];
$tbstat_id=$_POST['stat_id'];
//Formats variables
if(!get_magic_quotes_gpc()) {
$tbvenue_name = addslashes($tbvenue_name);
$tbstat_id = addslashes($tbstat_id);
}
//Inserts new record
$query = "call sp_insert_venue('".$tbvenue_name."',".$tbstat_id.")";
$result = $db->query($query);
} elseif (isset($_POST['btnedit'])) {
switch($page) {
default:
include("venue2.php");
break;
}
} else {
//If cancel button is clicked, goes straight to switch code below
}
//Closes database connection
$db->close();
//Goes back to Venue page
switch($page) {
default:
include("venue.php");
break;
}
echo var_dump($aval);
print_r($aVal);
//$test = $_POST['cbupdate'];
?>
Deleting records works fine.
Inserting records works fine.
If I hard code the data (ie. set venue_name = 'blah blah blah') for updates, I can get the relevant row id from cbupdate and update the row in question. However I can not get the data from venue2.php (typed into a textbox on the row in question) and submit this to the database to be updated.
This scenario must come up for zillions of developers, but so far no-one has been able to help.
Please can anyone assist...
