lorsungcu
asked on
PHP to MSSQL DB: mssql_query(): Cannot insert the value NULL into column...
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).
And the error:
<?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];
?>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
if message changes, replace all GET with REQUEST
The problem is that $job does not contain anything, therefore NULL.
Depending on what datatype it is, you should do something like:
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
Depending on what datatype it is, you should do something like:
If(isset($_GET['job'])) {
$job = $job = $_GET['job'];
} else {
$job = "Some default value";
}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
ASKER
Ive replaced GET with REQUEST with no change to the error. I will implement scifo's suggestion and let you know.
"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...
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...
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.
"$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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ended up with something like this.
Thanks.
Thanks.
$job = $_GET['job'];
--> try this to see if message changes...
$job = $_REQUEST['job'];