Avoid Duplicate Entries In MySQL While Inserting Data?

Question

I am having a serious problem and I did not figure it out. I have a table name called 'results'. What I want to achieve is that when I insert new record it insert correctly in database.

At that level my script is working fine. But I want second time when click to add new result record if data is entered before then it will show my data so that if I want to update my data I could. If data is not entered previously then I will insert data into database. I got success to restrict to user to enter duplicate data but I did not get success to show that data on the same page.

I have a show test page where I place link manage results when I click on this link a new page is open where all students are listed with particular class_id and after their name there are fields like biology, chemistry etc...where I enter their marks.

My table structure is like that:

Table structure for table results

CREATE TABLE IF NOT EXISTS `results` (
  `result_id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(10) NOT NULL,
  `student_id` int(10) NOT NULL,
  `test_id` int(10) NOT NULL,
  `biology` int(10) NOT NULL,
  `chemistry` int(10) NOT NULL,
  `math` int(10) NOT NULL,
  `english` int(10) NOT NULL,
  `urdu` int(10) NOT NULL,
  `marks_obtained` int(10) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`result_id`),
  UNIQUE KEY `student_id` (`student_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

If you have any question then ask me. Sorry for long explanation!

<?php 
include("includes/config.php"); 

$id=$_GET['class_id'];
$id2 = $_GET['test_id'];

if($_SERVER['REQUEST_METHOD']=='POST'){

foreach($post as $key=>$val){
    $$key   =   $val;
}

$z  =   0;
foreach($post['biology'] as $biology1){
    $biology    =   $biology1;
    $student_id =   $post['student_id'][$z];
    $test_id =   $post['test_id'][$z];


    $chemistry      =   $post['chemistry'][$z];
    $math       =   $post['math'][$z];
    $english        =   $post['english'][$z];
    $urdu       =   $post['urdu'][$z];
    $marks_obtained =   $post['marks_obtained'][$z];
    $marks_obtained .= $biology+$chemistry+$math+$english+$urdu;

        if(isset($get['class_id']) and $get['test_id']){        

        $link = mysql_query("SELECT * from results where student_id='$student_id'");
        $res = mysql_num_rows($link);


        if($res>0) {

            $link2 = mysql_query("SELECT * from results where class_id='$class_id' AND test_id='$test_id'");

        }else {



    mysql_query("INSERT into results values('','$class_id','$student_id','$test_id','$biology','$chemistry','$math','$english','$urdu','$marks_obtained',now(),'')")or die(mysql_error());
    $z++;

            }
        }
    }
}
?>

HTML

<form method="post" action="" enctype="multipart/form-data">
    <table border="1" />


        <tr>
        <th>Student Name</th>

        <th>Biology</th>
        <th>Chemistry</th>
        <th>Math</th>
        <th>Engllish</th>
        <th>Urdu</th>



        </tr>
        <?php 

        $query = mysql_query("SELECT id,class_id,student_name from student where class_id='$id'") or die(mysql_error());
        while($rec =mysql_fetch_array($query)) {

        ?>

        <?php do { ?>

        <tr>
         <input name="class_id" type="hidden" value="<?php echo $rec['class_id'];?>" />
        <input name="student_id[]" type="hidden" value="<?php echo $rec['id'];?>" tabindex="1" />
        <input name="test_id[]" type="hidden"  value="<?php echo $id2;?>" tabindex="1" />

        <td><?php echo $rec['student_name'];?></td>




        <td><input name="biology[]" type="text" tabindex="2" value="<?php echo $rows['biology'];?>" /></td>
        <td><input name="chemistry[]" type="text" tabindex="5"  value="<?php echo $rows['chemistry'];?>" /></td>
        <td><input name="math[]" type="text" tabindex="7" value="<?php echo $rows['math'];?>" /></td>
        <td><input name="english[]" type="text" tabindex="4"  value="<?php echo $rows['english'];?>" /></td>
        <td><input name="urdu[]" type="text" tabindex="6" value="<?php echo $rows['urdu'];?>" /></td>
            <input name="marks_obtained[]" type="hidden" tabindex="9" />

      <?php } while($rows=mysql_fetch_array($link2))?>  <?php } ?>

        </tr> 

        <tr><td><button type="submit">Add Records</button></td></tr>
        </table>



    </form>

Answer 1

You can avoid the duplication of data by using this character && example

if($_post['usename'] && $_post['password'] && $_post['items']){
  echo "You fill all items";
} else {
  echo fill all items recommended!!
}

Answer 2

I see there is a unique index on student_id. This means your table will only hold one set of results for each student at any time. So I guess when you say "if data is entered before" you mean "if a second set of results is entered for the same student".

You have two options: either

• catch the exception thrown by the foreign key constraint when trying to enter a duplicate student_id and then fetch the results of that student_id and populate your form

• or, probably better, check if student_id exists before inserting, and if it does, don't insert, fetch the results of that student_id and populate your form. So you would need something like:

  SELECT COUNT(*) FROM results WHERE student_id=12345

(where 12345 is the student_id you are trying to insert)

If the result is greater than zero (in this case it can only be 1), don't insert, fetch instead and populate your form. That is:

SELECT * FROM results WHERE student_id=12345

Edit: You can also use

INSERT... ON DUPLICATE KEY UPDATE

...and avoid having to check, showing results and updating them.

Look at this question

Answer 3

For this you have to fetch data currently resides in database and then compare it with data u r inserting. If match found then Show an error message otherwise fire insert query