updating table with data

[Bob42]

I have a script that when you click on the Add button, a pair of input boxes appear. When you click on Add more times, input boxes keep on appearing and you can add data into them.


<script type="text/javascript">
function typeresistance()
{
str = "Type: <input type=\"text\" name=\"type_resistance1[]\">";
str += "&nbsp;&nbsp;Percentage: <input type=\"text\" name=\"type_resistance_percentage2[]\"><br />";
document.getElementById("typeresistance").innerHTML += str;
}
</script>


<div id='typeresistance'><input type='button' onclick='typeresistance()' value='Add' style=\"color:#FFFFFF; font-size: 8pt; background:#383838\"><br /></div>


Now the button is in a form. When the form submits I want the data in all the input boxes to be added into a table in a database. However, the amount of input boxes that are submitted will be different for each table. For example, I may decide to input data into 6 boxes, and in another table 10 boxes. I'm using something like this to make the data be displayed on a page.


$type_resistance = $_POST['type_resistance1'];
$type_resistance_percentage = $_POST['type_resistance_percentage2'];
for ($i = 0; $i < count($type_resistance); $i++)
{
echo $type_resistance[$i];
}
for ($j = 0; $j < count($type_resistance_percentage); $j++)
{
echo $type_resistance_percentage[$j];
}


But instead of having the data be displayed on a page, I would like the data to be added into a table of mine. I know how to add this data into a table, but because the data will be different for each table, I don't know how to do it. I'm hoping someone can help me through this.

[Fumigator]

The answer is actually simple-- you add one row for every iteration of input elements. This is called normalizing your data, in fact. Instead of a table with data such as resistance1, resistance2, resistance3, etc, you have a column named resistance and insert one row for every different input. You may or may not have another table that acts as a "header" to store other information that is common to all of these rows in the resistance table.

[Bob42]

Well, I already have a table created with data in it. I was planning on adding the resistance data to it too, if possible. And just so you know, I'm going to have multiple tables with different amounts of resistance data. Perhaps I should create a separate database altogether just for the resistance data, and for each different amount of resistance data, there is a separate table?

[guelphdad]

look into database normalization, it is an important idea to understand when using databases.

[Fumigator]

Well, I already have a table created with data in it. I was planning on adding the resistance data to it too, if possible.

No don't do that-- create a new table to store the resistance data. Connect the resistance table to your "main" table using a foreign key.

As Guelph mentioned, this is an important concept that you should learn about and use.

[Bob42]

Okay, I created a new table called 'resistance'. The table has three columns called name, type, and percentage. The resistance table and the "main" table have the same primary ID so that they can be linked up with each other. Now each of those tables represents one character in a game of mine. I have over 100 characters in the game. Would I have to create 100 different "resistance" tables, or is there a way to fit all the resistance data into one table?

[Fumigator]

Please don't create 100 different tables. You destroy the whole point of a relational database if you do that.

Your main table has a unique primary ID, let's call it mainID.

Your resistance table should have its own unique primary ID as well, which can be made up of more than one column. Let's assume there's a resistance type, such as "fire", and each row in the Main table will only have one "fire" resistance. Therefore we can make the unique primary ID "mainID" + "resistanceType". (If a Main table row can have two "fire" resistances then you'll have to come up with another unique primary ID for your resistance table.)

Now you can see that all resistance data can be stored in just one resistance table. To pull all of the resistance data for a character, you'd use a join query:


SELECT m.mainID, m.mainName, r.resistanceType, r.resistancePercentage
FROM mainTable as m
JOIN resistanceTable as r
ON m.mainID = r.mainID
WHERE m.mainID = 1000000

[Bob42]

Using your code, would this work?


$query="SELECT m.gengar, m.Gengar, r.fire
FROM Gengar as m
JOIN resistance as r
ON m.gengar = r.094
WHERE m.gengar = 1";


"gengar" being the ID of my main table, "Gengar". "094" being the ID of the resistance table.

I want the data that is inputted from the fields to be entered into the resistance table. I already have a row called "fire" entered in the resistance table. If I am understanding correctly, my main table needs to be connected with the row "fire" upon submission of the form. In order for that to happen, I need a unique ID that contains "gengar" + "094". I'm still a bit confused on how this unique ID is made and where it is placed in the code.

[guelphdad]

show your tables and show some sample rows of data and then your expected output.

[Fumigator]

I hope you didn't name a column "fire"? Because that's what you're selecting, a column from the resistance table named fire. The value "fire" should be stored in the table; it should not be a column name. The column name would be something like resistance_type.

Also maybe you should read up on SQL queries, as you do seem to be confusing the "data" in the table and the "column names" that make up the table. (ON m.column_name = r.column_name, not r.094 where 094 is a value in the table)

[Bob42]

mysql_query("SELECT m.094, m.Gengar, r.type_resistance
FROM Gengar as m
JOIN resistance as r
ON m.type_resistance = r.type_resistance
WHERE m.gengar = 1");


Gengar is the main table that I want the resistance data to go into. I have a value called "fire" stored in the type_resistance column in resistance table. There is also a type_resistance column in the Gengar table where I want the fire value to go into. Is this correct?

BTW, what does the '1' in the WHERE m.gengar = 1 mean?