Solved

php/MySQL not showing info on first record

Posted on 2010-08-18
10
249 Views
Last Modified: 2012-05-10
I built a small web app for a church to find sunday school classes.  When searched by age by clicking on the age range, it doesn't show the information for the first record.  

the site is at www.electroniclifegroupfinder.com

0
Comment
Question by:axessJosh
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 3

Expert Comment

by:dockhand
ID: 33465502
Are you certain that there isn't any empty record in your data base?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 33465505
This is a common error.  Often the mysql_fetch_assoc() function is run once before the while() iterator.  Please post the PHP code that generates the page.  Thanks, ~Ray
0
 
LVL 4

Expert Comment

by:javaftper
ID: 33465544
PHP code is stored server side therefore to investigate this problem you will need to upload your groupsByAge.php file here.  
Once you've done this I shall investigate.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Author Comment

by:axessJosh
ID: 33465581
My code for that file is attached below.  There is data in that field because it shows up in the other query fields.  It is only the info for the first record shown in the PHP file.  Thanks in advance.
<?php require_once('Connections/conELF.php'); ?>
<?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;
}
}

$var2_rsAges = "-1";
if (isset($_GET['ageRangeID'])) {
  $var2_rsAges = $_GET['ageRangeID'];
}
mysql_select_db($database_conELF, $conELF);
$query_rsAges = sprintf("SELECT tblages.ageRangeID, tblages.ageDescription, tbltimes.timeID, tbltimes.timeDescription, tbltypes.typeID, tbltypes.typeDescription, tblgroup.groupID, tblgroup.firstName, tblgroup.lastName, tblgroup.phone, tblgroup.pictureLoc, tblgroup.typeID, tblgroup.ageRangeID, tblgroup.location, tblgroup.timeID FROM tblgroup, tblages, tbltimes, tbltypes WHERE tblgroup.typeID = tbltypes.typeID  AND tblgroup.ageRangeID = tblages.ageRangeID  AND tblgroup.timeID = tbltimes.timeID AND tblages.ageRangeID = %s", GetSQLValueString($var2_rsAges, "int"));
$rsAges = mysql_query($query_rsAges, $conELF) or die(mysql_error());
$row_rsAges = mysql_fetch_assoc($rsAges);
$totalRows_rsAges = mysql_num_rows($rsAges);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Groups Listed by Age</title>
<link href="mainStyles.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.style2 {font-size: 14px}

#wrapper {
	overflow-y: scroll;
}
-->
</style>
</head>

<body>
<div id="header"></div>
<div id="wrapper">
<h1>LIFE Group Finder</h1>
<h2>Groups Listed by Average Age</h2>

<h5>Click on the Teacher's Name for more information and to Register.</h5>

<?php if ($totalRows_rsAges > 0) { // Show if recordset not empty ?>
  <p><?php do { ?>
    <a href="groupDetail.php?groupID=<?php echo $row_rsAges['groupID']; ?>"><?php echo $row_rsAges['firstName']; ?> <?php echo $row_rsAges['lastName']; ?></a> <span class="style2">- <?php echo $row_rsAges['timeDescription']; ?> - <?php echo $row_rsAges['typeDescription']; ?></span>
    <hr />
    <br />
      <?php } while ($row_rsAges = mysql_fetch_assoc($rsAges)); ?>
  <?php } // Show if recordset not empty ?>
  <?php if ($totalRows_rsAges == 0) { // Show if recordset empty ?>
    We Are Sorry, there are currently no groups in that age range.
  <?php } // Show if recordset empty ?>
</p>

<a href="index.php">Home</a>
</div><!-- end wrapper -->
</body>
</html>
<?php
mysql_free_result($rsAges);
?>

Open in new window

0
 
LVL 3

Expert Comment

by:wuff
ID: 33465923
Check your SQL string. You are joining with several tables. Maybe the missing record has one of its field values missing in the table being joined.

Have you executed the SQL in phpAdmin to make sure your record is within the result set?
0
 
LVL 2

Author Comment

by:axessJosh
ID: 33466298
yes, it is there. and the SQL should be correct because the data is showing for all the other records in the results except the first.
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 33466516
Line 38 runs the query.  

Line 39 removes the first row from the results set of that query.

Lines 68 through 72 is an iterator pattern that consumes the rest of the results set from that query.

Suggest you remove line 39.  As I said, it is a common error!

bet of luck with it, ~Ray
0
 
LVL 9

Expert Comment

by:Snarfles
ID: 33466531
$row_rsAges = mysql_fetch_assoc($rsAges);

this line steps the array of your results through to the first result... you then step to the second when you reach your do while loop. Since you don't need this before the do while loop you can comment out this line and it should work better.
0
 
LVL 2

Author Closing Comment

by:axessJosh
ID: 33466557
I think i had this issue with the other pages before, but its been a long time since i've looked at this site i built.  I have redesigned it since to have much better SQL parsing to be much cleaner and more effective.
0
 
LVL 3

Expert Comment

by:wuff
ID: 33470126
Yes, the while pattern is commonly used to go through the result set. However there's a difference between "do...while" and "while...do". The first one always executes the loop once and then checks the while condition(s). The "while...do" first checks and then executes the loop if all conditions are true.

I use "while...do" since I then don't need the assignment at line 39 above.

I still can't understand why Josh' code didn't work.

At line 39 we get the first record
At line 68 we get the record data and print them
At line 72 we try to fetch record #2. If there are no more records we leave the loop. If there is another record we loop again.

Lines 68-72 are always executed. Is often a bit dangerous since you can easily guess what happens if there are no records in the result set. Luckily Josh has an "if..." to verify this in line 67
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question