php - mysql : query from multiple tables.

[amitjain2711]

Hi everyone!
I am a newbie stuck at this query.
___________________________________
Table1 "subscribers" with the following fields:

1. subscriber_id
2. email

PRIMARY KEY (`subscriber_id`),
___________________________________

Table 2 "subscriber_data" with the following fields:

1. data_id
2. field_id
3. subscriber_id
4. value

PRIMARY KEY (`data_id`),
KEY `subscriber_id` (`subscriber_id`,`field_id`)
___________________________________

now field_id has the following values

1 implies : Company Name
2 implies : products manufactured
8 implies : Address line1
9 implies : telephone
10 implies : websites URL
11 implies : address line2
12 implies : city
13 implies : state
14 implies : country

___________________________________

Now what i want out of both these tables is when you search for a " string" , if it is present in any of the fields ( like company name or state or products manufactured ), it should show that corresponding subscriber.

For example if i search string is "transformer"

Then the Search results should look like :

Company name : Abc transformers limited
Products manufactured : xyz, adc, dfg
Address line1 : asds, sdsd, sdfsd
Address line2 : lsmf, dsf,fsdf
city: Gurgaon
state: haryana
country : India

Company name : xyz limited
Products manufactured : transformer, adc, dfg
Address line1 : asds, sdsd, sdfsd
Address line2 : lsmf, dsf,fsdf
city: Gurgaon
state: PA
country : usa

Company name : ABB limited
Products manufactured : , adc, dfg
Address line1 : asds, sdsd, sdfsd
Address line2 : lsmf, dsf,fsdf
city: transformer
state: CA
country : USA

_________________________________________

[coopster]

Welcome to WebmasterWorld, amitjain2711.

One way to accomplish this is to join the tables together and include the search criteria in your WHERE clause. JOIN the tables on their primary-to-foreign key relationships.

[amitjain2711]

Thanks Coopster!

I already solved my query but i split it into two ::

first : I get all the subscriber ids which contain the search string using the following query:

$sql = "SELECT DISTINCT subscriber_id FROM subscriber_data WHERE value LIKE '%$searchString%' ORDER BY subscriber_id";

Then second --> or each subscriber id : i do the following

while($row=mysql_fetch_array($result)) //$result has all the needed subscriber ids to display
{

$subscriber_id = $row["subscriber_id"];

$sql2 = "SELECT subscriber_data.field_id, subscriber_data.subscriber_id, subscriber_data.value, subscribers.email FROM subscriber_data, subscribers WHERE subscriber_data.subscriber_id = '$subscriber_id' AND subscribers.subscriber_id = '$subscriber_id' " ;

$result2 = mysql_query("$sql2") or die(mysql_error()); // result2 now has all field_ids , values and email for this particular subscriber_id

// now to display

while($row2=mysql_fetch_array($result2))
{

$field_id = $row2['field_id'];
$value = $row2['value'];
$email = $row2['email'];

if($field_id == 1)
echo "Company Name :". $value ." <br />";

if($field_id == 2)
echo " Products/Services :". $value ." <br />";

if($field_id == 8)
echo "Address Line 1 :". $value ." <br />";
if($field_id == 11)
echo "Address Line 2 :". $value ." <br />";
if($field_id == 12)
echo "City :". $value ." <br />";
if($field_id == 13)
echo "State :". $value ." <br />";
if($field_id == 14)
echo "Country :". $value ." <br />";
if($field_id == 9)
echo "Tel :". $value ." <br />";
if($field_id == 10)
echo " website URL :". $value ." <br />";
}
echo " Email :". $email ." <br />";
echo " <br/><br/><br/>";
}