Multiple Table Query, while inside a while, results=table length?

devil614 · February 23, 2014

I'm trying to display information based on a cross-reference of table data from 3 tables using multiple 'while's'. The problem I'm having is that the data inside the innermost 'while' is being displayed 6881 times -- the exact number of entries in the first table. The rest seems to be working fine.

Working example: http://l2-x.no-ip.org/search.html Enter the term "bone" and hit enter. Then view the details in the "ITEM" column. Repeated identical monster entries.

Tables:

etcitem
item_id (A), name, crystal_type

droplist
mobId (B), itemId (matches up with A), min, max, category, chance

npc
id (matches up with B), name, level

What I'm doing is trying to display items queried b y name or item ID, then for each one, display which monster drops it - by referencing the item's ID in 'etcitem' table to the matching ID in 'droplist' table, then take the monster [mobId] and drop chance from that droplist entry, and again reference it to the npc table to display the monster's name and level)

(In the back of my mind, the 2 'names' in the query might get confused?)

SNIPPET::

if($method == "EQUALS")
  {
  $searche = mysql_query("SELECT * FROM etcitem WHERE $type = '$query' ORDER BY $omethod $smethod");
  $searcha = mysql_query("SELECT * FROM armor WHERE $type = '$query' ORDER BY $omethod $smethod");
  $searchw = mysql_query("SELECT * FROM weapon WHERE $type = '$query' ORDER BY $omethod $smethod");
  } else {
  $searche = mysql_query("SELECT * FROM etcitem WHERE $type LIKE '$query' ORDER BY $omethod $smethod");
  $searcha = mysql_query("SELECT * FROM armor WHERE $type LIKE '$query' ORDER BY $omethod $smethod");
  $searchw = mysql_query("SELECT * FROM weapon WHERE $type LIKE '$query' ORDER BY $omethod $smethod");
 }
 
 echo "<center>";
 echo "<table border=0 width=100% cellpadding=0 cellspacing=0><tr><td align=center valign=top>";
 
 $x=1;
 
 if(mysql_num_rows($searche) > 0)
 {
 echo "<table border=1 width=100% cellpadding=0 cellspacing=0>";
 echo "<tr style=background-color:#00CC00><td align=center colspan=2><b>ITEMS</b></td></tr>";
 echo "<tr style=background-color:#00CC00><td align=center><b>Item ID</b></td><td align=center><b>Name</b></td></tr>";
 $e=0;
 
 while ($row = mysql_fetch_assoc($searche))
 {
 echo "<tr style=background-color:green;cursor:move onclick=toggle('y".$x."') title='Click to toggle info'><td align=center><b>".$row['item_id']."</b></td><td align=center><b>".$row['name']."</b></td></tr>";
 $rowup = strtoupper($row['crystal_type']);
 echo "<tr style=background-color:#005500><td align=center colspan=2><div id=y".$x." style=".$divdef.">Grade: ".$rowup;
 
 $eitem = $row['item_id'];
 
 //
 $edrop = mysql_query("SELECT etcitem.item_id, etcitem.name, etcitem.crystal_type, droplist.mobId, droplist.itemId, droplist.min, droplist.max, droplist.category, droplist.chance, npc.id, npc.name, npc.level FROM etcitem,droplist,npc WHERE $eitem = droplist.itemId AND npc.id = droplist.mobId ORDER BY npc.level ASC");
 if(mysql_num_rows($edrop) > 0)
 {
 while ($edrow = mysql_fetch_assoc($edrop))
 {
 echo "<br>Monster: ".$edrow['name']." Lvl: ".$edrow['level'];
 if($edrow['category'] == -1)
 {
 $edtype = "Spoiled";
 } else {
 $edtype = "Dropped";
 }
 $edper = ($edrow['chance'] / 10000);
 echo "<br>".$edtype." ".$edper."%";
 }
 }
 //
 
 echo "</div></td></tr>";
 $x++;
 $e++;
 }
 echo "</table>";
 } else {
 echo "<table border=1 width=100% cellpadding=0 cellspacing=0><tr style=background-color:green><td align=center colspan=2><b>No results found!</b></td></tr></table>";
 }

ENTIRE CODE::

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   <html xmlns="http://www.w3.org/1999/xhtml">
   <head>
   <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
   <link href="stylez.css" rel="stylesheet" type="text/css" />
   <!--[if IE 6]> <style> #main {height:243px;} </style> <![endif]-->
   <style>
   body {
   scrollbar-base-color: #888888;
  scrollbar-arrow-color: #888888;
  scrollbar-3dlight-color: #888888;
  scrollbar-darkshadow-color: #888888;
  scrollbar-face-color: #000000;
  scrollbar-highlight-color: #000000;
  scrollbar-shadow-color: #888888;
  scrollbar-track-color: #888888;
  }
  </style>
  
  <script type="text/javascript">
  function toggle(id)
  {
  var e = document.getElementById(id);
  e.style.display = ((e.style.display!='none') ? 'none' : 'block');
  }
  </script>
  </head>
  <body style=background-color:transparent onload=this.focus() topmargin=0 leftmargin=0 bottommargin=0 rightmargin=0>
  <!--
      <div id="menu2" style=width:582px;float:center>
  -->
  
  <?php
  include 'ip.php';
  
  $connect = @mysql_pconnect( $server, $username, $password )
  or die();
  $db_select = @mysql_select_db( $database, $connect )
  or die();
  
  //show details by default? (0 or 1)
  $show = 1;
  if($show == 1)
  {
  $divdef = "display:block";
  } else {
  $divdef = "display:none";
  }
  
  $a=0;
  $e=0;
  $w=0;
  
  $query = mysql_real_escape_string($_POST['query']);
  $type = mysql_real_escape_string($_POST['type']);
  $method = mysql_real_escape_string($_POST['method']);
  $smethod = mysql_real_escape_string($_POST['smethod']);
  $omethod = mysql_real_escape_string($_POST['omethod']);
  
  if($method == "CONT")
  {
  $queryx = "%";
  $query .= $queryx;
  $queryx .= $query;
  $query = $queryx;
  $queryx = "";
  }
  
  if($method == "BEGIN")
  {
  $queryx = "%";
  $query .= $queryx;
  $queryx = "";
  }
  
  if($method == "END")
  {
  $queryx = "%";
  $queryx .= $query;
  $query = $queryx;
  $queryx = "";
  }
  
  if($method == "EQUALS")
  {
  $searche = mysql_query("SELECT * FROM etcitem WHERE $type = '$query' ORDER BY $omethod $smethod");
  $searcha = mysql_query("SELECT * FROM armor WHERE $type = '$query' ORDER BY $omethod $smethod");
  $searchw = mysql_query("SELECT * FROM weapon WHERE $type = '$query' ORDER BY $omethod $smethod");
  } else {
  $searche = mysql_query("SELECT * FROM etcitem WHERE $type LIKE '$query' ORDER BY $omethod $smethod");
  $searcha = mysql_query("SELECT * FROM armor WHERE $type LIKE '$query' ORDER BY $omethod $smethod");
  $searchw = mysql_query("SELECT * FROM weapon WHERE $type LIKE '$query' ORDER BY $omethod $smethod");
  }
  
  echo "<center>";
  echo "<table border=0 width=100% cellpadding=0 cellspacing=0><tr><td align=center valign=top>";
  
  $x=1;
  
 if(mysql_num_rows($searche) > 0)
 {
 echo "<table border=1 width=100% cellpadding=0 cellspacing=0>";
 echo "<tr style=background-color:#00CC00><td align=center colspan=2><b>ITEMS</b></td></tr>";
 echo "<tr style=background-color:#00CC00><td align=center><b>Item ID</b></td><td align=center><b>Name</b></td></tr>";
 $e=0;
 
 while ($row = mysql_fetch_assoc($searche))
 {
 echo "<tr style=background-color:green;cursor:move onclick=toggle('y".$x."') title='Click to toggle info'><td align=center><b>".$row['item_id']."</b></td><td align=center><b>".$row['name']."</b></td></tr>";
 $rowup = strtoupper($row['crystal_type']);
 echo "<tr style=background-color:#005500><td align=center colspan=2><div id=y".$x." style=".$divdef.">Grade: ".$rowup;
 
 $eitem = $row['item_id'];
 
 //
 $edrop = mysql_query("SELECT etcitem.item_id, etcitem.name, etcitem.crystal_type, droplist.mobId, droplist.itemId, droplist.min, droplist.max, droplist.category, droplist.chance, npc.id, npc.name, npc.level FROM etcitem,droplist,npc WHERE $eitem = droplist.itemId AND npc.id = droplist.mobId ORDER BY npc.level ASC");
 if(mysql_num_rows($edrop) > 0)
 {
 while ($edrow = mysql_fetch_assoc($edrop))
 {
 echo "<br>Monster: ".$edrow['name']." Lvl: ".$edrow['level'];
 if($edrow['category'] == -1)
 {
 $edtype = "Spoiled";
 } else {
 $edtype = "Dropped";
 }
 $edper = ($edrow['chance'] / 10000);
 echo "<br>".$edtype." ".$edper."%";
 }
 }
 //
 
 echo "</div></td></tr>";
 $x++;
 $e++;
 }
 echo "</table>";
 } else {
 echo "<table border=1 width=100% cellpadding=0 cellspacing=0><tr style=background-color:green><td align=center colspan=2><b>No results found!</b></td></tr></table>";
 }
 
 
 echo "</td><td align=center valign=top>";
 
 
 if(mysql_num_rows($searcha) > 0)
 {
 echo "<table border=1 width=100% cellpadding=0 cellspacing=0>";
 echo "<tr style=background-color:cyan><td align=center colspan=2><b>ARMORS</b></td></tr>";
 echo "<tr style=background-color:cyan><td align=center><b>Item ID</b></td><td align=center><b>Name</b></td></tr>";
 $a=0;
 while ($row = mysql_fetch_assoc($searcha))
 {
 echo "<tr style=background-color:#008888;cursor:move onclick=toggle('y".$x."') title='Click to toggle info'><td align=center><b>".$row['item_id']."</b></td><td align=center><b>".$row['name']."</b></td></tr>";
 $rowup = strtoupper($row['crystal_type']);
 $rowbody = strtoupper($row['bodypart']);
 if($rowbody == "REAR,LEAR")
 {
 $rowbody = "EARRING";
 }
 if($rowbody == "RFINGER,LFINGER")
 {
 $rowbody = "RING";
 }
 if($rowbody == "NECK")
 {
 $rowbody = "NECKLACE";
 }
 if($rowbody == "FEET")
 {
 $rowbody = "BOOTS";
 }
 if($rowbody == "DHAIR")
 {
 $rowbody = "MASK/HAIR";
 }
 if($rowbody == "HAIR")
 {
 $rowbody = "MASK/HAIR";
 }
 if($rowbody == "FACE")
 {
 $rowbody = "MASK/HAIR";
 }
 $rowtype = strtoupper($row['armor_type']);
 echo "<tr style=background-color:#005555><td align=center colspan=2><div id=y".$x." style=".$divdef.">Grade: ".$rowup." Body: ".$rowbody."<br>Type: ".$rowtype." P.Def: ".$row['p_def']." M.Def: ".$row['m_def']."</div></td></tr>";
 $x++;
 $a++;
 }
 echo "</table>";
 } else {
 echo "<table border=1 width=100% cellpadding=0 cellspacing=0><tr style=background-color:cyan><td align=center colspan=2><b>No results found!</b></td></tr></table>";
 }
 
 
 echo "</td><td align=center valign=top>";
 
 
 if(mysql_num_rows($searchw) > 0)
 {
 echo "<table border=1 width=100% cellpadding=0 cellspacing=0>";
 echo "<tr style=background-color:red><td align=center colspan=2><b>WEAPONS</b></td></tr>";
 echo "<tr style=background-color:red><td align=center><b>Item ID</b></td><td align=center><b>Name</b></td></tr>";
 $w=0;
 while ($row = mysql_fetch_assoc($searchw))
 {
 
 if($row['bodypart'] == "rhand")
 {
 $wield = "1-Hand";
 }
 if($row['bodypart'] == "lrhand")
 {
 $wield = "2-Handed";
 }
 if($row['bodypart'] == "lhand")
 {
 $wield = "Shield";
 }
 
 echo "<tr style=background-color:#AF0000;cursor:move onclick=toggle('y".$x."') title='Click to toggle info'><td align=center><b>".$row['item_id']."</b></td><td align=center><b>".$row['name']."</b></td></tr>";
 $rowup = strtoupper($row['crystal_type']);
 echo "<tr style=background-color:#880000><td align=center colspan=2><div id=y".$x." style=".$divdef.">Grade: ".$rowup." Wield: ".@$wield;
 if($wield == "Shield")
 {
 echo "<br>Shield Defense: ".$row['shield_def']." Shield Defense Rate: ".$row['shield_def_rate'];
 }
 else
 {
 echo "<br>P.Dmg: ".$row['p_dam']." M.Dmg: ".$row['m_dam'];
 }
 
 
 
 echo "</div></td></tr>";
 
 $x++;
 $w++;
 }
 echo "</table>";
 } else {
 echo "<table border=1 width=100% cellpadding=0 cellspacing=0><tr style=background-color:red><td align=center colspan=2><b>No results found!</b></td></tr></table>";
 }
 
 
 echo "</td></tr></table>";
 
 
 $t = (($e) + ($a) + ($w));
 if($t == 0)
 {
 $t = "no";
 }
 echo "<script>alert('Found ".$t." result(s)!')</script>";
 
 mysql_close();
 ?>
 
 <!--
     </div>
 -->
 </body>
 </html>

EDITED by: Ch0cu3r - When posting code please use

tags

Edited February 23, 2014 by Ch0cu3r

mac_gyver · February 23, 2014

the other forum where you copy/pasted your code from messed up the formatting.

i suggest you post your original code with correct formatting and using this forum's

bbcode tags (the edit form's <> button) if you want anyone to look at your code.

ginerjm · February 23, 2014

As Mac_Gyver says - if you want people to even try and help you, help them by limiting your post to the parts that directly related to the problem.

One question - do you Really need to do 3 separate queries of your 3 tables to get the info that you want? The beauty of a properly constructed db and sql is the power to tie data together in a result as needed.

devil614 · February 23, 2014

The snippet is the important part, and it is intact. As for ginerjm, look at the original post where it shows the 3 tables. the information I want to post is contained within 3 tables, each linking to another table by 1 value (etcitem contains itemID's, so does droplist. droplist contains mobID's, so does npc table. npc table contains mob names and levels also. so yes, i need data from all 3.) as for linking and obtaining it more efficiently, thats why I'm here

ginerjm · February 23, 2014

It would behoove you to do some research on sql syntax and learn how to join those tables in a query with just one where clause.

mac_gyver · February 23, 2014

i have a recommendation that needs to come even before learning how to join your tables in a query, you need to separate your 'business logic' (the php logic that is determining what to do on the page and retrieving the data you need) from your 'presentation logic' (minimal php code and the html/javascript/css making up the output you are sending to the browser.) basically, a majority of the php code will be first on the page, followed at the end by essentially a html template that is just echoing php variables or at most looping over arrays of data producing the html output from that data.

by doing this, the logic that is retrieving the relevant data will be together in one place so that you (or us) will be able to see why your code isn't doing what you want. then, once your code is retrieving the correct data, producing the output from that data is a straight-forward task.

here's an example showing the separation of business/presentation logic - http://forums.phpfreaks.com/topic/286008-use-dropdown-box-to-filter-data-on-same-page/?hl=%2Bbusiness&do=findComment&comment=1468017

mac_gyver · February 23, 2014

i finally figured out WHAT you are trying to, i.e. loop over the ect items and display any monster's data for each item. here's how you would do this -

in the business logic getting the data -

// note: you should be building your WHERE clause in a variable so that it can be used in each place it is needed without repetition of code

// get the ect item data w/the id as the key (to allow all id's to be extracted at once)
$query = "SELECT * FROM etcitem $where ORDER BY $omethod $smethod";
$result = mysql_query($query);
$ect_item_data = array();
while($row = mysql_fetch_assoc($result)){
    $ect_item_data[$row['item_id']] = $row;
}

// get the monster data that are using the ect item id's
if(!empty($ect_item_data)){ // only do this if there are matching items/ids
    $ids = implode(',',array_keys($ext_item_data)); // get a list of ids
    $query = " ........ WHERE some_column_reference IN ($ids) ......"; // query to get all the monster data using the ect_item ids
    $result = mysql_query($query);
    $ect_item_monster_data = array();
    while($row = mysql_fetch_assoc($result)){
        $ect_item_monster_data[$row['item_id']][] = $row; // this is stored as arrays of arrays using the ectitem.item_id as the main array index
    }
}

note: i didn't actually write or fix your query (the Olympics are currently on), only showed the IN ($ids) term that gets all the matching rows at once. you will need to work on producing the correct query.

in your presentation logic producing the output from the data -

if(empty($ect_item_data)){
    // no ect item data found at all, output your 'no result' content here...
    
} else {
    // loop over the ect items
    foreach($ect_item_data as $row){
        // output your item heading here....
        
        // check if there is any monster data for this item
        $eitem = $row['item_id'];
        if(!count($ect_item_monster_data[$eitem])){
            // no monster data for this item, output your 'no result' content here...
            
        } else {
            // one or more monsters use this item, output that here...
            foreach($ect_item_monster_data[$eitem] as $arr){
                // reference the $arr[...] elements to output the data for the monster here...
                
            }
        }
    }
}

Edited February 23, 2014 by mac_gyver

devil614 · February 24, 2014

i finally figured out WHAT you are trying to, i.e. loop over the ect items and display any monster's data for each item. here's how you would do this -

in the business logic getting the data -

// note: you should be building your WHERE clause in a variable so that it can be used in each place it is needed without repetition of code

// get the ect item data w/the id as the key (to allow all id's to be extracted at once)
$query = "SELECT * FROM etcitem $where ORDER BY $omethod $smethod";
$result = mysql_query($query);
$ect_item_data = array();
while($row = mysql_fetch_assoc($result)){
    $ect_item_data[$row['item_id']] = $row;
}

// get the monster data that are using the ect item id's
if(!empty($ect_item_data)){ // only do this if there are matching items/ids
    $ids = implode(',',array_keys($ext_item_data)); // get a list of ids
    $query = " ........ WHERE some_column_reference IN ($ids) ......"; // query to get all the monster data using the ect_item ids
    $result = mysql_query($query);
    $ect_item_monster_data = array();
    while($row = mysql_fetch_assoc($result)){
        $ect_item_monster_data[$row['item_id']][] = $row; // this is stored as arrays of arrays using the ectitem.item_id as the main array index
    }
}

note: i didn't actually write or fix your query (the Olympics are currently on), only showed the IN ($ids) term that gets all the matching rows at once. you will need to work on producing the correct query.

in your presentation logic producing the output from the data -

if(empty($ect_item_data)){
    // no ect item data found at all, output your 'no result' content here...
    
} else {
    // loop over the ect items
    foreach($ect_item_data as $row){
        // output your item heading here....
        
        // check if there is any monster data for this item
        $eitem = $row['item_id'];
        if(!count($ect_item_monster_data[$eitem])){
            // no monster data for this item, output your 'no result' content here...
            
        } else {
            // one or more monsters use this item, output that here...
            foreach($ect_item_monster_data[$eitem] as $arr){
                // reference the $arr[...] elements to output the data for the monster here...
                
            }
        }
    }
}

I've learned what php I know from reading and testing out. I've never understood or even attempted an 'implode' or 'foreach'. some of your answer makes sense, and some is still a bit mindboggling to me lol

devil614 · February 25, 2014

I've learned what php I know from reading and testing out. I've never understood or even attempted an 'implode' or 'foreach'. some of your answer makes sense, and some is still a bit mindboggling to me lol

You mentioned to "reference the $arr[...]" near the end of the last bit of code... like $arr['cost'] ? or as numericals? $arr[0][4] ? Also, when I have whichever monster drops the item, I then need to take that monster's ID and find it in the spawn table (i plan on making a map to show where that monster spawns) would, and if so, how, would I include that query (with the others as 1?)

Edited February 25, 2014 by devil614

mac_gyver · February 25, 2014

$ect_item_monster_data is an array of arrays (one main array for each eitem_id) holding the rows (each row is an array as well) that the JOIN'ed query produced. id would recommend that you use echo '<pre>',print_r($ect_item_monster_data,true),'</pre>'; so that you can SEE what it contains, so that you will be able to understand what the foreach loop is doing.

devil614 · February 27, 2014

$ect_item_monster_data is an array of arrays (one main array for each eitem_id) holding the rows (each row is an array as well) that the JOIN'ed query produced. id would recommend that you use echo '<pre>',print_r($ect_item_monster_data,true),'</pre>'; so that you can SEE what it contains, so that you will be able to understand what the foreach loop is doing.

Well I can now see what it's doing, but im still lost on how to connect it to another array, and where to implement the whole thing, either inside or after the entire "while {}"

Are you interested in doing some coding for me? Your efforts will be compensated for.

devil614 · February 27, 2014

The EDIT button isnt showing up, but my offer stands to anyone willing to help me with this.

Sign In

Multiple Table Query, while inside a while, results=table length?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information