Making the Table main column name as a variable based on data in a row

[Quostin]

I brought this up yesterday in the mysql forums, but felt that it should have been put here since it can be both related. I tried using examples on top of my head, but decided to do a much better example now with more coding done.

 

Here is what I have:

<?php

include ("connect.php"); //Has database connection

$username = $_SESSION['name'];
$sql = "SELECT * FROM players where name= '$username'"; 
$users1 = $db->query($sql);
$users = $users1->fetch(PDO::FETCH_ASSOC);

$selmonster = "SELECT * FROM pet where id='$users[pet]'";
$selmonster2 = $db->query($selmonster);
$monster = $selmonster2->fetch(PDO::FETCH_ASSOC);

$res1 = "select * from spelllevel1 where monstername='$monster[monstername]'";
$res2 = $db->query($res1);
$res = $res2->fetch(PDO::FETCH_ASSOC);

if ($res[$levelmonster] == "")
	{
	print "No spell learned!";
	}
else
	{
	print "Learned $res[$levelmonster]";
	}

?>

This works great, and does exactly what it supposed to do. It grabs the data from the players data and insert the number from $users[pet] into $monster. Then it uses that $monster to grab the monster name in the spell list. Here is how the spelllevel1 table is set up:

 

monstername           | 2                  | 3                         | 4                   | 5                 |...

Small Purple Slime   | Rock Throw | Ground Collapse | Earth Prison | Terras End | ...

 

Now, it uses the $monster[level] to grab the column name and then the data in the column and learns the spell, unless it is blank. It works greats and exactly like I need. The problem is... I'm going to be needing more rows for levels, even more than 100 if I wanted, which can make a lot of columns that may not be needed and needed to expand if I want the monster to get to a higher level. Here is another way I had before I changed it to the one above.

 

monstername             | Rock Throw | Ground Collapse | Earth Prison | Terras End | Fireball | ....

Small Purple Slime     | 2                  | 3                         | 4                   | 5                 | 0           | ....

 

The one that I have above is what I want to do it as. The problem is that I don't know how to grab the main column name. I want to run $monster[level] to scan through Small Purple Slime until it finds the exact number from its level, then look at the main column, grab it, and make it learn that. For example: Monster level is 5, it goes through Small Purple Slime and sees 5, then looks at the main column and say "Hey, Column name is Terras End, lets $spelllearn = 'Terras End' and exit and continue with the rest of the code.

 

Is this even possible? Or should I stick with what I have now?

[requinix]

Normalize. You should never be adding more columns to a table unless you're fundamentally changing the structure of the data. If you have multiple pieces of data then you should be using multiple rows for them.

 

Like this:

monsters

id | name
---+-------------------
 1 | Small Purple Slime


spells

id | name
---+----------------
 1 | Rock Throw
 2 | Ground Collapse
 3 | Earth Prison
 4 | Terras End


monster learning

monster | level | spell
--------+-------+------
      1 |     2 |     1
      1 |     3 |     2
      1 |     4 |     3
      1 |     5 |     4

[requinix]

Oh, and that's exactly what mac_gyver was telling you to do in that other thread.

 

Please don't make multiple threads for the same topic so people don't end up repeating the same things like I just did.

[Quostin]

Okay. I think I understand. I thought he may have been misunderstanding me based on My example. I felt your answer was much more clear, maybe it was explained better. Thank you.