CODEWITHSUNDEEP
phpmysql
Netra
Netra

Reputation: 338

Add data from an other table to the loop of an other table

I'm trying to add a column from an other table to a while loop from an other table, and I tried something like this but doesn't seems to work. How can I achieve something like this? As far as I understand I need to save the variables from data_auth in a array but how do I echo them withing the loop ?

$SQL = mysql_query("SELECT * FROM users ORDER BY data_reg DESC LIMIT $offset, $rowsperpage");
while($rand = mysql_fetch_assoc($SQL)){
      $id = $rand['id'];            
      $user= $rand['user'];

      $SQL2 = mysql_query("SELECT data_auth FROM access_log WHERE user = '$user'");
      while($rand = mysql_fetch_assoc($SQL2)){
            $data_auth = $rand['data_auth'];
      }

      ?>
      <li><?php echo "$user"; ?></li>
      <li><?php echo "$data_auth"; ?></li>
      <?php  
      }
      ?>

Upvotes: 0

Views: 102

Answers (3)

Viktor S.
Viktor S.

Reputation: 12815

As I understand - you just want to show a list of all data_auth values in a column related to user. Simplest way is:

$SQL = mysql_query("SELECT * FROM users ORDER BY data_reg DESC LIMIT $offset, $rowsperpage");
while($rand = mysql_fetch_assoc($SQL)){
      $id = $rand['id'];            
      $user= $rand['user'];

      $SQL2 = mysql_query("SELECT data_auth FROM access_log WHERE user = '$user'");

      ?>
      <li><?php echo "$user"; ?></li>
      <li><?php          
              while($rand2 = mysql_fetch_assoc($SQL2)){
                       echo $rand2['data_auth'] . '<br/>';
            }
          ?>
      </li>
      <?php  
      }
      ?>

Besides, you use of the same variable name in second while is not safe.

Or with temporary array:

$SQL = mysql_query("SELECT * FROM users ORDER BY data_reg DESC LIMIT $offset, $rowsperpage");
while($rand = mysql_fetch_assoc($SQL)){
      $id = $rand['id'];            
      $user= $rand['user'];

      $SQL2 = mysql_query("SELECT data_auth FROM access_log WHERE user = '$user'");
      $data_auth = array();
      while($rand2 = mysql_fetch_assoc($SQL2)){
                       $data_auth[] = $rand2['data_auth'];
            }
      ?>
      <li><?php echo "$user"; ?></li>
      <li><?php  foreach($data_auth as $da){
             echo $da . "<br/>";
           }   
          ?>
      </li>
      <?php  
      }
      ?>

Upvotes: 2

Nelson Ben&#237;tez Le&#243;n
Nelson Ben&#237;tez Le&#243;n

Reputation: 50643

You can just use ONE sql query, like so:

//$SQL = mysql_query("SELECT users.*,access_log.data_auth FROM users,access_log WHERE users.user = access_log.user ORDER BY data_reg DESC LIMIT $offset, $rowsperpage");
//SQL with JOIN syntax
$SQL = mysql_query("SELECT users.*,access_log.data_auth FROM users INNER JOIN access_log ON users.user = access_log.user ORDER BY data_reg DESC LIMIT $offset, $rowsperpage");
while($rand = mysql_fetch_assoc($SQL)){
      $id = $rand['id'];            
      $user= $rand['user'];
      $auth = $rand['data_auth'];
      ?>
      <li><?php echo $user; ?></li>
      <li><?php echo $auth; ?></li>
 <?php  
 }
 ?>

Upvotes: 3

rsz
rsz

Reputation: 1161

The data_auth variable cannot be printed in the other while loop where it is not fetched by mysql and therefore you have a typo: 

 <li><?php echo "data_auth"; ?></li>

Upvotes: 0

Related Questions