SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table column default set to NULL, but NULL insert query makes a "0" instead.

    .
    Greetings!

    I have got a table that has a columm that has the following settings:

    Field (or name): Opponent
    Type: smallint(5)
    Collation: blank
    Attributes: UNSIGNED
    Null: Yes
    Default: NULL

    If I do an insert query using the below $team_id_opp variable the table new row will have a value of zero (0) instead of the value of NULL.
    PHP Code:
    $team_id_opp    =    NULL;

    INSERT INTO Games Opponent VALUES ('$team_id_opp')  // Value for the Opponent cell turns out to be "0" instead of NULL 
    If I put single quotes or double quotes around NULL like below the table row still has a value of zero instead of NULL for the Opponent table cell value.
    PHP Code:
    $team_id_opp    =    'NULL'// Still has 0 instead of NULL
    $team_id_opp    =    "NULL"// Still has 0 instead of NULL 
    If I remove the $team_id_opp variable from the script altogether and do an insert query with no variable value at all it still inserts a 0 instead of the NULL value.

    Question. How can I make the Opponent column cell in a new row have the default NULL value instead of a value of 0?

    Thanks.
    .

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Volitics View Post
    Question. How can I make the Opponent column cell in a new row have the default NULL value instead of a value of 0?
    easy peasy

    INSERT INTO Games ( Opponent ) VALUES ( NULL )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    .
    That won't work. Some of the Opponent column cells need to have a numerical value. Other records/rows have no value for the opponent column. That's why I need to use NULL.

    The Opponent column is for Division 1 baseball opponents. If the opposing team is not a Division 1 team then I have another column in the same row for those ones. For a non D1 opposing team the Opponent column cell needs to have no value or a value of NULL.

    Just as a test I used the value as you suggested just to see the results.

    INSERT INTO Games ( Opponent ) VALUES ( NULL )

    Strangely, oddly the value, using the above query, still resulted in a cell value of 0 instead of NULL.

    It's unusual that it would do that. There has got to be a simple answer.
    .

  4. #4
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Volitics View Post
    There has got to be a simple answer.
    Try a little checking:
    Code:
    SELECT *
      FROM Games
     WHERE Opponent is NULL
    and

    Code:
    SELECT *
      FROM Games
     WHERE Opponent = 0
    Have you declared any triggers on the Games table?

    Do you have an index on that column?

    Are you using a test db and a production db?

    Have you replaced default display values in your SQL manager?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Volitics View Post
    Just as a test I used the value as you suggested just to see the results.

    INSERT INTO Games ( Opponent ) VALUES ( NULL )

    Strangely, oddly the value, using the above query, still resulted in a cell value of 0 instead of NULL.
    fascinating

    would you kindly do a SHOW CREATE TABLE for that table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    .

    PHP Code:
    //----
    CREATE TABLE `Games` (
      `
    team_idsmallint(5unsigned NOT NULL,
      `
    Yearyear(4NOT NULL,
      `
    game_idmediumint(8unsigned NOT NULL,
      `
    GameDateint(10unsigned NOT NULL,
      `
    GameOrdertinyint(2NOT NULL default '1',
      `
    Stadiumset('vs','at','neu','t','c','r','sr','cws'NOT NULL default 'vs',
      `
    Opponentsmallint(5unsigned default NULL,
      `
    NonD1varchar(75NOT NULL COMMENT 'Not D1 opponent',
      `
    ConferenceGameset('','*'NOT NULL,
      `
    DivisionalPlayset('','*'NOT NULL,
      `
    Commentvarchar(75NOT NULL COMMENT 'For footnotes',
      `
    Statusset('ForTesting','Tournament','Scheduled','Done','Resumed','Suspended','Postponed','Cancelled'NOT NULL,
      `
    Inningstinyint(2unsigned default NULL,
      `
    Outcomeset('','W','L','T'NOT NULL,
      `
    RunsScoredtinyint(3unsigned default NULL,
      `
    OpponentScoretinyint(3unsigned default NULL,
      `
    tourn_idsmallint(5unsigned default NULL COMMENT 'id for in season tournament',
      `
    TournHomeTeamset('','Yes','No'NOT NULL,
      `
    TournRoadTeamset('','Yes','No'NOT NULL,
      `
    TournGameNumsmallint(3unsigned default NULL,
      `
    Brackettinyint(1) default NULL COMMENT 'CWS has two brackets',
      `
    Finalsset('','Yes','No'NOT NULL COMMENT 'CWS has finals',
      
    UNIQUE KEY `game_id` (`game_id`),
      
    KEY `team_id` (`team_id`),
      
    KEY `Year` (`Year`),
      
    KEY `GameDate` (`GameDate`),
      
    KEY `GameOrder` (`GameOrder`),
      
    KEY `ConferenceGame` (`ConferenceGame`),
      
    KEY `DivisionalPlay` (`DivisionalPlay`),
      
    KEY `tourn_id` (`tourn_id`)
    ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Schedules and game results.';
    //--- 
    .

  7. #7
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's unrelated to the actual question, but the use of a SET field for a yes/no isn't suitable, since a SET can be both yes AND no in this case (and '' looking at your code). You would want an enum, and rather than allow '' I would allow it to be NULL instead. Even better, use a TINYINT and set 1 for yes, 0 for no, and NULL for, well, NULL.

    SET should only be used where you (may) need to choose multiples as not only does it allow this, but internally it also takes up more space. An ENUM, for example, would store the values internally as incrementing numbers (0, 1, 2, 3, 4, 5, 6, 7 etc), whereas a SET stores them as follows: 1, 2, 4, 8, 16, 32, 64.

  8. #8
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Actually related to your question, I just created the table according to your code and inserted a NULL record to the Opponent row with no problems at all. Are you able to insert NULL anywhere? Can you do it with a GUI such a PHPMyAdmin, or HeidiSQL? I'm not aware of a setting for it, nor a reason why you'd want to, but I wonder if there is a server config value that disallows NULL and forces a 0 instead?

    Also, while I think, how are you checking the value? Are you checking it via PHP? If you use something like the aforementioned HeidiSQL it will tell you EXACTLY what the value of the field is, and not what another language interprets it as

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    antnee beat me to it -- while i was fixing errors!

    i created your table, then tried this --
    Code:
    INSERT INTO Games ( Opponent ) VALUES ( NULL )
    unfortunately, this failed because i was missing a value for some column

    so i supplied a value, and tried again, and got a similar message for another column

    so i tried it again... and again... and again...

    eventually, this was the query that actually ran --
    Code:
    INSERT 
      INTO Games 
         ( team_id
         , `year`
         , game_id 
         , gamedate
         , nond1
         , conferencegame
         , divisionalplay
         , `comment`
         , `status`
         , outcome
         , tournhometeam
         , tournroadteam
         , finals
         , Opponent ) -- this is the column i was trying to fill
    VALUES 
         ( 9
         , 2012
         , 37
         , UNIX_TIMESTAMP()
         , 0
         , 0
         , 0
         , 'meh'
         , 'cancelled'
         , 't'
         , 'yes'
         , 'no'
         , 'yes'
         , NULL ) -- this is the value i was trying to fill it with
    you'll be happy to know that mysql ~does~ accept NULL for Opponent, and does not change it to 0

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I wonder if we have conflicting server configs, r397? I had no problem creating the table and immediately inserting NULL

    Code MySQL:
    CREATE TABLE `Games` (
    `team_id` smallint(5) unsigned NOT NULL, 
    `Year` year(4) NOT NULL, 
    `game_id` mediumint(8) unsigned NOT NULL, 
    `GameDate` int(10) unsigned NOT NULL, 
    `GameOrder` tinyint(2) NOT NULL default '1', 
    `Stadium` set('vs','at','neu','t','c','r','sr','cws') NOT NULL default 'vs', 
    `Opponent` smallint(5) unsigned default NULL, 
    `NonD1` varchar(75) NOT NULL COMMENT 'Not D1 opponent', 
    `ConferenceGame` set('','*') NOT NULL, 
    `DivisionalPlay` set('','*') NOT NULL, 
    `Comment` varchar(75) NOT NULL COMMENT 'For footnotes', 
    `Status` set('ForTesting','Tournament','Scheduled','Done','Resumed','Suspended','Postponed','Cancelled') NOT NULL, 
    `Innings` tinyint(2) unsigned default NULL, `Outcome` set('','W','L','T') NOT NULL, 
    `RunsScored` tinyint(3) unsigned default NULL, 
    `OpponentScore` tinyint(3) unsigned default NULL, 
    `tourn_id` smallint(5) unsigned default NULL COMMENT 'id for in season tournament', 
    `TournHomeTeam` set('','Yes','No') NOT NULL, 
    `TournRoadTeam` set('','Yes','No') NOT NULL, 
    `TournGameNum` smallint(3) unsigned default NULL, 
    `Bracket` tinyint(1) default NULL COMMENT 'CWS has two brackets', 
    `Finals` set('','Yes','No') NOT NULL COMMENT 'CWS has finals', 
    UNIQUE KEY `game_id` (`game_id`),
    KEY `team_id` (`team_id`), 
    KEY `Year` (`Year`), 
    KEY `GameDate` (`GameDate`), 
    KEY `GameOrder` (`GameOrder`), 
    KEY `ConferenceGame` (`ConferenceGame`), 
    KEY `DivisionalPlay` (`DivisionalPlay`), 
    KEY `tourn_id` (`tourn_id`) ) 
    ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Schedules and game results.';
     
    INSERT INTO Games ( Opponent ) VALUES ( NULL );
    /* 1 rows affected, 0 rows found. Duration for 2 queries: 0.047 sec. */
    I ran that all at once via HeidiSQL

  11. #11
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    .
    Antnee;

    Thanks for helping out with my problem.

    Quote Originally Posted by Antnee View Post
    I just created the table according to your code and inserted a NULL record to the Opponent row with no problems at all. Are you able to insert NULL anywhere?
    Yes.

    I'm using the phpMyAdmin. I can insert a new row with the phpMyAdmin "Insert" button and it creates a NULL record in the Opponent row.

    It's just with the PHP script that inserting a row with the $team_id_opp set to NULL that the Opponent row sets to 0 (this might be a question for the PHP forum).

    I was thinking yesterday that I might ought to post the PHP script. I just didn't want to put too much information in one post.

    If you scroll about 3/5's of the way down the page you will see the snippet:
    // --------------------------------------------------
    // ### For non d1 opponent empty team id. ###
    // --------------------------------------------------

    $team_id_opp = NULL;

    Then if you look down at the first query you will see the following:
    (The query is not yet complete. I am still working on it.)

    team_id,
    Year,
    game_id,
    GameDate,
    GameOrder,
    Stadium,
    Opponent,
    NonD1 )

    VALUES



    ('$team_id',
    '$Year',
    '$game_id_this_team',
    '$GameDateTS',
    '$Sec',
    '$StadiumThisTeam',
    '$team_id_opp',
    // This value should be NULL when the $team_id_opp var above is set to NULL but it sets to 0 instead.
    '$ShiftedOffNonD1')"

    PHP Code:
    <?php


    // ############################################################
    // ###                                                                                                           ###
    // ###                                                                                                             ###
    // ###                                                                                                             ###
    // ###                                                                                                             ###
    // ############################################################

    // --------------------------------------------------
    // ### Assign post values to vars. ###
    // --------------------------------------------------
    $Month                    =    $_POST["Month"];
    $Day                        =    $_POST["Day"];
    $GameOrder            =    $_POST["GameOrder"];
    $Stadium                =    $_POST["Stadium"];
    $Opponent                =    $_POST["Opponent"];
    $NonD1                    =    $_POST["NonD1"];
    //$Comment                =    $_POST["Comment"];
    //$Status                    =    $_POST["Status"];
    //$Innings                =    $_POST["Innings"];
    //$Outcome                =    $_POST["Outcome"];
    //$RunsScored            =    $_POST["RunsScored"];
    //$OpponentScore    =    $_POST["OpponentScore"];
    //$tourn_id                =    $_POST["tourn_id"];
    //$TournHomeTeam    =    $_POST["TournHomeTeam"];
    //$Bracket                =    $_POST["Bracket"];
    //$Finals                    =    $_POST["Finals"];
    //$TournGameNum        =    $_POST["TournGameNum"];
    //$            =    $_POST[""];
    //$            =    $_POST[""];

    // --------------------------------------------------
    // ### Call the configuration file for the MySQL database. ###
    // --------------------------------------------------
    require('/home/content/59/4519259/html/baseball/conf/config.inc.php');

    // --------------------------------------------------
    // ### Set the default time zone. ###
    // --------------------------------------------------
    date_default_timezone_set('America/New_York');

    // --------------------------------------------------
    // ### Tells session start the name of the session cookie. ###
    // --------------------------------------------------
    session_name("AdminSession");

    // --------------------------------------------------
    // ### Start the session. ###
    // --------------------------------------------------
    session_start();

    // --------------------------------------------------
    // ### Assign session vars num add, year, and team id to vars. ###
    // --------------------------------------------------
    $NumAdd        =    $_SESSION["NumAdd"];
    $Year            =    $_SESSION["Year"];

    // --------------------------------------------------
    // ### Set both vars to type integer. ###
    // --------------------------------------------------
    settype($NumAdd,"integer");
    settype($Year,"integer");

    // --------------------------------------------------
    // ### Set both vars to type integer. ###
    // --------------------------------------------------
    $ValidRecord    =    FALSE;

    // --------------------------------------------------
    // ### Require the function to find the game ids for this team and the opponent. ###
    // --------------------------------------------------
    require('./functions/GameRef.inc.php');

    // --------------------------------------------------
    // ### Remove empty values from non d1 array. ###
    // --------------------------------------------------
    $NonD1    =    array_filter($NonD1);

    // --------------------------------------------------
    // ### Loop through each of the games to be added. ###
    // --------------------------------------------------
    for ($n 0$n $NumAdd$n++){

        
    // --------------------------------------------------
        // ### Assign session var to team id var. ###
        // --------------------------------------------------
        
    $team_id    =    $_SESSION["team_id"];

        
    // --------------------------------------------------
        // ### Assign game order, month, day array values to vars. ###
        // --------------------------------------------------
        
    $Sec    =    $GameOrder[$n];
        
    $M        =    $Month[$n];
        
    $D        =    $Day[$n];
        
    // --------------------------------------------------
        // ### Set type to integer. ###
        // --------------------------------------------------
        
    settype($Sec"integer");
        
    settype($M"integer");
        
    settype($D"integer");    
        
    // --------------------------------------------------
        // ### mktime (hour, minute, second, month, day, year) Make time stamp. Game order is seconds past midnight Eastern Time. ###
        // --------------------------------------------------
        
    $GameDateTS    =    mktime(0,0,$Sec,$M,$D,$Year);

        
    // --------------------------------------------------
        // ### If conditionals for stadium. ###
        // --------------------------------------------------
        
    if ($Stadium[$n] == 'vs'){
            
    $StadiumThisTeam    =    'vs';
            
    $StadiumOpponent    =    'at';
            }
        if (
    $Stadium[$n] == 'at'){
            
    $StadiumThisTeam    =    'at';
            
    $StadiumOpponent    =    'vs';
            }
        if (
    $Stadium[$n] == 'neu'){
            
    $StadiumThisTeam    =    'neu';
            
    $StadiumOpponent    =    'neu';
            }
        if (
    $Stadium[$n] == 't'){
            
    $StadiumThisTeam    =    't';
            
    $StadiumOpponent    =    't';
            }
        if (
    $Stadium[$n] == 'c'){
            
    $StadiumThisTeam    =    'c';
            
    $StadiumOpponent    =    'c';
            }
        if (
    $Stadium[$n] == 'r'){
            
    $StadiumThisTeam    =    'r';
            
    $StadiumOpponent    =    'r';
            }
        if (
    $Stadium[$n] == 'sr'){
            
    $StadiumThisTeam    =    'sr';
            
    $StadiumOpponent    =    'sr';
            }
        if (
    $Stadium[$n] == 'cws'){
            
    $StadiumThisTeam    =    'cws';
            
    $StadiumOpponent    =    'cws';
            }

        
    // --------------------------------------------------
        // ### Team id for the opponent. ###
        // --------------------------------------------------
        
    $team_id_opp    =    $Opponent[$n];
        
    // --------------------------------------------------
        // ### Call the game id reference function. Calculates the game ids for this team and for the opponent. ###
        // --------------------------------------------------
        
    GameRef($MySQLi,$team_id_opp);

        
    // --------------------------------------------------
        // ### If month, day, and D1 opponent has been selected. ###
        // --------------------------------------------------
        
    if ( (!empty($Opponent[$n])) AND 
                 (!empty(
    $Month[$n]))         AND 
                 (!empty(
    $Day[$n]))          AND 
                 (empty(
    $NonD1[$n])) ){
            
    // --------------------------------------------------
            // ### Since this opponent is a d1 school set non d1 to empty. ###
            // --------------------------------------------------
            
    $ShiftedOffNonD1    =    '';
            
    // --------------------------------------------------
            // ### Set valid record to true. ###
            // --------------------------------------------------
            
    $ValidRecord    =    TRUE;
            }

        
    // --------------------------------------------------
        // ### If month, day, and non D1 opponent has been input. ###
        // --------------------------------------------------
        
    if ( (count($NonD1) > 0)          AND 
                 (!empty(
    $Month[$n]))         AND 
                 (!empty(
    $Day[$n]))          AND         
                 (empty(
    $Opponent[$n])) ) {

            
    // --------------------------------------------------
            // ### For non d1 opponent empty team id. ###
            // --------------------------------------------------
            
    $team_id_opp    =    NULL;
            
            
    // --------------------------------------------------
            // ### Shift off and assign the first value off the non d1 opponent array to non d1 var. ###
            // --------------------------------------------------
            
    $ShiftedOffNonD1    =    array_shift($NonD1);

            
    // --------------------------------------------------
            // ### Set valid record to true. ###
            // --------------------------------------------------
            
    $ValidRecord    =    TRUE;
            }

        
    // --------------------------------------------------
        // ### If user has input enough this loop to make this a valid record. ###
        // --------------------------------------------------
        
    if ($ValidRecord    ==    TRUE){


            
    //$team_id                         = $MySQLi -> real_escape_string($team_id);
            //$Year                             = $MySQLi -> real_escape_string($Year);
            //$game_id_this_team     = $MySQLi -> real_escape_string($game_id_this_team);


            // --------------------------------------------------
            // ### Insert game record into database for this team. ###
            // --------------------------------------------------
            
    $MySQLi -> query("INSERT INTO Games 
                                                                ( team_id,
                                                                    Year,
                                                                    game_id,
                                                                    GameDate,
                                                                    GameOrder,
                                                                    Stadium,
                                                                    Opponent,
                                                                    NonD1 )            
                                                                    
                                                                    VALUES
                                                                    
                                                                 ('
    $team_id',
                                                                     '
    $Year',
                                                                     '
    $game_id_this_team',
                                                                     '
    $GameDateTS',
                                                                     '
    $Sec',
                                                                     '
    $StadiumThisTeam',
                                                                     '
    $team_id_opp',
                                                                     '
    $ShiftedOffNonD1')");

        }

        
    // --------------------------------------------------
        // ### If input is valid and opposing team is a d1 opponent. ###
        // --------------------------------------------------
        
    if ( ($ValidRecord    ==    TRUE) AND (!empty($team_id_opp)) ){
            
    // --------------------------------------------------
            // ### Insert game record into database for opposing team. ###
            // --------------------------------------------------
            
    $MySQLi -> query("INSERT INTO Games 
                                                                ( team_id,
                                                                    Year,
                                                                    game_id, 
                                                                    GameDate, 
                                                                    GameOrder,
                                                                    Stadium,
                                                                    Opponent,
                                                                    NonD1 )            
                                                                    
                                                                    VALUES
                                                                    
                                                                 ('
    $team_id_opp',
                                                                     '
    $Year',
                                                                     '
    $game_id_opponent',
                                                                     '
    $GameDateTS',
                                                                     '
    $Sec',
                                                                     '
    $StadiumOpponent',
                                                                     '
    $team_id',
                                                                     '
    $ShiftedOffNonD1')");

        }

    // --------------------------------------------------
    // ### Set valid record back to false for next loop. ###
    // --------------------------------------------------
    $ValidRecord    =    FALSE;

    // --------------------------------------------------
    // ### Set this var to empty in case next opponent is non d1 team. ###
    // --------------------------------------------------
    $team_id_opp    =    '';

    // --------------------------------------------------
    // ### Close for loop. ###
    // --------------------------------------------------
    }

    /*
    // --------------------------------------------------
    // ### Run the mysql query insert into db. ###
    // --------------------------------------------------
    @ mysql_query("INSERT INTO Members 
                                                        ( remote_address,
                                                            web_ring,
                                                            email, 
                                                            username, 
                                                            password,
                                                            join_date,
                                                            domain_name,
                                                            your_name,
                                                            mailing_address,
                                                            business_name,
                                                            address_locale,
                                                            state_business )            
                                                            
                                                            VALUES
                                                            
                                                         ('$InternetProtocol',
                                                             '$WebRing',
                                                             '$Email',
                                                             '$UserName',
                                                             '$PassWord1',
                                                             '$CurrTS',
                                                             '$DomainName',
                                                             '$YourName',
                                                             '$StreetAddress',
                                                             '$BusinessName',
                                                             '$Locale',
                                                             '$DescribeBusiness')");

    */
    // --------------------------------------------------
    // ### Free the result. ###
    // --------------------------------------------------
    //$Result->free();


    ?>
    .

  12. #12
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP uses what they call "loose" comparisons as default, and isn't very good at understanding types. For example, if you do this:

    Code php:
    <?php
    $foo = FALSE;
    $bar = NULL;
    $baz = TRUE;
     
    if ($foo == FALSE)  echo "Foo is FALSE\n";
    if ($foo == 0)      echo "Foo is also 0\n";
    if ($foo == NULL)   echo "hang on, Foo is also NULL?\n";
    if ($foo == TRUE)   echo "Foo is true\n";
    if ($foo === FALSE) echo "Foo is EXACTLY FALSE\n";
    if ($foo === 0)     echo "Foo is EXACTLY ZERO\n";
    if ($foo === NULL)  echo "Foo is EXACTLY NULL\n";
    if ($foo === TRUE)  echo "Foo is EXACTLY TRUE\n";
     
    if ($bar == FALSE)  echo "Bar is FALSE\n";
    if ($bar == 0)      echo "Bar is also 0\n";
    if ($bar == NULL)   echo "Bar is also NULL?\n";
    if ($bar == TRUE)   echo "Bar is true\n";
    if ($bar === FALSE) echo "Bar is EXACTLY FALSE\n";
    if ($bar === 0)     echo "Bar is EXACTLY ZERO\n";
    if ($bar === NULL)  echo "Bar is EXACTLY NULL\n";
    if ($bar === TRUE)  echo "Bar is EXACTLY TRUE\n";
     
    if ($baz == FALSE)  echo "Baz is FALSE\n";
    if ($baz == 0)      echo "Baz is also 0\n";
    if ($baz == NULL)   echo "hang on, Baz is also NULL?\n";
    if ($baz == TRUE)   echo "Baz is true\n";
    if ($baz === FALSE) echo "Baz is EXACTLY FALSE\n";
    if ($baz === 0)     echo "Baz is EXACTLY ZERO\n";
    if ($baz === NULL)  echo "Baz is EXACTLY NULL\n";
    if ($baz === TRUE)  echo "Baz is EXACTLY TRUE\n";
     
    echo "Foo = $foo\n";
    echo "Bar = $bar\n";
    echo "Baz = $baz\n";

    You will get this output:
    Code:
    Foo is FALSE 
    Foo is also 0 
    hang on, Foo is also NULL? 
    Foo is EXACTLY FALSE 
    Bar is FALSE 
    Bar is also 0 
    Bar is also NULL? 
    Bar is EXACTLY NULL 
    Baz is true 
    Baz is EXACTLY TRUE
    Foo = 
    Bar = 
    Baz = 1
    Not always what you expect

    You need to check EXACTLY what you expect from PHP. And don't set a variable to be equal to the NULL constant before you insert, as that WILL go in as a zero. What you'd need to do is more like this:

    Code php:
    $var = 'NULL';
    $sql = "INSERT INTO Games ( Opponent ) VALUES ( $var );";

    That will translate to the exact:

    Code mysql:
    INSERT INTO Games ( Opponent ) VALUES ( NULL );
    that you're after

    If you did this:

    Code php:
    $var = 'NULL';
    $sql = "INSERT INTO Games ( Opponent ) VALUES ( '$var' );";

    This would translate in to:

    Code mysql:
    INSERT INTO Games ( Opponent ) VALUES ( 'NULL' );
    and that would try to insert the string "NULL" instead, but since it's a SMALLINT field it will instead fall back to the type default, which is a zero (and not the NULL that you would expect). This happens when you try to insert an invalid value when you're not running MySQL in strict mode. If you immediately ran the MySQL command:

    Code mysql:

    You would see somthing like this:

    Code:
    Incorrect integer value: 'NULL' for column 'Opponent' at row 1

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    I wonder if we have conflicting server configs, r397?
    well, there has to be some kind of explanation, doesn't there?

    for instance, what was the value that got assigned to these two columns, for which you failed to provide a value, and which don't have a default --
    Code MySQL:
    `game_id` mediumint(8) unsigned NOT NULL, -- this is also UNIQUE
    `TournHomeTeam` set('','Yes','No') NOT NULL,
    btw i'm using heidisql as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    game_id = 0, TournHomeTeam = ''

    Full dataset in CSV:
    Code:
    team_id,Year,game_id,GameDate,GameOrder,Stadium,Opponent,NonD1,ConferenceGame,DivisionalPlay,Comment,Status,Innings,Outcome,RunsScored,OpponentScore,tourn_id,TournHomeTeam,TournRoadTeam,TournGameNum,Bracket,Finals
    0,0000,0,0,1,vs,NULL,,,,,,NULL,,NULL,NULL,NULL,,,NULL,NULL,
    Are you in strict mode? I'm using the default config that comes with WAMP Server and MySQL 5.5.20

  15. #15
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937, Antnee, itmitică for responding to my problem.

    Yesterday I thought it was a MySQL issue. Today I'm starting to think that it's more of a PHP issue.

    I think what I need to do is go back and make a table with just one column and then just insert one variable at a time until I figure out what's causing the problem.

    Thanks again.
    .

  16. #16
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I agree, I think it's a PHP issue. Don't worry, we've all done it Just sort out your SETs and make the ENUMs or TINYINTs (Except where you do actually need multiple options)

  17. #17
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I did actually write some code for a previous employer where you could pass in a load of PHP data, whether an object or an array, and as long as you had the same key in your array (or property name in your object) as the DB row name, it would deal with PHP types correctly, so NULL would actually insert NULL instead of 0. Technically I guess it was a form of Object Relational Mapping (ORM) and it did a really good job of properly sanitising data and stopping mistakes like this happening. It's a shame that I had to leave it behind, as I think it would've helped you no end. Although some would argue that it also allows lazy coding

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    Are you in strict mode?
    i honestly don't know

    i installed mysql without touching a thing, so whatever its presets are, that's what i'm using
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code mysql:
    SELECT @@GLOBAL.sql_mode;
    SELECT @@SESSION.sql_mode;
    SHOW VARIABLES LIKE "%mode%";
    The first two are blank for me. The last one shows innodb_strict_mode OFF and sql_mode blank

    I don't suppose it's important, unless you really want to know why it works for one of us and not for the other. Though I couldn't insert using that query above if there was any data already in the table

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    ... unless you really want to know why it works for one of us and not for the other.
    not really, no

    i'm happy just being able to run normal queries


    Code:
    SELECT @@GLOBAL.sql_mode;
    STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    
    SELECT @@SESSION.sql_mode;
    STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    
    SHOW VARIABLES LIKE "%mode%";
    innodb_autoinc_lock_mode  1
    innodb_strict_mode        OFF
    slave_exec_mode           STRICT
    sql_mode                  STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Knew it

    Mine threw warnings, yours threw errors. So mine continued, yours did not. Knew there'd be a simple explanation You're a good boy, I'm clearly not


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •