Page 1 of 1

PHP My_SQL Linking Tables W/ Loop Question

Posted: Fri Jan 14, 2005 7:41 am
by gjb79
Hi,
I've been working with php for a while and feel as if I have a fairly good handle on how things work, though maybe not advanced/expert level. Anyways I am having trouble understanding how to get information from four tables to loop with each other. Any help would be appreciated.

Table one (character) would contain information such as
ID, FirstName, LastName, BirthYear, DeathYear.

Table two (details) contains info such as
ID, characterID, description, eventyear1, eventyear2

Table three (chargroups)
ID, characterID, detailsID

Table four (groupnames)
ID, groupname

While there are several characters, linked to each one might be several items under detail where details.characterID would be the same as character.ID also chargroups would link each character to one or more groups listed under groupnames table.

I want them all to appear in a table but I believe would then need to implement several loops together to get it to work.

Loop 1 the character loop
Loop 2 the details for the above character
Loop 3 the groups for the above character

I assume loops 2 and 3 would look the same and not have to be set within each other.

Any ideas if I have this right? and how to go about doing it?

Thanks a million! :)

Posted: Fri Jan 14, 2005 9:00 am
by feyd
something like this should work

Code: Select all

SELECT groupname, a.*, b.* FROM characterTable a
INNER JOIN detailsTable b ON b.characterID = a.ID
INNER JOIN chargroups c ON c.characterID = b.characterID
INNER JOIN groupnames d ON c.ID = d.ID

Thanks, heres my code

Posted: Fri Jan 14, 2005 10:46 am
by gjb79
Hey thanks for the suggestion. I played around with it and ended up with this:

Truthfully its a bit more complex than what I wrote above, but I only need help with what I mentioned.

Code: Select all

<?php require_once('Connections/lexicon.php'); ?>
<?php
$maxRows_characterinfo = 10;
$pageNum_characterinfo = 0;
if (isset($HTTP_GET_VARS&#1111;'pageNum_characterinfo'])) &#123;
  $pageNum_characterinfo = $HTTP_GET_VARS&#1111;'pageNum_characterinfo'];
&#125;
$startRow_characterinfo = $pageNum_characterinfo * $maxRows_characterinfo;

mysql_select_db($database_lexicon, $lexicon);
$query_characterinfo = "SELECT * FROM `character`, charGroups, groups, description, house WHERE description.CharID=character.ID AND charGroups.CharID=character.ID AND charGroups.GroupID=groups.ID AND house.ID=character.HouseID";
$query_limit_characterinfo = sprintf("%s LIMIT %d, %d", $query_characterinfo, $startRow_characterinfo, $maxRows_characterinfo);
$characterinfo = mysql_query($query_limit_characterinfo, $lexicon) or die(mysql_error());
$row_characterinfo = mysql_fetch_assoc($characterinfo);

if (isset($HTTP_GET_VARS&#1111;'totalRows_characterinfo'])) &#123;
  $totalRows_characterinfo = $HTTP_GET_VARS&#1111;'totalRows_characterinfo'];
&#125; else &#123;
  $all_characterinfo = mysql_query($query_characterinfo);
  $totalRows_characterinfo = mysql_num_rows($all_characterinfo);
&#125;
$totalPages_characterinfo = ceil($totalRows_characterinfo/$maxRows_characterinfo)-1;
?>


Here is just what I put in the body for testing:

Code: Select all

<?php do &#123; ?>
  <p> <?php echo $row_characterinfo&#1111;'CNameF']; ?> <?php echo $row_characterinfo&#1111;'CNameL']; ?>(<?php echo $row_characterinfo&#1111;'Birth']; ?>-<?php echo $row_characterinfo&#1111;'Death']; ?>) Perfect (<?php echo $row_characterinfo&#1111;'PyearStart']; ?>-<?php echo $row_characterinfo&#1111;'PyearEnd']; ?>) <?php echo $row_characterinfo&#1111;'house']; ?> (<?php echo $row_characterinfo&#1111;'HyearStart']; ?>-<?php echo $row_characterinfo&#1111;'HyearEnd']; ?>) <?php echo $row_characterinfo&#1111;'groupName']; ?> </p>
  <p><?php echo $row_characterinfo&#1111;'description']; ?></p>
  <p>.............. </p>
  <?php &#125; while ($row_characterinfo = mysql_fetch_assoc($characterinfo)); ?>
The areas I need Looped are in the second code bock.
I need $row_characterinfo['groupName']; to loop, displaying a list of groups associated with the character
Also I need $row_characterinfo['description']; to loop and display a list of descriptions associated with the character.

Currently the script looks like this:

Code: Select all

CNameF CNameL (Birth-Death) Perfect (PyearStart-PyearEnd) 
House (HyearStart-HyearEnd) GroupName
Details

CNameF CNameL (Birth-Death) Perfect (PyearStart-PyearEnd) 
House (HyearStart-HyearEnd) GroupName
Details

CNameF CNameL (Life-Death) Perfect (PyearStart-PyearEnd) 
House (HyearStart-HyearEnd) GroupName
Details


Repeating everything with only details changing. It repeats for as many entries as there are details.

and I'm looking for:

Code: Select all

CNameF CNameL (Life-Death) Perfect (PyearStart-PyearEnd) 
House (HyearStart-HyearEnd) #Loop This entry to show multiple groups per above name#GroupName
#Loop This entry to show all details per above name#Details
#Loop the above moving on to the next name#

Posted: Fri Jan 14, 2005 2:32 pm
by JAM
I moved this into the Database part of the forum, but after that realized that it might be solved within the displaying loop intead. I moved it back, but if something foobared, sorry...

---

What about something as:

Code: Select all

$previous = '';
while ($row_characterinfo = mysql_fetch_assoc($characterinfo)) &#123;
    if ($previous != $row_characterinfo&#1111;'CharID']) &#123; 
        echo $row_characterinfo&#1111;'CNameF'] .' '. $row_characterinfo&#1111;'CNameL'];
        echo '('. $row_characterinfo&#1111;'Birth'] .'-'. $row_characterinfo&#1111;'Death'] .')';
        echo ' Perfect ';
        echo '('. $row_characterinfo&#1111;'PyearStart'] .'-'. $row_characterinfo&#1111;'PyearEnd'] .')';
        $previous = $row_characterinfo&#1111;'CharID'];
    &#125;
    echo $row_characterinfo&#1111;'house']; 
    echo '('. $row_characterinfo&#1111;'HyearStart'] .'-'. $row_characterinfo&#1111;'HyearEnd'] .')';
    echo $row_characterinfo&#1111;'groupName'];
    echo $row_characterinfo&#1111;'description'];
&#125;
Use the ID (ie. CharID) field for each user, as to verify if the first part of the data you fetched allready have been shown.
Untested and just ideas, but might work.

And ignore the multible echo's and write it how you like. I just personally cant read code that combines php and html to well, so I rearranged it abit. =)

Still not quite looping

Posted: Mon Jan 17, 2005 7:29 am
by gjb79
Thanks for the help, but its still not quite what I need.

Looping is the problem, but I need two loops to happen together pulling information from different sql tables.

Table one character 1
table two detailed information 1 about character 1.
table two detailed information 2 about character 1.
table two detailed information 3 about character 1.

Table one character 2
table two detailed information 1 about character 2.

Table one character 3
table two detailed information 1 about character 3.
table two detailed information 2 about character 3.
table two detailed information 3 about character 3.
table two detailed information 4 about character 3.
table two detailed information 5 about character 3.

As you can see table 1 in the sql has individual character names while table two has details about the character. Table 2 doesn't always have the same amount of information about each character, some characters have one line of info, others have 2, 3, 4, or 5 lines of info even more.

So I need to get the second loop to loop within the first loop.

I hope that helps I can't quite explain it any better.

Thanks again! :)