[SOLVED] Records Omitted

jrm · December 11, 2008

I am having a problem with records being skipped when I pull this query from my intranet server:

SELECT Cadets.CadetID, Cadets.SSN, Cadets.Last, Cadets.First, Cadets.Middle, Date_Format(Cadets.DOB, '%d %b %Y') as DoB, Cadets.PhaseID, Cadets.Number, Year(CadetsDates.DTG) as SelectYear
FROM Cadets_be.Cadets INNER JOIN Cadets_be.CadetsDates ON Cadets.CadetID = CadetsDates.CadetID
WHERE (CadetsDates.SDID=11 and Year(CadetsDates.DTG)=2008) AND  Cadets.Last<>'Test'
ORDER BY Cadets.Last, Cadets.First, Cadets.Middle;

There are three records that get skipped, for privacy reasons(juvenile records) I can not give the information. I have also pulled the data from MySQL Query Browser, MS Access, and from the terminal on the mysql server and I get 43 records. On the intranet server, I get 39??? I thought that it was with the JOIN, but I pulled the data for those that were not showing, and there are corresponding records. What else could be the problem?

Be fore warned, I wrote this when I just started to write PHP, and I just haven't gone back to redo it. My codeing and commenting have gotten alot better.

<HTML>
<HEAD>
<Title>Cadet Roster</Title>
<link rel=stylesheet type="text/css" href="themes/theme.css">
</HEAD>
<BODY background="/themes/subtxtr.gif">
<?php
     include "Functions.php"; // Original: Mike McGrath  (mike_mcgrath@lineone.net)  web Site: http://website.lineone.net/~mike_mcgrath
     include "CadetNameFunc.php";
     include "CadetImageFunc.php";
?>
<p><font size = 4><a href = CadetNumbers.htm target = Info >Return to Cadet Roster</a></p>
<?php
     // Get Past or Present Cadet Roster and Year if Past
     if(isset($_GET['PorP']))
     {
        $PorP = (int) $_GET['PorP'];
        //echo "<p>" . $PorP . "</p>";
        if(isset($_GET['year']))
        {
            $SelectYear = (int)$_GET['year'];
            //echo "<p>" . $SelectYear . "</p>";
        }
        else
        {
            echo "No Year Selected. Current Year Selected";
            $SelectYear = date("Y");
        }
    }
    else
    {
        echo 'No Time Frame selected!';
        exit;
    }
    echo "<p><font size =3>Report Generated at: " . date("F j, Y, g:i a") . "</font></p>\n";


    // set the Query based on Past or Present
     switch ($PorP)
     {
        case 1:  //Current Cadets
            echo "<table width = '100%' ><tr><td align = center>Current Cadets</td></tr></table>\n";
            $query = " SELECT Cadets.CadetID, Cadets.SSN, Cadets.Last, Cadets.First, Cadets.Middle, Date_Format(Cadets.DOB, '%d %b %Y') as DoB, Cadets.PhaseID, Cadets.Number
                       FROM Cadets_be.Cadets
                       WHERE Cadets.PhaseID<5 AND Cadets.Last<>'Test'
                       ORDER BY Cadets.Last, Cadets.First, Cadets.Middle; ";
            break;
        case 2:  //Former Cadets
            echo "<table width=100% ><tr><td align = center>Past Intakes for Year: " . $SelectYear . "</td></tr></table>\n";
            $query = " SELECT Cadets.CadetID, Cadets.SSN, Cadets.Last, Cadets.First, Cadets.Middle, Date_Format(Cadets.DOB, '%d %b %Y') as DoB, Cadets.PhaseID, Cadets.Number, Year(CadetsDates.DTG) as SelectYear
                       FROM Cadets_be.Cadets INNER JOIN Cadets_be.CadetsDates ON Cadets.CadetID = CadetsDates.CadetID
                       WHERE (CadetsDates.SDID=11 and Year(CadetsDates.DTG)='" . $SelectYear ."') AND  Cadets.Last<>'Test'
                       ORDER BY Cadets.Last, Cadets.First, Cadets.Middle;";
            break;
        case 3: //Aftercare Residents
            echo "<table width=100% ><tr><td align = center>Current Aftercare Residents</td></tr></table>\n";
            $query = " SELECT Cadets.CadetID, Cadets.SSN, Cadets.Last, Cadets.First, Cadets.Middle, Date_Format(Cadets.DOB, '%d %b %Y') as DoB, Cadets.PhaseID, Cadets.Number
                       FROM Cadets_be.Cadets WHERE Cadets.PhaseID=5 AND Cadets.Last<>'Test'
                       ORDER BY Cadets.Last, Cadets.First, Cadets.Middle;";
            break;
     }
?>

<br>
<table width="100%" border="2">
<tr>
	<td align = middle width = 2% ><big></big></td>
	<td align = middle width = 15% ><big>Name</big></td>
	<td align = middle width = 5% ><big>SSN</big></td>
	<td align = middle width = 8% ><big>Phase</big></td>
  		<td align = middle width = 10% ><big>DOB</big></td>
	<td align = middle width = 10% ><big>Date of Entry</big>
	<td align = middle width = 10% ><big>Est Date of Exit</big></td>
        <td align = middle width = 5% ><big>Viol Ct</big></td>
        <td align = middle width = 5% ><big>PNC Ct</big></td>
	<td align = middle width = 20% ><big>Remarks</big></td>
</tr>

<?php
     // Get the Roster
     // Start Cadet Roster
     $result = mysql_query($query, $link) or die("Could not run Cadet Query: " . mysql_error());
     $count = 0;
     while ($Cadets = mysql_fetch_array($result, MYSQL_ASSOC))
     {
         $count += 1;
         $cadetid = $Cadets["CadetID"];
         $Name = $Cadets["Last"] . ", " . $Cadets["First"] . " " . $Cadets["Middle"];
         $CadetNum = $Cadets["Number"];
         $fullSSN = str_split($Cadets["SSN"]);
         $Last4 = $fullSSN[7] . $fullSSN[8] . $fullSSN[9] . $fullSSN[10];
         $Phaseid = $Cadets["PhaseID"];
         $DOB = $Cadets["DoB"]=="" ? 0 : $Cadets["DoB"];
         
         // for error checking
         echo "Getting Data for Cadet : " . $Cadets["Last"] . ", " . $Cadets["First"] . " " . $Cadets["Middle"];

        switch(fmod($count,2))
        {
            case 0:
?>
    <tr>
        <td align = center width = 2% > <? echo $count ?></td>
        <td align = center width = 15% >
            <a href="CadetDetails.php?CadetID=<? echo $cadetid ?>"
                onmouseover="popup('<? echo $imagepath ?>')"
                onmouseout="kill()" ><? echo $Name ?></a>
        </td>
        <td align = center width = 5% ><? echo $Last4?></td>
        <td align = center width = 8% ><? echo $CurPhase?></td>
        <td align = center width = 5% ><? echo $DOB ?></td>
        <td align = center width = 10% ><? echo $DoE ?></td>
        <td align = center width = 10% ><? echo $EstDoR ?></td>
        <td align = center width = 5% ><? echo $ViolCount ?></td>
        <td align = center width = 5% ><? echo $PNCCount ?></td>
        <td align = center width = 20% ><? echo $Remarks ?></td>
    </tr>
<?php
                break;
            default:
?>
    <tr>
        <td bgcolor = lightgrey  align = center width = 2% ><? echo $count ?></td>
        <td bgcolor = lightgrey  align = center width = 15% >
            <a href="CadetDetails.php?CadetID=<? echo $cadetid ?>"
            onmouseover="popup('<? echo $imagepath ?> ')"
            onmouseout="kill()" ><? echo $Name ?></a>
        </td>
        <td bgcolor = lightgrey align = center width = 5% ><? echo $Last4 ?></td>
        <td bgcolor = lightgrey align = center width = 10% ><? echo $CurPhase ?></td>
        <td bgcolor = lightgrey align = center width = 10% ><? echo $DOB ?></td>
        <td bgcolor = lightgrey align = center width = 10% ><? echo $DoE ?></td>
        <td bgcolor = lightgrey align = center width = 10% ><? echo $EstDoR ?></td>
        <td bgcolor = lightgrey align = center width = 5% ><? echo $ViolCount ?></td>
        <td bgcolor = lightgrey align = center width = 5% ><? echo $PNCCount ?></td>
        <td bgcolor = lightgrey align = center width = 20% ><? echo $Remarks ?></td>
    </tr>
<?php
	}
   }
?>
</table>

DROP TABLE IF EXISTS `Cadets_be`.`CadetsDates`;
CREATE TABLE  `Cadets_be`.`CadetsDates` (
  `DateId` int(10) NOT NULL auto_increment,
  `CadetID` int(10) default NULL,
  `SDID` int(10) NOT NULL default '0',
  `DTG` date default NULL,
  `Comments` varchar(255) default NULL,
  `CurDTG` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `User` varchar(50) default NULL,
  `Computer` varchar(50) default NULL,
  PRIMARY KEY  (`DateId`),
  KEY `CadetID` (`CadetID`),
  KEY `Cadets DatesSDID` (`SDID`),
  KEY `DateId` (`DateId`)
) ENGINE=MyISAM AUTO_INCREMENT DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `Cadets_be`.`SpecificDates`;
CREATE TABLE  `Cadets_be`.`SpecificDates` (
  `SDID` int(10) NOT NULL auto_increment,
  `Classification` varchar(25) default NULL,
  `Level` int(10) default NULL,
  `CurDTG` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`SDID`),
  KEY `PhaseID` (`SDID`)
) ENGINE=MyISAM AUTO_INCREMENT DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `Cadets_be`.`Cadets`;
CREATE TABLE  `Cadets_be`.`Cadets` (
  `CadetID` int(10) NOT NULL auto_increment,
  `CurDTG` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `CauseNumber` varchar(50) default NULL,
  `SSN` varchar(50) default NULL,
  `CRId` int(10) NOT NULL default '0',
  `Last` varchar(50) default NULL,
  `First` varchar(50) default NULL,
  `Middle` varchar(50) default NULL,
  `DOB` datetime default NULL,
  `PhaseID` int(10) NOT NULL default '0',
  `Dorm` varchar(50) default NULL,
  `Number` int(10) default NULL,
  `TJPC_PID` varchar(50) default NULL,
  `UserName` varchar(50) default NULL,
  `Computer` varchar(50) default NULL,
  `PO` varchar(75) NOT NULL,
  `ChargeType` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`CadetID`),
  KEY `CadetID` (`CadetID`)
) ENGINE=MyISAM AUTO_INCREMENT DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

fenway · December 11, 2008

It's impossible that the same query run on the same database gives different results. I'd confirm it's actually the same query, and check the count of the records.

jrm · December 11, 2008

Yeah, I do not know what happened. But after about 20 mins, I pulled the intranet page and the data is there.

Sign In

[SOLVED] Records Omitted

Recommended Posts

jrm

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

jrm

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information