Query to search in multiple tables + Specify the returned value

Question

My original query is taking a term from text field and start searching for it in two columns of one table [in order in create an autocomplete]

$query = $_GET['query'];      
$sql ="SELECT f_name, l_name FROM student WHERE (f_name ILIKE '%$query%' OR l_name ILIKE '%$query%');
$result = pg_query($con, $sql);
while ($row = pg_fetch_array($result))
           {  
            $names[] = array('f_name' => $row[0], 'l_name' => $row[1]);
           }

    $name_list = array();
    $name_list['success']       = true;
    $name_list['names']     = $names;


    $name_list['totalCount']    = count( $name_list['names'] );

echo json_encode($name_list);

No I want to add an extra criteria from another table (e.g: subject), So the user can enter a part of students first name, last name or subject. also I need to know the type of returned value, I mean if this is a name or a subject. so i can show a small icon depending on the type.

This is the model so you can imagine how I am doing it:

     Ext.define("Post", {
        extend: 'Ext.data.Model',
           proxy: {
         type: 'ajax',
        url: 'app/search.php',
        reader: {
            type: 'json',
            root: 'names',

        autoLoad: true,
            totalProperty: 'totalCount'
        }
    },

    fields: ['f_name','l_name'
, {
                    name : 'display',
                    convert : function(v, rec) {                        
                        return rec.get('f_name') + ',' + rec.get('l_name');
                    }
                }
    ]
    });

So if user entered: mat the result would be:

Mathio,Jay << student's first and last name

Mark,Matt << student's first and last name

After my changes it should be like:

Mathio,Jay << student's first and last name

Mark,Matt << student's first and last name

Mathematics << subject

Sparky Sparky · Accepted Answer · 2013-05-12T17:18:32

What you need to do is adjust your original query to pick up the subject the student is enrolled in (via a join)

SELECT f_name, l_name, subj.name
FROM student stud
JOIN subjects subj on subj.stud_id=stud.stud_id
WHERE (f_name ILIKE '%$query%' OR l_name ILIKE '%$query%' 
                               OR subj.name ILIKE '%query%');

Now, you probably have a multiple relationship between student and subject, so you'll need to adjust the SQL code to accommodate that. If you add your table structures, someone can jump in and show you how to do the actual join you need...

SELECT 'STUD' as theFlag,f_name, l_name, '' as SubjName
FROM student stud
WHERE (f_name ILIKE '%$query%' OR l_name ILIKE '%$query%')
UNION
SELECT 'SUBJ' as theFlag,'' as f_name,'' as l_name, subj.name as SubjName
FROM subject
WHERE (subj.name ILIKE '%query%');

You'll now get a record back with 4 columns. The first column indicates the source and will be either STUD or SUBJ. For STUD record, first and last name will be populated and the SubjName field will be blank. For SUBJ record, first and last name will be blank, but SubjName will be populated...

The only caveat would be if a record was found in both. For that, you need to decide which one you want to use...

Query to search in multiple tables + Specify the returned value

1 Answers