Mysql Retrive unique info from 2 tables with UNION ?

[gettingit]

Hi.

I am trying to retrieve unique and separate info from two different tables and insert into a third. I have been trying UNION with limited success this morning. Perhaps its impossible and I should use another method ?

 

Whatever is in the left select works but right is blank regardless of which is which so I know the variables are good.

 

Any help greatly appreciated!

$query = "(SELECT `myid` FROM `table1` WHERE `myid` = '$newid') UNION (SELECT `herid` FROM `table2` WHERE level='$level') ";
  $res = mysql_Query($query) or die (mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
  $sql = "insert into `table3` (`id`,`my_id`,`her_id`) values ('','".$row['myid']."','".$row['herid']."')";
  $result = mysql_Query($sql) or die (mysql_error());

 

I have changed it a bit for this post so there may now be a typo, but close enough to see what I am attempting and need to do. It will execute and place select before union but not after union and returns no error.

[fenway]

I don't know what you're trying to do... but when you use union, you can't possibly get back the original column names from a unioned field that had two different names to begin with.

[gettingit]

So it is impossible then.

 

What I need to do is get information from 2 tables at the same time based on a  known value $myid in one table from running script and input passed by the form $level.

 

then insert as many rows that are found in table 1 with my id into 3rd table and associate with $herid gained from form input $level.

 

This is actually table insert 15 after a form submit. Neither value is fixed as it is a new record and each row with a different id just placed by the script into the tables just before so I some how need to do it at the same time while the script is running with the information. What it does in basic is create a new user in one table (myid) insert information across a few tables then link this information to known users based on "level" at this last step.

 

I have looked at join but it appears that the table field names must be the same in both tables, but the examples of union I have seen don't have the same table name fields.

 

 

[fenway]

So it is impossible then.

No, I don't think so.

 

Why not:

 

INSERT INTO table3 ( my_id, her_id) SELECT myid, ( SELECT `herid` FROM `table2` WHERE level='$level' LIMIT 1 ) FROM table1 WHERE myid = '$newid'

 

[gettingit]

That looks sweet!

Thing is there are more than 1 record "herid" based on level, so I must get all those records and link them all together in the 3rd table. "herid" could be 50 different women with different levels assigned.

 

I haven't tried it yet but see the limit 1 so am assuming it will only get and place one row?

[gettingit]

Then theres the whole issue of the table fields being different names in all 3 tables....

[fenway]

Thing is there are more than 1 record "herid" based on level, so I must get all those records and link them all together in the 3rd table. "herid" could be 50 different women with different levels assigned.

So let me get this straight... you want all possible combinations of myid/herid from table1 and table2?

 

Then theres the whole issue of the table fields being different names in all 3 tables....

That's not an issue....

[gettingit]

"you want all possible combinations of myid/herid from table1 and table2"

 

sort of.

myid is known as it was just created on submit and I have new areas of interest created. herid is many and she has separate levels of interest.

 

So then I need to look up herid  and I need to get his inserted levels (all unique) that were just inserted, and herid(s) based on levels of interest and link them together in one look up and insert.

 

and actually by making changes when submitted here I confused it (sorry) it should look more like this.

 

$query = "(SELECT `levelid` FROM `table1` WHERE `myid` = '$newid')

and not select myid

 

again herid is not known but must be retrieved based on her "level"

 

Hope that makes some sense. Thanks for all your help.

[fenway]

So you want to pull levelid from table1 and herid from table2, and get all of those possible combinations?