SQL Table Nesting? Need help deciphering this code

[wolfcry044]

Ok, please bear with me as I have only begun to learn php and sql about 3 days ago. Since then I've done a few things which can be seen at http://www.uafclan.com. I've been doing tutorial after tutorial, reading page after page after page of code, and studying phpbb forums code to familiarize myself with it. I have most of the concepts down, though I'm still shaky with alot of it.

Here is my problem:
I used to have a coder for my gaming clan a few years ago, who created a php/sql website for us. After he left however, not knowing anything about php/sql, I deleted the database unknowingly. The database the site was using was the same as the forums were using. I had deleted the forums database, and there went the site one too.

Anyway, as I've been learning PHP and SQL lately, I've been stuck on trying to figure out how the other person did some of the things he did. I can't refer to the database because there is no database anymore, so I have to guess at what he was doing.

I tried doing the same thing, but somehow he seems to have database tables nested deeper than just one level or something, because nomatter what I try, I cannot duplicate what he did before.

I want to display a page with a list of users, along with their ranks, and name in the order from the highest rank to the lowest.

I also want to display a page that states what pacts we have with other gaming clans, their clan tag, clan name, clan url. I tried doing it the way he had it, but he had all the stuff in one table. I can't figure out how he did it. I finally got it working by making seperate tables for each pact, but I'd like to get it to work in one table like he did if I can.

Here is three bits of code he used. Could anyone tell me how he made the database tables for this? Please make a note, that I'm only posting one chunk. He posted these same chunks over and over, with only the type of pact or rank heierarchy changed in the code.



                                                  [u]  Roster Example[/u]
[code]$query_rs_juniors = "SELECT user_id, username, user_rank, user_image, rank_id, rank_title, rank_image FROM phpbb_9999users, phpbb_9999ranks WHERE user_rank = rank_id AND rank_value > 12 AND rank_value < 21 AND user_onleave = 0 ORDER BY rank_value ASC, username ASC";
$rs_juniors = mysql_query($query_rs_juniors, $conn_local) or die(mysql_error());
$row_rs_juniors = mysql_fetch_assoc($rs_juniors);
$totalRows_rs_juniors = mysql_num_rows($rs_juniors);
?>
[/code]



                          [u]Ranks Page Example: [/u]   
[code]mysql_select_db($database_conn_local, $conn_local);
$query_rs_juniors = "SELECT rank_id, rank_title, rank_image FROM phpbb_9999ranks WHERE rank_value > 12 AND rank_value < 21 ORDER BY rank_value ASC";
$rs_juniors = mysql_query($query_rs_juniors, $conn_local) or die(mysql_error());
$row_rs_juniors = mysql_fetch_assoc($rs_juniors);
$totalRows_rs_juniors = mysql_num_rows($rs_juniors);
?>[/code]



                      [u]Relations Page Example:[/u]
[code]mysql_select_db($database_conn_local, $conn_local);
$query_rs_mpp = "SELECT relation_tag, relation_name, relation_website FROM website_relations WHERE relation_type = 2 ORDER BY relation_name ASC";
$rs_mpp = mysql_query($query_rs_mpp, $conn_local) or die(mysql_error());
$row_rs_mpp = mysql_fetch_assoc($rs_mpp);
$totalRows_rs_mpp = mysql_num_rows($rs_mpp);
?>[/code]



I've made up a sketch of what I believe the database that my old designer created might have looked like, according to the php code in the files.

This is only a guess mind you. I am not sure what order it went in, but this seems to make the most sense. Either way he either had to have somehow nested the tables, or he was pulling from two different tables. Can someone take a look at this and help me figure out how I can duplicate what he did in my own database? Thanks.

----------------------
website_relations <---TABLE
----------------------
relation_type
----------------------
mpp
----------------------
relation_tag relation_name relation_website
---------------------------------------------

[AV1611]

If you need some long term help you can email me... I'll give you my yahoo info

bill at baptistrevival dot com

[wolfcry044]

I have MSN messenger. I added that email address and it took it. It says you are offline however.
Are you a Christian? I am too!  :D
Anyway, my handle is wolfcry044 at msn dot com.

[wolfcry044]

Ok I understand all of that code now except one thing.

there is one thing in his code that I don't understand. It's the reason that lead me to wonder if the tables were nested.

Here is the piece of code taken from the blocks of code I showed above:

$query_rs_mpp --- For the RELATIONS page
$rs_juniors --- For the RANKS page
$rs_juniors --- For the ROSTER page


Where is that comming from? Are they tables?

To give you an idea of what they are I will explain them a bit.

mpp is one type of pact out of like 4 or 5 pact types. Each one is listed in it's own sql chunk.

juniors is a type of rank. There are 4 rank types. There is High Command, Officers, juniors, and cadets. The one I chose to display to you was mpp for relations page, and juniors for ranks and roster page.

Is mpp, and juniors tables of their own, or just php variables that have nothing to do with the database?

[corbin]

[code]
$query_rs_juniors = "SELECT rank_id, rank_title, rank_image FROM phpbb_9999ranks WHERE rank_value > 12 AND rank_value < 21 ORDER BY rank_value ASC";
$rs_juniors = mysql_query($query_rs_juniors, $conn_local) or die(mysql_error());
[/code]

Whats happening is that $rs_juniors is set as the return from the mysql query of $query_rs_juniors...  It is not related to the DB except for that it stores the result from a SQL query.

[wolfcry044]

For the Relations page, I put this PHP there, and it keeps saying "UAF has no mutual protection pacts with anyone"

It's set to say that if there are no MPP pacted clans in the database. Did I do something wrong? It seems to be all set right. I'll post the code I have for both the SQL, and PHP echo statement to grab the info. My database layout is at the bottom.


                                                  [u]      SQL Code  [/u]
[code]<?php
$query_rs_MPP = "SELECT relation_tag, relation_name, relation_website FROM website_relations WHERE relation_type = 2 ORDER BY relation_name ASC";
$rs_MPP = mysql_query($query_rs_MPP, $config) or die(mysql_error());
$row_rs_MPP = mysql_fetch_assoc($rs_MPP);
$totalRows_rs_MPP = mysql_num_rows($rs_MPP);
?>[/code]



                           [u]PHP Code to print out the SQL information[/u]
[code]<?php if ($totalRows_rs_MPP > 0) { // Show if recordset not empty ?>

<?php do { ?>

<?php echo $row_rs_MPP['relation_tag']; ?>


<?php $length = (strlen($row_rs_MPP['relation_website'])); ?>
<?php if (strlen($length > 0))  { ?>
<a href="<?php echo $row_rs_MPP['relation_website']; ?>" target="_blank"><?php echo $row_rs_MPP['relation_name']; ?></a>
<?php } else { ?>
<?php echo $row_rs_MPP['relation_name']; ?>
<?php } ?>
</td>
</tr>
<?php } while ($row_rs_MPP = mysql_fetch_assoc($rs_MPP)); ?>



<?php } else {// Show if recordset not empty ?>


<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center" height="15">UAF has no Mutual Protection Pacts with anyone.</td>
</tr>
</table>

<?php } ?>[/code]




This is what my SQL table looks like:


relation_type    relation_tag     relation_name      relation_website
pact type          clan tag           clan name           clan website

[wolfcry044]

I've copied all the information from the database and pasted it in here so it's more clear. The stuff in the database is just mock information right now. The relation_type is where I'm getting thrown off. If you add more clans to the database you have to keep listing the relation_type over and over. But in order to make them all listed by relation types you have to make a different table for each relation_type, which my old web designer did not do to my knowledge. How did he make it work? I think if I know how he did it here, then it will allow me to know how he did the other ones as well. Heres my current database information that is not working out very well.



relation_type       relation_tag             relation_name              relation_website

   Alliance                 DHD                  Another Test                 [url=http://www.anothertest.org]www.anothertest.org[/url]
      MPP                   TEST                     testname                   [url=http://www.testname.com]www.testname.com[/url]
      NAP                     UAF             United Armed Forces            [url=http://www.uafclan.com]www.uafclan.com[/url]
      Neutral              DUNNO                Neutral Clan                 [url=http://www.dunnoclan.com]www.dunnoclan.com[/url]
      Sister Fleet       FRIEND                 Friendly Clan              [url=http://www.ourbestfriend.com]www.ourbestfriend.com[/url]
      War                     BAD                      BadClan                         [url=http://www.badclan.org]www.badclan.org[/url]

[wolfcry044]

Ok, after alot of studying, I've found something. For the relation_type he was not using pact abbreviations as I had thought he was. It seems he was using mediumInt type. When I tried setting a clan to number 2, it displayed it on the website. When I set another clan to number 2 it also displayed it on the website.

Now the only problem I seem to have, is figuring out why he even put MPP in the code at all. Why not just set the number value to the value that would display the proper websites?

Does anyone know what all the MPP are doing in the code? I could just leave it be, and move on since it works and all, but I can't do that. I won't be able to sleep until I figure out how it works. I don't see any reason for MPP to even be in the code since it's not in the database at all.