aixen083 Posted February 14, 2015 Share Posted February 14, 2015 Good Day, So this is the scenario. i have two tables Organization, and Course, so in organization.tbl i have org_name, org_description, and course_id and in course.tbl i have course_id, course_name. there is no problem in inserting the org_name and org_description but i have a problem in inserting the multiple course_id into organization.tbl This is my checkbox script. <?php $query = "SELECT * FROM course"; $result = mysql_query($query); while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { ?> <input type="checkbox" name="course" value="<?php echo $line["course_name"]?>"> <?php echo $line["course_name"]?> <br> <?php } ?> now how to insert multiple course id into org.tbl this is my query in saving the course_id. <?php if (isset($_POST['save'])) { $course_id = $_POST['course_id']; mysql_query("insert into organization (course_id) values ('$course_id') ") or die(mysql_error()); } ?> Quote Link to comment Share on other sites More sharing options...
kierany5 Posted February 14, 2015 Share Posted February 14, 2015 (edited) Use <input type="checkbox" name="course[]" value="<?php echo $line["course_id"]; ?>" /><?php echo $line["course_name"]; ?> Then $_POST['course'] is an array(); if (isset($_POST['save'])) { for($x=0, $c=count($_POST['course']); $x<$c; $x++) { $course_id = mysql_real_escape_string($_POST['course'][$x]); // I recommend doing more validation, e.g. checking if it exists in the courses table... mysql_query("insert into organization (course_id) values ('$course_id')") or die(mysql_error()); } } Note the real_escape_string - I am preventing an SQL Injection by escaping the value... Always escape and validate user inputs. Also note the mysql function are depreciated. You should be using mysqli or PDO instead, as mysql WILL be removed in a future version of PHP. Hope this answers your question. Edited February 14, 2015 by kierany5 Quote Link to comment Share on other sites More sharing options...
aixen083 Posted February 15, 2015 Author Share Posted February 15, 2015 it works fine. But i want to save 2 or more course_id to my organization table. please guide me.. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 15, 2015 Share Posted February 15, 2015 you need a third table to do this correctly. your organization table should just contain the unique information about each organization. your course table should just contain the unique information about each course. the third table that relates them would have columns for an id, org_id, and course_id. the org_id and course_id columns should also be setup as a unique composite index to prevent duplicate org_id/course_id entries. Quote Link to comment Share on other sites More sharing options...
aixen083 Posted February 15, 2015 Author Share Posted February 15, 2015 you need a third table to do this correctly. your organization table should just contain the unique information about each organization. your course table should just contain the unique information about each course. the third table that relates them would have columns for an id, org_id, and course_id. the org_id and course_id columns should also be setup as a unique composite index to prevent duplicate org_id/course_id entries. Sorry for this question but How can i Do that ... ? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 15, 2015 Share Posted February 15, 2015 exactly which part are you not clear on? creating the table or inserting the data? and what have you tried, for we cannot help you unless you have something that you have tried to do that didn't work the way you expected it to. Quote Link to comment Share on other sites More sharing options...
Barand Posted February 15, 2015 Share Posted February 15, 2015 The set up you describes is the first in the three below. The one you probably want is the third ORGANISATION COURSE ------------ ------------ (MANY-to-ONE) org_id +----- course_id courses are org_name | course_name run by many org_description | organisations course_id >----+ ORGANISATION COURSE ------------ ------------ (ONE-to-MANY) org_id -----+ course_id organisations org_name | course_name run many org_description +----< org_id courses ORGANISATION ORG_COURSE COURSE (MANY-to-MANY) ------------ ------------ ----------- organisations run org_id ---+ id +--- course_id many course and org_name +-< org_id | course_name courses run by course_id >--+ many organisations Quote Link to comment Share on other sites More sharing options...
aixen083 Posted February 15, 2015 Author Share Posted February 15, 2015 exactly which part are you not clear on? creating the table or inserting the data? and what have you tried, for we cannot help you unless you have something that you have tried to do that didn't work the way you expected it to. inserting data to my table. Quote Link to comment Share on other sites More sharing options...
aixen083 Posted February 15, 2015 Author Share Posted February 15, 2015 The set up you describes is the first in the three below. The one you probably want is the third ORGANISATION COURSE ------------ ------------ (MANY-to-ONE) org_id +----- course_id courses are org_name | course_name run by many org_description | organisations course_id >----+ ORGANISATION COURSE ------------ ------------ (ONE-to-MANY) org_id -----+ course_id organisations org_name | course_name run many org_description +----< org_id courses ORGANISATION ORG_COURSE COURSE (MANY-to-MANY) ------------ ------------ ----------- organisations run org_id ---+ id +--- course_id many course and org_name +-< org_id | course_name courses run by course_id >--+ many organisations Sir, this is actually i do to my database. but the problem is how to insert multiple course id into my organization table. So for example i have org_id which i want to insert the course into 1 org_id.. Quote Link to comment Share on other sites More sharing options...
Barand Posted February 15, 2015 Share Posted February 15, 2015 As you have been told by myself and mac_gyver, if an organisation has multiple courses, those courses would not go in the organisation table. Quote Link to comment Share on other sites More sharing options...
aixen083 Posted February 15, 2015 Author Share Posted February 15, 2015 As you have been told by myself and mac_gyver, if an organisation has multiple courses, those courses would not go in the organisation table. No sir if the organisation has multiple course that organisation must go to organisation table. Quote Link to comment Share on other sites More sharing options...
Solution mac_gyver Posted February 15, 2015 Solution Share Posted February 15, 2015 inserting data to my table. to allow for both the possibility of adding a new or removing an existing course entry for an organization, you should probably use a set of radio buttons for each choice. your form should submit the org_id, course_id, and if the course should be added or removed for that org_id. you would then remove any existing rows that have been selected for removal (the remove radio button was picked) and add any new rows that have been selected to be added (the add radio button was picked.) trying to delete rows that don't exist won't hurt anything and trying to insert duplicate rows, using the IGNORE keyword in the query, also won't hurt anything, so your logic doesn't need to try to determine which data has been changed, it can simply delete/insert whatever the submitted form data says to do. i would set up the radio buttons with a two dimensional array name in the html, with the org_id value as the index for the first dimension and the course_id as the index for the second dimension. there would be two radio buttons with the same array index values, one radio button would have the value 'add' and the second would have the value 'remove'. you would pre-select the 'add' radio button based on if there is already a row in the database table and pre-select the 'remove' radio button if there is not already a row in the database table. initially, with no data in the database table, all the remove radio buttons would be selected. an example of what the browser would show - Quote Link to comment Share on other sites More sharing options...
aixen083 Posted February 16, 2015 Author Share Posted February 16, 2015 Thank you for help sir @mac_gyver and sir @Barand Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.