I have this code so far, which reads a simple table with 3 varchar fields:
<?php
//db connection code...
// select database
mysql_select_db($db) or die ("Unable to select database!");
// create query
$query = "SELECT * FROM Sheet1";
// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
// yes
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
// yes
// print them one after another
echo "<html><body><table cellpadding=10 border=1>";
while($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>".$row['stickerID']."</td>";
echo "<td>" .$row['stickerName']."</td>";
echo "<td>".$row['stickerSection']."</td>";
echo "<td>"?>
<form name="some form" action="editform.php" method="post">
<input type="checkbox" name="<?php echo $row['stickerID'] ?>" value=" <?php echo $row['stickerStatus'] ?> ">
<?php "</td>";
echo "</tr>";
}
echo "</table></body></html>";
echo " " ?>
<input type="submit" name="editWish" value="Edit">
</form>
<?php " ";
} else {
// no
// print status message
echo "No rows found!";
}
// free result set memory
mysql_free_result($result);
// close connection
mysql_close($connection);
?>
The database has 4 fields, 3 varchar and 1 int with current value of 0. I checked the page source code and confirmed each checkbox name is the stickerID. Now I will post this to the editform.php which I must create. What Im wondering is how should I write the update sql so that it takes into account each new value selected by the user in the form?
This is my idea, but how to I do it for every checkbox?
editform.php
<?php
//update multiple records
//UPDATE user_items SET stickerStatus = $_POST["stickerStatus"] WHERE stickerID = $_POST["stickerID"];
?>
First question: use mysql_fetch_assoc() instead of mysql_fetch_row(). That will return an associative array instead of an enumerated one.
Second question: read up on HTML forms and form handling.
The answer to the question in the comments:
// The <form> tag should only be echoed once.
echo '<form name="some form" action="editform.php" method="post">';
while($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>".$row['stickerID']."</td>";
echo "<td>" .$row['stickerName']."</td>";
echo "<td>".$row['stickerSection']."</td>";
echo "<td>"?>
<input type="hidden" name="status_<?php echo $row['stickerID"; ?>" value="0">
<input type="checkbox" name="status_<?php echo $row['stickerID'] ?>" value="<?php echo $row['stickerStatus'] ?> ">
<?php "</td>";
echo "</tr>";
}
// You need a submit button to send the form
echo '<input type="submit">';
// Close the <form> tag
echo '</form>';
Using a hidden input with the same name as the checkbox makes sure a value for the given input name is sent to the server. The value of a checkbox that's not checked will not be sent. In that case the hidden input will be used.
You can get the submitted values in editform.php as follows:
<?php
foreach ($_POST as $field => $value) {
if (strpos($field, 'status_')) {
// Using (int) makes sure it's cast to an integer, preventing SQL injections
$stickerID = (int) str_replace('status_', '', $field);
// Again, preventing SQL injections. If the status could be a string, then use mysql_real_escape_string()
$stickerStatus = (int) $value;
// Do something with the results
}
}
Do
print_r($row)
to find out exactly how your row arrays are constructed and work from there.
For your comparison operator, use
$row[3] === 0
instead of
$row[3] == 0
This will return true if both the value and data type match rather than just the value.
0 can mean the Boolean false aswell as the numeric value 0