Invalid results from simple select

[stockton]

If I run

Select * from Event WHERE ParentID=32 ORDER BY EventID

in Enterprise manager I get correct results of 7 rows but if I run

Select * from Event WHERE ParentID=" . $EventID." ORDER BY EventID

in my php proggie I only get 5 rows as is proved by doing a print_r of the result.

    $EventID=32;
    $SQL = "Select * from Event WHERE ParentID=" . $EventID." ORDER BY EventID";
    $rs = mssql_query($SQL, $link);
    if ($rs == FALSE)
        {
        $Message = sprintf("%s %d <br>Query failed!", __FILE__, __LINE__);
        trigger_error($Message, E_USER_ERROR, $link);
        exit;
        }

    $Count = 0;
    while ($row = mssql_fetch_array($rs))
        {
print_r($row);

and the print_r shows

Array ( [0] => 175 [EventID] => 175 [1] => Test1 [EventName] => Test1 [2] => 0 [MessageID] => 0 [3] => 32 [ParentID] => 32 [4] => 2009-01-05 00:00:00 [startDate] => 2009-01-05 00:00:00 [5] => 2009-07-02 23:59:59 [EndDate] => 2009-07-02 23:59:59 [6] => 2009-12-07 23:59:59 [LastDrawDate] => 2009-12-07 23:59:59 [7] => 1 [ManualTicketIssue] => 1 [8] => 0 [subtractBack] => 0 [9] => 1 [Threshold] => 1 [10] => 15 [userID] => 15 [11] => 0 [RegTick] => 0 [12] => SLT_1500 [EarnRate] => SLT_1500 ) 
Array ( [0] => 205 [EventID] => 205 [1] => Test 2 [EventName] => Test 2 [2] => 14 [MessageID] => 14 [3] => 32 [ParentID] => 32 [4] => 2009-02-03 00:00:00 [startDate] => 2009-02-03 00:00:00 [5] => 2009-05-03 23:59:59 [EndDate] => 2009-05-03 23:59:59 [6] => 2009-12-03 23:59:59 [LastDrawDate] => 2009-12-03 23:59:59 [7] => 1 [ManualTicketIssue] => 1 [8] => 0 [subtractBack] => 0 [9] => 4 [Threshold] => 4 [10] => 15 [userID] => 15 [11] => 0 [RegTick] => 0 [12] => SLT_500 [EarnRate] => SLT_500 ) 
Array ( [0] => 207 [EventID] => 207 [1] => Test3 [EventName] => Test3 [2] => 14 [MessageID] => 14 [3] => 32 [ParentID] => 32 [4] => 2009-01-03 00:00:00 [startDate] => 2009-01-03 00:00:00 [5] => 2009-07-01 23:59:59 [EndDate] => 2009-07-01 23:59:59 [6] => 2009-12-03 23:59:59 [LastDrawDate] => 2009-12-03 23:59:59 [7] => 1 [ManualTicketIssue] => 1 [8] => 0 [subtractBack] => 0 [9] => 1 [Threshold] => 1 [10] => 15 [userID] => 15 [11] => 0 [RegTick] => 0 [12] => SLT_2000 [EarnRate] => SLT_2000 ) 
Array ( [0] => 210 [EventID] => 210 [1] => ECD Test [EventName] => ECD Test [2] => 0 [MessageID] => 0 [3] => 32 [ParentID] => 32 [4] => 2009-04-17 00:00:00 [startDate] => 2009-04-17 00:00:00 [5] => 2009-07-17 23:59:59 [EndDate] => 2009-07-17 23:59:59 [6] => 2009-08-23 23:59:59 [LastDrawDate] => 2009-08-23 23:59:59 [7] => 1 [ManualTicketIssue] => 1 [8] => 0 [subtractBack] => 0 [9] => 1 [Threshold] => 1 [10] => 15 [userID] => 15 [11] => 0 [RegTick] => 0 [12] => SLT_500 [EarnRate] => SLT_500 ) 
Array ( [0] => 211 [EventID] => 211 [1] => ECD Test 2 [EventName] => ECD Test 2 [2] => 15 [MessageID] => 15 [3] => 32 [ParentID] => 32 [4] => 2009-04-23 00:00:00 [startDate] => 2009-04-23 00:00:00 [5] => 2010-12-25 23:59:59 [EndDate] => 2010-12-25 23:59:59 [6] => 2010-12-30 23:59:59 [LastDrawDate] => 2010-12-30 23:59:59 [7] => 1 [ManualTicketIssue] => 1 [8] => 2 [subtractBack] => 2 [9] => 1 [Threshold] => 1 [10] => 15 [userID] => 15 [11] => 0 [RegTick] => 0 [12] => SLT_2000 [EarnRate] => SLT_2000 ) 

not enough rows????

BTW I added the line breaks to make reading easier.

Suggestions please

[PFMaBiSmAd]

Where would the missing rows occur in that output? All at the beginning, all at the end, or randomly through out the data? Post the missing rows, so that someone could possibly identify if there is something about them that would cause this? Also, what is the complete code in your while(){} loop?

 

What does a 'view source' in your browser show, because you could have something in the data that looks like HTML tags and is causing the missing rows to show up in the source in your browser but are not being rendered by the browser?

 

If you wrap your print_r() statement in HTML <pre> </pre> tags the output will be formatted and easier to read.

[stockton]

The missing rows are the last ones and as print_r also has them missing I suspect that those rows are not returned by the mssql_fetch_array().

As the missing rows are also missing in the print_r I do not feel that we have an HTML error.

The browser "show source" also has those rows missing.

The complete loop follows.

    $SQL = "Select * from Event WHERE ParentID=".$EventID." ORDER BY EventID";
// echo $SQL;
    $rs = mssql_query($SQL, $link);
    if ($rs == FALSE)
        {
        $Message = sprintf("%s %d <br>Query failed!", __FILE__, __LINE__);
        trigger_error($Message, E_USER_ERROR, $link);
        exit;
        }

    echo "<TABLE BORDER=3 width=99% height=15 cellpadding=1 align=center>";
    echo "<tr bgcolor=deeppink><th>ID<th>EventName</th><th>MessageName</th><th>StartDate</th><th>EndDate</th><th>LastDrawDate</th><th>ManualTicketIssue</th><th>EarnRate</th><th>Dependants</th><th>Areas</th><th>Threshold</th><th>FreeTickets</th></th></tr>\n";
    $Count = 0;
    while ($row = mssql_fetch_array($rs))
        {
echo "<pre>";
print_r($row);
echo "</pre>";
        $intID = $row['EventID'];
        $txtEventName  = $row['EventName'];

        $SQL = "Select MessageID, MessageName, Message, UserID from Message where MessageID = ".$row['MessageID'];
        $result = mssql_query($SQL, $link);
        $wor = mssql_fetch_array($result);

        $intMessageID = $wor['MessageName'];
        $intParentID = $row['ParentID'];
        $StartDate  = $row['StartDate'];
        $EndDate  = $row['EndDate'];
        $LastDrawDate  = $row['LastDrawDate'];
        $StartDate = dateconvert($StartDate,2);
        $EndDate = dateconvert($EndDate,2);
        $LastDrawDate = dateconvert($LastDrawDate,2);
        $ManualTicketIssue = $row['ManualTicketIssue'];
        if ($ManualTicketIssue == 0) $ManualTicketIssue = "Yes";
           else $ManualTicketIssue = "No";
        $intAreas = $row['SubtractBack'];
// echo "AreasID = ".$intAreas;
//
// Now get applicable Areas Name
// 
        if ($intAreas == 0) $AreasName = "All Areas";
        else
            {    
            $SQL = "SELECT * FROM Areas where AreasID = $intAreas"; // was EarnRateName;
            $rs = mssql_query($SQL, $link);
            while ($rws = mssql_fetch_array($rs))
                {
                $AreaID = $rws['AreasID'];
                $AreasName = sprintf("%s", $rws['AreasName']);
                }
            }
        $intThreshold = $row['Threshold'];
        if ($intThreshold == "") $intThreshold = 0;
        $FreeTickets = $row['RegTick'];
        if ($FreeTickets == "") $FreeTickets = 0;
        $EarnRate = $row['EarnRate'];
        $UserID = $row['UserID'];
//        $SQLER = "SELECT COUNT(*) FROM EarnRate WHERE EarnRateID = $intID";
//        $rser = mssql_query($SQLER, $link);
        $Numrecs = 0;
        $SQLDP = "SELECT COUNT(*) FROM Event WHERE ParentID = ".$intID;
        $rsdp = mssql_query($SQLDP, $link);
        $wordp = mssql_fetch_array($rsdp);
        $Numrecs = $wordp[0];

// echo $Datum;
        $Kleur = '"#000000"';
        if (($Count % 2) != 0) $Kleur = '"#0000FF"';
        echo "<tr bgcolor=$Kleur onMouseover=this.bgColor='gray' onMouseout=this.bgColor=$Kleur>
        <td align=\"center\"><a href=\"eventsUD.php?UID=$intID\"><font color=yellow>$intID</font></td>
        <td align=\"center\">$txtEventName</td>
        <td align=\"center\">$intMessageID</td>
        <td align=\"center\">$StartDate</td>
        <td align=\"center\">$EndDate</td>
        <td align=\"center\">$LastDrawDate</td>
        <td align=\"center\">$ManualTicketIssue</td>
        <td align=\"center\">$EarnRate</td>
        <td align=\"center\"><a href=\"events.php?EventID=$intID\"><font color=yellow>$Numrecs</font></td>
        <td align=\"center\">$AreasName</td>
        <td align=\"center\">$intThreshold</td>
        <td align=\"center\">$FreeTickets</td>
        </tr>\n";
        ++$Count;
//         if ($Count > 15) break;
        }

[Brian Swan]

Looks to me like you reset the $rs statement resource within one of your conditions. I've removed some of your code to try and show this. Your initial query defines the $rs statement resource, but then you reset it within the while loop.

 

$rs = mssql_query($SQL, $link);

while ($row = mssql_fetch_array($rs))
{
...
if ($intAreas == 0) $AreasName = "All Areas";
else
{ 
	$SQL = "SELECT * FROM Areas where AreasID = $intAreas"; // was EarnRateName;
	$rs = mssql_query($SQL, $link);
	while ($rws = mssql_fetch_array($rs))
	{
		$AreaID = $rws['AreasID'];
		$AreasName = sprintf("%s", $rws['AreasName']);
	}
}
...
}

 

Is that what you intended to do? Seems like that could be causing the problem.

 

-Brian

[straygrey]

Sorry, that was sloppy of me. I'll fix it and test again. Thanks.

[stockton]

That silly/sloppy $rs problem was the problem & as soon as I altered it to $rsArea all was well.

Thank you again.

Now how do I mark this as resolved?

[Brian Swan]

Not silly/sloppy really....just something that you need a second set of eyes for. :-)

 

Glad it's working now.