We help IT Professionals succeed at work.

PHP to MSSQL DB: mssql_query(): Cannot insert the value NULL into column...

Medium Priority
1,467 Views
Last Modified: 2012-05-11
Below is the code I am trying to use to insert some vlaues into an MS SQL table.  Below that is a log of errors I am receiving.  This is done on Ubuntu 10.04, PHP5, unknown MS SQL version (althouhg I can find that out if it matters).
<?php
error_reporting(E_ALL);

//database credentials
$db_host = "xxx";
$db_user = "xxx";
$db_pass = "xxx";
$db = "xxx";
$table = "yyy";

//define variables
$type = $_GET['type'];
$ext = $_GET['ext'];
$did = $_GET['indid'];
$job = $_GET['job'];
//cid_name = $_GET['cidName'];
$cid_number = $_GET['cidNumber'];

// Connect to MSSQL
$link = mssql_connect($db_host, $db_user, $db_pass);

if (!$link || !mssql_select_db($db, $link)) {
    die('Unable to connect or select database!');
}
var_dump($link);

// Perform the query
$sql = "INSERT INTO $table (callID, calltype, extension, phonenumber, calldatetime) VALUES ($job, $type, $ext, $cid_number, CURRENT_TIMESTAMP)";
$result = mssql_query($sql);
//$row = mssql_fetch_array($result);

//echo $row[0];

?>

Open in new window


And the error:
[Tue Apr 19 16:40:23 2011] [error] [client 192.168.1.90] PHP Warning:  mssql_query(): message: Cannot insert the value NULL into column 'callID', table 'xxx.yyy'; column does not allow nulls. INSERT fails. (severity 16) in /var/www/cdr_sql.php on line 29
[Tue Apr 19 16:40:23 2011] [error] [client 192.168.1.90] PHP Warning:  mssql_query(): General SQL Server error: Check messages from the SQL Server (severity 16) in /var/www/cdr_sql.php on line 29
[Tue Apr 19 16:40:23 2011] [error] [client 192.168.1.90] PHP Warning:  mssql_query(): Query failed in /var/www/cdr_sql.php on line 29

Open in new window

Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
looks like you are not passing any values to page

$job = $_GET['job'];

--> try this to see if message changes...

$job = $_REQUEST['job'];
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if message changes, replace all GET with REQUEST

Commented:
The problem is that $job does not contain anything, therefore NULL.

Depending on what datatype it is, you should do something like:
If(isset($_GET['job'])) {
$job = $job = $_GET['job'];
} else {
$job = "Some default value";
}

Open in new window


That way, $job will always have a value. Depending on what you expect the users to write, its always a good idea to validate the fields before doing a query. Common saying is NEVER trust the user! :)

//Scifo_dk

Author

Commented:
Ive replaced GET with REQUEST with no change to the error.  I will implement scifo's suggestion and let you know.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
"I will implement scifo's suggestion"

he just say put a default value, but your problem is not default value... you expect the value from request...

how do you call this page or how do you use? you cannot call the page directly, some application or page should call this page... anyway, to test it modify your code like

#$job = $_REQUEST['job'];
$job = 1;

and try again...
G Trurab KhanSnr. Development Manager
CERTIFIED EXPERT

Commented:
replace the line
"$sql = "INSERT INTO $table (callID, calltype, extension, phonenumber, calldatetime) VALUES ($job, $type, $ext, $cid_number, CURRENT_TIMESTAMP)";"
 With
"$sql = "INSERT INTO $table (callID, calltype, extension, phonenumber, calldatetime) VALUES ("+$job+", "+$type+", "+$ext+", "+$cid_number+", CURRENT_TIMESTAMP)";"

In this way you will be passing the values as these variables which you are passing means nothing in SQL. Add quotes & type conversion where required.


Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ended up with something like this.

Thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.