0

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!

mariakz
  • 19
  • 6
  • 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](https://en.wikipedia.org/wiki/SQL_injection). – homer Nov 09 '21 at 16:31
  • Hi @homer It says mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given... – mariakz Nov 09 '21 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 Nov 09 '21 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 Nov 09 '21 at 16:49
  • If you used it correctly, it would not do that. Make sure you add it _before_ you run `mysqli_connect()` – ADyson Nov 09 '21 at 16:51
  • @ADyson it says Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Column 'idNum' in where clause is ambiguous' – mariakz Nov 09 '21 at 16:55
  • Ok so that just means you need to specify which of the tables you want to take `idNum` from. For future reference... "column is ambiguous" is a standard SQL error which you can google and find the solution to easily. Most errors are google-able. – ADyson Nov 09 '21 at 16:56
  • Yes I know. So what? Someone suggesting a query doesn't guarantee it would work exactly. You need to fix that error. – ADyson Nov 09 '21 at 16:57
  • @ADyson what do you mean by specifying on which table? – mariakz Nov 09 '21 at 16:57
  • @ADyson yes..sorry im just stating – mariakz Nov 09 '21 at 16:58
  • Well presumably `tb_ctsreport` and `tb_usersreg` both contain columns called `idNum`. Which one do you want to use in your CONCAT statement? SQL doesn't know and can't guess, so you have to tell it. https://stackoverflow.com/questions/6638520/1052-column-id-in-field-list-is-ambiguous explains this error and how to resolve it. – ADyson Nov 09 '21 at 17:04
  • @ADyson it finally worked. TYSM FOR UR HELP. GODBLESS :) – mariakz Nov 09 '21 at 17:09

1 Answers1

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.

homer
  • 882
  • 8
  • 23