Fetching data from more than one table

[uramagget]

<?

//Error Reporting
error_reporting (E_ALL);

I'm fetching data directly from the forums and it updates when your profile is. Cool, right?
$dbhost = "mysql_real_escape(localhost)";
$dbname = "mysql_real_escape(*********)";
$dbusername = "mysql_real_escape(***********)";
$dbpassword = "mysql_real_escape(*******)";
//Tables for data fetching
$users = "mysql_real_escape(mybb_users)";
$fields = "mysql_real_escape(mybb_profilefields)";
//Connect
$mysql = mysql_connect("$dbhost", "$dbusername", "$dbpassword");
mysql_select_db($dbname, $mysql);
//Query MemberBase 1
$sql1 = "mysql_real_escape(SELECT * FROM $users WHERE loyalty='staff')";
$result1 = @mysql_query($sql1);
//Query MemberBase 2
$sql2 = "mysql_real_escape(SELECT * FROM $fields)";
$result2 = @mysql_query($sql2);

while ($rows1=@mysql_fetch_array($result1)){
?>

<h2><? echo $rows1['username']; ?></h2>
<img src="<? echo $rows1['avatar']; ?>"><br>
<b>Position:</b> <? echo $rows1['position']; ?><br>
<b>Birthdate:</b> <? echo $rows1['birthday']; ?><br>
<b>E-Mail:</b> <? echo $rows1['email']; ?><br>
<b>MSN:</b> <? echo $rows1['msn']; ?><br>
<b>AIM:</b> <? echo $rows1['aim']; ?><br>
<b>YIM:</b> <? echo $rows1['yahoo']; ?><br>
<b>ICQ:</b> <? echo $rows1['icq']; ?><br>
<?
while ($rows2=@mysql_fetch_array($result2)){
?>
<b>Sprite Submissions:</b> <? echo $rows2['5']; ?>
<b>Gender:</b> <? echo $rows2['3']; ?><br>
<b>Personal Quote:</b> <? echo $rows2['6']; ?><br>
<b>Stuffs:</b> <? echo $rows2['2']; ?><br>

<?
}}
mysql_close($mysql);
?>

 

$rows2 does not show anything at all, not even the bolded indicators (ie Sprite Submissions). I want to know how I can make a page select data from different tables, while keeping the loop working. This code simply shows the number of staff depending if their loyalty is staff. Though, this code shows their profile. everything works, except for what's in the $rows2 array. Can anybody assist me in this if possible?

[grimmier]

are both the tables relational?

ie. is there a common column between them?

 

 

[uramagget]

I'm fetching this directly from the myBB database. I haven't created any of the tables, I just used SQL to fetch data from it with PHP.

 

I don't get your question much though.

[grimmier]

Ok in that case, Yes they are relational tables

 

and i believe you want to pull from mybb_userfields and not profilefields.

 

either way. By relational i mean that in the case of using userfields (which contains the answers for each user's profile questions)

 

the field `uid` in the users table is the users id.

the field `ufid` in the usersfields table is also the users id.

 

The 2 of them are set to the same numbers so that say you wanted information on a user with a uid of 2

if you didn't look for the same      user in the other table, you would get the first result which is not correct as it would be the answers for user 1.

 

So with that being said. try this.

<?php 

//Error Reporting
error_reporting (E_ALL);

//I'm fetching data directly from the forums and it updates when your profile is. Cool, right?
$dbhost = "mysql_real_escape(localhost)";
$dbname = "mysql_real_escape(*********)";
$dbusername = "mysql_real_escape(***********)";
$dbpassword = "mysql_real_escape(*******)";
//Tables for data fetching
$users = "mysql_real_escape(mybb_users)";
// not sure if this is really the field you want, as its the questions not the answers.
$fields = "mysql_real_escape(mybb_profilefields)"; 

//Connect
$mysql = mysql_connect("$dbhost", "$dbusername", "$dbpassword");
mysql_select_db($dbname, $mysql);

//Query MemberBase 1
$sql1 = "mysql_real_escape(SELECT * FROM `mybb_users` , `mybb_userfields` 
WHERE `mybb_userfields`.`ufid` = `mybb_users`.`uid` 
and `mybb_users`.`loyalty`='staff')";
$result1 = @mysql_query($sql1);

//Ignore the second query as it isn't needed anymore. 

/* 
$sql1 = "mysql_real_escape(SELECT * FROM $users WHERE loyalty='staff')";
$result1 = @mysql_query($sql1);
//Query MemberBase 2
$sql2 = "mysql_real_escape(SELECT * FROM $fields)";
$result2 = @mysql_query($sql2);
*/

while ($rows1=@mysql_fetch_array($result1)){
?>

<h2><?php echo $rows1['username']; ?></h2>
<img src="<?php echo $rows1['avatar']; ?>"><br>
<b>Position:</b> <?php echo $rows1['position']; ?><br>
<b>Birthdate:</b> <?php  echo $rows1['birthday']; ?><br>
<b>E-Mail:</b> <?php  echo $rows1['email']; ?><br>
<b>MSN:</b> <?php echo $rows1['msn']; ?><br>
<b>AIM:</b> <?php echo $rows1['aim']; ?><br>
<b>YIM:</b> <?php echo $rows1['yahoo']; ?><br>
<b>ICQ:</b> <?php echo $rows1['icq']; ?><br>
<?php /* change these to all $rows1 as its the only query now for the results. 
and double check the field names.  
on my setup of mybb they are actually labled fid1, fid2 etc. 
which correspond to the id numbers in the mybb_profilefields table (which is the questions) */ ?>

<b>Sprite Submissions:</b> <?php echo $rows1['5']; ?>
<b>Gender:</b> <?php echo $rows1['3']; ?><br>
<b>Personal Quote:</b> <?php echo $rows1['6']; ?><br>
<b>Stuffs:</b> <?php echo $rows1['2']; ?><br>

<?php
}
mysql_close($mysql);
?>