[SOLVED] Won't display more than one record

Kemik · July 30, 2007

Hello all,

I'm trying to display a list of users attached to a team, then for each user I need to pull their country and PSN and display it in the same table.

Here's my current code:

<?php

function displayUsers($clanid){
   global $database;
   
   $q = "SELECT * "
       ."FROM ".TBL_CLAN_MEMBERS." WHERE clanid = '$clanid' AND approve = '1' ORDER BY level DESC,username";
   $result = $database->query($q);
   /* Error occurred, return given name by default */
   $num_rows = mysql_numrows($result);
   if(!$result || ($num_rows < 0)){
      echo "<p>Error displaying info</p>";
      return;
   }
   if($num_rows == 0){
      echo "<p>Database table empty</p>";
      return;
   }
   /* Display table contents */
   echo "<table>\n";
   echo "<tr><th class=\"top\" scope=\"col\" widht=\"40%\">Username</th><th class=\"top\" scope=\"col\" width=\"10%\">Rank</th><th class=\"top\" scope=\"col\" width=\"32%\">PSN</th><th class=\"top\" scope=\"col\" width=\"18%\">Eligible</th></tr>\n";

   for($i=0; $i<$num_rows; $i++){
      $uname  = mysql_result($result,$i,"username");
      $time  = mysql_result($result,$i,"timestamp");
  $level = mysql_result($result,$i,"level");
  if ($level == 1){
  	$level = 'Member';
  } else if ($level == 9){
  	$level = 'Leader';
  } else {
  	$level = 'Peasant';
  }
  	  
  $q2 = "SELECT country, psn FROM ".TBL_USERS." WHERE username = '$uname'";
   $result2 = $database->query($q2);
   /* Error occurred, return given name by default */
   $num_rows2 = mysql_num_rows($result2);
   	   if(!$result2 || ($num_rows2 < 0)){
   	  echo "</table>";
	  echo "<p>Error displaying country and psn.</p>";
	  return;
   }
  
      $country   = mysql_result($result2,$i,"country");
  $psn		 = mysql_result($result2,$i,"psn");

  $today = strtotime(date('M j, Y'));
      $relhours = ($today - $time)/1440;
  
  if ($relhours > 48){
  	$eligible = '<img src="img/icons/yes.png" width="16" height="16" class="none" alt="Ready To Play" />';
  } else {
  	$eligible = 'In '.round($relhours).' hours';
  }

      echo "<tr>
<th scope=\"row\"><img src=\"img/flags/$country.gif\" width=\"16\" height=\"11\" class=\"noborder\" alt=\"$country\" /><a href=\"userinfo.php?user=$uname\">$uname</a></th>
<td>$level</td>
<td>$psn</td>
<td>$eligible</td>
</tr>\n";
   }
   echo "</table>\n";
}
?>

Originally it wouldn't display the any records after the first query but I played with the code and managed to get past that bit, now it gets stuck on the second query.

Here's the errors (The line numbers aren't accurate as there is some code above this function in my version):

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 22 in /home/sh/public_html/ocwars/claninfo.php on line 60 ($country line)

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 22 in /home/sh/public_html/ocwars/claninfo.php on line 61 ($psn line)

thedarkwinter · July 30, 2007

Just a quick browse shows that the <tr> is before the for loop....

is that maybe your problem??

Kemik · July 30, 2007

The first <tr> is the table header.

Below the queries is the actual looped data which displays the variables.

thedarkwinter · July 30, 2007

...yeah sorry, my bad

okay i cant see anything wrong, so im going to suggest using a while loop and see if it helps...


<?php

function displayUsers($clanid){
   global $database;
   
   $q = "SELECT * "
       ."FROM ".TBL_CLAN_MEMBERS." WHERE clanid = '$clanid' AND approve = '1' ORDER BY level DESC,username";
   $result = $database->query($q);
   /* Error occurred, return given name by default */
   $num_rows = mysql_numrows($result);
   if(!$result || ($num_rows < 0)){
      echo "<p>Error displaying info</p>";
      return;
   }
   if($num_rows == 0){
      echo "<p>Database table empty</p>";
      return;
   }
   /* Display table contents */
   echo "<table>\n";
   echo "<tr><th class=\"top\" scope=\"col\" widht=\"40%\">Username</th><th class=\"top\" scope=\"col\" width=\"10%\">Rank</th><th class=\"top\" scope=\"col\" width=\"32%\">PSN</th><th class=\"top\" scope=\"col\" width=\"18%\">Eligible</th></tr>\n";

//   for($i=0; $i<$num_rows; $i++){
// my code
     while ($row = mysql_fetch_array($result)) {
       $uname = $row["username"];
       $time = $row["timestamp"];
       $level = $row["level"]; 
// end my code
//      $uname  = mysql_result($result,$i,"username");
//      $time  = mysql_result($result,$i,"timestamp");
//	  $level = mysql_result($result,$i,"level");
  if ($level == 1){
  	$level = 'Member';
  } else if ($level == 9){
  	$level = 'Leader';
  } else {
  	$level = 'Peasant';
  }
  	  
  $q2 = "SELECT country, psn FROM ".TBL_USERS." WHERE username = '$uname'";
   $result2 = $database->query($q2);
   /* Error occurred, return given name by default */
   $num_rows2 = mysql_num_rows($result2);
   	   if(!$result2 || ($num_rows2 < 0)){
   	  echo "</table>";
	  echo "<p>Error displaying country and psn.</p>";
	  return;
   }
    
//      $country   = mysql_result($result2,$i,"country");
//	  $psn		 = mysql_result($result2,$i,"psn");
//    ***** ALSO just noticed in the lines above, that you are still using $i, try just putting a zero there, or
        $row2 = mysql_fetch_array($result2);
        $country = $row2["country"];
       $psn = $row2["psn"];
// ...end or
  $today = strtotime(date('M j, Y'));
      $relhours = ($today - $time)/1440;
  
  if ($relhours > 48){
  	$eligible = '<img src="img/icons/yes.png" width="16" height="16" class="none" alt="Ready To Play" />';
  } else {
  	$eligible = 'In '.round($relhours).' hours';
  }

      echo "<tr>
<th scope=\"row\"><img src=\"img/flags/$country.gif\" width=\"16\" height=\"11\" class=\"noborder\" alt=\"$country\" /><a href=\"userinfo.php?user=$uname\">$uname</a></th>
<td>$level</td>
<td>$psn</td>
<td>$eligible</td>
</tr>\n";
   }
   echo "</table>\n";
}
?>

i only noticed the $i thing while i was typing, that might well be the problem ???

Psycho · July 30, 2007

There's a lot of inefficiency in that code.

1) You do not need to check for $num_rows <0 - that could not happen. You are also using the wrong function. It is mysql_num_rows, not mysql_numrows - This was probably causing your problem

2) You are using this ($i=0; $i<$num_rows; $i++) to itterate through the records. YOu should be using a while loop instead.

3) You are using nested queries (i.e. queries within a loop)! It is horribly inefficient and causes a lot of unnecessary load on your server. You should never run nested queries unless absolutely necessary. In almost every situation it is possible to get all of the needed data with a single query.

Here is a more efficient method using a single query. Note this has not been tested so there may be some typos, but the logic is sound:

<?php

function displayUsers($clanid){
  global $database;
  
  $q = "SELECT cm.level, cm.username, cm.timestamp, u.country, u.psn
        FROM ".TBL_CLAN_MEMBERS." cm, 
          LEFT JOIN ".TBL_USERS." u ON cm.username = u.username
        WHERE cm.clanid = '$clanid'
          AND approve = '1'
        ORDER BY cm.level DESC, cm.username";

  $result = $database->query($q);
  /* Error occurred, return given name by default */

  if(!$result){
     echo "<p>Error displaying info</p>";
     return;
  }

  if(mysql_num_rows($result) == 0){
     echo "<p>Database table empty</p>";
     return;
  }

  /* Display table contents */
  echo "<table>\n";
  echo "<tr><th class=\"top\" scope=\"col\" widht=\"40%\">Username</th><th class=\"top\" scope=\"col\" width=\"10%\">Rank</th><th class=\"top\" scope=\"col\" width=\"32%\">PSN</th><th class=\"top\" scope=\"col\" width=\"18%\">Eligible</th></tr>\n";

  while ($user = mysql_fetch_assoc($result)) {

     switch ($user['level']) {
        case '1':
           $level = 'Member'; break;
        case '2':
           $level = 'Leader'; break;
        default:
           $level = 'Peasant'; break;
     }
  	  

     $today = strtotime(date('M j, Y'));
     $relhours = ($today - $user['timestamp'])/1440;
  
     if ($relhours > 48){
        $eligible = '<img src="img/icons/yes.png" width="16" height="16" class="none" alt="Ready To Play" />';
     } else {
        $eligible = 'In '.round($relhours).' hours';
     }

     echo "<tr>
             <th scope=\"row\">
               <img src=\"img/flags/$user['country'].gif\" width=\"16\" height=\"11\" class=\"noborder\" alt=\"$user['country']\" />
               <a href=\"userinfo.php?user=$user['username']\">$user['username']</a>
             </th>
             <td>$level</td>
             <td>$user['psn']</td>
             <td>$eligible</td>
           </tr>\n";
  }

  echo "</table>\n";
}
?>

Kemik · July 30, 2007

There's a lot of inefficiency in that code...

Thanks for the help mate. I've been learning via a PHP book. It obviously isn't very good or I'm just being clumsy. I appreciate you took the time to type out some new code. I'll be able to apply some of it to other pages too.

I'll mark the topic solved once I finish editing one or two things to make it work.

Kemik · July 30, 2007

It seems the SQL query you gave won't work. It keeps saying error displaying info so I tried inputting it directly in to phpmyadmin

SELECT cm.level, cm.username, cm.timestamp, u.country, u.psn
FROM clan_members cm,

LEFT JOIN users u ON cm.username = u.username

WHERE cm.clanid = '4'

AND approve = '1'

ORDER BY cm.level DESC , cm.username

And that said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN users AS u ON cm.username = u.username WHERE cm.clanid = '4' AND app' at line 1

Psycho · July 30, 2007

I did say there might be some typos!

Try removing the comma at the end of the FROM line. I think that should do it.

Sign In

[SOLVED] Won't display more than one record

Recommended Posts

Kemik

Link to comment

Share on other sites

thedarkwinter

Link to comment

Share on other sites

Kemik

Link to comment

Share on other sites

thedarkwinter

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Kemik

Link to comment

Share on other sites

Kemik

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information