Two SQL queries for a single do/while loop?

[gamefreak13]

I'm not sure how to best describe this.

 

Table LOGGER contains UNAME, IP, DATE, TIME, REFERER

Table IDENTITIES contains UNAME, IP, NAME

 

My script pulls eveything from the LOGGER table and outputs it in an HTML table with a new row for each MySQL row.

 

So far so good.

 

Now comes the part that I know is really easy but I just can't figure it out.

 

The page outputs IP, NAME, DATE, TIME, REFERER. NAME is in the IDENTITIES table, whereas everything else is in the LOGGER table. I must first fetch the row of data from the LOGGER table with the IP, DATE, TIME, REFERER. Then, once I now the IP, I can fetch the NAME from the IDENTITIES table (by matching the IP I just retrieved).

 

Confusing huh? Here is my original code that only retrieves the info from the LOGGER table. I need to add the part where it retrieves the NAME (by matching IP) from the IDENTITIES table. How do I do this?

 

$sname = $_SESSION['USERNAME'];

$query = mysql_query("SELECT * FROM logger WHERE uname='$sname' ORDER BY date DESC, time DESC");

if($result = mysql_fetch_array($query)) {

echo "<table width=\"100%\">";

do {

	echo "  <tr>";
	echo "    <td width=\"100\">".$result['ip']."</td>";
	echo "    <td width=\"75\"><a href=\"identify.php?ip=".$result['ip']."\">NAME NEEDS TO GO HERE</a></td>";
	echo "    <td width=\"50\">".$result['date']."</td>";
	echo "    <td width=\"75\">".$result['time']."</td>";
	echo "    <td><a href=\"".$result['referer']."\" target=\"_blank\">".my_parse($result['referer'])."</a></td>";
	echo "  </tr>";

}

while($result = mysql_fetch_array($query));

echo "</table>";

}

[gamefreak13]

It was recommended in another thread to use this code. However, the problem is that if the table IDENTITIES does not contain the ip address, the entire row (from LOGGER) will not appear. You'll notice the link to identify.php. This page simply inserts a name alias to an IP. So if the person does not add a name for the IP, the entire row will fail to display.

 

$query = mysql_query("SELECT l.ip, l.uname, l.date, l.time, l.referer, i.name from logger l, identities i WHERE l.ip = i.ip AND i.uname='$sname' ORDER BY date DESC, time DESC");

.......

	echo "    <td width=\"75\"><a href=\"identify.php?ip=".$result['ip']."\">".$result['name']."</a></td>\n";

.......

 

 

[gamefreak13]

To the top..

[KenDRhyD]

If all Logger records have an Identifies record, use a JOIN

SELECT L.xxx, I.zzz FROM Logger L JOIN Identifies I ON L.ID = I.ID WHERE ... ORDER BYE ...

 

On the other hand, if some Logger records may not have an Identify, use a LEFT OUTER JOIN, which will report all Logger records, but in some cases the fields from Identify will be NULL )in which case you could use ISNULL)

SELECT L.xxx, ISNULL(I.zzz,'Non') FROM Logger L LEFT OUTER JOIN Identifies I ON L.ID = I.ID WHERE ... ORDER BYE ...