taking data from 2 tables and putting into a third

[wormsss]

Hi, I’m trying to select specific (not all) fields from two tables that already contain data (having being entered from a form) and insert them into a third table which currently has no data. These tables all belong to same database. I will eventually add if statements for insertation into the third table, so for example if userAge (from table1) <= ageLimit (from table 2) then that user gets inserted into the third table which is a table that will eventually list the approved members on a webpage. Does this make sense? Or is there an easier way to do this? It does seem a bit excessive to me to have to insert all the fields I want to make conditions for into a table. I was thinking of doing the condition statements on the php pages that insert data from the forms(1 php page for each) but I’ll need the data from BOTH tables.

Anyway, I’m already having trouble. I’m just trying to get data from the first table currenrly and it’s not going in! I don’t get any errors, it just doesn’t work. Also I’m uncertain of how to do it with a second table without overwriting anything. Maybe a simple join query? Can anybody help?

TABLE1:
userID
age
address
contact number
firstChoice
secondChoice
daysAvailable
maxHoursPerDay
healthProblems

TABLE 2:
activityID
instructor
ageLimit
numOfDays
hoursPerDay
suitablility

TABLE 3:
userID
activityID
firstChoice
secondChoice
daysAvailable
maxHoursPerDay
healthProblems
ageLimit
numOfDays
hoursPerDay
suitablility
completed

[php]mysql_connect("$host", “$username”, “$password”)or die(“cannot connect”);
mysql_select_db(“activities”)or die(“cannot select DB”);
$query = “SELECT userID, firstChoice, secondChoice, daysAvailable, maxHoursPerDay, healthProblems FROM table1”;
$result = mysql_query($query)or die(mysql_error());
while ($line = mysql_fetch_assoc($result)) {
mysql_query(“INSERT INTO table3 (userID, firstChoice, secondChoice, daysAvailable, maxHoursPerDay, healthProblems ) VALUES (’$line[userID]’, ‘$line[firstChoice]’, ‘$line[secondChoice]’, ‘$line[daysAvailable]’, ‘$line[maxHoursPerDay]’, ‘$line[healthProblems]’)”);
}[/php]

[m11]

Add or die(mysql_error()) to your insert query

[xerxes]

You might also want to take a look at SELECT INTO: http://www.w3schools.com/sql/sql_select_into.asp

Is there any relationship between tables 1 and 2? If so, using SELECT INTO would allow you to do the whole thing in one hit.

[wormsss]

Add or die(mysql_error()) to your insert query

Thank you! That worked, the information from table 1 goes in, but now I can’t get the information from table 2 to go in. If I comment out the first query it goes in fine, so it’s just something about there being TWO tables, it says “Column count doesn’t match value count at row 1”.

Also my condition still doesnt work even if I’m only using values from the first table, as you can see. There is a condition for the second table data to be pulled, I’ve mentioned it in a comment in the code. I am desperately stuck here!

[php]mysql_connect("$host", “$username”, “$password”)or die(“cannot connect”);
mysql_select_db(“activities”)or die(“cannot select DB”);

$query = “SELECT userID, firstChoice, secondChoice, daysAvailable, maxHoursPerDay, healthProblems FROM table1”;
$result = mysql_query($query)or die(mysql_error());
while ($line = mysql_fetch_assoc($result)) {
if (’$firstChoice’ == ‘$secondChoice’) { //NOT WORKING
$sql1=“INSERT INTO table (completed) VALUES (’$firstChoice’)”;
}
mysql_query(“INSERT INTO table3 (userID, firstChoice, secondChoice, daysAvailable, maxHoursPerDay, healthProblems ) VALUES (’$line[userID]’, ‘$line[firstChoice]’, ‘$line[secondChoice]’, ‘$line[daysAvailable]’, ‘$line[maxHoursPerDay]’, ‘$line[healthProblems]’)”) or die(mysql_error());
}

//if a user has an entry in ‘completed’ (if first=second as above) this is when we pull data from table 2…how to do this?
$query = “SELECT activity ID, ageLimit, numOfDays, hoursPerDay, suitability FROM table2”;
$result = mysql_query($query)or die(mysql_error());
while ($line = mysql_fetch_assoc($result)) {
mysql_query(“INSERT INTO table3 (activity ID, ageLimit, numOfDays, hoursPerDay, suitability) VALUES (’$line[activityID]’, ‘$line[ageLimit]’, ‘$line[numOfDays]’, ‘$line[hoursPerDay]’, ‘$line[suitability]’)”) or die(mysql_error());
}[/php]

[wormsss]

Hold on, realised why my conditions weren’t working…doh, I’m going to use this to get the rest of it rolling but I may still need help! I shall let you know