SEARCH YOUR SOLUTION HERE  

Creating DB help

Hi ,

I need some help for my databases. My know knowledge with SQL is not big.

I want to make a database for job candidates. I've made the html form and now im working on the db.

So i created a db and i need to creat one table but im not sure now to do it right.

The columbs are:
0. ID
1.Name
2.Surname
3.Family Name
4. Personal ID
5. Sex
6. Job Type
7. Email
8. Phone
primary key ( ID, Personal ID ). Also the ID and PerID should be unique. I've made a simple sql code but it's not right. pls have a look and tell what to do. The second thing is how the users will fill those tables with info.

Code: CREATE TABLE users ( userid int unsigned not null auto_increment, name int char(20) not null, surname int char(20) not null, familyname int char(20) not null, personalid int unsigned char (10) not null, sex int char(10) not null, jobtype int char (20) not null, email , phone , );

Posted On: Thursday 25th of October 2012 10:51:35 PM Total Views:  148
View Complete with Replies




Related Messages:

Help in creating Table View   (91 Views)
I had a table called 'FruitSaleList', that contains the following columns sl.no, shop_id, fruitName, price, .... I had also another table called 'FruitList' that contains the columns shop_id, fruitName, salesManId, .... the problem is 'FruitList' table doesn't contain a column like fruitId. Let us assume there are following fruits are available in all shops Apple Banana Cherry Dates Elderberry Figs Grapes Jackfruit Orange (it is a lengthy list, around 50) In some case I need to list the details of fruits after 'Grapes' from 'FruitSaleList'. That means I have to list the 'Jackfruit' and 'Orange' from 'FruitSaleList'. I'm struggling to list these using a MySQL query in php. If I create a table view(FruitDetails) like below then I will proceed as specified below |FruitId|FruitName| |1 |Apple | |2 |Banana | |3 |Cherry | |4 |Dates | |5 |Elderberry| |6 |Figs | |7 |Grapes | |8 |Jackfruit | |9 |Orange | If it is possible then I will proceed by selecting the fruits after the fruitId 7. I can create virtual column or table but should avoid creating or alter table with real column or table. If I haven't explained clearly, please let me know so that I will explain even more. any help is appreciated.
Error creating database [was: Plaese help newbee here :)]   (136 Views)
hi , i am using mysql for the first time and struct with the first statement :: CREATE DATABASE 'books'; showing error:: ERROR 1064 (42000): 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 ''books'' at line 1
Problems with creating stored procedures, can't figure out the syntax   (153 Views)
so im trying to make some stored procedures but im still stuck at the first procedure still. SQL Code: Original - SQL Code use fromScratch; DROP PROCEDURE IF EXISTS personsGet; CREATE PROCEDURE personsGet () SELECT [id], [name], [lastname] FROM persons;
Difficulty creating triggers   (142 Views)
Im having difficulty writing a trigger in mysql. I have listed the trigger details and My code. Could you please help me out I can post my attempts at the triggers if this will be useful also "1.Check Approving User Trigger This trigger fires before updating the approving user number ApprUserNo in the ExpenseReport table. The trigger body compares the organization number of the approving user to the organization number of the submitted user. The organization number of the approving user must be the same as the organization number of the submitting user or the organization number of the parent organization of the submitting user. If the organization number of the approving user does not match either, raise an application error" SHOW DATABASES; CREATE DATABASE BEEP; USE BEEP; DROP TABLE ExpenseItem; DROP TABLE ExpenseReport; DROP TABLE BudgetItem; DROP TABLE Asset; DROP TABLE ExpCat; DROP TABLE Users; DROP TABLE OrgUnit; CREATE TABLE OrgUnit ( OrgNo INTEGER(9) UNSIGNED NOT NULL AUTO_INCREMENT, OrgName VARCHAR(50) NOT NULL, OrgParentNo INTEGER(9) NULL, CONSTRAINT OrgUnit_PK1 PRIMARY KEY(OrgNo) ) Engine = InnoDB; CREATE TABLE Users ( UserNo INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, UserFirstName VARCHAR(50) NOT NULL, UserLastName VARCHAR(50) NOT NULL, UserPhone VARCHAR(20) NULL, UserEmail VARCHAR(50) NOT NULL, UserOrgNo INTEGER(10) UNSIGNED NOT NULL, CONSTRAINT Users_CK1 CHECK(UserEmail LIKE '%@%'), CONSTRAINT Users_PK1 PRIMARY KEY(UserNo), CONSTRAINT Users_AK1 UNIQUE(UserEmail), CONSTRAINT Users_FK1 FOREIGN KEY(UserOrgNo) REFERENCES OrgUnit(OrgNo) ON DELETE NO ACTION ON UPDATE NO ACTION ) Engine = InnoDB; CREATE TABLE ExpCat ( ECNo INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, ECName VARCHAR(255) NOT NULL, ECLimit DECIMAL(10,2) NOT NULL DEFAULT 0, CONSTRAINT ExpCat_PK1 PRIMARY KEY(ECNo) ) Engine = InnoDB; CREATE TABLE Asset ( AssetNo INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, AssetDesc VARCHAR(255) NOT NULL, CONSTRAINT Asset_PK1 PRIMARY KEY(AssetNo) ) Engine = InnoDB; CREATE TABLE BudgetItem ( BINo INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, BIYear INT(4) NOT NULL DEFAULT 2005, BIAmt DECIMAL(10,2) NOT NULL DEFAULT 0, BIActual DECIMAL(10,2) NOT NULL DEFAULT 0, OrgNo INTEGER(9) UNSIGNED NOT NULL, ECNo INTEGER(10) UNSIGNED NOT NULL, CONSTRAINT BudgetItem_CK1 CHECK (BIYear >= 1900), CONSTRAINT BudgetItem_AK1 UNIQUE (BIYear,OrgNo,ECNo), CONSTRAINT BudgetItem_PK PRIMARY KEY(BINo), CONSTRAINT BudgetItem_FK1 FOREIGN KEY(OrgNo) REFERENCES OrgUnit(OrgNo) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT BudgetItem_FK2 FOREIGN KEY(ECNo) REFERENCES ExpCat(ECNo) ON DELETE NO ACTION ON UPDATE NO ACTION ) Engine = InnoDB; CREATE TABLE ExpenseReport ( ERNo INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, ERDesc VARCHAR(255) NOT NULL, ERSubmitDate DATETIME NOT NULL, ERStatusDate DATETIME NOT NULL, ERStatus VARCHAR(8) NOT NULL DEFAULT 'PENDING', SubmitUserNo INTEGER(10) UNSIGNED NOT NULL, ApprUserNo INTEGER(10) UNSIGNED NOT NULL, CONSTRAINT ExpenseReport_CK1 CHECK ERStatusDate >= ERSubmitDate, CONSTRAINT ExpenseReport_CK1 CHECK ERStatus = 'PENDING'/'APPROVED'/'DENIED', CONSTRAINT ExpenseReport_PK1 PRIMARY KEY(ERNo), CONSTRAINT ExpenseReport_FK1 FOREIGN KEY(SubmitUserNo) REFERENCES Users(UserNo), CONSTRAINT ExpenseReport_FK2 FOREIGN KEY(ApprUserNo) REFERENCES Users(USerNo) ) Engine = InnoDB; CREATE TABLE ExpenseItem ( EINo INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, ExpDesc VARCHAR(255) NOT NULL, ExpenseDate DATETIME NOT NULL, ExpAmt DECIMAL(10,2) NOT NULL DEFAULT 0, ExpApprAmt DECIMAL(10,2) DEFAULT 0, ERNo INTEGER(10) UNSIGNED NOT NULL, ECNo INTEGER(10) UNSIGNED NOT NULL, AssetNo INTEGER(10) UNSIGNED NULL, CONSTRAINT ExpenseItem_PK1 PRIMARY KEY(EINo), CONSTRAINT ExpenseItem_CK1 CHECK (ExpApprAmt
Problems with Foreign Keys when Recreating Data   (90 Views)
I have a mysql database that has multiple foreign key constraints. I changed laptops and now I'm trying to re-create the database on the new computer. I created a script of the sql structure of the database that worked fine. I then created a dump of the data using phpMyAdmin. But when I went to run this .sql script of the data, I got loads of foreign key constraint errors. I understand that the "chicken can't come before the egg" when populating tables with data, but is there a solution to this problem
Help with creating a foreign key   (101 Views)
trying to create 2 tables. i need one column, to reference the id of another table's column. here is the sql: Code: CREATE TABLE `user_types` ( `id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `usertype` VARCHAR( 50 ) NOT NULL , `general` ENUM( '0', '1' ) NOT NULL DEFAULT '0', `jaesun` ENUM( '0', '1' ) NOT NULL DEFAULT '0', `files` ENUM( '0', '1' ) NOT NULL DEFAULT '0' ) ENGINE = innodb; CREATE TABLE `user_logins` ( `id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `user` VARCHAR( 20 ) NOT NULL , `password` VARCHAR( 20 ) NOT NULL , `usertype` INT( 5 ) NOT NULL , `files_directory` VARCHAR( 20 ) NOT NULL , INDEX ( usertype ) , FOREIGN KEY ( usertype ) REFERENCES user_types( id ) ) ENGINE = INNODB doing that gives me this error: #1005 - Can't create table './whiteazn_gotrice/user_logins.frm' (errno: 150) read around, says if i get that error message: Quote: then check your foreign key definitions carefully - something is wrong with the definition. Common causes are a table not being of type InnoDB, a missing index on the same field (customer_id), or attempting to set a field to NULL when it cannot be. I am using MySQL client version: 4.1.21-standard according to CPanel.
Merging accounts creating a non unique error   (120 Views)
Sometimes my users create two accounts and so I have a query that goes into the searches table and attempts join the searches from both of their accounts into one: Code MySQL: UPDATE searches SET author = '$newAuthor' where author = '$oldAuthor' On my website form I simply type in the userID that I want the old searches to be reassigned to. This works fine if the keyword column doesn't contain the same search words for each account. How would I restructure my query so that it will only change the author of the search IF it doesn't already exist Does this make sense
creating user fails, but why?   (109 Views)
I have been told that below syntax is alright with mysql5, but I still get a syntax error: GRANT SELECT (username, password, ppid) INSERT (username, password, ppid, sample, digifolder, graphics) UPDATE (username, password, ppid) ON test.cart TO 'owner@localhost' IDENTIFIED BY 'a55ghyhY'; Gives me: 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 'INSERT (username, password, ppid, sample, digifolder, graphics) ' at line 1 Why does it fail
Need help creating a query that will only return records with matching counterparts   (91 Views)
Since I don't know how to explain this easily, I'm using the table below as an example. I want to create a MySQL query that will return only records that have matching counter-parts where 'col1' = 'ABC'. Notice the 'ABC / GHI' record does not have a counter-matching 'GHI / ABC' record. This record should not be returned because there is no matching counter-part. With this table, the 'ABC / GHI' record should be the only one returned in the query. How can I create a query that will do this id | col1 | col2 -------------------- 1 | ABC | DEF 2 | DEF | ABC 3 | ABC | GHI 4 | DEF | GHI 5 | GHI | DEF *Please let me know if you have no idea what I'm trying to explain.
sql server management studio - problem creating sql file   (107 Views)
all, I have an online MSSQL database and am using sql server management studio to manage this database. Is there any way that I can create a .sql database (for the table definition and the create rows)
creating a table   (132 Views)
I made a following code for creating a table. Code: code create table 'myTable' ( 'myField' int(12) not null ) result ERROR What's wrong with the code above
Need help creating a trigger... (MSSQL)   (121 Views)
Hi , I need some help creating a trigger. The trigger should work like so... After inserting a new record into the ProgramWaitlist table it should set the priority to the highest priority if the inserted priority is greater then zero... Here is what I have: Code: ALTER TRIGGER trig_setWaitlistPriority ON dbo.ProgramWaitlist FOR INSERT AS IF Inserted.priority > 0 BEGIN DECLARE @hPriority INT ...some how get the highest priority (UPDATE Inserted SET priority = @hPriority + 1) END
Getting a '> sign while creating a mysql entry..   (102 Views)
Hi all, While trying to enter data into a table with mysql the continue on next line prompt changed from a -> to a '> which a semicolon will not seem to escape from.... Is there a simple solution... I have just started to delve into the scary world of mysql and php. Thank You Steve
Error 105 while creating a table   (154 Views)
Hi everybody! I'm trying to create a new table but I receive this error message: "#1005 - Can't create table 'gestioneOrdini.Ordine' (errno: 150)" I obtain this error while trying to execute this query: create table Ordine ( CodOrd char(6) primary key, CodCli char(6) not null default '999999', Data date, Importo integer, foreign key (CodCli) references Cliente (CodCli) on delete set default on update set default ); If I use "on delete no action on update no action" instead of "on delete set default on update set default" I have no error and the table is created. Why do I have this kind of error I read the important thing is that CodCli must not be null, but it can't be.. I'm sorry if I used mysql tag not properly but I'm new in the forum. If it helps this is the query I used to create the table "Cliente": create table Cliente ( CodCli char(6) primary key, Indirizzo char(50), PIva char(12) unique )
Error creating database: Access denied for 'username'@'localhost'   (238 Views)
ALL my other MySQL connections are good except this one and mysql_fetch_array. Im using FileZilla for FTP and Dreamweaver CS4. What am I missing Isnt it the same username & password as my connect_to_mysql
Problem creating a function!   (127 Views)
Hi I am trying to create a function that wll return a UUID value. This is my attempt ============ DELIMITER $$ DROP FUNCTION IF EXISTS `gaia`.`fn_getUUID`$$ CREATE DEFINER=`root`@`%` FUNCTION `gaia`.`fn_getUUID`() RETURNS varchar(36) CHARSET latin1 BEGIN DECLARE uid varchar; SET uid = select uuid(); RETURN uid; END $$ DELIMITER ; ======================== However i'm getting the following error: Error Code: 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 '; SET uid = select uuid(); I'm pretty new to mysql. Can anyone please help
Problem creating DB from text file...   (95 Views)
I am trying to create a mysql db from a text using the DOS command prompt... However, I keep getting errors... I am using the following command under mysql/bin: mysql -u root -p < mailman.sql Below is the text in mailman.sql: CREATE DATABASE mailman; USE mailman; CREATE TABLE address (addressid int not null primary key, firstname varchar(25), lastname varchar(30), address1 varchar(35), address2 varchar(35), city varchar(25), prov char(2), postal varchar(12), phone varchar(14), email varchar(20) ); INSERT INTO address (addressid, firstname, lastname, address1, address2, city, prov, postal, phone, email) VALUES (1, 'Peter', 'MacIntyre', 'PO Box 2076', 'Station A', 'Stratford', 'PE', 'C1A 7J6', '902-555-1234', 'peter_maci@anymail.com') ; INSERT INTO address (addressid, firstname, lastname, address1, address2, city, prov, postal, phone, email) VALUES (2, 'Dawn', 'Riley', '31 Marjorie Cres.', '', 'Stratford', 'PE', 'C1B 1X4', '902-555-1236', 'testmail@anymail.com') ; INSERT INTO address (addressid, firstname, lastname, address1, address2, city, prov, postal, phone, email) VALUES (3, 'Fred', 'Flintstone', 'PO Box 2076', 'Station F', 'Bedrock', 'PE', 'C1A 7J6', '902-555-4234', 'fflint@anymail.com') ; INSERT INTO address (addressid, firstname, lastname, address1, address2, city, prov, postal, phone, email) VALUES (4, 'Frodo', 'Baggins', 'PO Box 2276', 'Station A', 'The Shire', 'ME', 'C1A 7J6', '902-556-1134', 'thering@anymail.com') ; INSERT INTO address (addressid, firstname, lastname, address1, address2, city, prov, postal, phone, email) VALUES (5, 'Simon', 'MacIntyre', 'PO Box 2076', 'Station A', 'Stratford', 'PE', 'C1A 7J6', '902-555-1114', 'simonsoccker@anymail.com') ; CREATE TABLE users (userid int not null primary key, firstname varchar(25), lastname varchar(30), username varchar(15), password varchar(10), accesslevel int not null, phone varchar(14), email varchar(35) ); INSERT INTO users (userid, firstname, lastname, username, password, accesslevel, phone, email) Values (1, 'administrator', 'administrator', 'admin', 'entry', 1, '555-1234', 'admin@admin.com') ; INSERT INTO users (userid, firstname, lastname, username, password, accesslevel, phone, email) Values (2, 'Peter', 'MacIntyre', 'pbmacintyre', 'happy', 1, '555-1234', 'peter_maci@hotmail.com') ; INSERT INTO users (userid, firstname, lastname, username, password, accesslevel, phone, email) Values (3, 'Fred', 'Flintstone', 'fredflint', 'pebbles', 2, '111-1234', 'fred@bedrock.com') ; CREATE TABLE prov (provid int not null primary key, provcode char(2), provname varchar(35) ); INSERT INTO prov (provid, provcode, provname) values( 1, 'PE', 'Prince Edward Island'); INSERT INTO prov (provid, provcode, provname) values( 2, 'NF', 'Newfoundland'); INSERT INTO prov (provid, provcode, provname) values( 3, 'NS', 'Nova Scotia'); INSERT INTO prov (provid, provcode, provname) values( 4, 'NB', 'New Brunswick'); INSERT INTO prov (provid, provcode, provname) values( 5, 'PQ', 'Quebec'); INSERT INTO prov (provid, provcode, provname) values( 6, 'ON', 'Ontario'); INSERT INTO prov (provid, provcode, provname) values( 7, 'MB', 'Manitoba'); INSERT INTO prov (provid, provcode, provname) values( 8, 'AB', 'Alberta'); INSERT INTO prov (provid, provcode, provname) values( 9, 'SK', 'Saskatechwan'); INSERT INTO prov (provid, provcode, provname) values( 10,'BC', 'British Columbia'); INSERT INTO prov (provid, provcode, provname) values( 11, 'ME', 'Maine'); INSERT INTO prov (provid, provcode, provname) values( 12, 'NH', 'New Hampshire'); INSERT INTO prov (provid, provcode, provname) values( 13, 'VT', 'Vermont'); INSERT INTO prov (provid, provcode, provname) values( 14, 'NY', 'New York'); INSERT INTO prov (provid, provcode, provname) values( 15, 'CO', 'Colorado'); INSERT INTO prov (provid, provcode, provname) values( 16, 'NV', 'Nevada'); INSERT INTO prov (provid, provcode, provname) values( 17, 'NM', 'New Mexico');
Need help with creating a portable application with database.   (140 Views)
I'm planning a portable logbook for employees to replace the current unstable Filemaker Pro one but I've got a few problems from the start. I don't know which database system to use. The project has to meet specific criteria: It is a logbook that stores information in a database and which can be used to produce reports detailing each employee's activity. It must also be: 1. Portable on a flash disk. 2. Easy to set up. Unzipping into a folder and being ready to go would be ideal. 3. Possible to create a backup tool that saves backups of the database from within the application. The frequency of the backups should be controllable and restoring should be easy. 4. Be able to import and export .csv files for integration/migration to and from the existing application. I do JavaScript, PHP, mySQL and general relational database design. I did know some ASP and VB some years ago. (I'm keen on CSS too, but that's icing). Options I've considered: a. MS Access. This would be easy to use since any machine with Access could be used and I could create the database, forms and reporting tools in one package. Cons are it is unstable, dependant on Access version on the machine in question and I don't know how to program it at the moment (I can learn). Even creating linked combo-boxes is hard compared to how easy it is in JavaScript/HTML. b. Portable Apache + mySQL and PHP/Javascript pages. This looks like it has a tough learning curve for me and it doesn't look like it will be easy for the users to setup and use. c. XAMPP and PHP/Javascript pages. You still have the setup difficulties for non-techy users and you have to start and stop services. This will be too much of a headache for the users - they really need something which is double-click and go. d. Online. Users would need net access, which they don't always have. This also means that the host (me) would have responsibility for the data held on my server. The users having responsibility for their own data is far preferable. I'm stumped at the moment. Access seems the easiest option atm but the instability of it is a big problem. Even small projects I've done recently have been prone to crashes (using Access 2007). Has anyone any ideas
creating table from a query   (85 Views)
Quite new to mysql and sql. I have just put a forum up on my web site. The database tables were readymade via phpbb. Because I want to send newsletters I need a table that just contains usernames and email addresses. The table for users in the phpbb forum is huge so i did sql query: SELECT user_id, username, user_email FROM USERS; Which gave me all the information I need to send the emails on mass when this table is linked to a newsletter application. however I am aware this is just a query. Can I create a new table from this query So i just have a table of users and emails. If so how do I do it I am using phpMyadmin.
HOw to: creating triggers ?   (100 Views)
how do i create a trigger in ms sql 2005 Am using MS SQL 2005 express edition. I right clicked the tables and dont see it anywhere