John Davidson

Message:

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!

---

My Answer:
To include the values of firstName and lastName from the tb_usersreg table in your search query, you can modify your query to include a JOIN with the tb_usersreg table. Here's an example query that includes the firstName and lastName fields in the search:

php
$query = "SELECT tb_ctsreport.qr_id, tb_ctsreport.idNum, CONCAT(tb_usersreg.firstName, ' ', tb_usersreg.lastName) AS Name, tb_ctsreport.date, tb_ctsreport.time
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.firstName, tb_usersreg.lastName) LIKE '%".$searchBox."%'";


In this query, we are selecting the qr_id, idNum, concatenated firstName and lastName as Name, date, and time from the tb_ctsreport table. We are also joining the tb_usersreg table on idNum to get the firstName and lastName fields. The CONCAT function is used to concatenate all the fields that you want to search for.

Make sure to replace $searchBox with the search term that you are looking for. This query will search for the search term in all the selected fields from both tables.

I hope this helps! Let me know if you have any further questions.