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

Posted on 2011-04-19
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).

//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!');

// 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] 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] 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] PHP Warning:  mssql_query(): Query failed in /var/www/cdr_sql.php on line 29

Open in new window

Question by:lorsungcu
    LVL 51

    Expert Comment

    looks like you are not passing any values to page

    $job = $_GET['job'];

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

    $job = $_REQUEST['job'];
    LVL 51

    Expert Comment

    if message changes, replace all GET with REQUEST
    LVL 6

    Expert Comment

    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! :)

    LVL 2

    Author Comment

    Ive replaced GET with REQUEST with no change to the error.  I will implement scifo's suggestion and let you know.
    LVL 51

    Expert Comment

    "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...
    LVL 8

    Expert Comment

    replace the line
    "$sql = "INSERT INTO $table (callID, calltype, extension, phonenumber, calldatetime) VALUES ($job, $type, $ext, $cid_number, CURRENT_TIMESTAMP)";"
    "$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.

    LVL 9

    Accepted Solution

    If the page is called without any variables, or with variables missing, you'll get NULL values.  Your script should check for that; it seems that all variables are required for it to work properly.

    if(isset($_GET['type'])) {
     $type = $_GET['type'];
    else {
     $type = 'default value';
    //... rinse, wash, repeat.

    Open in new window

    LVL 2

    Author Closing Comment

    Ended up with something like this.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now