Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

When inserting a mysql row, how do I prevent non specified values from being set automatically

When I run an insert statement using PHP mysqli_query ... I want only the columns I specified to be set .. all the enum columns are going to a random value (it appears) although there is no default value set ... please advise
4 Solutions
Dave BaldwinFixer of ProblemsCommented:
From http://dev.mysql.com/doc/refman/5.0/en/enum.html 
If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.
agree with @dave.
Please set default value.
What is the value that you have set is it just 'Y/N' or some thing else. Please provide table schema if possible.
MurfurFull Stack DeveloperCommented:
I agree with both but that assumes that you have access to the database structure

As with everything there is more than one way to crack this nut:

1. Set the default value on the database column
2. Set the default in your PHP code

Personally, I do both as it gives me the most flexibility in managing the actual data. I set the default value on table columns to NULL  - this lets me see empty fields immediately when I perform a query and prevents any confusion over fields being blank but not NULL i.e. empty or spaces etc.

I set a default value when building the insert/update SQL string - this also allows me to easily change the default should the application requirements change. It also allows me to have a backup default of NULL set by the database in case the code fails to set a value, which also aids debugging.

Here's a really simple example of running the switch and if the default value should need to be changed you just have to change the one line.
$default= "default_value";
$value1	= "value1";

#	set default in php
((isset($_REQUEST['value2']) && $_REQUEST['value2'] != "")?$value2=$_REQUEST['value2']:$value2=$default);

$strSQL2 = "INSERT INTO table_name (field1".($value2?", field2) ":") ")."VALUES ('".$value1."'".($value2?", '".$value2."') ":") ");
echo("<b>set default in php</b><br />");
echo( $strSQL2 . "<br /><br />");

#	rely on database default
((isset($_REQUEST['value2']) && $_REQUEST['value2'] != "")?$value2=$_REQUEST['value2']:$value2=FALSE);

$strSQL2 = "INSERT INTO table_name (field1".($value2?", field2) ":") ")."VALUES ('".$value1."'".($value2?", '".$value2."') ":") ");
echo("<b>rely on database default</b><br />");
echo( $strSQL2 );

Open in new window

Ray PaseurCommented:
I always set the default value in the column definition when I create the table.

Though we come at this problem from different approaches, the same end objective is in sight: predictable values in the "empty" columns.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now