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, Aug 01 '15 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, Aug 01 '15 at 10:21
Wait, you want to *replace* the existing value entirely? Why you're using the operator `+=`, then, in the first place? — stef77, Aug 02 '15 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, Aug 03 '15 at 05:30
@stef77 yeah that's what i decided to do..And tried it, however i'm not sure how to check in an if/else statement where a value in column is null. Any link? — dan, Aug 03 '15 at 05:51
Doesn't matter whether it's null. `SELECT` the `AFNumber` from `editedworkhours`. If your result set contains a row, you've inserted before, hence: execute an `UPDATE`, else execute an `INSERT`. — stef77, Aug 03 '15 at 06:28
@stef77 the thing is that the rows are already there, the only thing that is inserted or not is the addedwh — dan, Aug 03 '15 at 06:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/84971/discussion-between-dan-and-stef77). — dan, Aug 03 '15 at 06:35
I'm on mobile, so sorry, no. Please rephrase your question or start a new one about this, this seems to have nothing to do with the original problem. At least, I'm lost now. Do you get errors? What's in the database before/after an update? What is wrong with the result, what should the result be? Your code isn't even valid PHP anymore, `if(AddedWH IS NULL)`...? — stef77, Aug 03 '15 at 06:43
@stef77 just one question if(AddedWH IS NULL) how do i check if the value is null in it in an if condition? — dan, Aug 03 '15 at 06:46
Your code is using both `mysql_` and `mysqli_` functions. You should use one or the other, but not both. — Andy Lester, Dec 14 '15 at 17:27

Mureinik · Answer 1 · 2015-08-03T05:37:24.153

5

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 03 '15 at 05:37

answered Aug 01 '15 at 09:52

Mureinik

297,002
52
306
350

Note: this will work by declaring `afnumber` to be unique (in case the table already has a primary key). – Gordon Linoff Aug 02 '15 at 23:41
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. – stef77 Aug 03 '15 at 05:34

Walker Farrow · Answer 2 · 2015-08-04T19:46:11.327

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...

score 1 · Answer 3 · edited May 23 '17 at 10:27

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?).

score 0 · Answer 4 · answered Aug 05 '15 at 19:36

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 05 '15 at 19:36

Rick James

135,179
13
127
222

Updating the database using php

4 Answers4