Insert Limited Data Into Table

[crescent]

Hello there!!!

I need your help.

I am working in a project of student admission using php & mysql. There are about ten subjects(like math, history, drama, music, science and so on) and 4 section(say A, B, C & D).

Now there has to be only 40 student with a certain subject in each section.

Exmaple:

40 student with math in section A
40 student with math in section B
40 student with math in section C
40 student with math in section D

Again

40 student with history in section A
40 student with history in section B
40 student with history in section C
40 student with history in section D

Again

40 student with drama in section A
40 student with drama in section B
40 student with drama in section C
40 student with drama in section D

And so on....

Now please tell me or suggest me how can I do this.

Thank you

[craygo]

Will the students have multiple subjects??

You could do it with 4 tables.

students
subjects
sections
classes


All tables should have a unique id and in the classes table link back to the other tables. Then let php control how many sections are allowed per subject based on the count of id's. You can do the check with a query prior to the insert query and reject if there are 40.

Ray

[craygo]

I thru this together for ya

Here is the table structure, you can add as many fields to each table as long as these are there
[code]CREATE TABLE `classes` (
  `class_id` int(11) unsigned NOT NULL auto_increment,
  `stu_id` int(11) NOT NULL default '0',
  `sub_id` int(11) NOT NULL default '0',
  `sec_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`class_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

-- Table structure for table `sections`

CREATE TABLE `sections` (
  `section_id` int(11) unsigned NOT NULL auto_increment,
  `sec_name` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`section_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

-- Table structure for table `students`

CREATE TABLE `students` (
  `student_id` int(11) unsigned NOT NULL auto_increment,
  `stu_name` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`student_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

-- Table structure for table `subjects`

CREATE TABLE `subjects` (
  `subject_id` int(11) unsigned NOT NULL auto_increment,
  `sub_name` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`subject_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;[/code]

I am going with the notion you can write your forms and such but here is the insert code

[code]
$section = $_GET['sec_id'];
$subject = $_GET['sub_id'];
$check = "SELECT sub_id, sec_id
        FROM classes
        LEFT JOIN subjects ON sub_id = subject_id
        LEFT JOIN sections ON sec_id = section_id
        WHERE sec_id='$section' AND sub_id='$subject'";
$chres = mysql_query($check) or die (mysql_error());
$num_rows = mysql_num_rows($chres);
if($num_rows < 41){
// put your insert code here

} else {
// Too many students here
echo "There are too many students for this class";
}[/code]

Ray

[crescent]

Dear Friend

Thanks for ur excellent reply. Here is my table structure...which will be more easier for me to use.


[code]

CREATE TABLE `_student_info` ( `st_sl` bigint(20) NOT NULL auto_increment, `st_id` varchar(25) NOT NULL default '', `st_name` varchar(50) NOT NULL default '', `st_sem` char(3) NOT NULL default '', PRIMARY KEY (`st_sl`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE `_student_reg_info` ( `st_id` varchar(25) NOT NULL default '', `st_coursecode` varchar(10) NOT NULL default '', `st_coursecredit` varchar(10) NOT NULL default '', `st_coursesection` char(2) NOT NULL default '', `st_date` varchar(20) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; [/code]






[code]
for($i=0;$i<count($_POST['cc']);$i++)
    {
      $cr="cr".$i;
      $sec="se".$i;
      $dt="dt".$i;
      
      $str=explode("#",$_POST['cc'][$i]);
      $cc=$str[1];
      if(($cc<>"")&& ($_POST[$dt]<>""))
      {
      $sql="insert into _student_reg_info(st_id,st_coursecode,st_coursecredit,st_coursesection,st_date) values('$dpt$id','{$cc}','{$_POST[$cr]}','{$_POST[$sec]}','{$_POST[$dt]}')";
     //echo $sql."ada";
      $rs=mysql_query($sql);
      if($rs)
      {
      echo "Successfully added course code {$cc} for Student id= '$dpt$id'<br>";
      }
      else
      {
       echo "Failed";
      }
      }
    }
    

$datetime=date("y-m-d h:i:s");
if($rs){
$sql1="insert into _student_info(st_id,st_name,st_sem,datetime) values ('$dpt$id', '$name', '$sem', '$datetime')";
     //echo $sql."ada";
     mysql_query($sql1);
}


[/code]


Feiends If I use the above code and table structure then how will I give the limitation to input that I described my first request.


Thanking You