check existing data insert into the table?

[mtvaran]

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);

?>

 

 

[corbeeresearch]

create a select statement query before the foreach statement to compare. if it match, then skip the foreach

[btherl]

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.

[mtvaran]

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'];

[btherl]

You forgot the "." between $_POST['sid'] and "AND CourseID ="

[mtvaran]

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

[btherl]

Which error does it give you?