-1

Hello i have table which in this table some of the field are optional to be filled.

what i want that whenever the user is not filling the optional fields the table must take

the value of N/A on those optional fields.

i have used As Defined in structure of fields as value of N/A but whenever i m submitting the

html form its not taking its value and the field is remaining empty.

Any one has any idea please.

David
  • 208,112
  • 36
  • 198
  • 279
user3724705
  • 31
  • 1
  • 5
  • Make the fields non-nullable and set a default value of "N/A" in the table definition? – David Jun 10 '14 at 09:48
  • i can not find the non-nullable in GUI can u write plz the query for an existing field. – user3724705 Jun 10 '14 at 09:51
  • http://stackoverflow.com/questions/1595372/how-to-change-mysql-column-definition (or a Google search for "MySQL alter table change column") – David Jun 10 '14 at 09:52
  • You should make the field nullable and substitute "N/A" only in the UI whenever you output the data. This can even be done in the query when retrieving the data. Don't store "N/A" in the database, it makes no sense. The SQL representation of a "non existing value" is `NULL`. – deceze Jun 10 '14 at 10:07
  • sorry bro i have tried lots of commands for making fields non-nullable but i haven't succeeded can u plz write the command for me. – user3724705 Jun 10 '14 at 10:09

2 Answers2

0

You are inserting it with empty value from variable, to get the default value of mysql field you will required to use DEFAULT as value this keyword will insert whatever you have given in as defined value 

INSERT INTO table_name 
     (fname, lname, phone, fax) 
VALUES
     ('Jhon', 'Do', DEFAULT, DEFAULT);

Edit

To do it using php create a simple function to handle inputs with a provision for default value like this 

function post($key, $default = 'N/A') {
   return ( isset($_POST[$key]) && !empty($_POST[$key]) ) ? $_POST[$key] : $default;
}

that way you don't have to get a default value by using $var = post('phone')

In you case you can use it like 

$information = post('moreaboutgeneralinfo');

and if you want default value other than N/A pass second param 

$information = post('moreaboutgeneralinfo', 'Not Available');

Hope above helps

Saqueib
  • 3,484
  • 3
  • 33
  • 56
  • ya it worked but my fields are optional may the user fill it or may not how to declare both my $var and DEFAULT in query. – user3724705 Jun 10 '14 at 10:25
  • @user3724705 check now hope this helps – Saqueib Jun 10 '14 at 11:00
  • its not working i have copied all code plz check it. "$connection=new mysqli($db_host, $db_username, $db_pass, $db_name); if($connection->connect_errno > 0){ die('Unable to connect to database [' . $connection->connect_error . ']'); } function post($key, $default = 'N/A') { return ( isset($_POST[$key]) && !empty($_POST[$key]) ) ? $_POST[$key] : $default; } $information = $_POST['moreaboutgeneralinfo']; $query= "INSERT INTO student(id, name) values('', '".$information."')"; $query_run = $connection->query($query); if($query) { echo 'success'; } else { echo 'failed'; } " – user3724705 Jun 10 '14 at 11:07
  • yes key will the input name form html – Saqueib Jun 10 '14 at 11:12
  • its not working i have pasted the code above plz check it once – user3724705 Jun 10 '14 at 11:18
  • use `$information = post('moreaboutgeneralinfo');` instead `$information = $_POST['moreaboutgeneralinfo'];` and it will work – Saqueib Jun 10 '14 at 11:18
0

You can use following queries:-

ALTER TABLE TAB_NAME MODIFY COL_NAME DATATYPE(SIZE) NOT NULL;

And then for adding default constraint:-

ALTER TABLE TAB_NAME ALTER COL_NAME SET DEFAULT 'N/A';

Hope this can help you.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • the queries performed, but whenever i m inserting data via my HTML+PHP form so its not taking the value of 'N/A'. its taking empty. – user3724705 Jun 10 '14 at 10:43
  • Then you can handle this at front end as well. means where there is a null value, you can convert this into a string like 'N/A'. and then try to insert this value to database. – Ankit Bajpai Jun 10 '14 at 11:13