Search a keyword in multiple tables from mysql with php

[svgmx5]

Not sure if this topic goes in here, it is related to PHP but also MySql, so if i'm on the wrong board sorry!

 

What i'm trying to do is search for a keyword in 5 different tables and return the keyword ID from the table that its in

 

The tables i'm trying to search are as follow

 

location

state

county

region

continent

 

The "location" table has all the locations i.e cities and each row has the following columns:

id | continent_id | country_id | state_id | region_id | city_name

 

The "state" table is set the the following:

id | name

 

"county" table :

id | name

 

"region" table:

id | region

 

and "continent" table

id | name

 

The way it works is the can search for any city or state or county or region or continent and ideally it should look into the five different tables and return the id of that table. So if the use searches for United States it will look for United States in all five tables, obviously it would find it in the "country" table so it should return that "id". The results are returned in "json format"

 

Below is the code i have:

 

<?
$input = $_GET['keyword'];
$data = array();

/*

In this query i'm attempting to search in all databases, but i'm not sure if i'm doing this right. 
I'm not getting any results so i know something is wrong just don't know how to write it. 

*/

$query = mysql_query("SELECT * FROM locations JOIN states ON states.id = locations.state_id JOIN countries ON countries.id=locations.country_id JOIN regions ON regions.region_id = locations.region_id JOIN continents ON continents.id=locations.continent_id WHERE name LIKE '$input%' OR state LIKE '$input%' OR region LIKE '$input%' OR country LIKE '$input%' OR continent LIKE '$input%'") or die(mysql_error());

/*

Here the values are added to to the $json array. The "value" should be the "id" from the table that the keyword matched. The 'name'
Should be the name of the actual keyword. Again if they search for United States the "id" will come from the "countries" table and the "value"
would come from the "countries" table as the name

*/

while ($row = mysql_fetch_assoc($query)) {
$json = array();
$json['value'] = $row['id'];
$json['name'] = $row['name'];
$data[] = $json;
}

header("Content-type: application/json");

echo json_encode($data);
?>

 

 

Any help would he be appreciated, i don't want people to do it for me, but rather just guide me a little bit.