Results 1 to 11 of 11

Thread: noob thread

  1. #1
    Join Date
    Dec 2011
    Posts
    16

    Unanswered: noob thread

    Being new I have many questions that are probably easy for you guys to answer, but time consuming for me to solve.

    First, I'm in phpMyAdmin and I can click "insert" and just "go" without specifying any values, and my table is accepting these empty-row insertions. Is it possible to disallow this, or do I just have to be careful?

    Second, is it possible to re-order the columns? eg I want to move "id" to column 1 instead from column 3.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hello-world View Post
    Is it possible to disallow this
    yes, just declare some of your columns as NOT NULL



    Quote Originally Posted by hello-world View Post
    Second, is it possible to re-order the columns?
    easiest would be simply to recreate the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by hello-world View Post
    Second, is it possible to re-order the columns? eg I want to move "id" to column 1 instead from column 3.
    why would you want to do that?
    providing you can store the data you need (in the proper datatype, such as dates in datetime, numbers in the most appropriate datatype (integers as one of the integer formats and so on).

    when it comes to retrieving the data the columns come out in the order they are specified in the query, unlless you use the select * form.
    eg

    assuming you had 5 cols names col1,col2..col5 in that order in the table
    select * from my table would be the same as
    Code:
    select col1, col2, col3,col4,col5 from mytable
    where col5 = 'blah'
    order by col2
    if you want to change the order
    Code:
    select col4, col5, col3,col1,col2 from mytable
    where col5 = 'blah'
    order by col2
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2011
    Posts
    16
    Quote Originally Posted by r937 View Post
    yes, just declare some of your columns as NOT NULL
    I did:
    ALTER TABLE tablename
    MODIFY columnname VARCHAR(#) NOT NULL;

    But I could still insert empty rows.

    I also tried to add a new column that required "not null" as such:
    ALTER TABLE `tablename` ADD `columnname` VARCHAR( # ) NOT NULL

    With the same result. It will give warnings like "Warning: #1366 Incorrect integer value" but it will insert it anyway. I could also type the value "NULL" during insertion and it would insert, though I guess this is not actuall NULL but the string "NULL".

    Can we actually prevent the insertion of empty rows or not? And how do you show that "not null" does work?

    easiest would be simply to recreate the table
    Like copying the table using SELECT INTO, ok.

  5. #5
    Join Date
    Dec 2011
    Posts
    16
    Quote Originally Posted by healdem View Post
    why would you want to do that?

    when it comes to retrieving the data the columns come out in the order they are specified in the query, unlless you use the select * form.
    Thanks. I assumed that having a certain order could help with the presentation when the administrator looks at the structure from a web interface.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how you present the data in the user interface has little or no bearing on the order of the columns in the row. what will have a bearing is sequence of rows within a dataset and to handle that use an appropriate order by clause

    without providing an explicit sort order the rows will be returned in a unspecified order (which could be the order they were entered into the table, the last index / retrieval order, the primary key order and so on.
    MySQL :: MySQL 5.0 Reference Manual :: 3.3.4.4 Sorting Rows

    MySQL :: MySQL 5.0 Reference Manual :: 12.2.8 SELECT Syntax
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hello-world View Post
    Can we actually prevent the insertion of empty rows or not?
    yes, of course

    please run this query and post the results --
    Code:
    SHOW CREATE TABLE yourtablename
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2011
    Posts
    16
    Quote Originally Posted by r937 View Post
    yes, of course

    please run this query and post the results --
    Code:
    SHOW CREATE TABLE yourtablename
    I get: Your SQL query has been executed successfully

    mytable CREATE TABLE `mytable` (
    `id` int(5) NOT NULL AUTO_INCREMENT,
    `columnone` varchar(15) NOT NULL,
    `columntwo` varchar(15) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

    When I go Insert -> Go (without entering any value), I get:
    1 row inserted.
    Inserted row id: 2

    ^I can keep inserting empty rows like this and they show up under Browse.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in that case, it's almost dead certain that phpmyadmin is sticking empty strings in there

    i reproduced the effect here --
    Code:
    CREATE TABLE `mytable` (
     `id` int(5) NOT NULL AUTO_INCREMENT,
     `columnone` varchar(15) NOT NULL,
     `columntwo` varchar(15) NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
    ;
    /* 0 rows affected, 0 rows found. Duration for 1 query: 0.000 sec. */
    
    INSERT 
      INTO mytable 
         ( columnone , columntwo )
    VALUES   
         ( 'curly' , 'howard' )
        ,( 'larry' , 'fine'   )
        ,( 'moe'   , 'howard' ) 
    ;    
    /* 3 rows affected, 0 rows found. Duration for 1 query: 0.015 sec. */
    so far so good

    now let's insert a row with nulls --
    Code:
    INSERT 
      INTO mytable 
         ( columnone , columntwo )
    VALUES   
         ( NULL , NULL ) 
    ;    
    /* SQL Error (1048): Column 'columnone' cannot be null */
    /* 0 rows affected, 0 rows found. Duration for 0 of 1 query: 0.000 sec. */
    as expected, this produces an error

    now watch this --
    Code:
    INSERT 
      INTO mytable 
         ( columnone , columntwo )
    VALUES   
         ( '' , '' ) 
    ;   
    /* 1 rows affected, 0 rows found. Duration for 1 query: 0.000 sec. */
    let's confirm this actually got in --
    Code:
    SELECT * FROM mytable 
    ;
    /* 0 rows affected, 4 rows found. Duration for 1 query: 0.000 sec. */
    
    id   columnone   columntwo
     3   curly       howard
     4   larry       fine
     5   moe         howard
     6
    and vwalah!! look ma, those columns are ~not~ NULL -- they contain zero-length strings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2011
    Posts
    16
    So that sounds like this is expected behavior, that it's normal for MySQL / phpmyadmin to allow insertions of rows with zero length strings. So if I want to prevent this, I'd need to do a check externally, such as via PHP code when the user attempts to insert such empty data. Does that sound right?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's one way to do it

    naturally, phpmyadmin is not what you'd use to insert data in a production system, so yes, you could use php code

    another way to ensure data integrity is with a foreign key check, but perhaps your "columnone" and "columntwo" aren't conducive to that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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