Updating the database using php

Question

I'm inserting addedworkhours into my database, but the problem is that if i insert several times for the same id (afnumber), the old and new values are kept. The new val doesn't replace the old one. I'm trying to update it (the commented section) however not at all successful. Always the same result. I'm trying to get it to work using an if/else condition, to check whether a value in a column is empty, then insert. If not update, any help in the if condition statement?

The way I'm getting the updates output:

   if(isset($_POST['submit'])){

    if(AddedWH IS NULL) THEN

        $addedhours = $_POST['AddedHours'];
        $selectaf = $_POST['SelectAF'];
              $sql1="INSERT INTO `editedworkhours` (`AFNumber`,`AddedWH`) VALUES('$selectaf','$addedhours')";

             $getResult =mysql_query($sql1);
             if(mysql_affected_rows() > 0)
             {

             } 
             else{

             }

    else


                $tempname = $row['Field'];
                $sql2 = "UPDATE editedworkhours SET AddedWH ='".$_GET["addedhours"]."' WHERE AFNumber='".$_GET["selectaf"]."'";
                $result2 = mysqli_query($con,$sql2);
                if ($con->query($sql2) === TRUE) {
                } else {
                    echo "Error: " . $sql2 . "<br>" . $con->error;
                    echo '<script>swal("Error", "Something went wrong '.$con->error.'", "error");</script>';
                }
     echo '<script>swal("Success", "Changes have been saved", "success");</script>';  


} END IF;
echo $menu;

You code is not very clear. How about an outline of what you are trying to do. For example, with an insert per post it looks like perhaps you are accumulating a record for each added hours entry? Is this intended? Also, the code you've commented out mixes up references to sql2 and sql3 which adds to the confusion. — A Smith
– A Smith, Commented Aug 1, 2015 at 10:15
@MaxHaaksman no the accumaulation isn't intended, it's the problem. The intention is for a new inserted value for an employee to replace the old existing value. And that's what i'm trying to do with the commented section, however it's not working. — dan
– dan, Commented Aug 1, 2015 at 10:21
Wait, you want to replace the existing value entirely? Why you're using the operator +=, then, in the first place? — stef77
– stef77, Commented Aug 2, 2015 at 15:28
So, actually, everything should work now...? If it's about deciding whether to INSERT or UPDATE, to for Mureinik's answer, though I would just check via SQL for the existence of the entry and then decide whether to INSERT or to UPDATE, but I don't really understand your question from this point on. — stef77
– stef77, Commented Aug 3, 2015 at 5:30

Mureinik · Accepted Answer · 2015-08-03 05:37:24Z

5

+50

One elegant way of doing this is defining afnumber as the table's primary key:

ALTER TABLE `editedworkhours` 
ADD CONSTRAINT `editedworkhours_pk` PRIMARY KEY (`afnumber`);

EDIT:
As Gordon noted in the comments, if you already have a primary key, you could add a unique constraint:

ALTER TABLE `editedworkhours` 
ADD CONSTRAINT `editedworkhours_uc` UNIQUE (`afnumber`);

Once you have the constraint in place, you can utilize MySQL's insert's on duplicate key update clause:

INSERT INTO `editedworkhours`
(`afnumber`, `addedwh`)
VALUES ($selectaf, $addedwh)
ON DUPLICATE KEY UPDATE `addedwh` = VALUES(`addedwh`)

edited Aug 3, 2015 at 5:37

answered Aug 1, 2015 at 9:52

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

Note: this will work by declaring afnumber to be unique (in case the table already has a primary key).

stef77 Over a year ago

Well, and if it wasn't a unique column, this wouldn't work at all. But it seems in this case, it's unique, so everything should be fine. If you set addedwh to $addedwh, that is. OP doesn't want to increment.

Walker Farrow · Accepted Answer · 2015-08-04 19:46:11Z

Well, what I would do is make two statements that run - 1 INSERT and 1 UPDATE. The INSERT however would check that the same afnumber does not already EXIST. Here would be a sample script:

  if(isset($_POST['submit'])) {

   $addedhours = $_POST['AddedHours'];
   $selectaf = $_POST['SelectAF'];

$sql1="INSERT INTO `editedworkhours` (`AFNumber`,`AddedWH`) 
select '$selectaf','$addedhours'
where not exists (select afnumber from editedworkhours where afnumber = '$selectaf')";

$getResult =mysql_query($sql1);

 if(mysql_affected_rows($getResult) == 0) {

 $sql2 = "UPDATE editedworkhours SET AddedWH ='".$_POST["addedhours"]."' WHERE AFNumber='".$_POST["selectaf"]."'";

 $result2 = mysql_query($sql2);
 }

}

The above does 2 things:

a) Inserts if the record does not exist b) Updates if the insert results in 0 records

I also noted that in your "update" section you have $_GET superglobals whereas the insert has $_POST so I changed them both to $_POST. Not sure if that was bugging anything...

Community · Accepted Answer · 2017-05-23 10:27:21Z

If you're talking about this fragment: $sql3 = "UPDATE editedworkhours SET AddedWH+='".$_GET["addedhours"]."' WHERE AFNumber='".$_GET["selectaf"]."'";, your question is about the column AddedWH, right? What data type is this, I suppose some sort of VARCHAR?

You should change the column's type to e.g. INTEGER and use ... SET AddedWH = AddedWH + ".$_GET["addedhours"]." ....

What you're doing now is adding a string (the $_GET variable is enclosed in ticks: '), hence the SQL statement is interpreted as string concatenation.

Of course, this is open fo all kinds of SQL injections, but that wasn't part of your question (consider using prepared statements, you will find a ton of information about this topic here and elsewhere on the internet (i.e., Google), see e.g. How can I prevent SQL injection in PHP?).

Rick James · Accepted Answer · 2015-08-05 19:36:57Z

0

Do NOT mix mysql_* and mysqli_* ! Do not use mysql_* any more.

Always test for errors. Could it be that the INSERT got "Duplicate key" and you did not notice?

Consider using

INSERT ... ON DUPLICATE KEY UPDATE ...

answered Aug 5, 2015 at 19:36

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Updating the database using php

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related