Semi-functional when inserting data based on if / else condition
I have a table called "edited hours" that is initially empty. I am inserting addwh and idnumber into it based on the if / else condition. Such that: If addedwh is empty (then insert) else (update because there is already a value). Now the update part works fine (because I manually added the values to the table in the database to try the update). However, if idnumber (which is id) does not exist in the table, no insert occurs. Any suggestions on what might be preventing the if / else from functioning correctly?
if(isset($_POST['submit']))
{
$addedhours = $_POST['AddedHours'];
$selectid = $_POST['SelectID'];
$sql1="SELECT addedwh FROM editedworkhours WHERE idnumber='$row[0]'";
$getResult = mysql_query($sql1);
$count = count($getResult);
if(empty($count))
{
$sql="INSERT INTO editedworkhours (IDNumber,AddedWH) VALUES('$selectid','$addedhours')";
}
else
{
$tempname = $row['Field'];
$sql2 = "UPDATE editedworkhours SET AddedWH = AddedWH +'$addedhours' WHERE IDNumber='$selectid'";
$result2 = mysql_query($sql2);
if (isset($result2))
{
}
else
{
echo '<script>swal("Error", "Something went wrong error");</script>';
}
}
}
echo $menu;
source to share
I would remove the conditional and just execute the query INSERT ... ON DUPLICATE KEY UPDATE ...
. This will allow you to exclude the original query SELECT
to determine if a record exists or not.
INSERT INTO editedworkhours (IDNumber,AddedWH)
VALUES($selectid, $addedhours)
ON DUPLICATE KEY UPDATE AddedWH = AddedWH + $addedhours
This will require a unique (or primary key) index on IDNumber
.
You can check the value mysql_affected_rows()
after executing the query to determine if an insert or update has occurred. For inserts, the return value will be 1. For updates, the value will be 2.
Note that I have also removed the line separators around your insert, as I am assuming you have fields of type INT in both cases.
Putting this together, it might look like this:
if(isset($_POST['submit'])) {
// validate user input as integer value string
$addedhours = filter_var($_POST['AddedHours'], FILTER_VALIDATE_INT);
$selectid = filter_var($_POST['SelectID'], FILTER_VALIDATE_INT);
if($addedhours === false || $selectid === false) {
// input was bad
// perhaps do some error messaging here
die('Bad input');
}
// both filters passed
// prepare variables for insert/update
$addedhours = mysql_real_escape_string($addedhours);
$selectid = mysql_real_escape_string($selectid);
// form query
$sql = "INSERT INTO editedworkhours (IDNumber,AddedWH)
VALUES($selectid, $addedhours)
ON DUPLICATE KEY UPDATE AddedWH = AddedWH + $addedhours";
// execute
$result = mysql_query($sql);
if(true === $result) {
// the query worked
// determine if insert or update was performed
// maybe present have different logic based on insert vs. update
if(mysql_affected_rows() === 1) {
// an insert occurred
} else {
// an update occurred
}
} else {
echo '<script>swal("Error", "Something went wrong error");</script>';
}
}
You should heed the advice you get in the comments and consider using MySQLi or PDO in combination with parameterized prepared statements. I did show some example code using mysql, although for consistency with your current use.
source to share
You will just forget to execute the insert query
if(empty($count))
{
$sql="INSERT INTO editedworkhours (AFNumber,AddedWH) VALUES('$selectaf','$addedhours')";
$result=mysql_query($sql);
if (isset($result))
{
}
else
{
echo '<script>swal("Error", "Something went wrong error");</script>';
}
echo '<script>swal("Success", "New Value has been inserted", "success");</script>';
}
source to share