Sorting & Matching Comma Separated String Values

[sintax63]

I have two fields in my database table that I need to sort out and match up. On is staff_id and the other is staff_name.

 

staff_id

1958324, 5930349, 6802941, 0592854

 

staff_name

John D. Doe, Sammy D'man, Lucy Lane, Jesse J. James

 

What I need to be able to do, is know that the first ID goes with the first name, second ID with second name, etc...

 

I'm passing a variable through (the staff_id number) and I need to be able to query my database and spit out the matching name.

 

Any help out there?

[Jessica]

You need to normalize the data.

[sintax63]

OK, I'm working now with something like this. I am using 7 variables for each because I don't know if it can be calculated automatically and add the number on the end via the loop or not. In any case...

 

$partsName = explode(",", $staff_name);
$staffName1 = str_replace("(", "", $partsName[0]);
$staffName2 = trim(str_replace(")", "", $partsName[1]));
$staffName3 = trim(str_replace(")", "", $partsName[2]));
$staffName4 = trim(str_replace(")", "", $partsName[3]));
$staffName5 = trim(str_replace(")", "", $partsName[4]));
$staffName6 = trim(str_replace(")", "", $partsName[5]));
$staffName7 = trim(str_replace(")", "", $partsName[6]));

$partsID = explode(",", $staff_id);
$staffID1 = str_replace("(", "", $partsID[0]);
$staffID2 = trim(str_replace(")", "", $partsID[1]));
$staffID3 = trim(str_replace(")", "", $partsID[2]));
$staffID4 = trim(str_replace(")", "", $partsID[3]));
$staffID5 = trim(str_replace(")", "", $partsID[4]));
$staffID6 = trim(str_replace(")", "", $partsID[5]));
$staffID7 = trim(str_replace(")", "", $partsID[6]));

 

Now I just need to figure out how to figure out what $facultyIDx belongs to my passed through variable, and then count that position and somehow translate that count to $facultyNamex. Am I even getting close?

[mikosiko]

...Am I even getting close?

 

not even.... could be done?... probably but unlikely will be correct/efficient/good practice ... should be done in that way?... NO

 

you already had a good starting point from Jessica... read on "database normalization", here http://en.wikipedia.org/wiki/Database_normalization is a simple way to start... if you don't understand it... just came again and ask... many will be offering help then.... for now probably the majority will just ignore the post or will be telling you the same over and over.

 

Also a clear explanation of your final objectives and details of your tables design will help to understand better what you are trying to achieve, allowing to the members to give you a better advise.

 

 

Here is another resource provided for a forum member (Christian) that could be interesting for you to watch

http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

Edited April 2, 2013 by mikosiko

[sintax63]

Alright, Mikosika. I was already working on what I posted above before I saw Jessica's suggestion. I'm not familiar with normalization but am currently reading into it...