[Resolved] Arrays, exploding, variables, and looping...?

Tad

Okay. I'm making a character database for a writing RPG and I'm a little stuck.

The characters are allowed to have multiple jobs. Then for each job, they also have a job rank. However, some characters only have one job, whereas others have up to around seven or so.

So here's how my table is set up:

CHARACTERDB

id
name
surname
siblings
siblingsid
jobs

jobsranks

Then I have all of the character's different jobs in the jobs area separated by commas. Then the different ranks separated by commas as well.

So the Jobs part containts the value of "Hunter,Weaver,Sailor", or something like that, and the ranks but contains the value "Basic,Apprentice,Basic".

What I want to do is cycle out each job and rank from the database, so that it would read:

Primary Job: Hunter, Basic
Secondary Job: Weaver, Apprentice
Tertiary Job: Sailor, Basic

I'm just not sure how I'd do that. I've been looking at "foreach" statements, but I'm not sure if I'm on the right track or what. Any advice would be great.

rachel2004

Your first problem is a database design problem. What you need to do is take those columns that contain more than one value (such as "jobs") and put them in their own table. I see you also have a column called "siblings" for which you probably also have multiple values? If yes, you should create a "linking" table where userID #1 is related to user#20 or whatever.

Afterwards, you can start addressing your loop.

Frederick

An example of what rachel2004 is trying to say is as below:

Table 1

userID

tag_name

Table 2

jobID
userID
job

job_ranks

So, you set a loop to go through the "Table 2" and relate the userID to call the users information;

 $sql = mysql_query("SELECT * FROM table_one T1,table_two T2 
WHERE T1.userID = T2.userID 
ORDER BY T2.job
") or die (mysql_error());
while ($row = mysql_fetch_array($sql)) {
echo "Primary Job: ".$row["tag_name"].", ".$row["job_ranks"]."<br>\n";
}

Tad

I'm really new to this sort of thing. 🙂 Let's see if I'm following though.

So for each individual job that a character has, a new entry in Table 2 would be made with its name and rank? So if character 1 has three jobs, there would be three entries for character 1 in Table 2?

Then /all/ of a character's jobs can be called and listed because Table 2 also specifies 'userID' which would contain the same value as the userID in Table 1?

I probably sound really stupid, but that's why I'm posting in the Newbies board. I'm crossing my fingers and hoping that I'm on the right track though.

Megahertza

If your stupid, then I'm a newt.

rachel2004

Frederick started breaking it down, but since you have some follow-up questions, you may want to check this out, Tad:

http://www.phpbuilder.com/columns/barry20000731.php3

If you Google for "database design", a number of other online tutorials should come up. Avoid situations where you are storing more than one value in a cell. Otherwise, you find yourself scratching your head on how to properly query for a certain value.

Tad

Thanks so much, Frederick & rachel2004. 🙂 That article you linked is wonderful too. All about cutting down on data duplication and relations. I feel like I'm learning! I've got the job fields all working and looping properly now too, so I guess this is resolved. Thanks again. 😛