connecting DB tables with mysql_insert_id

[ntroycondo]

I am building a product review application and want to link different tables in the DB together.

 

For example, if I have a table for Products, a table for Product review questions, and a table for Product review answers. More specifically lets say I have product table row Bike. I want a row in Questions table that ties in with Bike table and a row in Answers table that also ties in with the previous 2 tables.

 

I think I need to use mysql_insert_id in my queries to create links to the table row that direct to the appropriate

row in the other DB.

 

Do I need unique ID in each table for this or can use the ID that auto-generates?

 

Is this where I see  /product.php?id=33 page work with /editproduct.php?id=33, and /deleteproduct.php?id=33

 

What is the proper syntax to add to my query statement to get the links added to my table when PHP create the table?

 

$query="SELECT * FROM models";

$result=mysql_query($query);

 

A huge thanks in advance for comments, hints, tips, and examples.

[ntroycondo]

Maybe another thing I can use is SESSION to reference the same Product survey. Not sure about this...

 

Here's the code I'm working with.

I have added a link to the <th> and the corresponding <td>.

Not sure what to use with the $f4 var to get this links to populate the table and point to the

correct table. This is where I guess the mysql_insert_id get used.

 

Anybody have some thoughts?

 

<div align=center>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Device</font></th>
<th><font face="Arial, Helvetica, sans-serif">Size</font></th>
<th><font face="Arial, Helvetica, sans-serif">Cost</font></th>
<th><font face="Arial, Helvetica, sans-serif">Edit Questions</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"name");
$f2=mysql_result($result,$i,"size");
$f3=mysql_result($result,$i,"cost");
#$f4=
#this is where i use?? (mysql_insert_id)
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$" . $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$" . $f4; ?></font></td>
</tr>

<?php
$i++;
}
?>

[KevinM1]

You're on the right track.  What you need to do is normalize your tables before you go any further.  This will aid in your design and help remove insert/delete anomalies.  See: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

To skip ahead a bit, your questions and answers tables will need two id columns - one for themselves, which would be auto incremented like you originally thought, and one that contains the product id, which you'd obtain with mysql_insert_id.  You may need an extra id column in either questions or answers to link each question with its appropriate answer, as well, but that's dependent on what your design needs are (might instead need to create a pivot table).  In any event, this will link your questions and answers to a specific product.

[ntroycondo]

Thanks. I'll be working on it in next few days and see where I get. I think I'll be able to design it with just two id columns, since I'll only have one page that will reference all the different tables.