Dynamically populated form for Insert?? It can't be this hard.

Ok, I give up. I've spent hours trying to figure something out that will allow me to do this and I'm sure it's basic php.

I have two tables already and I want to create a third that will include some information from the other two.
Mostly their, ID, Name, Image.

I will be matching one ID from each table and creating a match record. So, what I have so far is an insert form and behavior. That's all working fine.
Then I created two drop down menus that are dynamically populated from record sets. (That works great).

But now, what I need is for the ID and Image to follow along in the insert to the new table. Their image is already on the server, so I just need the image_name.jpg to be inserted into the new table.

I first thought I could use Dynamic Dropdowns. But it wants to use only the ID for values all the way accross and I can't figure out how to do it.

Then, I thought if I could create two hidden fields next to each drop down, then I could change the value of each as the dropdowns are selected. I can't figure this out.

I tried using server behaviors with no luck. I even tried an onChange Javascript.

I know that I am missing something. Really tired from studying Relational Tables, Joins, etc. Which was my first thought.

Is there a basic way to select a record name or id from a drop down menu of choices and have a couple of fields populate?

Any help would be appreciated.

Thanks,
TroyD

I might be going in the wrong direction here, but I have tried this so far with minor results.

<form id="form1" name="form1" method="post" action="">
  <select name="select_id" id="select_id">
    <option value="">Select ID</option>
    <?php
do {  
?>
    <option value="<?php echo $row_rs['id']?>"><?php echo $row_rs['name']?></option>
    <?php
} while ($row_rs = mysql_fetch_assoc($rs));
  $rows = mysql_num_rows($rs);
  if($rows > 0) {
      mysql_data_seek($rs, 0);
	  $row_rs = mysql_fetch_assoc($rs);
  }
?>
  </select> 
<input name="name" type="text" value="<? echo $_POST['select_id']?>" />
  <input type="submit" name="submit" id="submit" value="Submit" />
 
</form>

I do have the insert behavior and it inserts a record but does not insert anything into the field I have the hidden field assigned to.

What I ultimately want is for there to be 2 hidden fields. So the Select will provide the ID dynamically from table 1, and the two hidden fields will provide the Name and Image_Name.

When I tested it by just submitting to itself, the value of the hidden field was updated correctly. However not with the name associated with the id as I would like it to.

SO, how can I update the values in the hidden fields and at the same time post them to the new table? AND exchange the ID value from table 1 with the associated Name value from table 1 as well?

Any thoughts?

Thanks,
TroyD

Troy,

I am not sure what your end goal is here but I have a couple of ideas.

I need to clarify though... are you simply just looking for a quick way to get data from two separate tables into this new table (meaning all the data is already in existence) or are you trying to create some sort of new form that allows a user to edit previous info and possibly add new info?

If you are just trying to move data, the best way would be to write a simple script that loops through a query result based on an inner join to merge the results of the original two tables and gets the data and then inserts that data into the new table.

Brian

Brian,

I am trying to move, or copy data from 1, 2 or both current table into a new third table. The reason for this is mostly to preserve the information for the image file name since it already exists on the server. I am trying to learn all forms of Joins. I just got my new book on SQL yesterday. But yes, I am hoping for a quick way (relatively speaking) to do this.

My end goal, though simplified here, is to pull up a new insert form where there is a menu/list on the left and a menu/list on the right. They are dynamically updated from both current tables. These lists have a value of the id in each table, and a label of the name in each table.

Once "Name1" is selected and "Name2" is selected, and you click submit, the Name, ID and Image_name for both are inserted into the new table. Right now, all I can get is the IDs to show up in the new table.

But to be totally honest, I'd be happy right now if I could simply carry information from one table to another table. I can't even seem to do that.

For study and practice I dumbied-down my project to include only two tables. 1 current table and 1 new table.

For example. Lets say tbl_user has ID, Name, Image.

tbl_orders has a column for ID, Name, Image, OrderID, Comments

How would I select a users name in a form, enter the new OrderID and some comments and have the ID, Name, and Image from tbl_user automatically come along for the ride?

Any help in the right direction for both INNER JOINS for two tables and the Loop would be appreciated.

Thanks,
TroyD

Let me simplify this even more.

If I have 1 table.
tblSubjects
ID Name Image Gender
------------------------------------
1 name1 image1.jpg male
2 name2 image2.jpg female
3 name3 image3.jpg male
4 name4 image4.jpg male
5 name5 image5.jpg female

Now I need an insert form for a second table where I match up two of the subjects, (1 male & 1 Female), making a pair.
I want two dynamic drop downs that are populated from this table. But rather than having all subject's name included in both table, I want just the males in one drop down menu and just the females in the other.

So far, I have created two record sets based on this one table. Here is what I have.

mysql_select_db($database_dbCMS, $dbCMS);

$query_rsMales = "SELECT * FROM tblBreedersp WHERE sex = 'Male'";

$rsMales = mysql_query($query_rsMales, $dbCMS) or die(mysql_error());

$row_rsMales = mysql_fetch_assoc($rsMales);

$totalRows_rsMales = mysql_num_rows($rsMales);



mysql_select_db($database_dbCMS, $dbCMS);

$query_rsFemales = "SELECT * FROM tblBreedersp WHERE sex = 'Female'";

$rsFemales = mysql_query($query_rsFemales, $dbCMS) or die(mysql_error());

$row_rsFemales = mysql_fetch_assoc($rsFemales);

$totalRows_rsFemales = mysql_num_rows($rsFemales);

This works fine but I can't seem to populate the related hidden fields when their names are selected. I found a really cool Javascript that works, but there seems to be some conflicts between it and what WA wants on the page. It uses an array object. But I would like to do this with straight php if I can.

How do I create two arrays that loop through and give one menu the females and one menu the males? And include the other information as it inserts them into the new table?

Thanks,
TroyD