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

Posted on 2014-08-18
Last Modified: 2014-08-31
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
Question by:Mark
    LVL 82

    Accepted Solution

    If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.
    LVL 15

    Assisted Solution

    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.
    LVL 11

    Assisted Solution

    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

    LVL 107

    Assisted Solution

    by:Ray Paseur
    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

    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.

    Join & Write a Comment

    Suggested Solutions

    Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    755 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

    17 Experts available now in Live!

    Get 1:1 Help Now