I'm working on a website, which accesses a database with two tables. One table contains a list of "quests" and the other table contains a list of "items" associated with the quests. The "quests" table has an ID field, which is the primary key, and the "items" table also has an ID field, which is the primary key. The "items" table also has a QID field, which corresponds with the primary ID of the quest it is associated with.

Currently in my code, I have a while loop that gets each quest from the "quests" db table, and puts the information into a html table.. Inside that loop, I have another while loop that goes through the "items" db table and populates the items into the html table. After it's done cycling through all the items, it starts a new row with the next quest.

This works, but it takes up to 15 seconds to populate the table, and it performs 215 queries for the 128 quests and 21 items that I have in the database. Is there a better way to do this?

    You should be able to do one query using a join to get all the info at once, e.g. (concatenation syntax may be different if not using MySQL):

    SELECT 
   q.quest_name 
   GROUP_CONCAT(i.item_name ORDER BY i.item_name SEPARATOR ', ') AS items
FROM quest_table AS q LEFT JOIN item_table AS i ON i.QID = q.ID
ORDER BY q.quest_name

      then I'd just do a while loop looking for the existence of i.id for the item list?

        With the query suggested above, each result row would give you two fields. Field 1 would be the quest name, field 2 would be a comma-separated list of items associated with that quest. So the PHP to pull it all together into a table might be something like:

        <table>
<?php
$sql = '
SELECT 
   q.quest_name 
   GROUP_CONCAT(i.item_name ORDER BY i.item_name SEPARATOR ', ') AS items
FROM quest_table AS q LEFT JOIN item_table AS i ON i.QID = q.ID
ORDER BY q.quest_name
';
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
   echo "<tr><td>" . $row['quest_name'] . "</td><td>" . $row['items'] . "</td></tr>\n";
}
?>
</table>
          NogDog wrote:

          With the query suggested above, each result row would give you two fields. Field 1 would be the quest name, field 2 would be a comma-separated list of items associated with that quest. So the PHP to pull it all together into a table might be something like...

          what about if there were several fields in the item table, that I want to use to make another table, inside of a row? If you would like to see the structure of my page, you can go to http://www.dereth.info/?ind=questxp

            Write a Reply...