Upload data to a mysql database

lurtnowski · June 8, 2025, 10:19pm

Im getting

**Fatal error** : Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'manufacturer' in 'field list' in C:\xampp\htdocs\SST\upload_sql_engine.php:26 Stack trace: #0 C:\xampp\htdocs\SST\upload_sql_engine.php(26): PDOStatement->execute() #1 {main} thrown in **C:\xampp\htdocs\SST\upload_sql_engine.php** on line **26**

on

  if($_SERVER["REQUEST_METHOD"] == "POST") {

 if($_FILES["fileToUpload"]["name"] != '')
 {
  $array = explode(".", $_FILES["fileToUpload"]["name"]);
  $extension = end($array);
  if($extension == 'sql')
  {
   include 'db/pdo_conn.php'; 
   $output = '';
   $count = 0;
   $file_data = file($_FILES["fileToUpload"]["tmp_name"]);
   foreach($file_data as $row)
   {
    $start_character = substr(trim($row), 0, 2);
    if($start_character != '--' || $start_character != '/*' || $start_character != '//' || $row != '')
    {
     $output = $output . $row;
     $end_character = substr(trim($row), -1, 1);
     if($end_character == ';')
     {
	  $stmt = $pdo->prepare($row);
	  
      if(!$stmt->execute())
      {
       $count++;
      }
      $output = '';
     }
    }
   }
   if($count > 0)
   {
	  header("location: ?add_data_error");
   }
   else
   {
	  header("location: ?add_data_success");
   }
  }
 }
}  if($_SERVER["REQUEST_METHOD"] == "POST") {

 if($_FILES["fileToUpload"]["name"] != '')
 {
  $array = explode(".", $_FILES["fileToUpload"]["name"]);
  $extension = end($array);
  if($extension == 'sql')
  {
   include 'db/pdo_conn.php'; 
   $output = '';
   $count = 0;
   $file_data = file($_FILES["fileToUpload"]["tmp_name"]);
   foreach($file_data as $row)
   {
    $start_character = substr(trim($row), 0, 2);
    if($start_character != '--' || $start_character != '/*' || $start_character != '//' || $row != '')
    {
     $output = $output . $row;
     $end_character = substr(trim($row), -1, 1);
     if($end_character == ';')
     {
	  $stmt = $pdo->prepare($row);
	  
      if(!$stmt->execute())
      {
       $count++;
      }
      $output = '';
     }
    }
   }
   if($count > 0)
   {
	  header("location: ?add_data_error");
   }
   else
   {
	  header("location: ?add_data_success");
   }
  }
 }
}  if($_SERVER["REQUEST_METHOD"] == "POST") {

 if($_FILES["fileToUpload"]["name"] != '')
 {
  $array = explode(".", $_FILES["fileToUpload"]["name"]);
  $extension = end($array);
  if($extension == 'sql')
  {
   include 'db/pdo_conn.php'; 
   $output = '';
   $count = 0;
   $file_data = file($_FILES["fileToUpload"]["tmp_name"]);
   foreach($file_data as $row)
   {
    $start_character = substr(trim($row), 0, 2);
    if($start_character != '--' || $start_character != '/*' || $start_character != '//' || $row != '')
    {
     $output = $output . $row;
     $end_character = substr(trim($row), -1, 1);
     if($end_character == ';')
     {
	  $stmt = $pdo->prepare($row);
	  
      if(!$stmt->execute())
      {
       $count++;
      }
      $output = '';
     }
    }
   }
   if($count > 0)
   {
	  header("location: ?add_data_error");
   }
   else
   {
	  header("location: ?add_data_success");
   }
  }
 }
}

mabismad · June 8, 2025, 11:27pm

Have you actually checked what the sql query statement is, that is being executed? Hint: it won’t be in $row (for statements that are made up of multiple lines.)

The code has a number of problems -

The [“name”] element being a non empty string doesn’t guarantee that the upload was successful. You must test the [“error”] element to insure that the upload was successful.
The conditional logic to process the lines is incorrect. The logic to skip/ignore lines would be - if($start_character == '--' || $start_character == '/*' || $start_character == '//' || $row == ''). The complement of this, to keep/process lines, would be - if($start_character != '--' && $start_character != '/*' && $start_character != '//' && $row != '')
As already hinted at, $row won’t contain the full sql query statement. Use $output.
Modern php (8+) uses exceptions for database statement errors by default, and there’s no point in having any conditional logic testing the result of database statements, e.g. the ->execute() call. If execution continues past a statement that can throw an exception, you know that there was no error, without needing any conditional logic.

lurtnowski · June 9, 2025, 4:29am

Is this better,

<?php 
if($_SERVER["REQUEST_METHOD"] == "POST") {

 if($_FILES["fileToUpload"]["error"] == 0)
 {
  $array = explode(".", $_FILES["fileToUpload"]["name"]);
  $extension = end($array);
  if($extension == 'sql')
  {
   include 'db/pdo_conn.php'; 
   $output = '';
   $count = 0;
   $file_data = file($_FILES["fileToUpload"]["tmp_name"]);
   foreach($file_data as $row)
   {
    $start_character = substr(trim($row), 0, 2);
    if($start_character != '--' || $start_character != '/*' || $start_character != '//' || $row != '')
    {
     $output = $output . $row;
     $end_character = substr(trim($row), -1, 1);
     if($end_character == ';')
     {
		try {
			
			$result = $dbh->query($output);
			
		} catch(Exception $e) {
			
			echo $e->getMessage();		
			
		}	 
     }
    }
   }
  }
 } 
header("location: ?add_data_success");
}
?>

I thought id first rule out comments or a blank llne, and if the last character is a ;. (then its a query)

m_hutley · June 9, 2025, 9:16am

As mab points out (#2), this will still fail.

Consider it this way: Let’s simplify the statement.

    if($start_character != 'Al' || $start_character != 'Be')
    {

If I give you the string “Alpha”. What happens?
if starts looking at its test.
The test is divided by an OR. <something> OR <something>. Evaluate the left something first.
$start_character != 'Al' is FALSE. Evaluate the OR.
$start_character != 'Be' is TRUE.

FALSE OR TRUE = TRUE.

What if i give it “Beta”
$start_character != 'Al' is TRUE. At this point, the condition short circuits - you have a TRUE OR <something> . It doesnt matter what the second <something> is, the first TRUE means the result will be TRUE.

What if i give it “Gamma”?
$start_character != 'Al' is TRUE. At this point, the condition short circuits again, for the same reason.

In fact, no matter what string i give it, the answer will always be TRUE, because you’ve given two disjointed sets and then asked for the sum of their inversions.

NOT A OR NOT B will only be false when the element being examined lies within both A and B. If A and B do not overlap, then all values will be TRUE (because they will belong to either A or B.)

NOT A AND NOT B will be false when the element to be considered lies within either A or B.

(What you are trying to do in english is “NOR”. In mathematical terms, trying to invert a logical statement inverts the operation: Not (A OR B) becomes Not A AND Not B.

EDIT: Pictures. Pictures always help. White = FALSE, Colors = TRUE.

Thallius · June 9, 2025, 10:37am

I can’t beliefe that you really want to execute a sql query created from the content of an uploaded file….

m_hutley · June 9, 2025, 11:06am

So you’ve never seen phpMyAdmin’s File to Import function?

Thallius · June 9, 2025, 11:32am

But that’s not an PHP script as part of a website or application. Also a production database should never be accessible with phpMyAdmin if you ask for my opinion.

m_hutley · June 9, 2025, 11:35am

phpMyAdmin isnt a PHP script loaded in a web browser? Huh. News to me…

And the OP hasnt said they’re doing this in production. It’s a bad idea to do it in production, I agree, but assuming facts not in evidence. (The user’s directory path indicates this file is in XAMPP, which i would also argue is a bad idea to do in production…)

mabismad · June 9, 2025, 1:57pm

Only slightly.

If there is no $_FILES data at all (an empty array), an == 0 comparison will be true (and also produce undefined index errors) and the code will try to use a non-existent uploaded file.

So, why would there not be any $_FILES data. There are three possible reasons -

The total size of the form data exceeds the post_max_size setting. This is the most common case, and your form processing code should (always) test to make sure there is $_FILES and/or $_POST data before referencing the form data. If there is no data, you should setup and display a message for the user that the form could not be processed.
The form markup isn’t valid or doesn’t contain a valid type=‘file’ field.
Uploads are not enabled on the server.

Once you have tested that there is $_FILES data, you can test the [“fileToUpload”][“error”] element. If it is not a zero, you need to setup and display messages for the user for those things that the user has control over, such as the size of the file being greater than the upload_max_filesize setting, aborting an upload, or not selecting a file.

As has already been pointed out by @m_hutley , you made no attempt at using the corrected logic to process the lines from the file. Depending on what is in your .sql file (you didn’t include an example), the sql error you are getting could be due to executing a query that is incomplete.

Per the first post in this thread, isn’t your database connection variable named $pdo? Are you actually running the code you are posting?

lurtnowski · June 14, 2025, 11:33pm

ok, heres my form

<form action="upload_sql_engine.php" method="post" enctype="multipart/form-data">
<label for="fileToUpload">Test Data</label>
<input type="file" name="fileToUpload" id="fileToUpload" accept=".sql" required>
<input type="submit" value="Upload" name="submit">
</form>

From there here is my thinking of the php page to handle the upload (I’ll be the only one using this form so the sql file will only have valid insert statements, but i’d like to put comments there)

First test to see if the form has been submitted
Then make sure a file has been uploaded
Test to see if its extension is sql
If it is we connect to the database ($dbh is my connection variable), and convert the uploaded file to an array (of rows.
Then we test each row to skip and comments or blank lines (this is where I am confused)
if the row starts with – OR /* OR // OR the row is blank
Since were looking skip these rows, I thought just to change == to !=
Like (x != – OR /* OR // OR is no line) then do something cause either its a comment or blank
so only my INSERT statements pass T,T,T,T
– becomes F,T,T,T which pass
/* becomes T,F,T,T which pass
// becomes T,T,F,T which pass
becomes T,T,T,F which pass

BUT (x != – AND /* AND // AND is no line) then do something cause either its a comment or blank
so only my INSERT statements pass T,T,T,T
– becomes F,T,T,T which fail
/* becomes T,F,T,T which fail
// becomes T,T,F,T which fail
becomes T,T,T,F which fail
Is that rite?
then I look at the row, if it ends in a ;, run the query+

<?php 
if($_SERVER["REQUEST_METHOD"] == "POST") {

 if($_FILES["fileToUpload"]["error"] == 0)
 {
  $array = explode(".", $_FILES["fileToUpload"]["name"]);
  $extension = end($array);
  if($extension == 'sql')
  {
   include 'db/pdo_conn.php'; 
   $output = '';
   $count = 0;
   $file_data = file($_FILES["fileToUpload"]["tmp_name"]);
   foreach($file_data as $row)
   {
    $start_character = substr(trim($row), 0, 2);
    if($start_character != '--' || $start_character != '/*' || $start_character != '//' || $row != '')
    {
     $output = $output . $row;
     $end_character = substr(trim($row), -1, 1);
     if($end_character == ';')
     {
		try {
			
			$result = $dbh->query($output);
			
		} catch(Exception $e) {
			
			echo $e->getMessage();		
			
		}	 
     }
    }
   }
  }
 } 
header("location: ?add_data_error");
}
header("location: ?add_data_success");

?>

m_hutley · June 15, 2025, 9:28am

See my previous comments. Mathematically, you cant just insert a NOT if you want the statement to hold.

X OR Y must become NOT X AND NOT Y.

Think of it like multiplication…

NOT * (X OR Y) = (NOT * X) (NOT * OR) (NOT * Y)
NOT * OR = AND.
(And, at the same time, NOT * AND = OR)

(Note that because NOT (X OR Y) = NOT X AND NOT Y, You can negate the entire thing in code and get the same result. These two statements are the same:
if (x != 1 && x != 2) {
if (!(x == 1 || x == 2)) {

mabismad · June 15, 2025, 2:15pm

Are you actually running this code and observing what the sql query statement is, so that you would know if the logic is correct? Hint: use echo $output.'<br>'; at the point of executing the query.

You have also removed the line of code setting $output=''; after the point of executing the query, that is in the original code.

You should add FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES as the 2nd parameter in the file() statement to, well, ignore new-line characters and skip empty lines in the file.

Zensei · June 15, 2025, 3:52pm

Notice how with || it does not work properly (as @m_hutley explained)

Changing to &&:

I used this to test the code:

https://onlinephp.io/

<?php
$row = "-- Insert into customer values(1, 2, 3)";
$start_character = substr(trim($row), 0, 2);
$should_run_sql = $start_character != '--' && $start_character != '/*' && $start_character != '//' && $row != '';

if($should_run_sql)
{
	print("Run SQL: ");
	print($row);
} 
else 
{
	print("Contains special characters at the beginning: ");
	print($row);
}
?>

Topic		Replies	Views
Shows this error when i connect with PDO PHP	3	698	July 25, 2021
I got a fatal error on INSERT Databases	3	1074	March 17, 2020
PHP Fatal Error PHP	3	874	June 27, 2017
Unknown column 'Data_Distance' in 'field list' PHP	12	2943	April 28, 2010
Fatal error: Uncaught exception 'PDOException' with message PHP	4	1667	January 13, 2016

Upload data to a mysql database

Related topics