SQL addition through php script is behaving strangely

Question

I have a strange problem with additions in SQL via a php script. I have the following form in PL.php:

        echo "<form id='editploptionform'>
            <div class='table-row'>
                <input type='hidden' name='id' value='".$row['id']."'>
                <input type='hidden' name='order_id' value='".$row['order_id']."'>
                <div class='table-column-options'><input type='text' name='edit_option_number' value='".$row['option_number']."'></div>
                <div class='table-column-options2'><input type='text' name='edit_option_description' value='".$row['option_description']."'></div>
                 <input type='submit' value='Bewerk' class='edit'>
            </div>
            </form>

This form is handled via a jQuery script in script.js:

    $(document).on('submit', '#editploptionform', function() {
            $.ajax({
                type: 'POST',
                data: $(this).serialize(),
                url: "editploption.php",
                success:function(data){

                    bootbox.alert("De optie is aangepast!", function(){

                            location.reload();
                    });

                }
            });
            return false;
    });

On editploption.php the function is called that updates the option:

$obj = new PLQuery($_POST['order_id']);
$obj->doEditPlOption($_POST['id'], $_POST['order_id'], $_POST['edit_option_number'], $_POST['edit_option_description']);

The function doEditPlOption() resides in the class PLQuery in PLQuery.php and looks as follows:

class PLQuery
{
    private $hookup;
    private $tableMaster3 = '[docgen].[dbo].[pl]';

public function doEditPlOption($id, $orderid, $optionnumber, $optiondescription)
    {
        $this->hookup=UniversalConnect::doConnect();

        try 
        {

            $updateresult = $this->hookup->query("
                                              UPDATE [docgen].[dbo].[pl] SET 
                                                order_id = '".$orderid."', 
                                                option_number = '".$optionnumber."', 
                                                option_description = '".$optiondescription."', 
                                                revision = (SELECT MAX( revision ) + 1 FROM pl where order_id  = '".$orderid."')
                                                WHERE id = '".$id."';
                                                ");

            $updateresult->execute();


        }
        catch(PDOException $e)
        {
            echo $e;
        }

        $this->hookup = null;
    }

}

Everything works fine, except the addition to the revision column in the query. It concerns the following piece of code where things go wrong:

 revision = (SELECT MAX( revision ) + 1 FROM pl where order_id  = '".$orderid."')

Everytime the function doEditPlOption() is executed, revision is updated by adding 2 instead of 1. So for instance, revision is first 2, which is also the max, then after the update, revision should be 3, but for some reason revision is set to 4.

However, when I execute the query directly on the SQL database by using the following query, for instance:

  UPDATE [docgen].[dbo].[pl] SET 
            order_id = '217109', 
            option_number = '423', 
            option_description = 'vcvx', 
            revision = (SELECT MAX( revision ) + 1 FROM pl where order_id = '217109')
            WHERE id = '7'

the addition is working correctly, which means that revision is set to max + 1. I checked wether I am calling the function doEditPlOption() twice, but that is not the case.

What else could cause the strange behavior explained above?

I think it is being executed twice because you already called your query on your first line which includes the query and it was already executed because it is not a prepared statement. Then after that you did `$updateresult->execute();` which executed the query again.. Try to remove the `$updateresult->execute();` — sshanzel, Apr 13 '18 at 08:34

score 1 · Accepted Answer · answered Apr 13 '18 at 08:37

I think it is being executed twice because you already called your query on your first line which includes the query and it was already executed because it is not a prepared statement. Then after that you did $updateresult->execute(); which executed the query again.. Try to remove the $updateresult->execute(); or prepare your query first.

SQL addition through php script is behaving strangely

1 Answers1