setting up my tables/fields

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

setting up my tables/fields

Post by d3ad1ysp0rk »

table1:

Code: Select all

CREATE TABLE `rants` (
`id` INT( 4 ) NOT NULL AUTO_INCREMENT ,
`author` VARCHAR( 25 ) NOT NULL ,
`date` VARCHAR( 10 ) NOT NULL ,
`title` TINYTEXT( 100 ) NOT NULL ,
`rant` TEXT( 15000 ) NOT NULL ,
`visits` INT( 8 ) NOT NULL ,
INDEX ( `id` ) 
)
what's wrong with that?
MySQL said:


You have an error in your SQL syntax near '(100) NOT NULL, `rant` TEXT(15000) NOT NULL, `visits` INT(8) NOT NULL, INDEX (`i' at line 1

thanks.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

EDIT: I have an error. Hold on.

Code: Select all

create table rants ( 
id int(4) not null auto_increment, 
author varchar(25) not null, 
date varchar(10) not null, 
title title(100) not null, 
rant text(15000) not null, 
visits int(8) not null, 
index (`id`) 
)
Just removed quotes round table name.

I've noticed you use quotes in your SQL statements where they are never needed.

Like it's incorrect syntax to go SELECT 'author', 'date' FROM 'rants' which is how I notice you write your select statements.

Anyway, just my 2c.

Also you may want to avoid calling fields "date" because thats typically a reserved word and db's (oracle for one) will spit up errors if you try.
Last edited by microthick on Sun Nov 30, 2003 12:10 am, edited 1 time in total.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

It's what phpMyAdmin added in ;)

I'm assuming it's a missing field, or wrong entry, or something.. i just don't know what..

my querys use backticks for table names/table fields and single quotes for page variables, ie "SELECT `sn` FROM `sns` WHERE `id` = '$id' LIMIT 1";
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

I use MySQL Control Center to create databases, tables, etc. It's pretty nice. You can get it at http://www.mysql.com .

Anyway, it kept throwing errors until I change your query to this:

Code: Select all

create table rants ( 
id int not null auto_increment, 
author varchar(25) not null, 
date varchar(10) not null, 
title varchar(100) not null, 
rant text not null, 
visits int not null, 
primary key (`id`) 
)
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

Code: Select all

CREATE TABLE `rants` (

`id` INT NOT NULL AUTO_INCREMENT ,
`author` VARCHAR( 25 ) NOT NULL ,
`date` VARCHAR( 10 ) NOT NULL ,
`title` TINYTEXT( 100 ) NOT NULL ,
`rant` TEXT NOT NULL ,
`visits` INT NOT NULL ,
INDEX ( `id` ) 
)
You have an error in your SQL syntax near '(100) NOT NULL, `rant` TEXT NOT NULL, `visits` INT NOT NULL, INDEX (`id`))' at line 1
hmm..
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

take out the ' marks around your table and field names. they are not ncessary as micro pointed out.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

it wasn't the single quotes or backticks, it was the fact that I put it as INDEX('id') instead of primary key (`id`)

thanks for your help
Post Reply