Default values are not defaulting?!

[kwilder]

I'm trying to modify a PHP application built by other developers.  I'm an ASP.NET guy but I know a little about PHP and MySql.

 

The application has been working fine for years on a Linux box, but now for many reasons it has to move.  I thinking of moving it to my Windows hosting servers, but there's a small issue.

 

For the most part the application works, but Inserts on any table only partially work.  Any database column that is marked NOT NULL and has a default value, is not defaulting to the value.  An error is returned saying that it needs a value for this column.

 

So the insert statements in the PHP code is already written without the columns that have "defaults" assigned in MySql, and it's these columns that are complaining that it needs a value.

 

None of the php code has changed since I've moved it onto my Windows Server 2003 development server, so I'm thinking it has to be either the php.ini settings or something to do with the Windows box specifically.

 

Does anyone know why default values for Inserts is not working?

 

MySql 5.1.11

PHP 5.2.6.6

 

Thanks.

 

Here's the sample insert setup code for one of the tables:

 

    var $status_id;
    var $company;
    var $attn;
    var $insured;
    var $claimant;
    var $company_file_number;	
var $date_loss;
var $file_number;
var $msg_1;
var $msg_2;	
var $msg_3;	
var $msg_4;	
var $date_contact_letter;
var $date_scheduled_inspection;
var $vehicle_too_damaged;
var $searching_parts;
var $reaching_agreed_price_shop;
var $date_expected_agreement;
var $additional_comments;
var $app_id;
var $current_date;
var $appraisal_completion;	 
var $second_status_date;


    function add()
    {
        $fields = array("status_id",
					"company",
					"attn",
					"insured",
					"claimant",
					"company_file_number",
					"date_loss",
					"file_number",
					"msg_1",
					"msg_2",
					"msg_3",
					"msg_4",
					"date_contact_letter",
					"date_scheduled_inspection",
					"vehicle_too_damaged",
					"searching_parts",
					"reaching_agreed_price_shop",
					"additional_comments",
					"app_id",
					"current_date",
					"appraisal_completion",
					"second_status_date");

        $values = array(addslashes($this->status_id),
					addslashes($this->company),
					addslashes($this->attn),
					addslashes($this->insured),
					addslashes($this->claimant),
					addslashes($this->company_file_number),
					addslashes($this->date_loss),
					addslashes($this->file_number),
					addslashes($this->msg_1),
					addslashes($this->msg_2),
					addslashes($this->msg_3),
					addslashes($this->msg_4),
					addslashes($this->date_contact_letter),
					addslashes($this->date_scheduled_inspection),
					addslashes($this->vehicle_too_damaged),
					addslashes($this->searching_parts),
					addslashes($this->reaching_agreed_price_shop),
					addslashes($this->additional_comments),
					addslashes($this->app_id),
					addslashes($this->current_date),
					addslashes($this->appraisal_completion),
					addslashes($this->second_status_date));

        $new_data_obj = new data_obj;
        $new_data_obj->add_record($GLOBALS["db"], "status", $fields, $values);

        return;
    }

 

These are the columns that are complaining about not being sent a value:

• status_id
  
• date_loss
  
• date_contact_letter
  
• date_scheduled_inspection
  
• vehicle_too_damaged
  
• searching_parts
  
• reaching_agreed_price_shop
  
• app_id
  
• current_date
  
• appraisal_completion
  
• second_status_date
  

 

Here's the actual Add_record method:

 

    #adds a record to a table
    function add_record($db, $table, $fields, $values)
    {
        $new_error_obj = new error_obj;
        $new_data_obj = new data_obj;

        $link = $new_data_obj->connect();
        if(!$link) $new_error_obj->error_page("(data_obj) Could not connect to the database");

        mysql_select_db($db, $link) or $new_error_obj->error_page("(data_obj) The specified database could not be selected [$db]");

        $insert_sql = "INSERT INTO `$table` (";

        $flag = 0;
        foreach($fields as $field)
        {
            if ($flag == 0)
            {
                $insert_sql = $insert_sql . "`$field`";
                $flag = 1;
            }
            Else
            {
                $insert_sql = $insert_sql . ", `$field`";
            }

        }

        $insert_sql = $insert_sql . ") VALUES(";

        $flag = 0;
        foreach($values as $value)
        {
            if ($flag == 0)
            {
                $insert_sql = $insert_sql . "'" . $value . "'";
                $flag = 1;
            } else {
                $insert_sql = $insert_sql . ", '" . $value . "'";
            }
        }

        $insert_sql = $insert_sql . ")";

        if (!mysql_query($insert_sql))
        {
            $new_error_obj->error_page("(data_obj) The follwing SQL error occured: <p>" . mysql_error() . "</p><p>while trying to run this SQL statement:</p><p>$insert_sql</p>");
            //mysql_close();
            return "-1";
        } else {
//			$result=mysql_query("select LAST_INSERT_ID();");
//			$a_row = @mysql_fetch_array($result);
		$a_row = mysql_insert_id();
            //mysql_close();
            return $a_row;
        }
    }

[fenway]

Echo $insert_sql.

[kwilder]

Echo $insert_sql.

 

Sorry, here's the insert statement.

 

INSERT INTO `status` (`status_id`, `company`, `attn`, `insured`, `claimant`, `company_file_number`, `date_loss`, `file_number`, 
`msg_1`, `msg_2`, `msg_3`, `msg_4`, `date_contact_letter`, `date_scheduled_inspection`, `vehicle_too_damaged`, `searching_parts`, 
`reaching_agreed_price_shop`, `additional_comments`, `app_id`, `current_date`, `appraisal_completion`, `second_status_date`) 
VALUES('', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '12000', '', '', '')

[gffg4574fghsDSGDGKJYM]

I think it's a MySQL issue. I don't know how to set it right in my.ini so that mysql will allow '' as a NULL value and set it to the default value instead.

You can probably find the answer here :

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

There probably a MySQL mode that will allow that (or it was a sql-mode for this some version ago and isn't supported anymore).

 

But you can make some change in your php to change the SQL query from this :

 

INSERT INTO test (a,b) VALUES(1, '');

 

To this :

 

INSERT INTO test (a) VALUES(1);

Or this if i don't work and request you a value :

INSERT INTO test (a,b) VALUES(1,DEFAULT);

 

If the value is empty remove it completely before sending it to MySQL.

 

By removing the column from the INSERT instead of inserting a empty string '' it will take the default value.

[fenway]

I think it's a MySQL issue. I don't know how to set it right in my.ini so that mysql will allow '' as a NULL value and set it to the default value instead.

That wouldn't make ANY sense -- then there would be no way to EVER insert a blank string!!!!

[gffg4574fghsDSGDGKJYM]

I think it's a MySQL issue. I don't know how to set it right in my.ini so that mysql will allow '' as a NULL value and set it to the default value instead.

That wouldn't make ANY sense -- then there would be no way to EVER insert a blank string!!!!

 

I'm agree its a little bit weird, but the script was working before. I'm assuming it was a old mysql version or a sql-mode that was working like that. (or a bug maybe)

 

INSERT INTO `status` (`status_id`, `company`, `attn`, `insured`, `claimant`, `company_file_number`, `date_loss`, `file_number`, 
`msg_1`, `msg_2`, `msg_3`, `msg_4`, `date_contact_letter`, `date_scheduled_inspection`, `vehicle_too_damaged`, `searching_parts`, 
`reaching_agreed_price_shop`, `additional_comments`, `app_id`, `current_date`, `appraisal_completion`, `second_status_date`) 
VALUES('', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '12000', '', '', '')

 

How can a INSERT like that ever work and using the default value instead of a empty string '' otherwise, that don't make sense, if you have a idea feel free to share it. I told that i think it was that, not that i'm sure it is.

 

No mather how the old script manage to work, the solution is the same.

Remove the column if you want a default value or use the DEFAULT like i said.

[kwilder]

Good suggestions.  I'll try removing the blank values to allow the default values to be entered.

 

It's just weird that it works on a Linux server presently as is.  ???