Combining Data From 2 Tables

[tomyknoker]

I have the following code

$results = mysql_query("SELECT * FROM `tblmembers` WHERE `member` = 'P'");

if (mysql_num_rows($results) < 1) {
die('No members were approved last month');
}

else {

while ($qry = mysql_fetch_array($results)) {
if (strtotime($qry['loginDateTime']) <= (time() + 86400*31)) {
$login .= 'Name: '.$qry["FirstName"].' '.$qry["LastName"].' ('.date("d/m/Y", strtotime($qry["JoinDate"])).') '.$qry["Email"].''.$qry["r_id"].' ('.$qry["State"].')
';
}
}

The 'r_id' is an ID from another table in my databse, so it outputs the ID number, but I want to pull the FirstName & LastName from that table so if the 'r_id' = 5 and 5 = Joe Bloggs I want to output that, just not sure how to do it, as it means another Query... I can explain further if this is a weak explanantion :)

[iLLin]

mysql_query("SELECT nametable.FirstName,nametable.LastName FROM tblmembers LEFT JOIN nametable ON tblmembers.r_id = nametable.id WHERE tblmembers.member = 'P'");


Research the JOIN method and this should probably be in the MySQL forums :)

[tomyknoker]

Hi iLLin... That worked! But now it only displays the rep_Firstname & rep_Lastname, it ignores everything else to do with tblmembers, so it won't show there FirstName, LastName, loginDateTime... Any ideas? Here's the changed code

//$results = mysql_query("SELECT * FROM `tblmembers` WHERE `memberApproved` = 'P'");
$results = mysql_query("SELECT tblrepresentatives.rep_Firstname,tblrepresentatives.rep_Lastname FROM tblmembers LEFT JOIN tblrepresentatives ON tblmembers.rep_NBR = tblrepresentatives.rep_NBR WHERE tblmembers.memberApproved = 'P'");


if (mysql_num_rows($results) < 1) {
die('No members were approved last month');
}

else {

while ($qry = mysql_fetch_array($results)) {
if (strtotime($qry['loginDateTime']) <= (time() + 86400*31)) {
$login .= 'Name: '.$qry["FirstName"].' '.$qry["LastName"].' ('.date("d/m/Y", strtotime($qry["JoinDate"])).') '.$qry["Email"].' '.$qry["rep_Firstname"].' '.$qry["rep_Lastname"].' ('.$qry["State"].')
';
}
}

[iLLin]

You need to select those fields too in your query.