This topic is locked

Regarding Generating script for my database

3/27/2005 9:54:05 PM
PHPRunner General questions
author

I have pasted my database below
*
drop database if exists team;
create database team;

use team;
drop table if exists employees;
create table employees

(

empid int unsigned not null auto_increment primary key,

title char(4) not null,

firstname varchar(40) not null,

lastname varchar(40) not null,

dob date not null,

street varchar(80),

suburb varchar(80),

state varchar(80),

country varchar(80),

pincode varchar(10),

picture text,

email_id varchar(10) not null,

phone int zerofill not null,

sex varchar(10) not null,

priority int unsigned not null,

about text

);
drop table if exists position;
create table position

(

positionid int unsigned not null auto_increment primary key,

position varchar(50) not null,

description longtext
);
drop table if exists location;
create table location

(

locationid int unsigned not null auto_increment primary key,

location varchar(50) not null,

description longtext

);
drop table if exists department;
create table department

(

departmentid int unsigned not null auto_increment primary key,

department varchar(40) not null,

descritpion longtext

);
drop table if exists emp_pos_loc;
create table emp_pos_loc

(

empid int unsigned not null, #foreign key employee.empid

positionid int unsigned not null, #foreign key position.positionid

locationid int unsigned not null, #foreign key location.locationid

departmentid int unsigned not null #foreign key department.departmentid
);
***

I would like to know how to generate a script which can give me information on the employee's name , pposition , location and department for which i need to access different tables conforming to the above database structure. I m not able to achieve this using PHP runner. is it possible to do it or not?

admin 3/28/2005

Hi,

you can enter the following query string on Edit SQL Query tab in PHPRunner.

select title,

firstname,

lastname,

position.position,

location.location,

department.department

from employees

inner join emp_pos_loc on (employees.empid = emp_pos_loc.empid)

inner join position on (position.positionid = emp_pos_loc.positionid)

inner join location on (location.locationid = emp_pos_loc.locationid)

inner join department on (department.departmentid = emp_pos_loc.departmentid)


It will show employees with corresponding locations, departments etc.