mtvaran Posted November 8, 2010 Share Posted November 8, 2010 Hi guys, im inserting data into the table using drop-down list & multi select list,well it works very well. but i need to make sure i should not insert same StudentID & CourseID twice. here my code for you could anyone tell me pls where should i write code to check existing data? <?php $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("uni", $con)or trigger_error('MySQL error: ' . mysql_error()); $result = mysql_query("SELECT * FROM student") or trigger_error('MySQL error: ' . mysql_error()); echo '<select name="sid">'; while($row = mysql_fetch_array($result)) { echo '<option value="' . $row['StudentID'] . '">' . $row['StudentName'] . '</option>'; } echo '</select>'; // ---------------- ?> </div> <div class="style41" id="Layer7"> <?php $result = mysql_query("SELECT * FROM course") or trigger_error('MySQL error: ' . mysql_error()); echo '<select name ="cid[]" multiple="multiple" size="10">'; while($row = mysql_fetch_array($result)) { echo '<option value="' . $row['CourseID'] . '">' . $row['CourseName'] . '</option>'; } echo '</select>'; mysql_close($con); ?> ------------------------------------ <?php $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("uni", $con)or trigger_error('MySQL error: ' . mysql_error()); if (!empty($_POST['sid']) && !empty($_POST['cid'])) { $ct = 0; $student = $_POST['sid']; foreach ($_POST['cid'] as $key => $course) { $sql = "INSERT INTO take (StudentID, CourseID) VALUES('".mysql_real_escape_string($student)."', '".mysql_real_escape_string($course)."')"; $query = mysql_query($sql) or trigger_error('MySQL error: ' . mysql_error()); if (mysql_affected_rows() > 0){$ct++;} } echo $ct . ' rows added.'; } mysql_close($con); ?> Quote Link to comment Share on other sites More sharing options...
corbeeresearch Posted November 8, 2010 Share Posted November 8, 2010 create a select statement query before the foreach statement to compare. if it match, then skip the foreach Quote Link to comment Share on other sites More sharing options...
btherl Posted November 8, 2010 Share Posted November 8, 2010 There's a few approaches to this. 1. Do a "SELECT" just before the insert, and hope no-one else inserts the same data inbetween. If you need high reliability you can lock the table first. 2. Add a unique index (or unique constraint) on StudentID and CourseID. Then if the same entry gets added twice, the second addition will get an error and will not get added. 3. Use a left join to check if the row is already in the table. This is a somewhat mind-bending option, so I don't recommend it unless you're comfortable with left joins. Quote Link to comment Share on other sites More sharing options...
mtvaran Posted November 9, 2010 Author Share Posted November 9, 2010 Can i write like this....? this gives me an error msg.... like Parse error: parse error in C:\wamp\www\finalresult.php on line 26 can anyone modify this for me? $sql = "SELECT StudentID FROM take WHERE StudentID =" . $_POST['sid'] "AND CourseID =" . $_POST['cid']; Quote Link to comment Share on other sites More sharing options...
btherl Posted November 9, 2010 Share Posted November 9, 2010 You forgot the "." between $_POST['sid'] and "AND CourseID =" Quote Link to comment Share on other sites More sharing options...
mtvaran Posted November 9, 2010 Author Share Posted November 9, 2010 you mean like this.... this also give me error..... $sql = "SELECT * FROM take WHERE StudentID =" . $_POST['sid'] . "AND CourseID =" . $_POST['cid']; if not could you please correct it for me. thanks Quote Link to comment Share on other sites More sharing options...
btherl Posted November 9, 2010 Share Posted November 9, 2010 Which error does it give you? 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.