PHP My_SQL Linking Tables W/ Loop Question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

PHP My_SQL Linking Tables W/ Loop Question

Post 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! :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Thanks, heres my code

Post 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#
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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. =)
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Still not quite looping

Post 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! :)
Post Reply