Jump to content

Invalid results from simple select


stockton

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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;
        }

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.