create mysql databse using phpmyadmin

[stokie-rich]

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

[diafol]

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

[iamthwee]

Doesn't look ok to me.

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

[stokie-rich]

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

[iamthwee]

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'

[stokie-rich]

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

[ckide]

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;

[stokie-rich]

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

[ckide]

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.

[stokie-rich]

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

[stokie-rich]

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

[ckide]

you should create database first
save your head here
http://www.w3schools.com/sql/sql_create_db.asp
http://www.tutorialspoint.com/mysql/mysql-create-database.htm

[stokie-rich]

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

[stokie-rich]

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

[parijatziw]

Doesn't look ok to me.

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