PHP search multiple table

Question

Table: tblBuilding
buildingid.......... buildingname................ buildinglocation
1......... ................Clinic............................... Alice Road
2......... ................ Winter Mall..................... Damansara
3......... ................ Faculty........................... Beijing Road
4......... ................ Star Mall......................... Pavillion

Table: tblperson
personid.......personname.................. personno.......... personbuilding
1 ....................Dr Andrew Clarkson...... 0312883232....... Clinic
2 ....................Mr Antoine...................... 0825112315....... Winter Mall
3 ....................Mrs Emmy Dahliana....... 0112357125....... Faculty
4.................... Prof Justin Andrew......... 0524128866....... Faculty
5.................... Dr Sanila ....................... 0225113070........ Clinic

If I enter the keyword as Andrew, my expected result is
id:1
personname: Dr Andrew Clarkson
personno: 0312883232
personbuilding: Clinic
buildinglocation: Alice Road

If I enter the keyword as Star Mall, my expected result is
buildingid: 4
buildingname: Star Mall
buildinglocation: Pavillion

My code:

if (isset($_GET["FirstName"])) 
{
    $FirstName = $_GET['FirstName'];
}

$query= "(SELECT * FROM tblBuilding where buildingname like '%".$FirstName."%' OR buildinglocation '%".$FirstName."%') UNION (SELECT * FROM tblperson where personname like '%".$FirstName."%' OR personno '%".$FirstName."%' or personbuilding (SELECT * FROM tblBuilding where buildingname like '%".$FirstName."%' OR buildinglocation '%".$FirstName."%')) "

mysql_query($query);

This query return empty result. The error is shown as below.

   Warning: mysql_num_rows() expects parameter 1 to be resource,
   boolean given in C:\xampp\htdocs\dbinfo\getDetails.php on line 68 {"success":0,"message":"Keyword(s) not found"}

The query returns nothing because it generates a syntax error. You should (1) include the error in your question. (2) Explain what you are trying to do. (3) Include sample data and desired results. SQL code that doesn't work does a poor job of explaining what one is trying to do. — Gordon Linoff
– Gordon Linoff, Commented Apr 18, 2015 at 12:18
Also, the two tables have different number of columns. So, the query should be changed to select only a few columns (those which match the schema of the columns of first table) from the second table if you wish to use union. — Sanketh
– Sanketh, Commented Apr 18, 2015 at 14:58
i tried the query by using inner join. SELECT * FROM tblbuilding INNER JOIN tblperson ON tblbuilding.buildingname = tblperson.personbuilding WHERE tblbuilding.BuildingName like '%".$FirstName."%' . The produced result was not the one that i wanted. — Aroma88
– Aroma88, Commented Apr 18, 2015 at 15:12
may i know is there any solution for this? Or I have to look for other solution as the schema of the tables are different , so UNION is not applicable for it. — Aroma88
– Aroma88, Commented Apr 18, 2015 at 15:14

Manish Shukla · Accepted Answer · 2015-04-18 16:34:19Z

1

UNION operator works with same column name. You should alias column names of both tables with same name. It should work.

edited Apr 18, 2015 at 16:34

answered Apr 18, 2015 at 12:24

Manish Shukla

1,3652 gold badges8 silver badges21 bronze badges

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

Comments

Jijo John · Accepted Answer · 2015-04-18 16:30:54Z

0

I think now it's fixed . give a try :) NB: Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

if (isset($_GET["FirstName"])) 
{
$FirstName = $_GET['FirstName'];
}

$query= "(SELECT * FROM tblBuilding where buildingname like '%".$FirstName."%' OR buildinglocation LIKE '%".$FirstName."%' ) UNION (SELECT * FROM tblperson where personname LIKE '%". $FirstName ."%' OR personno '%".$FirstName."%' or personbuilding (SELECT * FROM tblBuilding where buildingname LIKE '%".$FirstName."%' OR buildinglocation '%".$FirstName."%')) ";

 mysql_query($query);

answered Apr 18, 2015 at 16:30

Jijo John

1,3759 silver badges21 bronze badges

Collectives™ on Stack Overflow

PHP search multiple table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related