[SOLVED] do while... inside of another do while?

[gamefreak13]

I have a mysql table with some data. I can retrieve it all and put it in to a page fine.

 

I have another table with more data that I retrieve aftering figuring out the data from the first table.

 

Specifically, the first table contains things like date, time, and ip address. The second table contains ip address and name. The sole purpose of the second table is to match the ip (provided from the first table) with a name (stored in the second table).

 

I am trying to do a "do / while loop" within a "do / while loop" if that makes any sense. This is so confusing I don't even know how to explain it.

 

Would a MySQL JOIN be what I need? I've never worked with JOIN's before so I don't know what they do or how they work.

 

The below code works with ONE exception - (because of the if statement), if the ip address does not exist in the table "identities", it will fail and use a name of a different ip address (wtf??).

 

$sname = $_SESSION['USERNAME'];

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

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

$getnameqry = mysql_query("SELECT * FROM identities WHERE ip='".$result['ip']."' LIMIT 1") or die(mysql_error());

if($getname = mysql_fetch_array($getnameqry)) {

	echo "<table align=\"center\" width=\"100%\" class=\"statistics\">\n";

	do {

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

	}

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

	echo "</table>\n</div>\n\n";

}

}

[mlin]

Yes, use a join. You'll make your life a lot easier and your app will perform much better.

 

$query = mysql_query("

  select l.ip, l.uname, l.date, i.name

  from logger l, identities i

  where l.ip = i.ip

  order by date desc, time desc

");

[gamefreak13]

Ok.. how would I insert that? And why the l. or i. prefix?

 

This is my original code without any reference or connection to the second table..

 

$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 align=\"center\" width=\"100%\" class=\"statistics\">\n";

do {

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

}

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

echo "</table>\n</div>\n\n";

}

[rameshfaj]

Using of joins is very efficient for your case.

Use the following format of the query:

 

Select name,ip from the table1 where ip IN(Select * from table2);

[gamefreak13]

Any chance someone can fit it in to the code I posted in my last reply? I don't understand.

[mlin]

rameshfaj: that looks like a subquery rather than a join.

 

the l and i are aliases to reference that tables your selecting from. they get defined in the from line of the query...ie: logger l, identities i

 

if you replace your 1st query, with the query I posted earlier, you should have everything you need in one resultset. You can then get rid of the 2nd query inside your loop and work with the results as normal.

 

On 2nd look, I do have a couple columns missing from the query I posted for your script. be sure to add referer and time to the select line of the query. Use the aliases. l == logger table, and i == identities table

[gamefreak13]

This works.. but if the "identities" table does not contain a name/ip match, it doesn't show the row from "logger" at all. Only the ones with valid names in the "identities" table show up.

 

IDENTITIES includes: uname, name, ip

 

LOGGER includes: uname, ip, date, time, referer

 

//$query = mysql_query("SELECT * FROM logger WHERE uname='$sname' ORDER BY date DESC, time DESC");
$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 ORDER BY date DESC, time DESC");

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

echo "<table align=\"center\" width=\"100%\" class=\"statistics\">\n";

do {

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

}

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

echo "</table>\n</div>\n\n";

}

[gamefreak13]

To the top.

[gamefreak13]

This code is worse than the one I had before because if it doesn't match the query, it won't return anything.

 

Each user has their own list of ip/name's. If I tell it to only return the rows with the username "test" and the database does not contain any rows with the username test, then it will not return anything.

 

This is so confusing. Uggh.

 

So I need to pull all the data from the LOGGER table. Then, if it exists, match the ip from the LOGGER table to the name/ip in the IDENTITES table. This code does that, but if there is no name/ip that matches the IP from the LOGGER table in the IDENTITIES table, it won't return the row at all.

 

$sname = $_SESSION['USERNAME'];

//$query = mysql_query("SELECT * FROM logger WHERE uname='$sname' ORDER BY date DESC, time DESC");
$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");

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

echo "<table align=\"center\" width=\"100%\" class=\"statistics\">\n";

do {

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

}

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

echo "</table>\n</div>\n\n";

}