Loop Issue With Php Fgetcsv And Mysql

[Edgyballz]

Hi everyone,

 

I am facing a problem with the following code snippet which invovles inserting data from a csv file into a mysql table.

 

<?php
if(isset($_POST['submit']))
{
	$file = $_POST['filepath'];

	$handle = fopen($file,"r");
	$fileop = fgetcsv($handle,2000,","); //read only headers (first row)

	// build qry to select data from assessment,assessmenttype tables
	$qry = "SELECT assessmenttype.AssessType_Code, Assess_Num
		FROM assessment, assessmenttype
		WHERE assessmenttype.AssessType_Code = assessment.AssessType_Code
		AND Offer_ID = '$offerId';";

	// execute query
	$result = mysql_query($qry) or die('Query failed: ' . mysql_error());
	$c = 1;

	  while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{

		$index = $_POST["lstAssess$c"];
		$assessNum = $row['Assess_Num'];
		$assessCode = $row['AssessType_Code'];
		echo $assessCode." ".$assessNum." ".$fileop[$index];
		$i = 1;

		while((${"fileop$i"} = fgetcsv($handle,10000,",")) !== false)
		{
		echo ${"fileop$i"}[$index];
		/*
		$Stud_Lname = $fileop[0];
		$Stud_Fname = $fileop[1];
		$Stud_Id = $fileop[2];
		$mark = $fileop[$index];

		$qry = mysql_query("INSERT INTO gradebook (Stud_ID,Offer_ID,Assess_Num,AssessType_Code,GB_Mark)
		 VALUES ('$Stud_Id','$offerId','$assessNum','$assessCode','$mark')")or die('Query failed: ' . mysql_error());*/
		 echo "<br>";
		 $i = $i + 1;
		}
		//echo $fileop[$index];
		$c = $c + 1;

	}		

}else
{
	echo "Error!";
}

	//close database connection
	mysql_close($conn);
	?>

As expected, each outer loop run should generate every inner loop run. This is not the case here. Only the first loop is successful and gets inserted into the DB.

 

Can anyone point me to directions where I can tackle this issue??

 

Thanks in advance.

[Jessica]

1. $index = $_POST["lstAssess$c"]; - you should be using an array, not a variable variable. This applies to the several places in your code you do this sort of thing.

2. $c = $c + 1; - can be $c++, fyi.

3. You can build a multi-insert statement rather than attempt to do many inserts.

4. Your insert is commented out anyway. ??

 

Finally, :codetags: !!

 

 

Now, what are you trying to do? Overall?

Edited October 1, 2012 by Jessica

[ManiacDan]

This code is so convoluted it's going to require YOU to do some debugging.

 

This is what it looks like to me:

 

Your code accepts X number of POST boxes, and doesn't check to see if they exist.

 

It then queries Y number of records from a database, and assumes X=Y

 

It then loops for C = 0 through C = Y-1, querying one of the X variables in POST to get the get the index of the headers of the CSV file somehow.

 

Once you have the index of the headers of a CSV (which is based on the order of the records in your mysql table keyed off the unseen POST variables), you then read an entire ROW from the csv file (despite the fact that the POST value determining the...

 

 

You know what, this is way too much for me to bother with. This is your problem:

 

As expected, each outer loop run should generate every inner loop run.

Whether or not you expect that to happen, this line doesn't do that:

while((${"fileop$i"} = fgetcsv($handle,10000,",")) !== false)

That goes to the end of the file and stops. You have to re-open the file or store it in memory if you want to run through the file X times.

 

Your use of variable variables, unknown numbers of un-validated POST fields, un-ordered queries, and reliance on everything to always match all the time makes this too much to figure out. Describe what you have, and what you want to happen. There's a better way.

[Psycho]

As expected, each outer loop run should generate every inner loop run. This is not the case here. Only the first loop is successful and gets inserted into the DB.

 

Your two loops are as follows:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

while((${"fileop$i"} = fgetcsv($handle,10000,",")) !== false)

 

Now, let's step through the logic. On the first iteration of the outer loop, the first record from $result is extracted. Then the inner loop will start by getting a line from the csv file. That inner loop continues as long as another line can be extracted from the csv file. Now, once the inner loop completes (i.e. no more lines can be read from the csv file the execution breaks out to the outer loop. So, on the second iteration of the outer loop the next record from $result is extracted and execution continues. But, now when the execution hits the second while loop it will skip it entirely since all the lines were already read from the file!

 

If you are wanting to create records for each line int he CSV file but have them duplicated for each record in the first query results you shoudl definitley take jesi's advice and build up a SINGLE query to create the records.

[Psycho]

OK, I've studies the code and I'm having some trouble understanding it all - the varaible variables definitly seem an over complication. I *think* this may work, but I'm sure there is a simpler solution if I really understood all the data and how it is used. Anyway, this will only echo the resulting single INSERT query. So, you can verify that it is generating the query before you actually execute it

 

if(!isset($_POST['submit']))
{
   echo "Error!";
}
else
{
   //Read CSV file into array
   $file = $_POST['filepath'];
   $handle = fopen($file, 'r');
   $fileop = fgetcsv($handle, 2000, ','); //read only headers (first row)
   $csvData = array();
   while(($csvLine = fgetcsv($handle,10000,",")) !== false)
   {
    $csvData[] = $csvLine;
   }

   // build and run qry to select data from assessment, assessmenttype tables
   $query = "SELECT assessmenttype.AssessType_Code, Assess_Num
		  FROM assessment
		  JOIN assessmenttype
		    ON assessmenttype.AssessType_Code = assessment.AssessType_Code
		  WHERE Offer_ID = '$offerId';";
   $result = mysql_query($query) or die('Query failed: ' . mysql_error());

   $c = 1;
   //Process query results into INSERT values
   $values = array();
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
    $assessNum = $row['Assess_Num'];
    $assessCode = $row['AssessType_Code'];
    $mark = $fileop[$_POST["lstAssess$c"]];
    //Loop through csv data to create insert values associated with this record
    foreach($csvData as $line)
    {
	    $values[] = "('$line[2]', '$offerId', '$assessNum', '$assessCode', '$mark')";
    }
    $c++;
   }

   //Create insert query
   $query = "INSERT INTO gradebook
			  (Stud_ID, Offer_ID, Assess_Num, AssessType_Code, GB_Mark)
		  VALUES " . implode(",\n", $vaules);
   //Debug line
   echo $query;
   //Execution line
   //$result = mysql_query($query) or die('Query failed: ' . mysql_error());
}

//close database connection
mysql_close($conn);