Query not returning all rows

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
mrgrinch12
Forum Newbie
Posts: 16
Joined: Mon Jun 07, 2010 10:20 am

Query not returning all rows

Post by mrgrinch12 »

I have a query set to return results based on the login information of the user. It should show all the players on their team, but it leaves off the first one when it shows on the web page. If I run the query on it's own all results are there. Any suggestions?

Code: Select all

<?php
if (!isset($_SESSION)) {
  session_start();
}
$MM_authorizedUsers = "Admin";
$MM_donotCheckaccess = "false";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && false) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "login_failed_a.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0) 
  $MM_referrer .= "?" . $QUERY_STRING;
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?><?php
session_start();

require('../Connections/conn_ssfhl.php'); 

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_DetailRS1 = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_DetailRS1 = $_SESSION['MM_Username'];
}
mysql_select_db($database_conn_ssfhl, $conn_ssfhl);
$query_DetailRS1 = sprintf("SELECT PlayerID, FanID, Type, InjuryID, Player, StatusID, StatusChangeChoice, CurrentWeek, GMEmail FROM tblplayers WHERE GMEmail = %s ORDER BY tblplayers.Type Asc", GetSQLValueString($colname_DetailRS1, "text"));
$DetailRS1 = mysql_query($query_DetailRS1, $conn_ssfhl) or die(mysql_error());
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);
$totalRows_DetailRS1 = mysql_num_rows($DetailRS1);


$colname_rsGMDetail = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_rsGMDetail = $_SESSION['MM_Username'];
}
mysql_select_db($database_conn_ssfhl, $conn_ssfhl);
$query_rsGMDetail = sprintf("SELECT GMFirst, GMLast FROM tbluseradmin WHERE GMEmail = %s", GetSQLValueString($colname_rsGMDetail, "text"));
$rsGMDetail = mysql_query($query_rsGMDetail, $conn_ssfhl) or die(mysql_error());
$row_rsGMDetail = mysql_fetch_assoc($rsGMDetail);
$totalRows_rsGMDetail = mysql_num_rows($rsGMDetail);

// Count rows in table
$count=mysql_num_rows($DetailRS1);


// Post variables
//$selectID = $_POST['select name'];
//$playerid = $_POST['PlayerID'];

if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form
}

?>
</head>
<body>
    </div></th>
    <td colspan="2" valign="top"><!-- InstanceBeginEditable name="EditRegionContent" -->
      <p class="style3"><span class="style10"><?php echo $row_rsGMDetail['GMFirst']; ?></span><span class="style21">,</span></p>
      <p class="style3"><span class="style21">Use the drop down to update the roster status on the player shown for week number</span> <span class="style10"><?php echo $row_DetailRS1['CurrentWeek']; ?></span>. <span class="style21">Hit the submit button to make the change and you will be returned to your roster change page. </span></p>
      <form action="Lineup_submitted.php" method="post" name="frmupdate" id="frmupdate">
  <table border="1" align="left">
    <tr class="statshdr">
      <td><div align="center">ID</div></td>
      <td><div align="center">Position</div></td>
      <td><div align="center">Player</div></td>
      <td><div align="center">Health</div></td>
      <td><div align="center">Current Status</div></td>
      <td><div align="center">Change Status</div></td>
    </tr>
    <?php
// Fetch record rows in $DetailRS1 by while loop and put them into $row.
while($row=mysql_fetch_assoc($DetailRS1)){
?>
    <tr>
      <td><strong><? echo $row['PlayerID']; ?></strong></td>
      <td><div align="center"><? echo $row['Type']; ?></div></td>
      <td><strong><? echo $row['Player']; ?></strong></td>
      <td><strong><? echo $row['InjuryID']; ?></strong></td>
      <td><div align="center"><? echo $row['StatusID']; ?></div></td>
      <td><label>
        <select name="players[<?=$row['PlayerID']?>]">
          <option value="Active" selected="selected">Active</option>
          <option value="Bench">Bench</option>
</select>
      </label></td>
    </tr>
    <tr> </tr>
    <?php } // End while loop. ?>
  </table>
   <p>&nbsp;        </p>
   <p>&nbsp;</p>
   <p>
     <input type="submit" name="submit" value="submit" />
      </p>
</html><?php
?>
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query not returning all rows

Post by mikosiko »

in this lines

Code: Select all

$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);
$totalRows_DetailRS1 = mysql_num_rows($DetailRS1);
the mysql_fetch_assoc() is getting the first record and moving the recordset pointer, therefore in this while loop later in your code... you are processing the remaining records.

Code: Select all

// Fetch record rows in $DetailRS1 by while loop and put them into $row.
while($row=mysql_fetch_assoc($DetailRS1)){
in your code I'm wondering also why are you selecting the Db twice (mysql_select_db())
mrgrinch12
Forum Newbie
Posts: 16
Joined: Mon Jun 07, 2010 10:20 am

Re: Query not returning all rows

Post by mrgrinch12 »

I am a newbie and I use dreamweaver so some code gets repeated

So you are saying where the code reads this:
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);
$totalRows_DetailRS1 = mysql_num_rows($DetailRS1);

I need to replace it with this:
while($row=mysql_fetch_assoc($DetailRS1)){
mrgrinch12
Forum Newbie
Posts: 16
Joined: Mon Jun 07, 2010 10:20 am

Re: Query not returning all rows

Post by mrgrinch12 »

So should I get rid of the while part of the code?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query not returning all rows

Post by mikosiko »

mrgrinch12 wrote:So should I get rid of the while part of the code?
NO
mrgrinch12 wrote:I am a newbie and I use dreamweaver so some code gets repeated

So you are saying where the code reads this:
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);
$totalRows_DetailRS1 = mysql_num_rows($DetailRS1);

I need to replace it with this:
while($row=mysql_fetch_assoc($DetailRS1)){
NO...

just comment this line for now (don't touch anything else)
$

Code: Select all

row_DetailRS1 = mysql_fetch_assoc($DetailRS1);
and test.
mrgrinch12
Forum Newbie
Posts: 16
Joined: Mon Jun 07, 2010 10:20 am

Re: Query not returning all rows

Post by mrgrinch12 »

That did it.

Thank you so much!

Scott
Post Reply