I have a database I've designed and I'm ready to add records (more than the test records that I already have).

Normally what I do is use MySql Query Browser to add the records, or import from excel. In the current situation, I have a join table and I'm not sure how to populate it. (I've modified the fields and data examples just to make life easy).

For example, the join table is:

Cars2Parts
----------
carID
partID

So really the data for this table will look like:
1 2
1 3
3 2
2 2
...and so on. I

I'm finding it difficult to populate this table because I have long list of cars and a long list of parts, so remembering the ID's for each car and part is not practical. (e.g for the Cars table: 1-camaro, 2-porsche, 3-ford, etc...and for the Parts table: 1-mirror, 2-hood, 3-engine, 4-radio).

So I was wondering if anyone could give me some advice as to what they do to initially populate tables -- especially the join tables/cross-reference tables.

Thanks.

It sounds to me as though you need to use the sql command line to insert records (via a join of tables)
Something along the lines of

INSERT INTO Cars2Parts (carID, PartID)
select c.car_id, p.part_id
from cars c
join parts p (on c.part_id = p.part_id);

Of course this will depend on whether you've recorded all the relevant part_ids against the car_id in the cars table.

Also practice getting the select statement part to run correctly first, then edit to perform the insert part.

I should have been clearer in my post. I understand about using INSERT and SELECT statements. The question I had was about a practical GUI way to add data to a join table.

For example, to build a form.

Because the join table is only holding integer primary keys, every time I want to add an entry for "chevy" I have to look up in the cardID table what the carID is for chevy. Then if I want to add a part for the "porsche" I have to
manually look up what the carID is for porsche, etc.

So while I understand full well how to use the syntax, I was wondering about a GUI way to add to this join table.

I hope that's clearer, and thanks for you reply.

Well if I'm understanding you correctly you are sick of using mysql commands to do your dirty work. So you can use: PHPMyAdmin

You are correct that I'm sick of entering data with the command line, but as I mentioned in my post, I'm currently using the mysql gui tools (e.g mysql query browser) which I like (form most tasks) much better than phpMyAdmin.

The issue, again, is that since the join table is made up of primary keys, it's difficult for me to enter the data without having to have to look up what each key is referring to every time I want to add data to the join table.

So for example, I wanted to add to the join table an entry that reflects that the "Porsche has a Mirror", I'd first have to lookup that the primary key (carID) for porsche is 2, and then that the primary key (partID) for mirror is one. Only then could I enter the values: 2,1 into the table.

So my issue is, other than looking up in the cars and parts table each time I need to add data (e.g to figure out the ID that corresponds to the data), is there a better way to do this?

Maybe I could create a web form that allows me to enter the data for the join table that calls the cars and parts table in a dropdown? That's what I'm talking about.....something like that.

Thanks.

Originally Posted by Rangy

Maybe I could create a web form that allows me to enter the data for the join table that calls the cars and parts table in a dropdown?

Yes.

You could create a html form with two drop down boxes, something like:

PHP Code:

<form name="match" action="process.php" method="post">
<select name="cars">
<?php
$sql = "SELECT * from cars ORDER BY carID ASC ";
$result = mysql_query($sql);
if(!$result)
{
die(mysql_error());
}
$num = mysql_num_rows($result);
for ($x=1; $x<=$num; $x++)
{
$row = mysql_fetch_array($result);
echo "<option value=\"".$row['carID']."\">".$row['carname']."</option>\n";
}
?>
</select>
<select name="parts">
<?php
$sql = "SELECT * from parts ORDER BY partID ASC ";
$result = mysql_query($sql);
if(!$result)
{
die(mysql_error());
}
$num = mysql_num_rows($result);
for ($x=1; $x<=$num; $x++)
{
$row = mysql_fetch_array($result);
echo "<option value=\"".$row['partID']."\">".$row['partname']."</option>\n";
}
?>
</select>
</form>

And add the choices to the link table something like:

PHP Code:

$sql = "INSERT INTO linktable VALUES ('"$_POST['cars']"', '"$_POST['parts']"')";
$result = mysql_query($sql); 


Yes. That's exactly what I'm talking about. I'll try it out. Thank you very much!