Website form not submitting data to mssql database

Question

I have spent days trying to figure how to get form data on my webpage to insert into my employee table on the forklift database mssql. when i click submit on the form it refreshes the page with Connection established but no data in the database.

<?php

/* Connect using Windows Authentication. */

$serverName = "EXAMPLE";
$connectionOptions = array("Database"=>"FORKLIFT");

/* Connect using Windows Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionOptions);

if($conn) 

{
echo "Connection established.<br />";
}

else 

{
echo "Connection could not be established.<br />";
die(print_r(sqlsrv_errors(), true));

}

if(empty($_POST) === false && empty($errors)=== true)

{ 
//assign form input to variables
$FIRSTNAME = $_POST["FIRSTNAME"];
$LASTNAME = $_POST["LASTNAME"];
$DATEOFBIRTH = $_POST["DATEOFBIRTH"];
$PHONENUMBER = $_POST["PHONENUMBER"];
$ADDRESS = $_POST["ADDRESS"];

/*Insert data.*/
$INSERT_ROW = $query = "INSERT INTO              
EMPLOYEE(FIRSTNAME,LASTNAME,DATEOFBIRTH,PHONENUMBER,ADDRESS)
VALUES ('$FIRSTNAME','$LASTNAME','$DATEOFBIRTH','$PHONENUMBER','$ADDRESS')";

$result = sqlsrv_prepare($conn,$query)or die('Error querying MSSQL     
database');
        sqlsrv_execute($result);              
}

?>

HTML

        <form name="submit" action="employee.php" method="POST" >


           <h2>Register New Member</h2>

           <table border="0">
              <tr>
                 <td>FIRSTNAME</td>
                 <td>
                    <input type="text" name="FIRSTNAME" id="FIRSTNAME"/>
                 </td>
              </tr>

              <tr>
                 <td>LASTNAME</td>
                 <td>
                    <input type="text" name="LASTNAME" id="LASTNAME"/>
                 </td>
              </tr>

              <tr>
                 <td>DATE_OF_BIRTH</td>
                 <td>
                    <input type="date" name="DATE_OF_BIRTH" id="DATE_OF_BIRTH"/>
                 </td>
              </tr>   

              <tr>
                 <td>PHONENUMBER</td>
                 <td>
                    <input type="text" name="PHONENUMBER" id="PHONENUMBER"/>
                 </td>
              </tr>     

              <tr>
                 <td>ADDRESS</td>
                 <td>
                    <input type="text" name="ADDRESS" id="ADDRESS"/>
                 </td>
              </tr>  


              <tr>
                 <td></td>
                 <td align="right"><input type="submit" name="submit" value="REGISTER"/></td>

              </tr>
           </table>

Print `$INSERT_ROW` before executing the query. Is it correct? Did you close form tag? — user4035, Sep 09 '16 at 15:11
I don't have MSSQL set up so I can't test your code, but have you tried echoing out the $_POST & $errors variables? It looks like your if statement is evaluating false and not executing the code in it as a result. — Barry Thomas, Sep 09 '16 at 15:12
Actually, where is $errors defined? If it is not defined, AFAIK your if will be evaluating false due to undefined var $errors — Barry Thomas, Sep 09 '16 at 15:15
Is this right? Sorry all new to me echo $FIRSTNAME; echo $LASTNAME; echo $DATEOFBIRTH; echo $PHONENUMBER; echo $ADDRESS; — Jane, Sep 09 '16 at 15:20
It's not directly relevant to your question, but I do notice that your code is vulnerable to sql injection attacks. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Brian, Sep 09 '16 at 15:42

Professor Abronsius · Answer 1 · 2016-09-10T08:47:48.793

I made a couple of minor alterations but cannot test to see - run it and see what the resultant sql statement looks like. I hope it helps.

<?php
    if( !empty( $_POST ) && isset( $_POST["FIRSTNAME"], $_POST["LASTNAME"], $_POST["DATE_OF_BIRTH"], $_POST["PHONENUMBER"], $_POST["ADDRESS"] ) ) {

        /* 
            There is no point creating a db connection if a later condition fails
            so create the db conn after testing key variables are set
        */

        $server = "EXAMPLE";
        $options = array( "Database"=>"FORKLIFT" );


        $conn = sqlsrv_connect( $server, $options );

        /* In production environment do not display actual errors! */
        if( !$conn ) die( print_r( sqlsrv_errors(), true ) );   


        /* as pointed out by @Daniel Rutter, the field is actually called `DATE_OF_BIRTH` */
        $FIRSTNAME = $_POST["FIRSTNAME"];
        $LASTNAME = $_POST["LASTNAME"];
        $DATEOFBIRTH = $_POST["DATE_OF_BIRTH"];
        $PHONENUMBER = $_POST["PHONENUMBER"];
        $ADDRESS = $_POST["ADDRESS"];



        $sql = "INSERT INTO EMPLOYEE
            ( FIRSTNAME,LASTNAME,DATEOFBIRTH,PHONENUMBER,ADDRESS )
                VALUES
            ( '$FIRSTNAME','$LASTNAME','$DATEOFBIRTH','$PHONENUMBER','$ADDRESS' )";



        /* Comment out the following line after running the script and observing the output here */
        exit( $sql );


        /* I assume that `sqlsrv_prepare` returns either true or false */
        $stmt = sqlsrv_prepare( $conn, $sql ) or die( 'Error querying MSSQL database' );

        if( $stmt ) sqlsrv_execute( $stmt );

    /* debug post vars */           
    } else { echo 'Error::' . print_r($_POST,true ); }
?>

Having briefly had a look at the online PHP manual for the various sqlsrv_* commands perhaps you ought to try along these lines - it follows closely with one of the examples given - though it is untested due to not having a mssql server instance to play with.

<?php
    if( !empty( $_POST ) && isset( $_POST["FIRSTNAME"], $_POST["LASTNAME"], $_POST["DATE_OF_BIRTH"], $_POST["PHONENUMBER"], $_POST["ADDRESS"] ) ) {

        /* 
            There is no point creating a db connection if a later condition fails
            so create the db conn after testing key variables are set
        */

        $server = "EXAMPLE";
        $options = array( "Database"=>"FORKLIFT" );


        $conn = sqlsrv_connect( $server, $options );

        /* In production environment do not display actual errors! */
        if( !$conn ) die( print_r( sqlsrv_errors(), true ) );   



        $FIRSTNAME = $_POST["FIRSTNAME"];
        $LASTNAME = $_POST["LASTNAME"];
        $DATEOFBIRTH = $_POST["DATE_OF_BIRTH"];
        $PHONENUMBER = $_POST["PHONENUMBER"];
        $ADDRESS = $_POST["ADDRESS"];



        $sql = "INSERT INTO EMPLOYEE
            ( FIRSTNAME, LASTNAME, DATEOFBIRTH, PHONENUMBER, ADDRESS )
                VALUES
            ( ?,?,?,?,? )";

        $params=array(
            &$FIRSTNAME,
            &$LASTNAME,
            &$DATEOFBIRTH,
            &$PHONENUMBER,
            &$ADDRESS
        );

        /* add the posted variables as an array here as the third arg */
        $stmt = sqlsrv_prepare( $conn, $sql, $params ) or die( 'Error querying MSSQL database' );
        if( $stmt ) sqlsrv_execute( $stmt );



    /* debug post vars */           
    } else { 
        echo 'Error::' . print_r( $_POST, true );
    }
?>

From the PHP online manual regarding sqlsrc_prepare:

Prepares a query for execution. This function is ideal for preparing a query that will be executed multiple times with different parameter values.

and

When you prepare a statement that uses variables as parameters, the variables are bound to the statement. This means that if you update the values of the variables, the next time you execute the statement it will run with updated parameter values. For statements that you plan to execute only once, use sqlsrv_query().

I put in your code and when i hit the form submit button it just takes me to a blank page. No data in the database, no error messages — Jane, Sep 09 '16 at 15:34
@Daniel Rutter pointed out ( and I hadn't spotted it ) that the date of birth field is actually called `DATE_OF_BIRTH` ~ try again with the new changes — Professor Abronsius, Sep 09 '16 at 15:37
I'll just leave the obligatory [sql injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) comment here. — Brian, Sep 09 '16 at 15:41
thanks for you help so far ramraider really appreciated, i made the changes but still no data appearing — Jane, Sep 09 '16 at 15:47
When I just tested this ( without the `mssql` commands ) it printed an sql statement that looks OK - — Professor Abronsius, Sep 09 '16 at 16:02
so i have been working on this all night. I started a new test database and form. The form submits, connection established and echos the newly entered data when submitted, however still no data in the ms sql database. Is there any way i can test that my connections are correct, is this a server problem? — Jane, Sep 10 '16 at 08:10
does the sql statement get echoed to screen? The `exit( $sql )` statement would prevent the execution of the sql - it is simply to test what the end sql statement looks like ( and it looked fine when I tested btw ) - if you can see the final sql on screen try running that sql in your `mssql` client directly... if that works ok then it does point to your connection not being correct perhaps — Professor Abronsius, Sep 10 '16 at 08:27
having never used these functions I don't know the syntax but looking at http://php.net/manual/en/function.sqlsrv-prepare.php shows that ( like other dbs ) that the prepared statement should be using placeholders and the variables are passed into `sqlsrv_prepare` as an array as the third argument ratherthan directly embedding the variables — Professor Abronsius, Sep 10 '16 at 08:31
does it run correctly in the mssql server client directly ( no php )?? Also, made some changes according to the manual regarding `sqlsrv` commands — Professor Abronsius, Sep 10 '16 at 08:45
I copied the echoed Insert data to ms sql and it added the data correctly into the table, so the connection is the problem? — Jane, Sep 10 '16 at 09:12

score 1 · Answer 2 · answered Sep 09 '16 at 15:34

1

I noticed that you are attempting to post data to a database, that is not actually named correctly on the PHP side, this would just result in the information related to your DATE OF BIRTH not being posted in the first place, as it has to match the PHP side exactly when you declare to retrieve posted data. I am pretty sure from memory that you only need to change the name in the form on the client side to DATEOFBIRTH, or you will have to change the PHP side to DATE_OF_BIRTH instead of this, whilst keeping the client side as DATE_OF_BIRTH

<input type="date" name="DATE_OF_BIRTH" id="DATE_OF_BIRTH"/>

NEEDS TO BE:

<input type="date" name="DATEOFBIRTH" id="DATEOFBIRTH"/>

answered Sep 09 '16 at 15:34

Daniel Rutter

57
6

Spotted and changed but still not working
what should form name be related to, have i got that right?
– Jane Sep 09 '16 at 15:44
Make sure to use lowercase in the form name, action and method at all times, you should never use uppercase. The PHP script named volunteer.php, is it linked to the PHP side where you have written your code for posting this data by the way? I would check this just in case. I have had this happen by me early on in my career :D What I mean by this is, you could have 2 PHP files and the wrong one could be linked. – Daniel Rutter Sep 09 '16 at 15:51
I think I know what the problem is, do any other PHP scripts work at all? Because if you have not got the MYSQLI Extention enabled or whichever one you are using, you will just be faced with a blank screen, as the script won't have permission to run. I had this issue when I first installed cPanel. – Daniel Rutter Sep 09 '16 at 16:14

Website form not submitting data to mssql database

2 Answers2