Help with sqlsrv_query - how to prevent insert from updating row count?

[benphp]

I have a script that looks for a record in the SQL Server db first before INSERTing. Trouble is, once it inserts, it always returns a row count of 1. I cannot figure this one out.

 

I have the same trouble no matter what I do. I orignially thought it was some problem with sqlsrv_num_rows($stmt), which is why I used the counter.

 

 

The following script will return "You are now enrolled! 1" whether there's a record in the table or not. I'm doing something dumb and I can't see it...

 

<?php

$sqlSelect = "
Select EID 
FROM Class_Students 
WHERE Class_Students.CLID = '1234'
";

$stmt1 = sqlsrv_query( $conn, $sqlSelect );
$numrows = 0;
while($row = sqlsrv_fetch_array($stmt1)) {
	$EID = $row[0];
	$numrows++;
}
sqlsrv_free_stmt($stmt1);

//if not already in the table, then insert them
if ($numrows > 0) {
	$msg = "<p>You are already enrolled in this class. $numrows</P>";
} else {
	$sqlInsert = "INSERT INTO Class_Students (EID, CLID) VALUES ('5555', '1234')";
	$stmt2 = sqlsrv_query( $conn, $sqlInsert );
	$msg = "<P>You are now enrolled! $numrows<P>";
	sqlsrv_free_stmt($stmt2);
}
print $msg;
sqlsrv_close($conn);
?>

If I comment out the insert, I can at least get it to print "You are now enrolled", but if I allow the insert, it always counts 1 record - even when the count comes BEFORE the insert.

Edited March 26, 2013 by benphp

[requinix]

I don't see how it's at all possible to get that "You are now enrolled! 1" message with the code you've given. Is it your actual code or did you paraphrase it for the post?

[mac_gyver]

does your code have any logic in it to detect and prevent the code from running multiple times or from running when your form hasn't been submitted at all or is that code inside of a file being included and it's being included more than once or in a loop?

[benphp]

There's no loops - no multiple submits. It's a straight GET method from one page to another.  

 

No paraphrasing. I know - it's driving me nuts.

 

The only thing I can think of is the parameters for sqlsrv_query aren't clear to me. Looking into the options: SQLSRV_CURSOR_KEYSET, etc. to see if that makes a difference. I think the answer may lie there.

[mac_gyver]

It's a straight GET method from one page to another

 

if your code has nothing in it to prevent it more than one request, the browser requesting the page two or more times can cause the symptom you are seeing. you are only seeing the output from the last time the page is requested.

[benphp]

Tried POST with the same results. I just rebooted my server too, just for good measure.

[benphp]

Been struggling with this for hours now. Can't figure out - looks like the select is done, finds no records, inserts a record, then finds 1 record.

 

Here's another attempt:

 

<?php
$serverName = "myserv";
$mainDb = "mydb";
$connectionInfo = array( "Database"=>"mydb");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( !$conn ) {
     echo "Connection could not be established.\n";
     die( print_r( sqlsrv_errors(), true));
}

/////ENROLL SINGLE MANUAL
if(isset($_POST['btnEnrollMe'])) {
	$CLID = $_POST['CLID'];
	$EID = $_POST['EID'];
	$month = $_POST['month'];
	$year = $_POST['year'];
	
	$classTitle = $_POST['classTitle'];
	
	$sqlSelect = "
	Select 
		$mainDb.dbo.Class_Students.EID
	FROM 
		$mainDb.dbo.Class_Students 
	WHERE 
		$mainDb.dbo.Class_Students.EID = $EID 
	AND
		$mainDb.dbo.Class_Students.CLID = $CLID
	";

	$params = array();
	$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
	$stmt = sqlsrv_query( $conn, $sqlSelect , $params, $options );
	$numrows = sqlsrv_num_rows($stmt);
	sqlsrv_free_stmt($stmt);
	$stmt = NULL;
	
	print "found:$numrows";
	if ($numrows > 0) {
		echo "<p>There are rows. </p>";
	} else {
		$sqlInsert = "
		INSERT INTO $mainDb.dbo.Class_Students 
		($mainDb.dbo.Class_Students.EID, $mainDb.dbo.Class_Students.CLID) VALUES ('$EID', '$CLID')
		";
		$stmt = sqlsrv_query($conn, $sqlInsert);
		print "<P style=\"color:green\">You are now enrolled! <P>";
		sqlsrv_free_stmt($stmt);
	}
	sqlsrv_close($conn);
	exit;
}
?>

[requinix]

Can't figure out - looks like the select is done, finds no records, inserts a record, then finds 1 record.

I must be misunderstanding you because it sounds like you said "there are no records, then it inserts one, and then there's a record".

[benphp]

Yes. I presume the select statement finds no records, because if it did, the IF statement wouldn't allow the insert. So the select finds 0 records, the IF statement allows the INSERT, then somehow the result is 1 record found. The script does insert a record, so that much works. But it's as though the select is done twice: once before the insert and once after the insert - or as others have suggested, the page is refreshed - which isn't happening.

 

What SHOULD happen is 1) select finds no record. 2) Insert done 3) "You are now enrolled" displays. 4) exit

 

What IS happeing is 1) select finds no record. 2) Insert done 3) "There are rows" displays. 4) exit

[mac_gyver]

how do you know the page isn't being requested twice? Have you looked in the web server access log? added a session based counter to your code?

[benphp]

mac_gyver and others - yes - you have found the problem. The page is being loaded twice. For what reason I have no idea.