Edit Button To Open New Window To Allow Edits And Update Sql Server With Php Code
Solution 1:
Original EntryEdit Button Once the edit button opens I want it to open a window kind of like the original entry page so that I can choose another dept. or another drop down and update the Transaction table where the Edit button is at.
Solution 2:
I'd try something along these lines, I haven't provided a full example as I really do not have the time, but you should get the idea enough to complete the rest of the code.
<?phprequire('dbcon.php');
include("header.php");
$transaction_id = $_GET['transaction_id'];
$stm = $pdo->prepare("SELECT * FROM [Transaction] WHERE transaction_id = ?");
$stm->execute(array($transaction_id));
$transaction = $stmt->fetch(PDO::FETCH_ASSOC);
?><!DOCTYPE html><html><head><metacharset="utf-8"><title>Update Transaction</title><linkrel="stylesheet"href="css/style.css" /></head><body><divclass="form"><h1>Update Transaction</h1><?php$status = "";
if (isset($_POST['update']) && $_POST['update'] == 1) {
$transaction_id = $_POST['transaction_id'];
$fund = $_POST['fund'];
$department = $_POST['department'];
$code_name = $_POST['code_name'];
$budget_year = $_POST['budget_year'];
$entry_date = $_POST['entry_date'];
$project_name = $_POST['project_name'];
$item_desc = $_POST['item_desc'];
$amount = $_POST['amount'];
$detail = $_POST['detail'];
$PO = $_POST['PO'];
$modified = $_POST['modified'];
$stm = $pdo->prepare("UPDATE [Transaction] SET fund = ? , department = ? , code_name = ? , budget_year = ? , entry_date = ? , project_name = ? , item_desc = ? , amount = ? , detail = ? , PO = ?");
$res = $stm->execute(array($fund,$department,$code_name,$budget_year,$entry_date,$project_name,$item_desc,$amount,$detail,$PO));
if($res){
$status = "Record Updated Successfully. </br></br>
<a href='transactions.php'>View Updated Record</a>";
}else{
$status = "Record Update Failed.";
}
echo'<p style="color:#FF0000;">' . $status . '</p>';
} else {
?><div><formname="form"method="post"action=""><inputtype="hidden"name="update"value="1" /><inputname="transaction_id"type="hidden"value="<?phpecho$transaction['transaction_id']; ?>" /><p>Fund:
<selectname= 'fund'class='form-control selectpicker'onChange='getState(this.value)'Required><?php$stm = $pdo->prepare("SELECT * FROM Funding");
$stm->execute();
$funding = $stmt->fetchAll();
foreach($fundingas$row)) {
$fund = $row['fund'];
if ($transaction['fund'] == $fund) {
$selected = 'selected="selected"';
} else {
$selected = "";
}
echo"<option value=\"$fund\" $selected>$fund</option>";
}
?></select></p><p>Department:
<selectname= 'department'class='form-control selectpicker'onChange='getState(this.value)'Required><?php$stm = $pdo->prepare("SELECT * FROM Department");
$stm->execute();
$department = $stmt->fetchAll();
foreach($departmentas$row) {
$department = $row['department'];
if ($transaction['department'] == $department) {
$selected = 'selected="selected"';
} else {
$selected = "";
}
echo"<option value=\"$department\" $selected>$department</option>";
}
?></select></p>So, first off use prepared statements, in your old code you have this:
$transaction_id = $_REQUEST['transaction_id'];
$sql = "SELECT * FROM [Transaction]where
transaction_id='" . $transaction_id . "'";
What if the value of $transaction_id was 1; DROP TABLE Transactions; ? The SQL query string you are then sending to your server would read as follows:
SELECT * FROM [Transaction] WHERE transaction_id = 1; DROP TABLE Transactions;
And then you would have lost all of your data, I took this basic example from bobby-tables.com
I would recommend using PDO to prepare and execute your queries in PHP, I have given you a brief taste of the syntax in my code example above.
Now, moving on from this there was a few things in your old code that didn't really make much sense, I'll try to name as many as I can;
You store your update query in a variable called
$update, then execute a query using a variable called$sql$update = "update [Transaction] set fund='" . $fund . "', department='" . $department . "', code_name='" . $code_name . "', budget_year='" . $budget_year . "', entry_date='" . $entry_date . "', project_name='" . $project_name . "', item_desc='" . $item_desc . "', amount='" . $amount . "', detail='" . $detail . "', PO='" . $PO . "' where transaction_id='" . $transaction_id . "'"; sqlsrv_query($conn, $sql);You initially get the transaction from the DB at the start of the script and store the row in a variable called
$row, then later on in the script do many queries for getting information for say 'Funding' all storing their results into a variable called$row, overwriting the transaction info. Just be more precise when naming your variables, it will make the code easier to read and also prevent errors like this.In the
selectloops, you are usingechotwice? and also testing to see if$row['fund'] == 'Operational'twice?
I'll finish off by explaining my code briefly, so you can finish the rest off.
First we get the transaction ID from the $_GET request, we use this to get the correct row from the database and store the result in an associative array name $transaction, so later on in the code, whenever we need information about the existing transaction we use this variable.
The update statement I just changed to a prepared statement, and added an else to display if the query had failed.
I added a closing select tag, as yours was missing. For both the funding and department we do the same thing, prepare a statement, fetch all the results as an array (named appropriately) and then foreach through them. In each iteration of the foreach we compare the value of the $row with the corresponding $transaction value (e.g. $transaction['fund']==$row['fund']) and if they match, make it selected! If they don't match then the $selected variable is set to be empty, and thus has no impact on the option.
Well this answer was longer than I expected, probably missed a lot of stuff out, but if you have any questions, leave them below!
Solution 3:
<?phprequire('dbcon.php');
include("header.php");
$transaction_id = $_GET['edit'];
$conn = sqlsrv_connect( $dbServer, $connectionInfo);
$sql = ("SELECT * FROM [Transaction] WHERE transaction_id = ?");
$parameters = array($transaction_id);
$MainQuery = sqlsrv_query($conn,$sql,$parameters);
/*if (!sqlsrv_query($conn, $sql)) {
die('An error has occurred. '.print_r(sqlsrv_errors()));
} else {
echo print_r MainQuery[0];
}*/
print_r($MainQuery[0]);
while($budget2=sqlsrv_fetch_array($MainQuery,SQLSRV_FETCH_ASSOC))
{
$ID =$budget2['transaction_id'];
$FUND =$budget2['fund'];
$DEPT =$budget2['department'];
$CODE =$budget2['code_name'];
$YEAR =$budget2['budget_year'];
$DATE =$budget2['entry_date'];
$PROJECT =$budget2['project_name'];
$DESC =$budget2['item_desc'];
$AMT =$budget2['amount'];
$DETAIL =$budget2['detail'];
$PO_NUM =$budget2['PO'];
}
?><!DOCTYPE html><html><head><metacharset="utf-8"><title>Update Transaction</title><linkrel="stylesheet"href="css/style.css" /></head><body><divclass="form"><h1>Update Transaction</h1><?php$status = "";
if(isset($_POST['update']) && $_POST['update']==1)
{
$transaction_id = $_POST['transaction_id'];
$fund = $_POST['fund'];
$department = $_POST['department'];
$code_name = $_POST['code_name'];
$budget_year = $_POST['budget_year'];
$entry_date = $_POST['entry_date'];
$project_name = $_POST['project_name'];
$item_desc = $_POST['item_desc'];
$amount = $_POST['amount'];
$detail = $_POST['detail'];
$PO = $_POST['PO'];
$stm = $pdo->prepare("UPDATE [Transaction] SET fund = ? , department = ? , code_name = ? , budget_year = ? , entry_date = ? , project_name = ? , item_desc = ? , amount = ? , detail = ? , PO = ?");
$res = $stm->execute(array($fund,$department,$code_name,$budget_year,$entry_date,$project_name,$item_desc,$amount,$detail,$PO));
if($res){
$status = "Record Updated Successfully. </br></br>
<a href='transactions.php'>View Updated Record</a>";
}else{
$status = "Record Update Failed.";
}
echo'<p style="color:#FF0000;">' . $status . '</p>';
} else {
?><div><formname="form"method="post"action=""><inputtype="hidden"name="update"value="1" /><inputname="transaction_id"type="hidden"value="<?phpecho$transaction_id;?>" /><p>Fund:
<?phpecho"<select name= 'fund' class='form-control selectpicker' onChange='getState(this.value)' Required>";
$sql = "SELECT * FROM Funding";
$query_fund = sqlsrv_query($conn,$sql);
while($row=sqlsrv_fetch_array($query_fund,SQLSRV_FETCH_ASSOC))
{
echo$row['fund'].'=='.$FUND;
echo'<option value=" ' . $row['fund']. '"' . ((trim($row['fund']) == trim($FUND)) ? ' selected="selected"' : "").'>'.$row['fund']. '</option>';
//continue;
}
?></select></p><p>Department:
<?phpecho"<select name= 'department' class='form-control selectpicker' onChange='getState(this.value)' Required>";
$sql = "SELECT department FROM Department";
$query_dept = sqlsrv_query($conn,$sql);
while($row=sqlsrv_fetch_array($query_dept,SQLSRV_FETCH_ASSOC))
{
echo'<option value="' . $row['department']. '"';
if (trim($row['department']) == trim($DEPT)) {
echo" selected";
}
echo'>'.$row['department'] . '</option>' . "\n";
}
?></select></p><p>Object Code:
<?phpecho"<select name= 'code_name' class='form-control selectpicker' onChange='getState(this.value)' Required>";
echo'<option value="$code_name">'.'--Select Object Code'.'</option>';
$sql = "SELECT code_name FROM Object_Code";
$query_code = sqlsrv_query($conn,$sql);
while($row=sqlsrv_fetch_array($query_code,SQLSRV_FETCH_ASSOC))
{
echo'<option value=" ' . $row['code_name']. '"' . ((trim($row['code_name']) == trim($CODE)) ? ' selected="selected"' : "").'>'.$row['code_name']. '</option>';
}
?></select></p>
Post a Comment for "Edit Button To Open New Window To Allow Edits And Update Sql Server With Php Code"