SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    Ohio, USA
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Do you see a problem with this small piece of SQL?

    The following code supposedly works. Unfortunately, it won't work for me. Do you see any error in this?

    Code:
    CREATE TABLE `admins` (
      `admin_id` int(11) NOT NULL auto_increment,
      `admin_username` varchar(40) NOT NULL default '',
      `admin_password` varchar(40) NOT NULL default '',
      `admin_status` varchar(40) NOT NULL default 'Offline',
      `admin_last_login` varchar(40) NOT NULL default '',
      PRIMARY KEY  (`admin_id`)
    ) TYPE=MyISAM AUTO_INCREMENT=2 ;
    
    INSERT INTO `admins` VALUES (1, 'admin', 'admin', 'Offline', '');
    (This is standard SQL or phpmyadmin, not PHP etc)

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what doesn't work the table creation or the insert?
    also, flaky in my opinion, you are creating column types that are NOT NULL but then make an empty value the default value. Why not just allow them to be NULLable then?

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You are using old syntax for the CREATE TABLE. The keyword TYPE has been deprecated and you should use ENGINE instead:

    Code:
    CREATE TABLE `admins` (
      `admin_id` int(11) NOT NULL auto_increment,
      `admin_username` varchar(40) NOT NULL default '',
      `admin_password` varchar(40) NOT NULL default '',
      `admin_status` varchar(40) NOT NULL default 'Offline',
      `admin_last_login` varchar(40) NOT NULL default '',
      PRIMARY KEY  (`admin_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2 ;
    
    INSERT INTO `admins` VALUES (1, 'admin', 'admin', 'Offline', '');

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    I believe you also want to use DEFAULT NULL, not ''. I believe this will actually cause a select of IS NULL to not return those rows, if queried.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, for username and password, these should ~not~ be DEFAULT NULL

    how much sense does it make to add an admin to the table with a NULL username and password????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    actually, for username and password, these should ~not~ be DEFAULT NULL

    how much sense does it make to add an admin to the table with a NULL username and password????
    But why change up the design from normal columns if it should never be null. It doesn't make sense for it to be an empty string either.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    But why change up the design from normal columns ...
    i'm afraid i don't understand what you mean by "normal" columns

    DEFAULT '' is not normal

    NOT NULL with no default is normal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Woops


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
  •