New to mySQL - Creation of table using MySQL Query Browser

[pppebble88]

I am trying to create a simple table that stores data that will be submitted on an online form. I am using the MySQL Query Browser program, enter the column values I want and click "Apply Changes"...

The code it creates to "apply" is below


CREATE TABLE `it350_t2`.`HonorBase` (
`ID` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
`CLASS` VARCHAR(1) NOT NULL,
`OFFENSE_TYPE` VARCHAR(45) NOT NULL,
`OFFENSE_SPECIFIC` VARCHAR(45) NOT NULL,
`REPORTER` VARCHAR(1) NOT NULL,
`DATE` VARCHAR(45) NOT NULL,
`PLEA` VARCHAR(2) NOT NULL,
`FINAL_STATUS` VARCHAR(45) NOT NULL,
`SEPARATED` VARCHAR(1) NOT NULL,
`PREVIOUS_OFFENSE` VARCHAR(1) NOT NULL,
`DESCRIPTION` VARCHAR() NOT NULL,
PRIMARY KEY (`ID`)
);


It gives me an error saying "1064 - error near PRIMARY KEY ('ID')...I am not sure what to do.

Any help you could offer would be appreciated. Thanks a lot.

-W

[Old Pedant]

There no size given for DESCRIPTION. It just says VARCHAR() without a size. If that's legal, I'm not aware of it.

I *doubt* this is the problem, but it seems strange to me to give DEFAULT NULL to a column (the ID column) that is also specified as NOT NULL.

I've never done that, but it might be legal, just because of the AUTO_INCREMENT modifier.

But why the error says "NEAR" what it says, I dunno.

[abduraooft]

It gives me an error saying "1064 - error near PRIMARY KEY ('ID')...I am not sure what to do.
Do you have anything (commented lines) before your query (generated at the tome of export)?

[pppebble88]

Thanks for all of the help. I put a default size of 5000 for description and it worked (this entry is a long paragraph that describes the given row)...I also had to specify a default value of "1" for the ID.

Can you explain to me how the ID/KEY works and what the default value is used for, etc? I was wanting to set the default value to "000001," but it wouldn't let me...

Also, the next step for me is to create the web based form with these given inputs and link the form to the mySQL database using PERL. Can anyone suggest a good place to start in order to tackle this? Thanks a lot.

[pppebble88]

#!/usr/bin/perl

use DBI;

print "Content-type: text/html \n\n";

# MYSQL CONFIG VARIABLES
$host = "";
$database = "";
$tablename = "";
$user = "";
$pw = "";

# PERL MYSQL CONNECT()
$dbh = DBI->connect("DBI:mysql:$database:$host", $user, $pw);

# SELECT DB
$connect->selectdb($database);

# DEFINE A MySQL QUERY
$myquery = "INSERT INTO
$tablename (ID, CLASS, OFFENSE_TYPE, OFFENSE_SPECIFIC, REPORTER, DATE, PLEA, FINAL_STATUS, SEPARATED, PREV_OFFENSE, DESCRIPTION)
VALUES (DEFAULT,'1', 'LYING', 'PRT', 'O', '2010', 'NG', 'DISMISSED', 'N', 'N', 'THIS IS A TEST XYZ ENTRY!')";

# EXECUTE THE QUERY FUNCTION
$execute = $connect->query($myquery);

# AFFECTED ROWS
$affectedrows = $execute->affectedrows($myquery);

# ID OF LAST INSERT
$lastid = $execute->insertid($myquery);

print $affectedrows."<br />";
print $lastid."<br />";


This is a test script I am trying to run to insert a new row...I am getting an error on line 15 and 18...Thanks for the help

[abduraooft]

This is public forum, so remove the connection parameters from your code, if they are real, for security reasons.

[pppebble88]

wow...i am an idiot...thanks for pointing that out

[pppebble88]

Also, with this script, I am assuming the ID will be automatically incremented for each entry?

Thanks for all of the help

[bazz]

This is how I would build your table.

Choose the engine and default charset that suits your app best.

CREATE TABLE it350_t2.HonorBase
( ID INT(11) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY
, CLASS VARCHAR(1) NOT NULL
, OFFENSE_TYPE VARCHAR(45) NOT NULL
, OFFENSE_SPECIFIC VARCHAR(45) NOT NULL
, REPORTER VARCHAR(1) NOT NULL
, DATE VARCHAR(45) NOT NULL
, PLEA VARCHAR(2) NOT NULL
, FINAL_STATUS VARCHAR(45) NOT NULL
, SEPARATED VARCHAR(1) NOT NULL
, PREVIOUS_OFFENSE VARCHAR(1) NOT NULL
, DESCRIPTION VARCHAR(5000) NOT NULL
) engine=innodb default charset=latin1;


I would also consider making the description 'text' and not varchar.
bazz

[Old Pedant]

As for the INSERT: Do *NOT* use the ID field *AT ALL* and it will automatically be auto incremented.


$myquery = "INSERT INTO $tablename "
. " (CLASS, OFFENSE_TYPE, OFFENSE_SPECIFIC, REPORTER, DATE, PLEA, FINAL_STATUS, SEPARATED, PREV_OFFENSE, DESCRIPTION) "
. " VALUES ('1','LYING', 'PRT', 'O', '2010', 'NG', 'DISMISSED', 'N', 'N', 'THIS IS A TEST XYZ ENTRY!')";


You should also consider changing VARCHAR(1) NOT NULL to just CHAR(1).

Also, the ZEROFILL that Bazz suggested will *only* change the appearance of the ID field when you access it with MySQL tools. If you are getting the value in PHP, it will simply be an integer number. Period. So you'll want to zero fill it in your PHP code.

Another solution would be to *start* the autonumber value at, say, 1000001.

[Coyote6]

Not that it is not that important any more but the reason your first table creation had to have a default value set was because you said that it was not to be null then told it the default value was null.

`ID` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,

It should be:
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,

In MySQL you can even add the PRIMARY key line into this statement to save you repeating later although I have no clue if other databases would support this.

`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

*** Didn't realize BAZZ had this in his statement. ***

[Old Pedant]

Yes, both Access and SQL Server allow you to put PRIMARY KEY into the base declaration. They also allow and enforce referential integrity contraints in the base declaration. MySQL allows them, but doesn't enforce them unless they are separate from the declaration, as I recall. (Hmmm...but come to think of that, I might have been using MyISAM at the time, and MyISAM never enforces referential integrity.)

The fun part is that there is no consistency in the "spelling" of AUTO_INCREMENT.

Access uses COUNTER (and AUTOINCREMENT--no underline--as an alias).
SQL Server uses IDENTITY.

Both of them allow you to specify the seed and increment value in the declaration, whereas MySQL supports it only *after* the table definition, as part of the engine declaration.

Aren't we glad SQL is a standard language?

[Coyote6]

Haha :rolleyes: