How to query data in two tables to get the name from one and the address from another based on a specific ID

Question

I am trying to get data from two different tables by entering a student ID in a search form and then getting the ID, Name from the first table and matching it with the Address in the second table.

If I use the search results from a form the query doesn’t return anything.

$sql = "SELECT     Students.StudentID, Students.StudentName, HomeADD.StudentADD
        FROM       Students
        INNER JOIN HomeADD
        WHERE      Students.StudentID = '%$searchq%'
        AND        HomeADD.StudentID  = '%$searchq%'";
$result = mysqli_query ($conn,$sql);
$count = mysqli_num_rows ($result);
$none = "";

To test the query I replaced $searchq with a literal J34765 and now it works.

$sql = "SELECT     Students.StudentID, Students.StudentName, HomeADD.StudentADD
        FROM       Students
        INNER JOIN HomeADD
        WHERE      Students.StudentID = 'J34765'
        AND        HomeADD.StudentID  = 'J34765";
$result = mysqli_query ($conn,$sql);
$count = mysqli_num_rows ($result);
$none = "";

To further test I used echo to output $searchq to make sure it was captured correctly and it was. I can’t understand why the query using the search results doesn’t work. The expected results are below.

Student ID  Student Name     Student Address
----------  ---------------  -----------------------------------
J34765      John Doe         123 Main Street, Fresno, California

your code is **vulnerable** to **sql injection** use **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — nbk, Apr 15 '23 at 14:54
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community, Apr 15 '23 at 15:02

score 1 · Accepted Answer · answered Apr 15 '23 at 14:29

It looks like the problem is with how '%$searchq%' is used in the WHERE part. You should use LIKE instead of =. But in this case, you don't need to find a pattern. Just use = with the variable $searchq without putting percentage signs around it.

   $sql = "SELECT     Students.StudentID, Students.StudentName, HomeADD.StudentADD
            FROM       Students
            INNER JOIN HomeADD
            ON         Students.StudentID = HomeADD.StudentID
            WHERE      Students.StudentID = '$searchq'";
    $result = mysqli_query ($conn,$sql);
    $count = mysqli_num_rows ($result);
    $none = "";

Or if you really need to search for pattern use LIKE instead

$sql = "SELECT     Students.StudentID, Students.StudentName, HomeADD.StudentADD
        FROM       Students
        INNER JOIN HomeADD
        WHERE      Students.StudentID LIKE '%$searchq%'
        AND        HomeADD.StudentID  LIKE '%$searchq%'";
$result = mysqli_query ($conn,$sql);
$count = mysqli_num_rows ($result);
$none = "";

Your first solution didn't work so I just removed the %% and now this works. $sql = "SELECT Students.StudentID, Students.StudentName, HomeADD.StudentADD FROM Students INNER JOIN HomeADD WHERE Students.StudentID = '$searchq' AND HomeADD.StudentID = '$searchq'"; — George, Apr 15 '23 at 15:23

How to query data in two tables to get the name from one and the address from another based on a specific ID

1 Answers1