Ignor certains db_table fields during php/mysql insert

[mbarandao]

I'm wondering if it is possible to skip certain fields in the mysql table in a sql statement. Here is what I mean:

I have a table that has the following columns:
firstname
lastname
phone
dob

Suppose I write the following statement:

$

mysql_query=("insert db_table (firstname, lastname, phone) VALUES ('$firstname' ,'$lastname', '$phone')");

In the above scenario, I want the statement to insert the above values, and IGNORE the field in "dob" in the database.

Currently, left as is, the dob is check for value and produces the following:

Error: Field 'dob' doesn't have a default value

This may sound a bit unorthodox, but it addresses a critical need at the moment.

I appreciate any thoughts on this!
Mossa

[Stefano Mtangoo]

There are two inserts
table have columns: col1, col2, col3

--Inserts into all columns, this kind of query should have values equal to number of columns
INSERT INTO table_name("Blah",  "bla2", "blah3")
--Insert any number, the other columns should have default defined for safety
INSERT INTO table_name(col1, col2) VALUES("Blah",  "bla2")

[mbarandao]

There are two inserts
table have columns: col1, col2, col3

--Inserts into all columns, this kind of query should have values equal to number of columns
INSERT INTO table_name("Blah",  "bla2", "blah3")
--Insert any number, the other columns should have default defined for safety
INSERT INTO table_name(col1, col2) VALUES("Blah",  "bla2")

Great, thanks for the reply. During research after posting, I fount and used

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 0;

to achieve similar outcome.

My concern now is if one of the fields is a BLOB/TEXT field, how to achieve the same outcome --since default values cannot be set to BLOB/TEXT?

Your thoughts!
Mossa

[quasipickle]

Allow the BLOB/TEXT fields to be NULL.

[mbarandao]

Allow the BLOB/TEXT fields to be NULL.

This works in allowing the insert statement to happen. But When I perform another statement as an update, I cannot write to that field (BLOB/TEXT) which is now NULL. I need to be to perform an update at other times

[quasipickle]

When you're updating a row, you explicitly state which columns you want to update. If you don't want to update the BLOB/TEXT column, don't specify it.

[mbarandao]

When you're updating a row, you explicitly state which columns you want to update. If you don't want to update the BLOB/TEXT column, don't specify it.

Sorry, that is not what I'm tying to convey. Let me clarify...

In an entirely different process --different from the initial insert in which I inserted values and IGNORED the other (some varchar fields other Text/blob), I want to update the same table. In this process, I am primarily updating the TEXT field.

Because earlier, to allow the INSERT statement to process, I made that field NULL, I'm unable to update to it now.

So I need to be able to perform the first task of INSERTING --as suggested with the ATER Statement as well as perform the second Task of UPDATING those field (earlier IGNORED during the INSERT statement...). I hope I'm making sense!

[quasipickle]

Sorry - you're not making sense (to me at least).

Are you saying that because you set a field to NULL in an initial INSERT statement, that you can't later UPDATE it? Thanks incorrect.

[mbarandao]

Sorry - you're not making sense (to me at least).

Are you saying that because you set a field to NULL in an initial INSERT statement, that you can't later UPDATE it? Thanks incorrect.

My apologies! but I am unable to update a particular field that was made null. Perhaps. it is the content of the field. here is what I have as an update statement:

<?php
if(isset($_POST['submit']))//this defines any variable that are not define below
  {
$edit_sel_id = mysql_real_escape_string($_POST['edit_sel_id']);
$paymentamount = $_POST['paymentamount'];
$paymenttype = mysql_real_escape_string($_POST['paymenttype']);
$paymentref = ($_POST['paymentref']);
$paymentdate = mysql_real_escape_string($_POST['paymentdate']);
$memo = mysql_real_escape_string($_POST['memo']);
$newbalance=$_POST['newbalance'];
$newbalance= "$".''.$newbalance;
//$paymentamount= "$".''.$paymentamount;
$pmthistory =($_POST['pmthistory']);
$paymentref =  "<br/>".'On '.date('m/d/y'.' @ '.'h:i')."<br/>".'We recorded the Following transaction:<br/>'.""."<br/>Payment Reference: $paymentref<br/>Payment Amount: $paymentamount<br/>PMT TYPE: $paymenttype<br/>Meno: $memo<br/><br/>----End of Service Report----"."<br/>";

if ($edit_sel_id == "") echo "! No identifier retrieved";
else

$mysql_query=("UPDATE billofservice SET newpayment_date=NOW(), paid=(paid +'$paymentamount'), newpayment_type='$paymenttype', 
newpmtmemo='$memo', Balancedue='$newbalance', paymentref = CONCAT('$paymentref', paymentref) WHERE id=$edit_sel_id");
$result=mysql_query($mysql_query);
if(!$result){die("Error: ".mysql_error());
}
mysql_close($link);
}

?>

The highlighted in red is the one in concern. Prior to making field null all updated fine with this outcome:

On 12/07/10 @ 11:59
We recorded the Following transaction:

Payment Reference: 207941291741141
Payment Amount: 20
PMT TYPE: Cash
Meno: 

----End of Service Report----

On 12/07/10 @ 08:46
We recorded the Following transaction:

Payment Reference: 97491291729485
Payment Amount: $203
PMT TYPE: Visa
Meno: 

----End of Service Report----

anything in there I got wrong!

[mbarandao]

Issue Resolved!
Thanks for all of the suggestions and assistance...

[Stefano Mtangoo]

what did you do?

[mbarandao]

what did you do?

Thanks for asking! My solution was a bit unorthodox. Let me attempt to explain--

I could not figure out what was causing the update to stop while the TEXT field was set to null(for the purpose of skipping that field during an INSERT). All other fields that were set to null for the same purpose, did in fact allow the update --except the one where I was concatenating --as illustrated in my post earlier.

I came up with a work around; whereas, instead of setting the field to null, I set it to none-null. And during the insert, I created a hidden field in the originating form with the same name as the one in the db. The value of the hidden field has a single character. Since this field in db is only for updating, during the first update the inserted character will be written over.

Now this work-around addressed the issue. It allowed for INSERTING (while ignoring certain fields, including BLOBs) and UPDATING to the same table. The reason for which the initial suggestion did not work, eluded me; though, I spent nearly an entire day debugging.

As I may guess, this "makes no sense to you"?

Best,
Mossa

[Stefano Mtangoo]

Thanks for asking! My solution was a bit unorthodox. Let me attempt to explain--

I could not figure out what was causing the update to stop while the TEXT field was set to null(for the purpose of skipping that field during an INSERT). All other fields that were set to null for the same purpose, did in fact allow the update --except the one where I was concatenating --as illustrated in my post earlier.

I came up with a work around; whereas, instead of setting the field to null, I set it to none-null. And during the insert, I created a hidden field in the originating form with the same name as the one in the db. The value of the hidden field has a single character. Since this field in db is only for updating, during the first update the inserted character will be written over.

Now this work-around addressed the issue. It allowed for INSERTING (while ignoring certain fields, including BLOBs) and UPDATING to the same table. The reason for which the initial suggestion did not work, eluded me; though, I spent nearly an entire day debugging.

As I may guess, this "makes no sense to you"?

Best,
Mossa

Thanks and all the best!

[mbarandao]

No, thank you for your interest in providing assistance!