Odd SQL Issue - Different Result Every Run

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Odd SQL Issue - Different Result Every Run

Post by Bigun »

This is running PHP-CGI on Windows 2003. This is using Microsoft's SQL driver for PHP.

There may be a way to make SQL do the work, and if there is, please enlighten me. For now I have PHP doing the work. Here is the code:

Code: Select all

$result=sqlsrv_query($dbhandle, $query, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$numrows = sqlsrv_num_rows($result);

echo $numrows . " Warrant Entries\n\r";

//Filter results to find out which are actually open warrants
$j=0;
for ($i=0; $i<$numrows; $i+=1) {
	$row = sqlsrv_fetch_array($result);
	if ($row['Capias_Case_Number'] <> $lastcase) {
		$lastcase = $row['Capias_Case_Number'];
		if ($row['Capias_Status'] == 'C') {
			$capiasinfo['court'][$j] = $row['Capias_Which_Court'];
			$capiasinfo['casenumber'][$j] = $row['Capias_Case_Number'];
			$capiasinfo['spn'][$j] = $row['SPN'];
			$capiasinfo['lastname'][$j] = $row['LastName'];
			$capiasinfo['firstname'][$j] = $row['FirstName'];
			$capiasinfo['middlename'][$j] = $row['MiddleName'];
			$capiasinfo['suffix'][$j] = $row['Suffix'];
			$capiasinfo['zip'][$j] = $row['ZipCode'];
			$capiasinfo['address'][$j] = $row['StreetAddress'];
			$capiasinfo['city'][$j] = $row['City'];
			$capiasinfo['state'][$j] = $row['State'];
			if ($row['Capias_Which_Court'] == 'G') {
				$capiasinfo['chargedesc'][$j] = $row['GS_TCA_Charge_Desc'];
			}
			if ($row['Capias_Which_Court'] == 'C') {
				$capiasinfo['chargedesc'][$j] = $row['Crim_TCA_Charge_Desc'];
			}
			$j+=1;
		}
	}
}

sqlsrv_close($dbhandle);

//Connect to local database
$dbhandle = sqlsrv_connect($sqlserver, array("UID"=>$sqlusername,"PWD"=>$sqlpassword,"Database"=>$sqldatabase));
if( $dbhandle === false ) { 
	die(FormatErrors( sqlsrv_errors() ) ); 
}

//begin insertion procedure.... *snicker*
$numrows = count($capiasinfo['casenumber']);

//Empty out table here
$query = "DELETE FROM OpenWarrants";
$result=sqlsrv_query($dbhandle, $query);

echo $numrows . " Active Warrants Found\n\r";

for ($i=0; $i<$numrows; $i+=1) {
	$lastname = $capiasinfo['lastname'][$i];
	$firstname = $capiasinfo['firstname'][$i];
	$middlename = $capiasinfo['middlename'][$i];
	$suffix = $capiasinfo['suffix'][$i];
	$casenumber = $capiasinfo['casenumber'][$i];
	$court = $capiasinfo['court'][$i];
	$spn = $capiasinfo['spn'][$i];
	$charge = $capiasinfo['chargedesc'][$i];
	$city = $capiasinfo['city'][$i];
	$state = $capiasinfo['state'][$i];
	$zip = $capiasinfo['zip'][$i];
	$address = $capiasinfo['address'][$i];
	$query = "INSERT INTO " . $sqltable . " (LastName, FirstName, MiddleName, Suffix, CaseNumber, Court, SPN, Charge, City, State, Zip, Address)
	VALUES ('$lastname', '$firstname', '$middlename', '$suffix', '$casenumber', '$court', '$spn', '$charge', '$city', '$state', '$zip', '$address')";
	$result=sqlsrv_query($dbhandle, $query);
}

echo "Insertion Procedure Complete\n\r";

sqlsrv_close($dbhandle);
This is kind of hard to explain, I'll do the best I can. This script was designed to fill an intermediate database with data for use on the web. The script resides in a non-web accessible part of the computer and is ran via Windows' Scheduled Task manager.

What determines an entry in the intermediate database is the 'Capias_Status' portion of the record. Each 'Capias_Case_Number' has multiple entries (either being issued, reissued, executed and so forth). The SQL statement sorts so that the newest entry comes first. It then looks to see if the last activity was "Issued". If it was it adds it to the array of active warrants. It then goes through the array and inserts the records into the intermediate database.

Now here's the weird part. I began to notice the amount of open warrants should be about 26K. And sometimes the result is between 1K and 16K... and sometimes the full 26K. I began to run the script via commandline and see what I could see.

Code: Select all

C:\Program Files\PHP>php updatewarrants.php
366045 Warrant Entries
11716 Active Warrants Found
Insertion Procedure Complete

C:\Program Files\PHP>php updatewarrants.php
366045 Warrant Entries
26459 Active Warrants Found
Insertion Procedure Complete
This was ran side by side with two completely different results. What is going on?

*edit*

One more thing I forgot to mention. The place where the script drops off is odd as well. The SQL data it is sorting is sorted by last name alphabetically. The rows would stop getting processed in midstream (like at the letter 'J' or 'L'). Running the query in the Management Studio I don't see any flaws in the records that would cause this.
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Re: Odd SQL Issue - Different Result Every Run

Post by Bigun »

New discovery: I've inserted some code in my PHP to echo out some values for troubleshooting:

Code: Select all

	
for ($i=0; $i<$numrows; $i+=1) {
$row = sqlsrv_fetch_array($result);  //Where the row is populated
	if ($row['Capias_Case_Number'] <> $lastcase) {
		$lastcase = $row['Capias_Case_Number'];
		if ($row['Capias_Status'] == 'C') {
			echo $row['Capias_Case_Number'] . " - " . $row['SPN'] . " - " . round(($i/$numrows)*100, 2) . "\n\r"; //Echos if case gets added
			$capiasinfo['court'][$j] = $row['Capias_Which_Court'];
			$capiasinfo['casenumber'][$j] = $row['Capias_Case_Number'];
			$capiasinfo['spn'][$j] = $row['SPN'];
			$capiasinfo['lastname'][$j] = $row['LastName'];
			$capiasinfo['firstname'][$j] = $row['FirstName'];
			$capiasinfo['middlename'][$j] = $row['MiddleName'];
			$capiasinfo['suffix'][$j] = $row['Suffix'];
			$capiasinfo['zip'][$j] = $row['ZipCode'];
			$capiasinfo['address'][$j] = $row['StreetAddress'];
			$capiasinfo['city'][$j] = $row['City'];
			$capiasinfo['state'][$j] = $row['State'];
			if ($row['Capias_Which_Court'] == 'G') {
				$capiasinfo['chargedesc'][$j] = $row['GS_TCA_Charge_Desc'];
			}
			if ($row['Capias_Which_Court'] == 'C') {
				$capiasinfo['chargedesc'][$j] = $row['Crim_TCA_Charge_Desc'];
			}
			$j+=1;
		}
	} else {
		echo $row['Capias_Case_Number'] . "\n\r"; //Echo a case number if it is repeating -- This is where I believe the blank lines were coming from
	}
}
I watched this script run... and after about 13% of the way through... it seemed to pause... then began to echo out blank lines. I think that the 'sqlsrv_fetch_array' function is failing for some reason. Is there a known bug?
Post Reply