How can I filter this select query result

[thara]

Hello everyone.

 

I am trying to filler a query result. This is my script

 

// Require the configuration :
require ('./includes/configuration.inc.php');

// Require the database connection:
require (MYSQL);

echo '<a href="">Tutor</a> | <a href="">Institute</a>';

$q = "SELECT
           'tutor' AS Member_Type,
           tutor_code AS Member_Code,
           tutor_name AS Member_Name
       FROM tutors
       UNION
       SELECT
           'institute',
           institute_code,
           institute_name
       FROM institutes";

$r = mysqli_query( $dbc, $q);

echo '<table border="1">
           <tr>
             <td>Member Type</td>
             <td>Member Code</td>
             <td>Member Name</td>
           </tr>';
while ( $row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
   echo '<tr>
             <td>'.$row['Member_Type'].'</td>
             <td>'.$row['Member_Code'].'</td>
             <td>'.$row['Member_Name'].'</td>
           </tr>';
}
echo '</table>';

 

My output is something similar to this..

 

+-------------+-------------+--------------------------------------+
| Member_Type | Member_Code | Member_Name		  |
+-------------+-------------+--------------------------------------+
| tutor	   |	    1250 | Edward 				 |
| tutor	   |	    1251 | Sachin 				  |
| tutor	   |	    1252 | Virath 				   |
| tutor	   |	    1253 | Cris 	                  |
| institute   |	    2500 | Montana               |
| institute   |	    2501 | Millanium 			  |
| institute   |	    2502 | Viswa 				   |
| institute   |	    2503 | Separd                  |
| institute   |	    2504 | Madurasanga 	  |
+-------------+-------------+--------------------------------------+

 

Now I need to filter this result using my tutor and institute link. So can I know is this possible to do it?

 

Hope someone will help me..

Thank you

[Barand]

Use WHERE clauses in the SELECT statements

[thara]

@Barand thanks for quick reply. But I need to display all result on the page. Although If user need to filter according to type I need to make it to filter. Thats why I have added 2 links to do it..

[daviddivine]

You should probably do this :

 

echo '<a href="?tutor=link">Tutor</a> | <a href="?institute=link">Institute</a>';


if (isset($_REQUEST['tutor'])) {

   $q = "SELECT
                       'tutor' AS Member_Type,
                       tutor_code AS Member_Code,
                       tutor_name AS Member_Name
               FROM tutors";
}


elseif (isset($_REQUEST['institute'] {
   $q = "SELECT
                       'institute',
                       institute_code,
                       institute_name
               FROM institutes";

} else {
$q = "SELECT
                       'tutor' AS Member_Type,
                       tutor_code AS Member_Code,
                       tutor_name AS Member_Name
               FROM tutors
               UNION
               SELECT
                       'institute',
                       institute_code,
                       institute_name
               FROM institutes";
}

$r = mysqli_query( $dbc, $q);


echo '<table border="1">
                       <tr>
                         <td>Member Type</td>
                         <td>Member Code</td>
                         <td>Member Name</td>
                       </tr>';
while ( $row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
       echo '<tr>
                         <td>'.$row['Member_Type'].'</td>
                         <td>'.$row['Member_Code'].'</td>
                         <td>'.$row['Member_Name'].'</td>
                       </tr>';
}
echo '</table>';

[Barand]

or

 

echo '<a href="?tutor=link">Tutor</a> | <a href="?institute=link">Institute</a>';


$tutor = isset($_GET['institute']) ? '0' : '1';    
$institute = isset($_GET['tutor']) ? '0' : '1';

$q = "SELECT
    'tutor' AS Member_Type,
    tutor_code AS Member_Code,
    tutor_name AS Member_Name
    FROM tutors
    WHERE $tutor
UNION
SELECT
    'institute',
    institute_code,
    institute_name
    FROM institutes
    WHERE $institute
    ";

[thara]

@Barand, can you tell how this two line work?

 

$tutor = isset($_GET['institute']) ? '0' : '1'; 
$institute = isset($_GET['tutor']) ? '0' : '1';

[Jessica]

Those are ternary statements, it's a consolidated if/else. 

if(isset($_GET['institute'])({
   $tutor = '0';
}else{
   $tutor = '1'; 
}

[thara]

Yes I know it. but my problem is how its behaving with the query to filter result...

[Jessica]

A clause of just WHERE 0 will return 0 results. Whichever has the 1 will have results. Compare his to David's code and it's obvious.