'Pushing' Data Down Thru A Table?

mrhinman

I'm building a site that allows applicants to input their own application data and manage it/update it themselves (it's for an employment agency). What I have is six places with forms that allow the applicant to put in employment history, most recent and on back.

Each section asks for the basics: Employer name, address, supervisor, etc., etc. But, let's say Johnny goes in and fills it out. He has his most recent employer and three others. Months go by, and Johnny wants to update his app and he's got a new 'recent employer'. I'd like to be able to "push" the data down in the table. One table for all six employers. When Johnny gets six past employers, I want him to be able to go in, add a new most recent employer, and the "seventh" one disappear from the table. Maximum six. I don't want applicants to have to re-type each one.

My table is set up like: E1, E1type, E1from,E1to,E1address,E1city.......E2,E2type,E2from,E2to,E2address, etc. etc.

Am I going about this the wrong way? Or is there a simpler way to do it?

--
M

rane500

I think I see what you're doing...you're doing a row per person, and then columns with data for every employer, right? There is a MUCH easier way to do that, but you'll have to change your entire table structure. The benefit is the ability to not only be able to push data down on the app - you could allow them to reorder everything however they wanted or have more than 6.

I'm assuming your overall table structure has the entire app as columns with one row per person. If so, you'd be better off breaking at least the employment history out into its own table. You use the columns for the types of data every block of employer data would have...type, from, to, address, etc. Have two other columns...one to hold the applicants name or row id from your master table, and then another to designate sort order. You then have one row per person per employer. Your table is larger, but the data is organized more efficiently so your end benefit is actually a faster system that you can reorganize much more easily. At that point you would just say something like

SELECT * FROM employer_data WHERE name='John Smith' ORDER BY sort_field

You'd end up with an array of data coming back, each value being one employer for John Smith.

Of course, as I said...the drawback is breaking out data into a new table, rewriting site code to use said new table, etc.

I hope I'm not stating something really obvious for you, if I am then I do apologize.

mrhinman

No apologies necessary: NEWBIE ALERT.

Great idea! I do have a separate table for employment history, keyed on the applicant's SSN. The table currently has an employee per row, with columns E1, E1type, E1address, ... E2.... E3....etc.

So, I likely have the right table structure, but need to add a sort order column? And, rather than have 6 sets of columns for employment history, have 1? But, the SSN is a unique field. Can that be a problem for inserting new rows?

Perhaps Imploding/Exploding arrays?

Roger_Ramjet

Yes, it cannot be unique if it is going to be used on multiple employment rows.

Best thing with this is to have an autoinc column in the main applicant file and use that as the primary key. The SSN could then be unique in there. Tables linked to the applicants data, like employments history, would be keyed on that autoinc id column instead of the SSN.

Normally one would just allow the applicant to input as many jobs as they require: I might want to go back more than 6 jobs so that I can include industry or technical experience specific to a particular vacancy.

Sorting would naturally occur on the employment dates: from: to:. All a user has to do then is add a new job or edit the dates to change the sort order. Overlaps do not matter if you sort on from: then to:.

mrhinman

Well, the HTML or PHP might require some creativity to allow the applicant to add or remove or sort the employment history. But, I really think that the SSN needs to be the key value. Autoinc would be great, but for some reason I'm not seeing how that would help.
I've considered using rane500's suggestion along with array imploding/exploding and sorting the imploded data within the table field.

Dunno, really. I'm very new to PHP and MySQL, but I'm learning so fast my head is spinning.

Perhaps the table could be structured like:
SSN(key) History (2 columns)
Then implode the array of (Dates,employer,address,etc) into the $History. ???
Then to have it show in the form for the applicant to manage, explode the array. Dates could be sorted (your suggestion), which may work better for organizing data. Also could help create more efficient applications that ask applicants for reasons for breaks in employment (a plus to my client).

A lot of experimentation here for me, but it's great learning....

rane500

Your explode/implode idea isn't a bad one, I've used that technique many times in specific situations...but it gets messy, and you don't have to do quite that much work in this case (unless you want to).

Essentially what we're discussing is called "database normalization." One of the better essays I've read on the subject is actually on this site, posted back in 2000. I'll link it, although it looks like some of his diagrams aren't showing up right. It's this one. I'll summarize the most relevant points for this situation.

The general idea behind it is to make the table structure as general as humanly possible and eliminate any duplicated data. Anytime you're thinking about columns that could be called X, Y, A1, B1, C1, A2, B2, C2 then you could use some normalization. It would be much simpler to just say X, Y, A, B, C and then store 2 rows instead. Easier to search, easier to put data in and take it out, and more importantly you don't have to alter table structure if you need A3, B3, C3...just add another row. The problem you're pointing out is that the X column has to be unique (like your SSN field). (You're also duplicating data in the Y field, but the same solution fixes both of these things.)

The answer is to create your own internal id number for each person...this is exactly what Roger Ramjet is suggesting. An internal id would be unique to each SSN, but could show up as many times as needed in a secondary table because it doesn't really mean anything...except to point to the unique SSN. The idea is to have two tables...one that contains X and Y (the fields that are either unique or only show up once per person) and one that contains fields that might show up many times for a person. The internal id would be an auto-incrementing field on the X and Y table...and use that as a reference number in a field in the other table. So for example you might have (hopefully this diagram will work out):

X               Y           ID
--------------------------------------
111-111-1111  |  Bob  |  1
444-444-4444  |  Jim   |  2

A                          B                         C                        ID
---------------------------------------------------------------------------------------------
Blockbuster         Montana            10 months             1
McDonalds           Arkansas           5 months               1
Sears                  Arkansas            6 months               1
Goodyear            Maryland            1 month                 2

Now you can have as many employer rows for Bob as you need...just give the ID of 1 and you have a link between the information. Normalization is something I've always felt was dead simple once you see a few diagrams...and yet it can have a HUGE impact on your database design. It can simplify almost any project, small or large. One of my larger pet projects dropped from 20+ tables to 8 after I normalized everything properly, and in turn that greatly reduces the PHP needed to get all of that data. You can link the data together with SQL joins or with the PHP instead (using multiple queries) depending on your level of comfort with those two options.

I hope this didn't get too wordy and sheds a bit more light on one way to tackle the issue.

mrhinman

Wow! That sounds great. I guess I couldn't see it, but now it makes (near) perfect sense. Still keep the unique ID, but reference a simple table to do it.

I'm off to try it now. Thanks! Oh, and it helps that this is still in development and the PHP code isn't complete, either.

Roger_Ramjet

And just incase someone still wants to know how to shuffle columns along

$sql = "INSERT INTO history t1 (id,c1,c2,c3,b1,b2,b3,a1,a2,a3)
     SELECT $id, t2.b1,t2.b2,t2.b3,t2.a1,t2.a2,t2.a3, '$one', $two', '$three'
     FROM history t2 WHERE t2.id=$id
     ON DUPLICATE KEY UPDATE";

This will work for an existing record. It selects the existing data for insert and does an update because the primary key already exists. So c1 gets set to b1, then b1 gets set to a1, then a1 gets set to the new data. The data gets pushed along and what was in c drops off the end.

A similar sort of thing can be done with subqueries and with REPLACE INTO.

mrhinman

Having an issue with the PHP now. This newbie needs some help:

Table Ehistref has two columns, ref (autoinc, key) and SSN
Table Ehistory has listid (autoinc, int(6), key),refnum (which should equal ref in Ehistref),employer,address,phone,etc.

SNIPPET:
<TABLE>
<?
$findref = "SELECT FROM Ehistref WHERE SSN='$idnumber'"; <-- This number is set in a session -->
$refq = mysql_query($findref) or die("Couldn't get from Ehistref.");
while( $refrow = mysql_fetch_assoc($refq) )
{
extract($refrow);
$findhist = "SELECT FROM Ehistory WHERE refnum='$ref' ORDER BY E1from";
$hist = mysql_query($findref) or die("Couldn't get from Ehistory.");
$histrow = mysql_fetch_assoc($hist);
extract($histrow);
?>
HTML CODE HERE TO MAKE TABLE
<? } ?>
</TABLE>
I get no values despite knowing I have sample data in the database. Not sure how I can implement the "listid" value, which basically increments each time an employee adds a new employment history.

rane500

When you say you get no values, is the query failing or are the variables you're attempting to print coming up blank?

As far as implementing a listing routine, my favorite method has always been:


TABLE START
$handle = mysql_query($query);
while($result = mysql_fetch_row($handle)) {
    CODE TO BE PERFORMED ON EACH ROW.
}
TABLE END

That'll give you an iteration per row.

mrhinman

VERY helpful, everyone. I've had quite a few successes with my new table structure.
Again, though, I've hit a PHP wall. When I WHILE loop in an HTML table, it shows everything fine. But say Johnny wants to update some past employer info, or delete one he entered incorrectly? I cannot seem to get my update script to change anything except the last entry, even though I tried using WHERE refnum='$refnum' && listid='$listid'. I have $_POST variables defined in my update script.

Perhaps I need to add checkboxes to indicate which ones to update? Or do I need a WHILE loop in my update script so as to get every one? Currently, I have the <FORM> inside the WHILE loop on the main page that displays the info.

rane500

So you're saying that even with a WHERE refnum='$refnum' AND listid='$listid' you're still just updating whichever row just happens to be last for that particular person? Let me ask you this...how are you getting listid from your form? Is it possible that you're reusing the same variable that you've used earlier in the script in the loop to build tables in the first place? If that happens then the number you pass will always be the last row retrieved. If that's not the case, how are you defining listid?

mrhinman

rane500 wrote:
So you're saying that even with a WHERE refnum='$refnum' AND listid='$listid' you're still just updating whichever row just happens to be last for that particular person? Let me ask you this...how are you getting listid from your form? Is it possible that you're reusing the same variable that you've used earlier in the script in the loop to build tables in the first place? If that happens then the number you pass will always be the last row retrieved. If that's not the case, how are you defining listid?

Well, when I look at the finished HTML source, I see the variable 'listid' value is unique, but the variable name is the same. Perhaps I need to have 'listid' for each listing, i.e., listid1, listid2, listid3, etc.

Trick is doing that....

rane500

How are you passing listid? So the appropriate value shows up for listid when you look at the rendered HTML? That should work fine then. Have you tried echoing out the finished update query to verify you've got the value populated properly there too?

rane500

Actually, no...let me ask more questions.

How are you letting the user edit his employee data? Let's say I'm a user and I have three employers in your system...how do I edit one of them? Do I choose one and get taken to a form with the data, are all three editable at once, do I put the row number in the form, or does the form itself decide which one to update via looking at the data I've entered?

mrhinman

rane500 wrote:
Actually, no...let me ask more questions.

How are you letting the user edit his employee data? Let's say I'm a user and I have three employers in your system...how do I edit one of them? Do I choose one and get taken to a form with the data, are all three editable at once, do I put the row number in the form, or does the form itself decide which one to update via looking at the data I've entered?

Actually, I have it where all employers are editable at once. The <TR>s in the HTML table are within the WHILE loop, so it lists each past employer correctly, but for some reason the update is not taking, except for the most recent listing. Initally, to create working data, I added sample data to the table using the phpmyadmin login on my server.

The SUBMIT button then references a separate PHP with the UPDATE code in it, and upon completion, gets a HEADER command to go back to the original page, showing the updated data (or supposed to).

rane500

Ah, so every field is editable at the same time...that will be what's causing the problem. Are all of the fields named the same, like EmployerName for every employer name field?

mrhinman

Unfortunately, yes. I figured that was a bad approach. Not sure how I'll do it, now. But, I know I've implemented similar type lists on other databases with similar field names, but each row had unique identification.

Perhaps I should script this separately. I did include a hidden field with the 'listid' in it, so that should work. Perhaps build a separate form for each employer with its own submit button? Seems radical. Hmmm.... I'll keep playing on this one.

Thanks rane500, for all your contributions.

Roger_Ramjet

If you have all the employers on one form then you need a way to indicate which one to edit. Otherwise all controls with the same name will post the value of the last one.

Seperate forms would be a good way, you can just use the same loop to generate a complete form from each row returned by your query. Another method is an edit link for each employer with the id in the url. Or you can use a checkbox/radiobutton to select and post the id to edit if the employers are all on one form.