CODEWITHSUNDEEP
phpmysql
John Dcruz
John Dcruz

Reputation: 61

How to query Mysql multiple tables

I have this code:

<?php
mysql_connect('localhost', 'sql', 'abc123');
mysql_select_db('common');
$phone_mobile = mysql_real_escape_string($_POST['phone_mobile']);
$result = mysql_query('SELECT phone_mobile FROM leads WHERE phone_mobile = "'.$phone_mobile.'"');
if(mysql_num_rows($result)>0){
echo 1;
} else {
echo 0;
}
?>

This works; however I have the same column in table "contacts".

Therefore I require to query table(leads) and table(contacts) for same column name(phone_mobile).

If the phone_mobile is in either "leads" or "contacts" or both then echo result.

I'm presuming a UNION or JOIN query is in order?

Thanx in advance.

John

Upvotes: 1

Views: 111

Answers (5)

echo_Me
echo_Me

Reputation: 37233

you can use UNION

$result = mysql_query('  SELECT phone_mobile FROM (
  SELECT phone_mobile FROM leads WHERE phone_mobile = "'.$phone_mobile.'"
  UNION
  SELECT phone_mobile FROM contacts WHERE phone_mobile = "'.$phone_mobile.'"
   )t
  GROUP BY phone_mobile ') ;

Upvotes: 1

ravikumar
ravikumar

Reputation: 893

try Using COALESCE,

SELECT COALESCE(l.phone_mobile,c.phone_mobile) mobilePhone  FROM leads l,
contacts c 
WHERE l.phone_mobile='".$phone_mobile."'

Upvotes: 0

Jinesh Gandhi
Jinesh Gandhi

Reputation: 77

You simply write below query 

$query = "select phone_mobile from leads,contacts where leads.phone_mobile = '".$phone_mobile."'";

$result = mysql_query($query) or die("Mysql error says : ".mysql_error());
if(mysql_num_rows($result)>0){
  echo 1;
} else {
  echo 0; 
}

Upvotes: 0

angel
angel

Reputation: 332

Add contacts to your query 

mysql_query("SELECT phone_mobile FROM leads, contacts WHERE phone_mobile = "'.$phone_mobile.'");

Generally : 

SELECET 'Column 1'[,'Column 2','Column 3', ...]
FROM 'Table1', [,'Table 2','Table 3', ...]
WHERE 'Condition 1' [AND 'Condition 2' AND 'Column 3', ...]

;

If your 'phone_mobile' column have not the same name in 2 tables or more, use this syntaxe :

SELECT Table1.phone_number_t1, Table2.phone_number_t2

whereas

SELECT phone_mobile FROM Table1, Table2

AND Condtions will be

WHERE Table1.phone_mobile = "'.$phone_mobile.'" OR Table2.phone_mobile = "'.$phone_mobile.'"

Whereas

WHERE phone_mobile = "'.$phone_mobile.'"

Upvotes: 0

Spikolynn
Spikolynn

Reputation: 4173

you could 

 SELECT l.phone_mobile, c.phone_mobile FROM leads l, contacts c WHERE l.phone_mobile = '$phone_mobile' OR c.phone_mobile = '$phone_mobile';

then check if either field contains a value.

Upvotes: 0

Related Questions