I am trying to retrieved data from database and update a single column.
column1 | column2 | column3
value1 | value1 | -------
value2 | value2 | ------- <=== this column3 rows does not have value in database yet so it will be blank when I retrieved the data
Now I would like to update column3 rows by putting values to it and send it back to the database with the new value in column3 rows.
The current code that I am using now can only retrieved and update a single ID and I’m stuck here. What I want to happen is to retrieved multiple ID’s and update the same column for all of the ID’s with different values.
Here is a sample of the code that I am using to retrieved data,
$(document).ready(function(){
$("#RetrieveList").on('click',function() {
var status = $('#status').val();
var date = $('#Date').val();
var date1 = $('#Date1').val();
$.post('retrieve.php',{status:status, date:date, date1:date1}, function(data){
$("#results").html(data);
});
return false;
});
This is the PHP code,
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sample_db";
// check data before use it and convert from string to expected type, use try, not like here:
$date = $_POST['date'];
$date1 = $_POST['date1'];
// use valid data to select rows
try {
//1. connect to MySQL database
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
//2. set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//3. create query string (here is answer on your question)
$sql = 'SELECT column1, column2, column3 FROM sample_table WHERE scheduled_start_date BETWEEN :d1 AND :d2';
//4. prepare statement from query string
$stmt = $conn->prepare($sql);
//5. bind optional parameters
//if ($status != 'All') $stmt->bindParam(':st', $status);
//6. bind parameters
$stmt->bindParam(':d1', $date);
$stmt->bindParam(':d2', $date1);
//7. execute statement
$stmt->execute();
//8. returns an array containing all of the result set rows
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//get count of rows
$numrow = count($result);
//print array - there is many solution to print array,
//to debug you can do:
//print_r($result);
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
if($numrow == 0)
echo "No results found.";
else
echo "Count: $numrow</br>";
{
echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
<tr>
<!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>-->
<th align='center'><strong>Column1</strong></th>
<th align='center'><strong>Column2</strong></th>
<th align='center'><strong>Column3</strong></th>
</tr>";
foreach ($result as $row => $info) {
echo "<form action='crqretrieve_status.php' method='post'>";
echo"<tr>";
echo "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>";
echo "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>";
echo "<td align='center'>" . "<input name=column3 value='' </td>";
echo "</tr>";
echo "</form>";
}
}
echo "</table>";
?>
From the code above, it has > name=column3 value=‘’, which I want then to assign a value and save it to db.
Tried searching around and I’m not sure how a case would be useful as my update would depend on the ID’s.
Here is the code that I am using for a single query update and I dont know how to associate it to my code above. I would appreciate any help on this.
// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$column1 = $_POST['column1'];
$column2 = htmlentities($_POST['column2'], ENT_QUOTES);
$column3 = htmlentities($_POST['column3'], ENT_QUOTES);
$column4 = htmlentities($_POST['column4'], ENT_QUOTES);
// check that fields are not empty
if ($column1 == '' || $column2 == '' || $column3 == ''|| $column4 == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($column1, $column2, $column3, $column4, $error, $id);
}
else
{
// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ?, column3 = ?, column4 = ?
WHERE id=?"))
{
$stmt->bind_param("ssssi", $column1, $column2, $column3, $column4, $id);
$stmt->execute();
$stmt->close();
}
// show an error message if the query has an error
else
{
echo "ERROR: could not prepare SQL statement.";
}
// redirect the user once the form is updated
header("Location: list.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];
// get the record from the database
if($stmt = $mysqli->prepare("SELECT column1, column2, column3, column4 FROM sample_table WHERE id=?"))
{
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->bind_result($column1, $column2, $column3, $column4);
$stmt->fetch();
// show the form
renderForm($column1, $column2, $column3, $column4, NULL, $id);
$stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the view.php page
else
{
header("Location: list.php");
}
}
}
// close the mysqli connection
$mysqli->close();