PHP not inserting into database SQL SERVER 2008

Question

I have this form that I want to use to capture data and insert into a database:

<form actoin="request-new-price.php" method="post" id="demo-form2" data-parsley-validate>
<div>
    <label for="salesRep">Sales Rep:</label>
    <div>
        <input type="text" name="salesRep" id="salesRep" required="required" value="<?php echo $user['userName']; ?>">
    </div>
</div>
<div>
    <label for="CardName">Customer Name</label>
    <div>
        <input type="text" id="CardName" name="CardName" required="required" value="<?php echo $selectedCustomerName ?>">
    </div>
</div>
<div>
    <label for="CardCode">Customer Code</label>
    <div>
        <input type="text" id="CardCode" name="CardCode" required="required" value="<?php echo $selectedCustomerID ?>">
    </div>
</div>
<div>
    <label for="ItemName">Product Name</label>
    <div>
        <input type="text" id="ItemName" name="ItemName" required="required" value="<?php echo $selectedProductName ?>">
    </div>
</div>
<div>
    <label for="ItemCode">Product Code</label>
    <div>
        <input type="text" id="ItemCode" name="ItemCode" required="required" value="<?php echo $selectedProductCode ?>">
    </div>
</div>
<div>
    <label for="Price">Current Price</label>
    <div>
        <input type="text" id="Price" name="Price" required="required" value="£<?php echo $selectedProductPrice ?>">
    </div>
</div>
<div>
    <label for="requestedPrice">Requested Price</label>
    <div>
        <input type="text" id="requestedPrice" name="requestedPrice" required="required" value="£">
    </div>
</div>
<div>
    <div>
        <a href="specialprice.php?custCode=<?php echo $customer['CardCode'];?>&custName=<?php echo $customer['CardName'];?>">Cancel</a>
        <button type="submit" id="submit" name="submit" value="1">Submit</button>
    </div>
</div>
</form>

And here is my SQL/PHP:

<?php 

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

    $query = prepare("INSERT INTO PriceRequests (salesRep, CardName, CardCode, ItemName, ItemCode, Price, requestedPrice) 
                VALUES (:salesRep, :cardName, :cardCode, :itemName, itemCode, :itemPrice, :newPrice)
            ");
            $insertSql = sqlsrv_query($sapconn, $query);

    $insertSql->bindParam(":salesRep",$salesRep);
    $insertSql->bindParam(":cardName",$cardName);
    $insertSql->bindParam(":cardCode",$cardCode);
    $insertSql->bindParam(":itemName",$itemName);
    $insertSql->bindParam(":itemCode",$itemCode);
    $insertSql->bindParam(":itemPrice",$itemPrice);
    $insertSql->bindParam(":newPrice",$newPrice);

    $salesRep = trim($_POST['salesRep']);
    $cardName = trim($_POST['CardName']);
    $cardCode = trim($_POST['CardCode']);
    $itemName = trim($_POST['ItemName']);
    $itemCode = trim($_POST['ItemCode']);
    $itemPrice = trim($_POST['Price']);
    $newPrice = trim($_POST['requestedPrice']);


    $insertSql->execute();   
    return $insertSql;
}

?>

But the data is not inserting into the database I am fairly new to PHP and this is my first attempt at writing back to the database, so I may be missing something simple, or it may be completely wrong.

Either way all help is appreciated.

EDIT:

My PHP is now this:

if(isset($_POST['submit'])){
   //print_r($_POST);

    $query = "INSERT INTO PriceRequests (salesRep, CardName, CardCode, ItemName, ItemCode, Price, requestedPrice) 
                                        VALUES (:salesRep, :cardName, :cardCode, :itemName, :itemCode, :itemPrice, :newPrice)
                    ";
    $stmt = $sapconn->prepare($query);

    $salesRep = (isset($_POST['salesRep']) && !empty($_POST['salesRep']))?$_POST['salesRep'] : NULL;
    $cardName = (isset($_POST['CardName']) && !empty($_POST['CardName']))?$_POST['CardName'] : NULL;
    $cardCode = (isset($_POST['CardCode']) && !empty($_POST['CardCode']))?$_POST['CardCode'] : NULL;
    $itemName = (isset($_POST['ItemName']) && !empty($_POST['ItemName']))?$_POST['ItemName'] : NULL;
    $itemCode = (isset($_POST['ItemCode']) && !empty($_POST['ItemCode']))?$_POST['ItemCode'] : NULL;
    $itemPrice = (isset($_POST['Price']) && !empty($_POST['Price']))?$_POST['Price'] : NULL;
    $newPrice = (isset($_POST['requestedPrice']) && !empty($_POST['requestedPrice']))?$_POST['requestedPrice'] : NULL;

    $stmt->bindValue(':salesRep', $salesRep, PDO::PARAM_STR);
    $stmt->bindValue(':cardName', $cardName, PDO::PARAM_STR);
    $stmt->bindValue(':cardCode', $cardCode, PDO::PARAM_STR);
    $stmt->bindValue(':itemName', $itemName, PDO::PARAM_STR);
    $stmt->bindValue(':itemCode', $itemCode, PDO::PARAM_STR);
    $stmt->bindValue(':itemPrice', $itemPrice, PDO::PARAM_STR);
    $stmt->bindValue(':newPrice', $newPrice, PDO::PARAM_STR);

    $stmt->execute();   
    return $stmt;
    }

But i still have no input to my database and i am getting the following error: PHP Fatal error: Uncaught Error: Call to a member function prepare() on resource

DB Connection:

<?php 
$serverName = "serverName";
$connectionInfo = array( "Database"=>"database_name", "UID"=>"user_Id", "PWD"=>"Password", "ReturnDatesAsStrings"=>true);
$sapconn = sqlsrv_connect( $serverName, $connectionInfo);
?>

You're mixing `sqlsrv_` with `PDO::`. – Phiter Nov 24 '16 at 10:29 — Phiter, Nov 24 '16 at 10:29
Can i ask which you would suggest in using? – PHPNewbie Nov 24 '16 at 10:33 — PHPNewbie, Nov 24 '16 at 10:33

score 1 · Answer 1 · edited Nov 25 '16 at 10:02

1

you know there is a typo in the first line? Won't submit with that.

 <form actoin="request-new-price.php" method="post" id="demo-form2" data-       parsley-validate>

change to form action for a start

edited Nov 25 '16 at 10:02

Samir Selia

7,007
2
11
30

answered Nov 24 '16 at 10:30

D.Wells

125
10

Thanks, I hadn't noticed that! but still my form doesn't submit. – PHPNewbie Nov 24 '16 at 10:32

Subhra Jyoti Lahiri · Accepted Answer · 2016-11-25T06:59:13.540

One more typo in the PHP code :

$query = prepare("INSERT INTO PriceRequests (salesRep, CardName, CardCode, ItemName, ItemCode, Price, requestedPrice) 
                VALUES (:salesRep, :cardName, :cardCode, :itemName, itemCode, :itemPrice, :newPrice)
            ");

The placeholder itemCode does not have the suffix ":". Check that and try. Thank you.

UPDATE:

I tried something that you wrote in the question. You have tried to bind the parameters to the placeholders before the parameters are assigned.

When I tried to do so, I got exception. I think this may the reason the data is not getting inserted.

I would suggest you to write the code in the following manner :

PHP CODE :

<?php 

if(isset($_POST['submit'])){
    print_r($_POST); //Unnecessary, you can remove it

    $query = prepare("INSERT INTO PriceRequests (salesRep, CardName, CardCode, ItemName, ItemCode, Price, requestedPrice) 
                VALUES (:salesRep, :cardName, :cardCode, :itemName, :itemCode, :itemPrice, :newPrice)
            ");
            $insertSql = sqlsrv_query($sapconn, $query);

    $salesRep = trim($_POST['salesRep']);
    $cardName = trim($_POST['CardName']);
    $cardCode = trim($_POST['CardCode']);
    $itemName = trim($_POST['ItemName']);
    $itemCode = trim($_POST['ItemCode']);
    $itemPrice = trim($_POST['Price']);
    $newPrice = trim($_POST['requestedPrice']);


    $insertSql->bindParam(":salesRep",$salesRep);
    $insertSql->bindParam(":cardName",$cardName);
    $insertSql->bindParam(":cardCode",$cardCode);
    $insertSql->bindParam(":itemName",$itemName);
    $insertSql->bindParam(":itemCode",$itemCode);
    $insertSql->bindParam(":itemPrice",$itemPrice);
    $insertSql->bindParam(":newPrice",$newPrice);

    $insertSql->execute();   
    return $insertSql;
}

?>

I would suggest a few change:

1. As PDO is used here, use a variable to get the Database connection (lets assume its $db_conn).

Instead of

$insertSql = sqlsrv_query($sapconn, $query);

use

$db_conn = new PDO(<connection-string>, <user-name>, <password>);

$stmt = $db_conn->prepare($query)

Then bind the value by :

$stmt->bindValue(<placeholder>, <variable_vlaue>, <value_type>);

eg : $stmt->bindValue(:itemName, $itemName, PDO::PARAM_STR);

Then perform execution:

$stmt->execute();

2. If you place some validation of the data it will be helpful :

Assign the value of POST to the variables via a validation

eg :

$itemName = (isset($_POST['ItemName']) && !empty($_POST['ItemName']))?$_POST['ItemName'] : NULL;

Here, when insert query is executed with 'NULL' it will throw an exception.

N.B. : try-catch block should be used.

I think it should work now.

Please feel free to tell if it does not work, I will check again.

Thanks for the feedback, I had spotted that and updated, to no avail. — PHPNewbie, Nov 24 '16 at 13:35
I have checked and updated my answer. Please check it and see, if you have any doubt, please comment it out. Thanks. — Subhra Jyoti Lahiri, Nov 25 '16 at 07:00
Thanks so much for taking the time to help, i have updated my code but i am getting this error: PHP Fatal error: Uncaught Error: Call to a member function prepare() I will update my original question to show you my code. — PHPNewbie, Nov 25 '16 at 09:58
@PHPNewbie : It will be helpful for you if you use PDO in order to do your work. It will be more easy and more secure. Can you write the code by which you are creating the database connection ? — Subhra Jyoti Lahiri, Nov 25 '16 at 10:01
`$sapconn = new PDO("sqlsrv:Server=YouAddress;Database=YourDatabase", "Username", "Password");` <= Try this for connecting to the Database instead of `sqlsrv_connect()`. Please do let us know. — Subhra Jyoti Lahiri, Nov 25 '16 at 10:19
No still not working, i get a blank screen with this error: PHP Fatal error: Invalid handle returned — PHPNewbie, Nov 25 '16 at 10:31
`$login = new PDO("sqlsrv:server=MYSQLSERVER\SQLEXPRESS;Database=YourDatabase;ConnectionPooling=0", "Username", "Password");` <= try this. Use ConnectionPooling = 0; [link](http://stackoverflow.com/a/38695723/7063928) — Subhra Jyoti Lahiri, Nov 25 '16 at 10:39
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/129031/discussion-between-newbiejavadeveloper-and-phpnewbie). — Subhra Jyoti Lahiri, Nov 25 '16 at 11:52

PHP not inserting into database SQL SERVER 2008

2 Answers2