I have 2 tables I need to search.
1st table Name: Personnel Fields: id, lname, fname, department, extension
2nd table Name: department Fields: id, department
The 2nd table has the full department name, while the 1st table has the id associated with the department. When the user searches, it should search for the lname, fname, and extension from the 1st table, and department from the 2nd table. I have the following sql code to run the search.
$sql = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix .
"dir_personnel where lname like '$search%' or fname like '$search%' or
extension like '$search%' or department=(SELECT id FROM " . $wpdb->prefix .
"dir_departments where department like '$search%') order by lname");
However, the following code runs fine by themselves.
$sql = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix .
"dir_personnel where lname like '$search%' or fname like '$search%' or
extension like '$search%' order by lname");
and
$sql = $wpdb->get_results("SELECT id FROM " . $wpdb->prefix .
"dir_departments where department like '$search%'");
I'm sure it's something simple I'm missing, but any help is appreciated.
I have 2 tables I need to search.
1st table Name: Personnel Fields: id, lname, fname, department, extension
2nd table Name: department Fields: id, department
The 2nd table has the full department name, while the 1st table has the id associated with the department. When the user searches, it should search for the lname, fname, and extension from the 1st table, and department from the 2nd table. I have the following sql code to run the search.
$sql = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix .
"dir_personnel where lname like '$search%' or fname like '$search%' or
extension like '$search%' or department=(SELECT id FROM " . $wpdb->prefix .
"dir_departments where department like '$search%') order by lname");
However, the following code runs fine by themselves.
$sql = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix .
"dir_personnel where lname like '$search%' or fname like '$search%' or
extension like '$search%' order by lname");
and
$sql = $wpdb->get_results("SELECT id FROM " . $wpdb->prefix .
"dir_departments where department like '$search%'");
I'm sure it's something simple I'm missing, but any help is appreciated.
Share Improve this question asked Nov 20, 2018 at 19:55 Darth Mikey DDarth Mikey D 1051 silver badge9 bronze badges1 Answer
Reset to default 0You need to identify the tables for each of the columns that have the same name. You should also be using an IN statement and not = for the department.
I can't test this since I don't have your DB but you can try this:
$sql = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix .
"dir_personnel as personnel where lname like '$search%' or fname like '$search%' or
extension like '$search%' or personnel.department IN (SELECT id FROM " . $wpdb->prefix .
"dir_departments as departments where departments.department like '$search%') order by lname");