08-10-2011, 08:42 PM
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.
08-10-2011, 09:25 PM
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?
08-11-2011, 01:35 AM
Might be silly, but wouldn't redefining the SQL statement work?
I.e. instead of:
SELECT * FROM <table name>
WHERE name1 = 'John Smith'
And thus pulling all fields from the record, use something along the lines of
SELECT 'name1','start1','stop1','total1' FROM <table name>
WHERE name1 = 'John Smith'
I may be wrong and misunderstanding the dilemma, if so I apologise
08-11-2011, 05:50 AM
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.)
08-11-2011, 06:58 AM
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:
CREATE VIEW viewAsThisShouldHaveBeenDone AS
SELECT 1 AS which, id, name1 AS name, start1 AS start, stop1 AS stop, total1 AS total FROM table
SELECT 2, id, name2, start2, stop2, total2 FROM table
SELECT 3, id, name3, start3, stop3, total3 FROM table
SELECT 8, id, name8, start8, stop8, total8 FROM table;
Then you can use that VIEW just as you would a properly normalized table:
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.
08-11-2011, 03:26 PM
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 ....
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
08-11-2011, 05:15 PM
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.
08-11-2011, 06:23 PM
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)
08-11-2011, 08:12 PM
Okay, so change it to two tables:
shiftid INT AUTO_INCREMENT PRIMARY KEY,
shift VARCHAR(xxx), [better would be an ENUM]
shiftid INT REFERENCES ShiftInfo(shiftid),
And now you would have:
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.
08-12-2011, 07:54 PM
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??
08-12-2011, 08:46 PM
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:
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 ...