Hi guys i'm creating a new table in sql to allow users to register with my website, this is the sql code that I'm suing does this seem ok to you?

CREATE DATABASE `dyscaculia` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

USE `dyscaculia`;

CREATE TABLE IF NOT EXISTS `users` (
  `Firstname` varchar(1) DEFAULT NULL
  `Lastname` varchar(1) DEFAULT NULL
  `Username` varchar(1) DEFAULT NULl 
  `Email` decimal(1) DEFAULT NULL,
  `ConfirmEmaill` varchar(1)  DEFAULT NULL
   `Password` varchar(1)  DEFAULT NULL
     `PasswordEmaill` varchar(1)  DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I've only connected PHP via the ADODB connection and nnot mysql so this is new to be any help would be appreicated

Member Avatar
diafol

Why? Can't you connect via mysqli/PDO?

Doesn't look ok to me.

  1. There should be a primary id (int) auto incremented
  2. The varchars only contain one character.

hey guys thanks for the replys ok so I take it the varchars need to be 99 for example? and I think i will add a new feild say "user id?"

I've just tried to run the query and this came up

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Lastname varchar(1) DEFAULT NULL

Username varchar(1) DEFAULT NULl
`Em' at line 3

Why aren't you just using phpmyadmin? This will eliminate any typo mistakes.

Looks like you've got a one in the word NULL. NUL 'ONE'

that is what i'm using phpmyadmin as i'm new to phpmyadmin ive only ever used ADODB.

phpmyadmin is the best for me.
look at next example:

/* Table users names */
CREATE  TABLE `users_DB`.`user_name` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `user_name` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '',
    `pass_word` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '',    
    `user_type` INT(2) NOT NULL DEFAULT 7,
    PRIMARY KEY (`ID`)
) 
ENGINE = InnoDB AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;



/* table users mor data */
CREATE  TABLE `users_DB`.`user_more_data` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `join_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `birth_date` DATE DEFAULT '0000-00-00',
    `gender` INT(1) DEFAULT NULL DEFAULT 2,
    `tel` INT(15) DEFAULT NULL,
    `salary` FLOAT NOT NULL,
    `about` TEXT COLLATE utf8_unicode_ci NOT NULL,
    `user_type` INT(2) NOT NULL,
    `user_name` INT(11) NOT NULL,
    PRIMARY KEY (`ID`)
) 
ENGINE = InnoDB AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

thanks for that :) so do you think that would work then if i just changed the feild names to the ones that I want to use?]

I would only need to use lines 1-10 wouldnt i? i then want users to be able to regsiter and then for them to be able to log in

you should first write all you need (site elements ,database elements) on paper, drow forms and every thing , think about how your site will go (functions)
this will help you.

thanks for the advice i will do that and then carry on with the rest of it thanks for the advice

hi guys i took the adivce from "ckide" and thanks for that much appreciated. this is the code that i put into the sql query

/* Table dyscaculia */
CREATE  TABLE `dyscaculia_DB`.`dyscaculia` (

    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '',
    `last_name` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '',
    `user_name` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '',
    `confirm_user_name` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '',
    `pass_word` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '', 
    `confirm_pass_word` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '',
    `e_mail` VARCHAR(25) COLLATE utf8_unicode_ci DEFAULT '',
    `user_type` INT(2) NOT NULL DEFAULT 7,
    PRIMARY KEY (`ID`)
) 
ENGINE = InnoDB AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci

when i clicked "go" the following came up
#1049 - Unknown database 'dyscaculia_db I must be doing something right but ive been trying to do this all day and now i'm ready to bang my head against a brick all :(

Hey ckide yeah I've created the database first or is it because I need to name the table something like "users" as I want to link a quiz and have the quiz questions in this database too sorry for been such a pain

hi guys i found a website with code specafically to the task that i wanted to complete, this is the code

CREATE TABLE `registration`.`registration` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`firstname` VARCHAR( 25 ) NOT NULL ,
`lastname` VARCHAR( 25 ) NOT NULL ,
`username` VARCHAR( 25 ) NOT NULL ,
 `confirmusername` VARCHAR( 25 ) NOT NULL ,
    `password` VARCHAR( 25 ) NOT NULL ,
   `confirmpassword` VARCHAR( 25 ) NOT NULL ,
    `email` VARCHAR( 25 ) NOT NULL ,
`confirmemail` VARCHAR( 25 ) NOT NULL ,
) ENGINE = MYISAM ;

this is the error message that i'm now getting #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ENGINE = MYISAM' at line 11

Doesn't look ok to me.

There should be a primary id (int) auto incremented
The varchars only contain one character.

commented: did you read the last post? -3