How to use search query with join tables?

Question

I have two tables in a database. The first table is "tb_ctsreport" with fields "qr_id, idNum, date, time" and the other one is "tb_usersreg" with many fields which includes idNum, firstName, lastName, age and address. I have displayed my table using this query:

$query = "SELECT * FROM tb_ctsreport LEFT JOIN tb_usersreg ON tb_ctsreport.idNum=tb_usersreg.idNum";

This gives me a resulting table of qr_id, idNum, Name(concatenated firstName and lastName), date, time. Then I wanted to create a search query from this table that I have created, however, I am lost and I don't know how will I include the values firstName and lastName when searching because it is placed in another table. This is my working query except for an undefined index for displaying the name since I don't know how.

 $query = "SELECT * FROM tb_ctsreport WHERE CONCAT(qr_id, idNum, time, date) LIKE '%".$searchBox."%'";

I have also tried this sql query but only gives me an error.

$query = "SELECT * FROM tb_ctsreport WHERE CONCAT(qr_id, idNum, time, date) LIKE '%".$searchBox."%'  
UNION
SELECT * FROM tb_usersreg WHERE CONCAT(lastName, firstName) LIKE '%".$searchBox."%'";

Please help me. I am just new to php. Thank you!

Hello, you can use the WHERE clause after your LEFT JOIN like: SELECT * FROM tb_ctsreport LEFT JOIN tb_usersreg ON tb_ctsreport.idNum=tb_usersreg.idNum WHERE CONCAT(qr_id, idNum, time, date, lastName, firstName) LIKE '%".$searchBox."%'. Also you have an SQL Injection. — homer
– homer, Commented Nov 9, 2021 at 16:31
Hi @homer It says mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given... — mariakz
– mariakz, Commented Nov 9, 2021 at 16:40
That means the query failed in some way, but doesn't tell you specifically how. Enable mysqli error reporting to get a better error message. Add mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); before you open your mysqli connection, and this will ensure that errors with your SQL queries are reported correctly to PHP. — ADyson
– ADyson, Commented Nov 9, 2021 at 16:43
@ADyson still gives me the same error. Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\IT3105\ctsReport.php on line 255 — mariakz
– mariakz, Commented Nov 9, 2021 at 16:49
If you used it correctly, it would not do that. Make sure you add it before you run mysqli_connect() — ADyson
– ADyson, Commented Nov 9, 2021 at 16:51

homer · Accepted Answer · 2021-11-10 09:54:47Z

1

You can use a WHERE clause after the JOINs clauses. So you can write your SQL query as:

SELECT *
  FROM tb_ctsreport
    LEFT JOIN tb_usersreg ON tb_ctsreport.idNum = tb_usersreg.idNum
  WHERE
    CONCAT(
      tb_ctsreport.qr_id,
      tb_ctsreport.idNum,
      tb_ctsreport.time,
      tb_ctsreport.date,
      tb_usersreg.lastName,
      tb_usersreg.firstName
    ) LIKE :searchBox

In the above query :searchBox is your query parameter.

Care when you concatenate user input with your SQL query, this introduces a huge security vulnerability called SQL Injection. You shuld prefer to use parameterized query to avoid this issue.

When you are referencing multiple tables in an SQL query I advise you to always use the fully qualified name for the columns in order to avoid any ambiguity.

answered Nov 10, 2021 at 9:54

homer

9148 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use search query with join tables?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related