Semi-functionality in inserting data based on if/else condition

Question

I have a table called"editedworkhours" that is initially empty. I'm inserting into it addedwh and idnumber based on an if/else condition. Such that: If addedwh is empty (then insertion) else (update, because there is already a value). Now the update part is functioning perfectly(because i manually added values in the table in the database to try the update). However if an idnumber (which is the id) doesn't exist in the table, the insertion doesn't occur. Any suggestions on what could be preventing the if/else condition from functioning accurately?

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;

Not sure, but shouldnt it be:$sql1="SELECT addedwh FROM editedworkhours WHERE afnumber='$selectaf'"; instead of row[0]? — kl78, Aug 03 '15 at 12:33
could you do an echo on the count, just to be sure you get 0 there when you expect it? Also an echo with mysql_num_rows()... — kl78, Aug 03 '15 at 12:47
k, i just saw there is a mysql_query($sql); missing, so if you did not forget to copy it in the question, this should be the error, you never run your query — kl78, Aug 03 '15 at 12:54
I added it as answer, but akshay did also already update his answer, so you can choose... — kl78, Aug 03 '15 at 13:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85006/discussion-between-dan-and-kl78). — dan, Aug 03 '15 at 13:07
Can I suggest you to switch to Mysqli or better PDO? Mysql is deprecated and will be removed in the next updates. Also your code is vulnerable to sql injection. You should prepare your statement to sanitize your input — Lelio Faieta, Aug 03 '15 at 13:36

Mike Brant · Answer 1 · 2015-08-03T18:58:59.423

I would remove the conditional and simply do an INSERT ... ON DUPLICATE KEY UPDATE ... query. This would allow you to eliminate the initial SELECT query to determine if the record exists or not.

INSERT INTO editedworkhours (IDNumber,AddedWH)
VALUES($selectid, $addedhours)
ON DUPLICATE KEY UPDATE AddedWH = AddedWH + $addedhours

This would require a unique (or primary key) index on IDNumber.

You can check the mysql_affected_rows() value after performing the query to determine if an insert or update occurred. For inserts, the returned value will be 1. For updates the value will be 2.

Note that I also removed the string delimiters around your insert, as I am assuming you have INT type fields in both cases.

Putting it together that may 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 are getting in comments and think about using MySQLi or PDO in combination with parametrized prepared statements. I did show code example using mysql though for consistency with your current usage.

Akshay · Answer 2 · 2015-08-03T12:55:50.257

0

Where's your mysql_query for $sql ?

 if(empty($count))
        {
    $sql="INSERT INTO editedworkhours (AFNumber,AddedWH) VALUES('$selectaf','$addedhours')";
    mysql_query($sql);
}

edited Aug 03 '15 at 12:55

answered Aug 03 '15 at 12:37

Akshay

2,244
3
15
34

Also, stop using Mysql. – Akshay Aug 03 '15 at 12:53
i didn't get any error, just no output... Any why should i stop it? – dan Aug 03 '15 at 12:54
@dan Updated my answer. You were missing `mysql_query`. And why you should stop using mysql? http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Akshay Aug 03 '15 at 12:57
i did it and also nothing\ – dan Aug 03 '15 at 13:01
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85005/discussion-between-dan-and-akshay). – dan Aug 03 '15 at 13:04

score 0 · Answer 3 · answered Aug 03 '15 at 13:03

You just forget the execution of your 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>';
 }

Semi-functionality in inserting data based on if/else condition

3 Answers3