How to setup MySQL DB

stanleyc · #1 (**permalink**) 08-16-11, 10:45

I am needing to know the best way to setup a DB, I am trying to create a shift report for my agency and it will have the following fields:

day
date
shift
comments
name
console
start
stop
total

These are the main fields BUT I need the name, console, start, stop, total to be on there as 8 different fields so users can enter up to 8 people that worked that day. I have tried setting this up and it will work if i do like name, name1, name2, start, start1, start2 etc but when I try to search between dates for a name it returns back that name plus all the other names on that same record and I only want the name I'm searching to be returned.

Any help on how to set this up properly would be greatly appreciated.

Thanks

Chris

it-iss.com · #2 (**permalink**) 08-16-11, 17:22

Hi Chris,

can you show us the SHOW CREATE TABLE table along with the SQL statement you are using to get the resulting rows?

stanleyc · #3 (**permalink**) 08-18-11, 16:37

Code:

CREATE TABLE `dailysr` (
  `shiftid` mediumint(9) NOT NULL auto_increment,
  `shift` varchar(10) collate latin1_general_ci NOT NULL,
  `day` varchar(10) collate latin1_general_ci NOT NULL,
  `date` date NOT NULL,
  `comments` varchar(500) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`shiftid`)

AND

Code:

CREATE TABLE `dailysrpeople` (
  `shiftinfo` int(11) NOT NULL auto_increment,
  `name` varchar(50) collate latin1_general_ci default NULL,
  `console` int(2) default NULL,
  `start` time default NULL,
  `stop` time default NULL,
  `total` int(2) default NULL,
  PRIMARY KEY  (`shiftinfo`)

I have other tables currently created that are the same as the dailysrpeople and have the same fields.

This DB is for a webpage form where employees enter information about who worked for a given day. There could be up to 8 different names entered. I started out with all fields in one db, name, name1, name2, start, start1, start2 etc etc.

I have set it up different ways, but here's what I need from it. I need to query 2 ways:

1 - Date - The user will enter a date and I want them to get back everything thats in the table for that date, when I had everything in one db this worked fine.

2 - Date/Name - This one is the one I can't get like I want it, I need to be able to put in a from date and to date and a persons name and the query brings back only the persons name that i am searching for and the days they worked between the date ranges. When I had everything setup in one db it would bring that back along with everyone else since it was all in the same id in the db.

I can setup the db however I need to, i just need to know what would be best for the queries I'm tryin to run.

Thanks

CS

stanleyc · #4 (**permalink**) 08-25-11, 10:00

Anyone have any ideas on this??

Internet Services

Web Development

Digital Marketing

Consumer Tech