Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Jul 2008
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query to return only matching names

    I have a MySQL database and am trying to display results on a webpage. My structure has 8 different name fields per record along with other data. I have it setup this way (name1,start1,stop1,total1,name2,start2,stop2,total2 etc) When I query to find all records between certain dates with the name of John Smith it does return those along with the data in like name2 and name3 fields if there is anything in there and all the data that goes along with those. I ONLY want to see part of the actual record where it shows John Smith in any name field and the information that goes along with him for that record.

    Any help would be greatly appreciated.

    Thanks

    Chris

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,494 Times in 4,458 Posts
    Well, you made it really really difficult on yourself by designing the DB that way.

    You should have put all those fields into a *separate* table, one set of info (name,start,stop,total) per row. And then linked that table back to your main table via a primary/foreign key link.

    As it is, the only practical way to do this in SQL code would be to reproduce that structure, using a complex UNION query.

    Under the circumstances, it might be better to just write the code to do what you asked for in PHP/ASP/JSP or whatever you use.

    Unless it's not too late to rebuild the database?

  • #3
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Might be silly, but wouldn't redefining the SQL statement work?

    I.e. instead of:
    Code:
    SELECT * FROM <table name>
    WHERE name1 = 'John Smith'
    And thus pulling all fields from the record, use something along the lines of

    Code:
    SELECT 'name1','start1','stop1','total1' FROM <table name>
    WHERE name1 = 'John Smith'
    I may be wrong and misunderstanding the dilemma, if so I apologise

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I think you are misunderstanding:
    My structure has 8 different name fields per record along with other data
    that sounds like it means that in every row there are eight different fields for name that have to be checked and not just one (as there would be in a properly normalized table.)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,494 Times in 4,458 Posts
    That's how I read it, too, GuelphDad.

    My thought was that you could essentially create a normalized version of that via an ugly UNION, like this:
    Code:
    CREATE VIEW viewAsThisShouldHaveBeenDone AS
    SELECT 1 AS which, id, name1 AS name, start1 AS start, stop1 AS stop, total1 AS total FROM table
    UNION
    SELECT 2, id, name2, start2, stop2, total2 FROM table
    UNION
    SELECT 3, id, name3, start3, stop3, total3 FROM table
    ...
    UNION
    SELECT 8, id, name8, start8, stop8, total8 FROM table;
    Then you can use that VIEW just as you would a properly normalized table:
    Code:
    SELECT V.which, V.id, V.name, V.start, V.stop, V.total, T.other, T.fields, T.as, T.needed
    FROM table AS T, viewAsThisShouldHaveBeenDone AS V
    WHERE T.id = V.id
    AND V.name = 'John Smith'
    And then, indeed, you would only get the start, stop, total values that match name you are looking for and the value of which tells you which one matched (if it's important). And the join back to the main table allows you to get other fields from the same record.

    It's ugly as pig snot, and the performance is likely to suck, but it should work.

  • #6
    New Coder
    Join Date
    Jul 2008
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, I want to make the DB as it should be, and I understand it for the most part. Here's what I have, the form is for workers to fill out a daily shift report, so there is a shift field, date, and day of week and comments field then there is 8 rows that will have name,console,starttime,stoptime,totaltime, thats where the name1,console1,start1,stop1,total1 came from and that goes through 8. I guess my question is do I setup those in a table by themselves and then setup the shift,date,day,comments in a table seperate??? my query is going to be select ....
    from....
    where date is between a range and table1.name like %x% and table2.name like %x% etc so they can search a date range and put their name in and see what they worked for the month to fill out their timesheet but will i have to join the table the date is in with all the other 8 tables??? how would that work, thats where I get confused, do i have to put like an autoincrement # in each one and join based on that??

    thanks for the help

    CS

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    How about you show us some of your data. We already know your table isn't normalized.

    That might make it a bit clearer.

    Do you understand the View that OP created for you? it basically normalizes your data, though really you should restructure the table at this point if you can.

  • #8
    New Coder
    Join Date
    Jul 2008
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This would be a normal record:

    Day(Shift) Thursday (Day) 2011-08-11 (Date)

    John Smith (Name1) 06:00 (Start1) 18:00 (Stop1) 12 Hours (Total1)
    Jane Smith (Name2) 06:00 (Start2) 18:00 (Stop2) 12 Hours (Total2)
    Jim Smith (Name3) 12:00 (Start3) 18:00 (Stop3) 6 Hours (Total3)

    This was a good day (Comments)

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,494 Times in 4,458 Posts
    Okay, so change it to two tables:

    Code:
    Table: ShiftInfo
        shiftid INT AUTO_INCREMENT PRIMARY KEY,
        shift VARCHAR(xxx),  [better would be an ENUM]
        theDate DATETIME,
        comments VARCHAR(xxx)
    
    Table: ShiftPeople
        shiftid INT REFERENCES ShiftInfo(shiftid),
        personNumber INT,
        name VARCHAR(xxx),
        start TIME,
        stop TIME,
        total NUMBER
    And now you would have:
    Code:
    Record in ShiftInfo:
        173, DAY, 2011-08-11, this was a good day   
    
    Records (multiple) in ShiftPeople
       173, 1, John Smith, 6:00, 18:00, 12
       173, 2, Jane Smith, 6:00, 18:00, 12
       173, 3, Jim Smith, 12:00, 18:00, 6
    173 is the auto increment shiftid, just an example number.

    The peopleNumber field is optional, as would be an auto_increment field in the ShiftPeople table. Up to you.

    You won't *believe* how much that kind of NORMALIZED table design will help you.

    Just for example, how would you find the answer to a question like this in your old design:
    "On which dates did the DAY shift work twice or more times as many hours as the NIGHT shift?"

    That's trivial with this design. Tough with yours.

    As are many other queries.

    *INCLUDING* the one you asked about.

  • #10
    New Coder
    Join Date
    Jul 2008
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, I almost have this in my head but I am confused about one thing, will this work if on my input form I have a place for the user to put in up to 8 names at a time along with the times etc for that person or would they have to put them in one by one??? I am thinking if I put in a name, start, stop and total for one person then go to the next line and do that for another person that it won't accept it??

    Chris

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,494 Times in 4,458 Posts
    You will have to write your server-side code (PHP?) to handle that, but it's not difficult.

    What I would do would be to leave the <form> with the names as "name1","name2", etc., and then just run a loop in the back-end code to pull those values one *set* at a time and insert them into the ShiftPeople (or whatever you end up naming it) table.

    I don't use PHP, but *something* along these lines:

    First, create (INSERT) the record in the ShiftInfo table and get back the shiftid for that new record. (PHP has a builtin way to do that, I believe, but you can also do it with a MySQL query.) Then loop:
    Code:
    for ( $n = 1; $n <= 8; ++$n )
    {
        $name = $_POST["name" . $n];
        $start = $_POST["start" . $n];
        $stop = $_POST["stop" . $n];
        $total = $_POST["total" . $n];
        ... if all of those values are there and look valid then do ...
        {
            $sql = "INSERT INTO ShiftPeople VALUES($shiftid,$n,'$name','$start','$stop','$total')";
            ... execute that query ...
        }
    }


  •  

    Posting Permissions

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