i have the requirement where need to match subjects known by student and posted_subjets by institute,and this subjects separated by comma .i have written following code,its working fine but it looks quite long..
can anyone tell me the other way to do it.
following is table structure
posted_subjets table student table
----------------------------------------------------- -----------------------------------
posted_subjets_id | degree | subject_required | institute_id student_id | subjects
1 | MS | maths,electronics,CAD 2 1 craft,drama
2 BSC chemistry,biology 3 2 maths
3 arts craft,drama,dancing 1 3 cad,electronics
following is working code
public function match_subjects($sub,$sub_id)
{
$student_id=$this->session->userdata('student_id');
$flag = False;
$y=0;
while($y<count($sub))
{
$sql="SELECT subjects
FROM student
WHERE subjetcs
LIKE '%$sub[$y]%'
AND student_id = '$student_id'";
$query=$this->db->query($sql);
if(count($query->result()))
{
$flag = TRUE;
}
$y++;
}
if($flag)
{
$sql='SELECT subjets
FROM posted_subjets
WHERE posted_subjets_id ='.$sub_id;
$query=$this->db->query($sql);
$b=array();
foreach ($query->result() as $row)
{
$subjets = $row->subjets ;
}
$subjets =$this->multiexplode(array(',',' '),$subjets );
$flag=false;
$x=0;
for($i=0;$i<count($sub);$i++)
{
echo $sub;
for($k=0;$k<count($subjets );$k++)
{
$subjets [$k] = strtolower($subjets [$k]);
$sub[$i] = strtolower($sub[$i]);
if($subjets [$k]==$sub[$i])
{
$flag=true;
break;
}
}
}
if($flag)
{
while($x<count($subjets))
{
$sql="SELECT subjects
FROM student
WHERE subjects LIKE '%$subjets[$x]%'
AND student_id = '$student_id'";
$query=$this->db->query($sql);
if(count($query->result()))
{
$reg_user_id=$this->session->userdata('student_id');
$query=$this->db->query("SELECT institute_id
FROM posted_subjets
WHERE posted_subjets_id=".$sub_id
);
foreach($query->result() as $row)
{
$eid=$row->employer_id;
}
$this->db->query("INSERT INTO applied_subject(posted_subjets_id,institute_id,student_id,action)
VALUES (".$job_id.",'$eid','$student_id','Applied')"
);
$this->db->query("UPDATE posted_subjetcs
SET application_count=application_count+1
WHERE posted_subjets_id=".$sub_id
);
return true;
}
$x++;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
if anyone can tell me the other way to do it.
As I commented out, I would change DB structure to this :
posted_sujects table
posted_subjets_id | degree | institute_id
1 MS 1
2 BSC 3
3 arts 1
student table
student_id | other fields
1 Boby
2 Roger
subjects table
subject_id | name
1 maths
2 electronics
3 chemistry
student_subject table
subject_id | student_id
1 1
2 1
1 2
post_subject table
subject_id | post_id
1 1
2 1
Basicaly you can join student_subject with post_subject to match the subjects required and the students. Here is a quick query (not tested out)
SELECT * FROM student_subject
INNER JOIN post_subject ON student_subject.subject_id = post_subject.subject_id
And then from there you can add GROUP BY clause to group by posts for example.