Thursday, March 7, 2013

MySQL: select one table from another table

Suppose MySQL tables edu_courses and edu_extra1_study have a smae primary key: course_id, to select from edu_courses based on the condition in edu_extra1_study, we can use following MySQL statement if requiring order by title:
SELECT * FROM edu_courses   WHERE course_id In (SELECT course_id FROM edu_extra1_study) ORDER BY title;
If  edu_extra1_study selection is based on the variable in url
where c1, c2 and c3 from checkbox, i.e, the value equal to if  the checkbox is checked.
We we defined an arry in PHP:
$q1= Array();
If the checkbox is checked., we can add a condition, such as:
 finally we can use PHP implode to join all the conditions:
implode(' AND ',$q1);
Final code:
$querystring='SELECT course_id FROM edu_extra1_study  ';
$q1= Array();
 if($_GET['c1']==1) { $q1[]='competency=3';}
 if($_GET['c2']==1) {$q1[]='competency=2';}
 if($_GET['c3']==1) {$q1[]='cognitive=1';}
if(!empty($q1))$querystring=$querystring.'WHERE '.implode(' AND ',$q1);
{$sql = "SELECT * FROM edu_courses ORDER BY title";}
{$sql = "SELECT * FROM edu_courses WHERE course_id in (".$querystring.")ORDER BY title";}


No comments:

Post a Comment