selecting a value from multiple tables

Question

I have added search option to a project. There I used jquery auto complete feature to display related result for the keyword when some particular user is entering in search text box.

this is my php script..

<?php

require_once('database.php');

if(isset($_POST['queryString'])) {

    $queryString = $dbc->real_escape_string($_POST['queryString']);

    if(strlen($queryString) >0) {
        //$query = $dbc->query("SELECT subjects FROM subject WHERE subjects LIKE '$queryString%' LIMIT 10");
        $q = "SELECT tutor_name FROM tutors WHERE tutor_name LIKE '%$queryString%' LIMIT 10";

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

        if($q) {
            while ($row = mysqli_fetch_array($r, MYSQL_ASSOC)) {
                echo '<li onClick="fill(\''.$row['tutor_name'].'\');">'.$row['tutor_name'].'</li>';
            }
        } else {
            echo 'ERROR: There was a problem with the query.';
        }
    } else {

    }
} else {
    echo 'There should be no direct access to this script!';
}

?>

This is working properly for me.. but my problem is I need to check multiple tables with typing keyword. Here I use only one table.. anyone can tell me how can I attach my other tables to this query?

does all you previous questions were all unanswered? – John Woo Nov 26 '12 at 05:50 — John Woo, Nov 26 '12 at 05:50

score 4 · Accepted Answer · edited May 23 '17 at 11:48

4

use UNION

SELECT keyword 
FROM
(
    SELECT tutor_Name AS keyword FROM tutors
    UNION
    SELECT subject_name AS keyword FROM subjects
    UNION
    SELECT institute_name AS keyword FROM institutes
) s
WHERE keyword LIKE '%$queryString%' 
LIMIT 10

you query is also vulnerable with SQL Injection, please read the article below

How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:48

Community

1
1

answered Nov 26 '12 at 05:39

John Woo

258,903
69
498
492

no... there are different field names and tables.. Example: tutor_name from tutor table, subject_name from subjects table, institute_name from institutes table etc... – ugnuku Nov 26 '12 at 05:48
what does mean in this line's s ') s' – ugnuku Nov 26 '12 at 05:55
it's called the `Alias` of the subquery., – John Woo Nov 26 '12 at 05:55
I have anther problem.. now my autocomplete box display all result in subjects,tutor names, institutes etc to related keyword. Now I want to categorize them in autocomplete box.. ex: subjects under subject category, tutor name under tutor category etc.. – ugnuku Nov 26 '12 at 07:43
the best way to do now is to create a separate question regarding the new problem. this has been solved and the new problem does not have any relation on this one. – John Woo Nov 26 '12 at 07:47
ok.. I created new question.. this is its link http://stackoverflow.com/questions/13560808/categorize-auto-complete-data – ugnuku Nov 26 '12 at 08:03

score 0 · Answer 2 · answered Nov 26 '12 at 06:12

0

The below Query merge three tables that returns you only those selective records present in tables on the basis of common column in the tables.

SELECT tutor_name FROM yourDB
LEFT JOIN tableA ON tutor_name = '%$queryString%'
LEFT JOIN tableB ON tutor_name = '%$queryString%';

Hope this works for you.

answered Nov 26 '12 at 06:12

Jai

74,255
12
74
103

if there are different field names and tables.. Example: tutor_name from tutor table, subject_name from subjects table, institute_name from institutes table etc... then how can I use this query? – ugnuku Nov 26 '12 at 07:45
LEFT JOIN subjectTbl ON subject_name = '%$queryString%', it should be this way. – Jai Nov 26 '12 at 07:50

selecting a value from multiple tables

2 Answers2