Strange Select Bug

[_newbie]

Hi, looking for a bit of input as to why the third sub record of the first record is not showing in the following table. I have set up a table to show information relating to an MMO I am quite fond of. It relates to items that can be collected from the monsters in game. However, when the table is displayed it is skipping out a line, and I can't fathom why. Here is the page code:

<?php
$con = mysql_connect("localhost","...","...");
if (!$con)  

{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db("...", $con);

$mobq = "SELECT mob_name, mob_level, mob_area, drop_name1, drop_name2, drop_name3 FROM gather_material INNER JOIN gather_mob ON (gather_material.drop_id=gather_mob.drop_id1)||(gather_material.drop_id=gather_mob.drop_id2)||(gather_material.drop_id=gather_mob.drop_id3) ORDER BY mob_id";
$mobres = mysql_query ($mobq, $con);
$mobrow = mysql_fetch_assoc($mobres);
echo "<h2>All information in this table is courtesy of Twadge/Fluppet.  These are her own findings, and this is her own work.</h2>";
echo "<table border='1' padding='4'> <tr><th>Name</th><th>Level</th><th>Race</th><th>Normal Drop</th><th>Rare Drop</th><th>Fine Drop</th></tr>";
WHILE ($mobrow = mysql_fetch_assoc($mobres))
{

echo "<tr><td>" . $mobrow['mob_name'] . " </td><td> " . $mobrow['mob_level'] . " </td><td> " . $mobrow['mob_area'] . " </td><td> " . $mobrow['drop_name1'] . " </td><td> " . $mobrow['drop_name2'] . " </td><td> " . $mobrow['drop_name3'] . "</td></tr>";

}
?>

All the other monsters display fully, an it is definately the drop_name3/drop_id3 that is not showing for the mob_id1

The tables are:

gather_mob:
mob_id - int
mob_name - varchar
mob_location - text
mob_level - int
drop_id1 - int
drop_id2 - int
drop_id3 - int

gather_material:
drop_id - int
drop_name1 - varchar
drop_name2 - varchar
drop_name3 - varchar

The reason for the complexity of this is that each monster(mob) drops up to three different items, and each of these items has three different "states".

the output can be seen at http://guildlr.com/query1.php

any assistance will be greatly appreciated

[djjjozsi]

its called poor designed or poor normalized database.

use a connector table to pair gather_mob ID's and gather_material Id's.

[_newbie]

I appreciate the pattronising critique, however that does nothing to explain what is causing it to happen or why.

while I will look into re-organising the database to create another table to hold the information that is already held in the existing ones, I was hopefull of an explination as to what exactly was going on.

[djjjozsi]

this is not the way to realize (at least) one to many relation.
i've a simple example for this:

You have the gather_mob table, 3 fields to store the 3 ID numbers, and other fields.

You have another table, gather_material, these table's primary IDs stored in the gather_mob table.

How should you build a better design ?

You should make a new table, called

connector_gather_mob_gather_material

add two fields into this table (integer types):
gather_mob_id And gather_material_id

Means gather_mob_id will be equal with gather_mob's mob_id field, and gather_material_id will be equal with the gather_material table's drop_id

How to select from these 3 tables all connected fields?

SELECT gather_mob.mob_id,gather_material.drop_id,gather_mob.mob_name,gather_mob.mob_location,gather_mob.mob_level,gather_material.drop_name1
FROM gather_material INNER JOIN
(gather_mob INNER JOIN connector_gather_mob_gather_material ON gather_mob.mob_id = connector_gather_mob_gather_material.gather_mob_id)
ON gather_material.drop_id = connector_gather_mob_gather_material.gather_material_id
GROUP BY gather_mob.mob_id

Notice that, i mentioned only one field from your gather_material table.

[_newbie]

So this connector table should only contain the ID's from the other 2 and look like

mob_id  |  drop_id
    1   |    4
    1   |    2
    1   |    9
    2   |    3
    2   |    5
    2   |    17

Sorry for the poor layout, don't know how to make it look better.

[djjjozsi]

Yes, you're right. Then use the query i mentioned in my previous post.
Feel free to ask if you need help in it.

[_newbie]

OK, still getting my head around this idea - but just about there. I get the table design and content, but what am I using as a key?

As for the SQL, I have looked through your sample above and can get the sence out of it (such as having the nested join, rather than the "black hole" style join I have), I will need to add in the other drop_name fields obviously, but I'm not sure what the GROUP BY is for, and doesn't that run the risk of only displaying a single record per mob_id refference?

[djjjozsi]

but what am I using as a key?

As you mentioned, in your connector table:
mob_id | drop_id

And for testing, do not use GROUP BY fields. Test in phpmyadmin and see what happens.