CODEWITHSUNDEEP
phpmysql
Nathan Link Abiola
Nathan Link Abiola

Reputation: 43

MySQL PHP id from on table to another

I have constructed a array that gets the information from checkboxes. This is working fine, however i require a second input where the id of a second table is placed next to the array data. For example, when a user inputs data to my php form, it generates a id on one table, whereas my checkboxes are saved to a second. I want when the user selects the checkbox, the id is placed here. The problem i have is that i dont know just where to put this data.

$insertSQL2 = "INSERT INTO Project_course (Proj_id Cour_id) SELECT Course_id FROM courses WHERE 
Code IN (";
foreach ($_POST['CheckboxGroup1'] as $Q){
$Q = mysql_real_escape_string($Q);
$insertSQL2.= "'$Q', ";
 } 
 $insertSQL2 = rtrim($insertSQL2, ", ");
$insertSQL2 .= ")";

Proj_id is where the id need to go, while Cour_id is where it checkboxes are saved. Aswell as this, it needs to happen simultaneously as the relationship in the mysql table means that one column cannot be blank.

I know this was long winded, so any help will be gratefully accepted

Upvotes: 1

Views: 230

Answers (1)

Tomasz Struczyński
Tomasz Struczyński

Reputation: 3303

If I understand your question correctly, the form first saves project data, and then - some course data connected with this project.

BTW - first thing I suggest is to leave pure mysql_* functions in favour of PDO. But this is offtopic.

Anyway - first you probably run something like

$sql = "INSERT INTO project (col1, col2) VALUES (blah1, blah2)";
mysql_query($sql)

Then you can check the new project ID simply by invoking 

$projectID =  mysql_insert_id();

And then something like

foreach ($_POST['CheckboxGroup1'] as $key => $val)
{
    $_POST['CheckboxGroup1'][$key] =  mysql_real_escape_string($val);
}
$sql = "INSERT INTO project_course(project_id, course_id) SELECT ".$projectID.", course_id FROM courses WHERE code IN (".implode(", ", $_POST['CheckboxGroup1']).")";

UPDATE - if you already have a project in your project table, you may get project ID in several manners. For example by using SELECT project_id FROM project ORDER BY project_id DESC LIMIT 1.

Upvotes: 2

Related Questions