Hi all! I'm having a little trouble with my datbase as I have already designed it and it works smoothly with innodb tables active. Problem is I am know coding my php and I need a select command that will bring up my desired tuples from my database and display them nicely as links in my browser window. The tables that are linked through foreign keys are artists, profession_cat and specialist_area.
table artists contains data on the artists and has a primary key memberid, and profession_cat has a primary key procatid and a profession field. Specialist_area links these two tables together with memberid being referenced from artists and procatid from profession_cat, it also has an extra field called genre. Here are my tables if it helps? oh and does mysql support views?
Ive tried unions, joins all sorts but no cigar!
DROP DATABASE TASTYWET;
CREATE DATABASE TASTYWET;
USE TASTYWET;
DROP TABLE /!32200 IF EXISTS/ clients;
CREATE TABLE CLIENTS
(
CID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CMPNYNAME VARCHAR(20),
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20),
POSTCODE VARCHAR(10) NOT NULL,
EMAIL VARCHAR(20),
CONTACT_NO CHAR(15) NOT NULL
)
TYPE=INNODB;
DROP TABLE /!32200 IF EXISTS/ artists;
CREATE TABLE ARTISTS
(
MEMBERID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20),
POSTCODE VARCHAR(10) NOT NULL,
DOB DATE NOT NULL,
EMAIL VARCHAR(20),
CONTACT_NO CHAR(15) NOT NULL,
STAGENAME VARCHAR(15),
EXPRNCE_IN_YEARS INT UNSIGNED NOT NULL,
SEX CHAR(1) NOT NULL,
NI_NO VARCHAR(12) NOT NULL,
PRICE FLOAT(4,2) NOT NULL,
)
TYPE=INNODB;
DROP TABLE /!32200 IF EXISTS/ specialist_area;
CREATE TABLE SPECIALIST_AREA
(
MEMBERID INT UNSIGNED NOT NULL,
SPECIAL_AREA VARCHAR(20) NOT NULL,
GENRE VARCHAR(20) NOT NULL,
PRIMARY KEY (MEMBERID, SPECIAL_AREA, GENRE),
FOREIGN KEY (MEMBERID) REFERENCES ARTISTS (MEMBERID)
)
TYPE=INNODB;
DROP TABLE /!32200 IF EXISTS/ client_booking;
CREATE TABLE CLIENT_BOOKING
(
ORDERID INT UNSIGNED NOT NULL AUTO_INCREMENT NOT NULL,
MEMBERID INT UNSIGNED NOT NULL,
CID INT UNSIGNED NOT NULL,
DATE_NOW TIMESTAMP(6) NOT NULL,
DATE_FOR DATE,
HOURS TINYINT(2),
AMOUNT FLOAT(4,2) NOT NULL,
PRIMARY KEY (ORDERID, MEMBERID, CID, DATE_NOW),
KEY CID_KEY (CID),
FOREIGN KEY (MEMBERID) REFERENCES ARTISTS (MEMBERID),
FOREIGN KEY (CID) REFERENCES CLIENTS (CID)
)
TYPE=INNODB;
DROP TABLE /!32200 IF EXISTS/ artists_hired;
CREATE TABLE ARTISTS_HIRED
(
ORDERID INT UNSIGNED NOT NULL,
MEMBERID INT UNSIGNED NOT NULL,
ARTISTS_PRICE FLOAT(4,2) NOT NULL,
PRIMARY KEY (ORDERID, MEMBERID),
FOREIGN KEY (ORDERID) REFERENCES ARTIST_BOOKING (ORDERID),
FOREIGN KEY (MEMBERID) REFERENCES ARTISTS (MEMBERID)
)
TYPE=INNODB;
DROP TABLE /!32200 IF EXISTS/ admin;
CREATE TABLE ADMIN
(
USERID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
USERNAME CHAR(16) NOT NULL,
PASSWORD CHAR(16) NOT NULL,
)
TYPE=INNODB;
Basically when someone clicks on a category say dj I want it to display all of the dj\'s that are linked to that category. I have thought about just redisigning my database but i keep getting foreign key errors (errno 150) all the time! Can anyone please help? Thanks a lot!
ps sorry for this massive post but i am really desperate!
