[hello-world]

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.

[r937]

Originally Posted by hello-world

Is it possible to disallow this

yes, just declare some of your columns as NOT NULL

Originally Posted by hello-world

Second, is it possible to re-order the columns?

easiest would be simply to recreate the table

[healdem]

Originally Posted by hello-world

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

select col1, col2, col3,col4,col5 from mytable
where col5 = 'blah'
order by col2

if you want to change the order

select col4, col5, col3,col1,col2 from mytable
where col5 = 'blah'
order by col2

[hello-world]

Originally Posted by r937

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.

[hello-world]

Originally Posted by healdem

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.

[healdem]

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

[r937]

Originally Posted by hello-world

Can we actually prevent the insertion of empty rows or not?

yes, of course

please run this query and post the results --

SHOW CREATE TABLE yourtablename

[hello-world]

Originally Posted by r937

yes, of course

please run this query and post the results --

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.

[r937]

in that case, it's almost dead certain that phpmyadmin is sticking empty strings in there

i reproduced the effect here --

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 --

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 --

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 --

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

[hello-world]

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?

[r937]

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