Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011

    Unanswered: How to setup MySQL DB

    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:


    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.



  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    Hi Chris,

    can you show us the SHOW CREATE TABLE table along with the SQL statement you are using to get the resulting rows?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2011
    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`)

    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.



  4. #4
    Join Date
    Mar 2011
    Anyone have any ideas on this??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts