I'm using PHP in order to create a website where managers have access and review forms that employees have submitted. In the reviewing PHP file, I have created two buttons which basically approve or disapprove the form. After they click on one of the buttons, they are being redirected to another PHP file which actually inserts into the MySQL Database a change in a column I named 'processed'. It changes 0 which is unprocessed to 1, which is processed. The table I am referring to has columns such as formid, fullname, department and other job related stuff, as well as the 'processed' column which allows the managers to see if there is a pending form to be reviewed.
My problem is that I have no idea how to actually allow MySQL to find the proper row and change only the cell with the name 'processed' from 0 to 1 without having to insert every cell again. Here's what I have tried till now:
$id = $_SESSION[id];
$fullname = $_SESSION[fullname];
$teamformid = $_SESSION[teamformid];
if (isset($_POST['approved'])) {
$sql = "INSERT INTO carforms (processed) where aboveid='$id' and processed='0' and teamformid=$teamformid
VALUES ('0')";
}
else if (isset($_POST['disapproved'])) {
//todo
}
How do I tell SQL to only find the specific row I want and change only one column which is processed?
Also, do I always have to type every column name when I use the INSERT INTO command?
Thanks in advance.
Use the Below code it'll work for you.
$id = $_SESSION[id];
$fullname = $_SESSION[fullname];
$teamformid = $_SESSION[teamformid];
if (isset($_POST['approved'])) {
$sql = "UPDATE `carforms` SET processed = '1' WHERE `aboveid` = '".$id."' AND `teamformid` = '".$teamformid."'";
}
Try:
"UPDATE carforms SET processed = 1 WHERE aboveid = $id AND teamformid = $teamformid"
From what I have interpreted from your question, it seems like you need to use the MySQL UPDATE command. This will update any existing rows.
For example, let's say you have a table called 'forms', consisting of a Primary Key 'form_id' and a field named 'processed'.
If we want to change the value of 'processed' to '1', we would run...
UPDATE forms SET processed = 1 WHERE form_id = [whatever number the form is];
Obviously this only works where the form (with a form_id) exists already
There is no "INSERT...WHERE" in SQL.
To change an existing record there are 2 options, REPLACE or UPDATE. The former will create the record if it does not already exist and has similar syntax to INSERT. UPDATE uses the WHERE clause to identify the record(s) to be changed.
Using REPLACE is tricky. It needs to work out whether it should INSERT a new record or UPDATE an existing one - it does this by checking if the data values presented already exist in a unique index on the table - if you don't have any unique indexes then it will never update a record. Even if you have unique indexes just now, the structure of these may change over time as your application evolves, hence I would recommend NOT using REPLACE for OLTP.
In your question you write:
where aboveid='$id' and processed='0' and teamformid=$teamformid
(it would have been helpful if you had published the relevant part of the schema)
'id' usually describes a unique identifier. So there shouldn't be multiple records with the same id, and therefore the remainder of the WHERE clause is redundant (but does provide an avenue for SQL injection - not a good thing).
If the relevant record in carforms is uniquely identifed by a value for 'id' then your code should be something like:
$id=(integer)$id;
$sql = "UPDATE carforms SET processed = $action WHERE aboveid=$id";
But there's another problem here. There are 3 possible states for a record:
But you've only told us about 2 possible states. Assuming the initial state is null, then the code should be:
$action=0;
if (isset($_POST['approved'])) {
$action=1;
}
$id=(integer)$id;
$sql = "UPDATE carforms SET processed = $action WHERE aboveid=$id";
if ($id &&
(isset($_POST['disapproved']) || isset($_POST['approved']))
) {
// apply the SQL to the database
} else {
// handle the unexpected outcome.
}