Page 1 of 1

Query not returning all rows

Posted: Mon Oct 04, 2010 9:00 am
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
?>

Re: Query not returning all rows

Posted: Mon Oct 04, 2010 10:18 am
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())

Re: Query not returning all rows

Posted: Mon Oct 04, 2010 10:31 am
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)){

Re: Query not returning all rows

Posted: Mon Oct 04, 2010 12:35 pm
by mrgrinch12
So should I get rid of the while part of the code?

Re: Query not returning all rows

Posted: Mon Oct 04, 2010 1:24 pm
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.

Re: Query not returning all rows

Posted: Mon Oct 04, 2010 1:33 pm
by mrgrinch12
That did it.

Thank you so much!

Scott