Join problem

[the_mysox1]

ok here is my question, I have 2 tables one containing the address of a person and the other containing the address of church that he started. now i want to make it so that the data from the 2 tables (pastors_info and church_info) shows up with the address for the pastor fist then a <hr> and then it shows the churche(s) he has started then 2<hr>'s then the next pastor and churches and so on. I have pastors_id currently in both tables thinking that i could link the tables so that if pastors_info.pastors_id==church_info.pastors_id then it would show the pastors name and address first and then all of the churches he started but i can't figure it out. What should i do?

this script currently shows all the pastors along with all the churches weather or not the pastors_id is the same.

<?php
// First connect to database
$host = "localhost";
$dbuser = "email";
$dbpass = "qwe";
$db = "bhi_info";

$con = mysql_connect($host, $dbuser, $dbpass);
if(!$con){
die(mysql_error());
}

$select = mysql_select_db($db, $con);
if(!$select){
die(mysql_error());
}


// This will take all info from database where row tutorial is $item and collects it into $data variable
$data = mysql_query("SELECT * FROM pastors_info, church_info"); // INNER JOIN church_info ON pastors_info.pastor_id=church_info.pastor_id");
print ("Here is a list of all the Pastors addresses and the Church(s) that they have started. <br><hr>");

// This creates a loop which will repeat itself until there are no more rows to select from the database. We getting the field names and storing them in the $row variable. This makes it easier to echo each field.
while($row = mysql_fetch_array($data)){ 
echo $row['lname']. "&nbsp;"; echo $row['fname']. "&nbsp;";
if (isset($row['wname']) && $row['wname'] != NULL && $row['wname'] != '') { 
    echo "&amp;"; echo "&nbsp;"; echo $row['wname']. "<br>"; 
} 
else {
    echo "<br>";
}
if (isset($row['address']) && $row['address'] != NULL && $row['address'] != '') { 
    echo "Address: ".$row['address']. "<br>"; 
} 
if (isset($row['city']) && $row['city'] != NULL && $row['city'] != '') { 
    echo "City: ".$row['city']. "<br>"; 
} 
if (isset($row['district']) && $row['district'] != NULL && $row['district'] != '') { 
    echo "District: ".$row['district']. "&nbsp"; 
} 
if (isset($row['pc']) && $row['pc'] != NULL && $row['pc'] != '') { 
    echo "Postal Code: ".$row['pc']. "<br>"; 
} 
if (isset($row['state']) && $row['state'] != NULL && $row['state'] != '') { 
    echo "State: ".$row['state']. "<br>" ; 
} 
if (isset($row['email']) && $row['email'] != NULL && $row['email'] != '') { 
    echo "Email Address: ".$row['email']. "<br>"; 
} 
if (isset($row['landline']) && $row['landline'] != NULL && $row['landline'] != '') { 
    echo "Landline: ".$row['landline']. "<br>"; 
} 
if (isset($row['mobile']) && $row['mobile'] != NULL && $row['mobile'] != '0' && $row['mobile'] != '') { 
    echo "Mobile: ".$row['mobile']. "<br>"; 
} 
if (isset($row['notes']) && $row['notes'] != NULL && $row['notes'] != '') { 
    echo "Notes: ".$row['notes']. "<br>"; 
}
echo "<hr>"."Church Address: <br>";


echo "Church ID: ".$row['church_id']."<br>";
if (isset($row['c_address']) && $row['c_address'] != NULL && $row['c_address'] != '') { 
    echo "Address: ".$row['c_address']. "<br>"; 
} 
if (isset($row['c_city']) && $row['c_city'] != NULL && $row['c_city'] != '') { 
    echo "City: ".$row['c_city']. "<br>"; 
}
if (isset($row['c_state']) && $row['c_state'] != NULL && $row['c_state'] != '') { 
    echo "State: ".$row['c_state']. "<br>" ; 
} 
if (isset($row['c_district']) && $row['c_district'] != NULL && $row['c_district'] != '') { 
    echo "District: ".$row['c_district']. "<br>"; 
} 
if (isset($row['c_pc']) && $row['c_pc'] != NULL && $row['c_pc'] != '') { 
    echo "Postal Code: ".$row['c_pc']. "<br>"; 
} 

if (isset($row['notes']) && $row['notes'] != NULL && $row['notes'] != '') { 
    echo "Notes: ".$row['notes']. "<br>"; 
} 
echo "<hr>"."<hr>";
}
mysql_close($con);
?>

[PradeepKr]

Try this,

SELECT * FROM pastors_info a  LEFT JOIN church_info b ON  a.pastors_id = b.pastors_id

[the_mysox1]

it doesn't work

[anoopmail]

how abt this?

SELECT pasters.*, churchs.*  FROM pastors_info pasters  

LEFT JOIN church_info churchs ON   pastors.pastors_id = churchs.pastors_id 

[the_mysox1]

I am getting this error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\d_bg,l_txt\retrive_pastors_church.php on line 27

for this line:

while($row = mysql_fetch_array($data)){ 

[anoopmail]

oops..that means an error in the query..let us see..
replace

$data = mysql_query("SELECT * FROM pastors_info, church_info");

with

$data = mysql_query("SELECT * FROM pastors_info, church_info") or die(mysql_error());

[the_mysox1]

Please if it is possible when you give code please check the spelling or tell me what field is what beceause i don't always follow the code since i just started php less then 3 weeks ago.

[the_mysox1]

it still comes up with biot of the pastors for all the churches when i use this

$data = mysql_query("SELECT * FROM pastors_info, church_info") or die(mysql_error()); 

[anoopmail]

sorry to confuse u, it should be

$data = mysql_query("SELECT pasters.*, churchs.*  FROM pastors_info pasters  

LEFT JOIN church_info churchs ON   pastors.pastors_id = churchs.pastors_id ") or die(mysql_error()); 

[the_mysox1]

what are you selecting with the pastors., church.? and pastor is spelt with an "o" not a "e"

[anoopmail]

u r correct..it should be

$data = mysql_query("SELECT pastors.*, churchs.*  FROM pastors_info pastors  

LEFT JOIN church_info churchs ON   pastors.pastors_id = churchs.pastors_id ") or die(mysql_error()); 

pastors., church. means all fields from both tables.

[the_mysox1]

ok.
so i tried this but it says "Unknown table 'church'"

$data = mysql_query("SELECT pastors.*, church.*  FROM pastors_info pastors LEFT JOIN church_info churchs ON   pastors.pastors_id = churchs.pastors_id ") or die(mysql_error()); 

my tables are called:
"pastors_info" and "church_info"
inside these two the field that is the same is called "pastor_id"

[anoopmail]

$data = mysql_query("SELECT pastors.*, church.*  FROM pastors_info pastors  

LEFT JOIN church_info church ON   pastors.pastors_id = church.pastors_id ") or die(mysql_error());

or even simpler

$data = mysql_query("SELECT pastors_info.*, church_info.*  FROM pastors_info  

LEFT JOIN church_info ON   pastors_info.pastors_id = church_info.pastors_id ") or die(mysql_error());

[the_mysox1]

Here is the latest problem
Unknown column 'pastors_info.pastors_id' in 'on clause'

[anoopmail]

it seems im a typo maniac 😉

pastors_info.pastors_id should be pastors_info.pastor_id

[the_mysox1]

the same message came up but for the church_info.pastors_id so i changed that to church_info.pastor_id and now it work but now how do i make it so that if the pastor has no church he dosen't come up and if the church has no pastor he won't come up?

[anoopmail]

LEFT JOIN should work in the way u need..no idea why it didnt. i'm a bit busy now with work, will look into this tommorw.

[the_mysox1]

ok thx for your help

[the_mysox1]

here is the code for the rest of the page there might be somthing in it that i did wrong

<?php
// First connect to database
$host = "localhost";
$dbuser = "email";
$dbpass = "qwe";
$db = "bhi_info";

$con = mysql_connect($host, $dbuser, $dbpass);
if(!$con){
die(mysql_error());
}

$select = mysql_select_db($db, $con);
if(!$select){
die(mysql_error());
}


// This will take all info from database where row tutorial is $item and collects it into $data variable
$data = mysql_query("SELECT pastors_info.*, church_info.*  FROM pastors_info LEFT JOIN church_info ON pastors_info.pastor_id = church_info.pastor_id ") or die(mysql_error()); 

// This creates a loop which will repeat itself until there are no more rows to select from the database. We getting the field names and storing them in the $row variable. This makes it easier to echo each field.
while($row = mysql_fetch_array($data)){ 
echo $row['lname']. "&nbsp;"; echo $row['fname']. "&nbsp;";
if (isset($row['wname']) && $row['wname'] != NULL && $row['wname'] != '') { 
    echo "&amp;"; echo "&nbsp;"; echo $row['wname']. "<br>"; 
} 
else {
    echo "<br>";
}
if (isset($row['address']) && $row['address'] != NULL && $row['address'] != '') { 
    echo "Address: ".$row['address']. "<br>"; 
} 
if (isset($row['city']) && $row['city'] != NULL && $row['city'] != '') { 
    echo "City: ".$row['city']. "<br>"; 
} 
if (isset($row['district']) && $row['district'] != NULL && $row['district'] != '') { 
    echo "District: ".$row['district']. "&nbsp"; 
} 
if (isset($row['pc']) && $row['pc'] != NULL && $row['pc'] != '') { 
    echo "Postal Code: ".$row['pc']. "<br>"; 
} 
if (isset($row['state']) && $row['state'] != NULL && $row['state'] != '') { 
    echo "State: ".$row['state']. "<br>" ; 
} 
if (isset($row['email']) && $row['email'] != NULL && $row['email'] != '') { 
    echo "Email Address: ".$row['email']. "<br>"; 
} 
if (isset($row['landline']) && $row['landline'] != NULL && $row['landline'] != '') { 
    echo "Landline: ".$row['landline']. "<br>"; 
} 
if (isset($row['mobile']) && $row['mobile'] != NULL && $row['mobile'] != '0' && $row['mobile'] != '') { 
    echo "Mobile: ".$row['mobile']. "<br>"; 
} 
if (isset($row['notes']) && $row['notes'] != NULL && $row['notes'] != '') { 
    echo "Notes: ".$row['notes']. "<br>"; 
}


if (isset($row['church_id']) && $row['church_id'] != NULL && $row['church_id'] != '') {
	echo "<hr>"."Church Address: <br>";
	echo "Church ID: ".$row['church_id']."<br>";
}
if (isset($row['c_address']) && $row['c_address'] != NULL && $row['c_address'] != '') { 
    echo "Address: ".$row['c_address']. "<br>"; 
} 
if (isset($row['c_city']) && $row['c_city'] != NULL && $row['c_city'] != '') { 
    echo "City: ".$row['c_city']. "<br>"; 
}
if (isset($row['c_state']) && $row['c_state'] != NULL && $row['c_state'] != '') { 
    echo "State: ".$row['c_state']. "<br>" ; 
} 
if (isset($row['c_district']) && $row['c_district'] != NULL && $row['c_district'] != '') { 
    echo "District: ".$row['c_district']. "<br>"; 
} 
if (isset($row['c_pc']) && $row['c_pc'] != NULL && $row['c_pc'] != '') { 
    echo "Postal Code: ".$row['c_pc']. "<br>"; 
} 

if (isset($row['notes']) && $row['notes'] != NULL && $row['notes'] != '') { 
    echo "Notes: ".$row['notes']. "<br>"; 
} 
echo "<hr>"."<hr>";
}
mysql_close($con);
?>