Help with PHP script

[Royal]

Hello Guys,

Please i would some help on an issue i am having on my code, the code basically is to insert data into a table from a CSV file, the code i wrote successfully inserts the data onto the database but when it comes to updating the data it keeps inserting a particular record out of all that was supposed to be updated from the CSV fIile.

Below is the screenshot of the CSV file

Also below is the code for inserting data into the db by uploading the CSV file above:

<?php
 if(isset($_POST["upload_by_subject"])){
 	$class = $_POST['class'];
 	$term =$_POST['term'];
 	$year = $_POST['year'];
	$check = "SELECT * FROM student_exam_result where class = '$class' and term = '$term' and year = '$year'";
	$result=mysqli_query($conn,$check);
	$row=mysqli_fetch_assoc($result);
	$exam_class = $row['class'];
	$exam_term =  $row['term'];
	$exam_year =  $row['year'];
	$subject1 = $row['subject1'];
	$subject1_test_score = $row['subject1_test_score'];
	$subject1_exam_score = $row['subject1_exam_score'];		
		if (empty($subject1) && empty($subject1_test_score) && empty($subject1_exam_score)) {
	         		 $filename=$_FILES["result_csv"]["tmp_name"];
	        if($_FILES["result_csv"]["size"] > 0)
		 			{
		  				$file = fopen($filename, "r");
	        			while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
	        				
	         		{
	         		$sql = "INSERT INTO  student_exam_result (class,index_number,term,subject1,subject1_test_score,subject1_exam_score) values ('".$getData[0]."','".$getData[1]."','".$getData[2]."','".$getData[3]."','".$getData[4]."','".$getData[5]."','".$getData[6]."')"; 
						$result1 = mysqli_query($conn, $sql);
			if(!isset($result1))
					{
					echo "<script>alert(Class Result CSV Was not Uploaded, Please try again!')</script>";
					echo "<script>window.location = 'upload_class_result_teacher.php';</script>";		
					}
					else {
					 echo "<script>alert('Class Result CSV Was Uploaded Successfully')</script>";
					echo "<script> window.location = 'upload_class_result_teacher.php';</script>";
					}}
				fclose($file);
			}
	?>

Below is a screenshot  result of the insert code above which works fine with the CSV file upload:

 

While below is the code for updating the table using the same CSV file:

<?php
 if(isset($_POST["upload_by_subject"])){
 	$class = $_POST['class'];
 	$term =$_POST['term'];
 	$year = $_POST['year'];
	$check = "SELECT * FROM student_exam_result where class = '$class' and term = '$term' and year = '$year'";
	$result=mysqli_query($conn,$check);
	$row=mysqli_fetch_assoc($result);
	$exam_class = $row['class'];
	$exam_term =  $row['term'];
	$exam_year =  $row['year'];
	$subject1 = $row['subject1'];
	$subject1_test_score = $row['subject1_test_score'];
	$subject1_exam_score = $row['subject1_exam_score'];		


	         	if (empty($subject1) && empty($subject1_test_score) && empty($subject1_exam_score)) {
	         		 $filename=$_FILES["result_csv"]["tmp_name"];
	         		 
	         		 if($_FILES["result_csv"]["size"] > 0)
		 			{
		  				$file = fopen($filename, "r");
	        			while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
	         		{

	         		$sql = "UPDATE  student_exam_result SET name = '$getData[0]', class = '$getData[1]',index_number = '$getData[2]',term = '$getData[3]',subject1 = '$getData[4]',subject1_test_score = '$getData[5]',subject1_exam_score = '$getData[6]' WHERE class = '$exam_class' and term = '$exam_term' and year = '$exam_year'"; 
						$result1 = mysqli_query($conn, $sql);
					if(!isset($result1))
					{
					echo "<script>alert(Class Result CSV Was not Uploaded, Please try again!')</script>";
					echo "<script>window.location = 'upload_class_result_teacher.php';</script>";		
					}
					else {
					 echo "<script>alert('Class Result CSV Was Uploaded Successfully')</script>";
					echo "<script> window.location = 'upload_class_result_teacher.php';</script>";
					}

					
				}
				fclose($file);
			}
    ?>

And below is the screenshot for the update result from the table in the db, here only one record from the CSV file gets inserted multiple times unlike the insert part:

I have tried to tweak the code severally but it keeps giving the same output on the update case, please anyone that has a better solution to it should help profer it and i will appreciate the help. Thanks

[mac_gyver]

the WHERE clause in the UPDATE query does not identify a single row. the UPDATE query is repeatedly updating all the rows matching the class, term, and year value with the new data, so it first updates all three rows with the first row of data, the second row of data, then the third, leaving all three rows with the third/last set of data in them.

when a row of data should be updated, what columns can contain new values and what columns identify the row of data? note: you won't include the columns that are in the WHERE clause in the SET ... list, because by definition, they won't have different values in them, because they are what identifies the row data.

also, don't use columns that end in numerical sequences. this indicates that you are trying to threat the database as a spreadsheet, which will result in overly complicated code and queries to accomplish any task. your database design should have one row for each data item. you would have a (one) subject column and a (one) test score column. you would insert a separate row for each test/test score. such data would also have a datetime column that indicates when the test/test score occurred.

[Royal]

Are you saying that the WHERE clause is the cause of the repetition?  Here i still tried it with out the where clause yet same repetitive result 

Can you proffer a better way to go about it please

<?php
 if(isset($_POST["upload_by_subject"])){
 	$class = $_POST['class'];
 	$term =$_POST['term'];
 	$year = $_POST['year'];
	$check = "SELECT * FROM student_exam_result where class = '$class' and term = '$term' and year = '$year'";
	$result=mysqli_query($conn,$check);
	$row=mysqli_fetch_assoc($result);
	$exam_class = $row['class'];
	$exam_term =  $row['term'];
	$exam_year =  $row['year'];
	$subject1 = $row['subject1'];
	$subject1_test_score = $row['subject1_test_score'];
	$subject1_exam_score = $row['subject1_exam_score'];		
		if (empty($subject1) && empty($subject1_test_score) && empty($subject1_exam_score)) {
	         		 $filename=$_FILES["result_csv"]["tmp_name"];
	        if($_FILES["result_csv"]["size"] > 0)
		 			{
		  				$file = fopen($filename, "r");
	        			while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
	        				
	         		{
	         		$sql = "UPDATE  student_exam_result SET name = '$getData[0]', class = '$getData[1]',index_number = '$getData[2]',term = '$getData[3]',subject1 = '$getData[4]',subject1_test_score = '$getData[5]',subject1_exam_score = '$getData[6]', grand_total = '$grand_total'"; 
						$result1 = mysqli_query($conn, $sql);
			if(!isset($result1))
					{
					echo "<script>alert(Class Result CSV Was not Uploaded, Please try again!')</script>";
					echo "<script>window.location = 'upload_class_result_teacher.php';</script>";		
					}
					else {
					 echo "<script>alert('Class Result CSV Was Uploaded Successfully')</script>";
					echo "<script> window.location = 'upload_class_result_teacher.php';</script>";
					}}
				fclose($file);
			}
          ?>

 

[kicken]

33 minutes ago, Royal said:

Here i still tried it with out the where clause yet same repetitive result

No where clause will update the entire table.  Every row.

The issue is that the where clause you specified is not specific enough to identify a single row of the table.  If you change your update to a select * with the same where clause you'll see that you get multiple rows in the result.  All those rows will get updated by your update query.

You need to add more or different conditions that will limit the update to a specific row (such as by using the id column for example).

 

[Royal]

Ok so I need to first select the row data that will be updated using the Select * query, then assign varriables to the selected details which I will then in turn update using the update query?

[phppup]

You should not use the asterisk * to select EVERYTHING unless you NEED everything pulled from each row of the table. Only SELECT what is required for reaching your goal.

SELECT merely pulls data.

UPDATE will overwrite columns. WHERE will tell the table which SPECIFIC cells should be effected.

You can search for helpful tutorials for SELECT, UPDATE and WHERE online.

Edited January 7, 2023 by phppup
Clean up post

[kicken]

1 hour ago, Royal said:

Ok so I need to first select the row data that will be updated using the Select * query, then assign varriables to the selected details which I will then in turn update using the update query?

Generally, no.  What you need to do is figure out what criteria you can use to specify the row you want updated and then use that in your where condition to specify that row.  Let's go back to just your CSV files.  You start with this:

name,class,index_number,term,subject1,subject1_test_score,subject1_exam_score,year
acho c jubilee,js1,4,first term,maths,24,66,2022
eke g linda,js1,5,first term,maths,24,66,2022
anacho prince,js1,7,first term,maths,24,66,2022

So you load that into your DB and have your three rows with that data.  Now you copy that csv and change the copy to:

name,class,index_number,term,subject1,subject1_test_score,subject1_exam_score,year
eke g linda,js1,5,first term,maths,50,100,2022
anacho prince,js1,7,first term,maths,50,100,2022
acho c jubilee,js1,4,first term,maths,50,100,2022

Open the two CVS's side-by-side then and look at them.  Look at the first row of your new CSV file.  How can you find the matching row in the original CSV file?  Which fields do you need to compare to narrow the list down to a single row?

Right now, you're looking at the class (js1), term (first term), and year (2022) columns.  That doesn't eliminate anything though, all the rows in your original CSV file have those same values.  You need something else.

Once you've identified which criteria you can use to match the rows 1-to-1, you use that same criteria in your update statement's where clause so it can update that row.

[Royal]

I appreciate your input so much, but my issue is, the updating is working but it updates every row in the table with just one data row from the csv... But when I do insert all the data from the csv gets inserted into there different rows in the table! So basically what I need is help to also update the table rows according to the csv rows and not one CSV data populating the entire table rows

[Barand]

15 minutes ago, Royal said:

what I need is help to also update the table rows according to the csv rows and not one CSV data populating the entire table rows

Have you read @kicken's posts? He's told you what's wrong and what you need to do.

The bigger problem for me is that given a CSV record with one test score value and a table (spreadsheet!) with 9 test score value columns, how do you know which of the 9 to update?