Nested PDO queries stopping after 1st run

[jcanker]

I'm writing PHP for my son's cub scout pack.  It populates the MySQL db users table from data contained in a csv file. 

So far, it puts the scout's info in just fine...until I try to run another query as a nested query to retrieve the auto-generated userID for the scout that was just placed in the DB.

 

The result of this if I don't include the second query--the one trying to get the new userID for that scout, it iterates through the whole file as expected and puts all the scouts in the users table.  The second I uncomment the 2nd query and try to get it to run, it only puts the 1st scout in the DB, properly retrieves the newly generated userID for him, and then stops. 

 

I need to have this userID for the scout to properly associate that scout with the parents later on and in other sections of this project. 

 

the db_connect() function just creates the PDO object and contains the username, etc for the connection.  That part is working fine so I'm not posting that function here.

 

I've tried renaming the connection, query, etc to add "ID" to the end to ensure I wasn't trampling on var names, but that hasn't made a difference

 

 

//include necessary files:
include_once("../../sys/php/includes/data_fns.php"); 

//start session
session_start();
$conn = db_connect();
$bufferConn = $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);



////////////////////////////////////////
//  Read through the .csv file and populate the users table
/*  Uses the model from the php documentation on fgetcsv:

*  $row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

*/
////////////////////////////////////////
$row = 0; //set the row counter
$table1 = "<table id='csvTable'>"; //start the table HTML markup
if(($handle= fopen("../../sys/source/pack238.csv", "r"))!== FALSE) //open the csv file
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) //fgetcsv reads through the csv file line by line
{
	//ignore the first row which has the header data--we don't want that in the database
	if($row==0){$row++; continue;}		
	$num = count($data);
	//get the scout's info, check to see if he's already in the db, then put it in if not--the scout should NOT already be in db when we run this for the first time, but later versions will only add new scouts.  For now, just alert us if they're already in and halt
	//TODO  Figure out why this still runs if the table name doesn't exist!!
	$query = "SELECT * FROM users WHERE fName=:firstName && lName=:lastName";
	$stmt = $conn->prepare($query);
	$stmt->execute(array(':firstName'=>$data[1],
				   		':lastName'=>$data[0]));
	$dbCount=0;
	while ($stmt->fetch()){$dbCount++;}
	if($dbCount >0) //we found this scout--he shouldn't be in the DB.  Halt the process and check what's going on
	{
		echo "<br/>The scout, $data[1] $data[0] was found in the database already.  We cannot continue.  
		<b/>Please check this duplicate data and try running this setup again";
		die();
	}
	else //he's not in the DB....put him in there
	{
		//get the Den unit as a number, dropping "Den"
		$den = substr($data[10],4);
		//get birthday as something to be used as PHP date object
		if($data[7]!="")
		{
			$bdayO = date_create($data[7]);
			$now = date_create("now");
			$interval = date_diff($bdayO, $now);
			$age = $interval->format('%y years');
			$bday = $bdayO->format('Y-m-d');
		}
		else{
			$bdayO = NULL;
			$bday = NULL;
			$age = NULL;
		}
		echo "<br/> Test message:  The scout $data[1] $data[0], Den:$den would be put into the database!  His bday is:$bday. AGE IS: $age";

		$query = "insert into users (gen,fName,lName, nName,memberOf,bday) VALUES ('M',:firstName,:lastName,:nickName,:den,:bday)";
		$stmt= $conn->prepare($query);
		$stmt->execute(array(':firstName'=>$data[1],
							':lastName'=>$data[0],
							':nickName'=>$data[3],
							':den'=>$den,
							':bday'=>$bday));
		//get the new userID for this scout now that he's in the db
		$connID = db_connect();
		$queryID = "SELECT userID from users WHERE fName=:firstName && lName=:lastName";
		$stmtID = $connID->prepare($queryID);
		$stmtID->execute(array(':firstName'=>$data[1],
							':lastName'=>$data[0]));
		while ($row = $stmtID->fetch())
		{
			$scoutUserID = $row[0]; 
			echo "<br/>This scout's userID is: $scoutUserID";
		}

	}//end else
//get the father's info, check to see if he's already in the db, then put it in if not
	//if he is in the db, get this scout's userID number and add it to the father's children array
	$query = "SELECT * FROM users WHERE fName=:firstName && lName=:lastName";
	$stmt = $conn->prepare($query);
	$stmt->execute(array(':firstName'=>$data[18],
				   		':lastName'=>$data[19]));
	$dbCount=0;
	while ($stmt->fetch()){$dbCount++;}
	if($dbCount >0) //This father is already in the DB...we need to add this scout to his children array
	{
		echo "<br/>For testing purposes, this father is already in the DB...let's add this scout, ".$data[1]." to his children array";
		//get the userID of the current scout
	}
	else //he's not in the DB....put him in there
	{}
//get the mother's info, check to see if he's already in the db, then put it in if not
//if she is in the db, get this scout's userID number and add it ot the mother's children array
}// end while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)  
}//end if(($handle= fopen("../../sys/source/pack238.csv", "r"))!== FALSE)

[jcanker]

Never mind...

I didn't change the name of $row so......

Changing it to $rowID makes it work like a charm

 

Maybe I should go take a break from this project for a little while...