gamefreak13 Posted May 22, 2008 Share Posted May 22, 2008 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"; } } Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/ Share on other sites More sharing options...
mlin Posted May 22, 2008 Share Posted May 22, 2008 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 "); Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/#findComment-547275 Share on other sites More sharing options...
gamefreak13 Posted May 22, 2008 Author Share Posted May 22, 2008 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"; } Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/#findComment-547276 Share on other sites More sharing options...
rameshfaj Posted May 22, 2008 Share Posted May 22, 2008 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); Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/#findComment-547281 Share on other sites More sharing options...
gamefreak13 Posted May 22, 2008 Author Share Posted May 22, 2008 Any chance someone can fit it in to the code I posted in my last reply? I don't understand. Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/#findComment-547287 Share on other sites More sharing options...
mlin Posted May 22, 2008 Share Posted May 22, 2008 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 Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/#findComment-547291 Share on other sites More sharing options...
gamefreak13 Posted May 22, 2008 Author Share Posted May 22, 2008 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"; } Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/#findComment-547304 Share on other sites More sharing options...
gamefreak13 Posted May 22, 2008 Author Share Posted May 22, 2008 To the top. Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/#findComment-547680 Share on other sites More sharing options...
gamefreak13 Posted May 22, 2008 Author Share Posted May 22, 2008 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"; } Link to comment https://forums.phpfreaks.com/topic/106761-solved-do-while-inside-of-another-do-while/#findComment-547702 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.