Uploading a csv error?

[lurtnowski]

On my site, I allow users to upload materials from a csv

after upload, I look at phpmyadmin…

Why is the ordering not the same?

[m_hutley]

Assuming you created the table and are letting users upload data to it… the column order is defined by the table definition.

You can reorder columns in the database by using ALTER TABLE syntax statements, but… it rarely actually matters what order the columns are in in the database.

[lurtnowski]

heres the csv file

How I create the table

CREATE TABLE materials (
   material_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   type VARCHAR(50),
   manufacturer VARCHAR(50),
   model VARCHAR(50),
   width DECIMAL(6,2),
   height DECIMAL(6,2),
   depth DECIMAL(6,2),
   ru_height TINYINT UNSIGNED,
   ports TINYINT UNSIGNED DEFAULT 0,
   notes TEXT DEFAULT 'None',
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50),
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   PRIMARY KEY ( material_id )
);

then…

[m_hutley]

and you can see that the headers on the database view match the CREATE TABLE statement’s order.

[lurtnowski]

if I look at the table after its emptied

then upload the csv,

mud doesn’t the csv file have manufacturer after type?

[m_hutley]

Emptying the table wont suffice; you will either have to DROP the table and re CREATE it in the order you want, or ALTER TABLE the structure to put the columns where you want them. The CSV does not dictate the order of columns to the database, no matter if its full or empty.

[m_hutley]

Though i will reiterate: The database’s ordering of columns usually only matters to you, the human looking at the PHPMyAdmin page.

if I have defined a table:

CREATE TABLE example (
  a VARCHAR(50),
  b VARCHAR(50),
  c INT(5)
);

the database doesnt care if i give it a CSV that has columns a,b,c or c,b,a or a,c,b, as long as it has a header row that tells the database “hey, this column of data goes into field a, this column goes into field b, and this column goes into field c”, it will fill my table correctly.

Likewise, when i query something from the database, if i tell it to SELECT c,b,a FROM example, it’s going to give me the fields, in that CBA order. If i tell it to SELECT b,c,a FROM example, it’s going to give me the fields, in BCA order. If i tell it SELECT * FROM example, it’s going to give me the fields in the order that the table was constructed in, because I didnt tell it what order to give me the fields in.

[lurtnowski]

Your right, thanks
just had to correct the page to handle the upload

<?php
session_start();

include '../db/pdo_conn.php'; 

	if($_SERVER["REQUEST_METHOD"] == "POST") {
		
		if(!file_exists($_FILES['Materials']['tmp_name']) || !is_uploaded_file($_FILES['Materials']['tmp_name'])) 
			{
				echo '<br><br><img src="../images/forgot.gif"><br><br>Whoops, did you forget to upload a csv file?<br><br>';
				echo '<a href="../index.php">Try again.,</a>';
			}   
			else
		   {
		      $handle = fopen($_FILES['Materials']['tmp_name'],'r');
			  
			  $sql = "INSERT INTO materials (name,type,manufacturer,model,width,height,depth,ru_height,ports) VALUES (:material_name,:material_type,:material_manufacturer,:material_model,:material_width,:material_height,:material_depth,:material_height_ru,:material_ports)";
			  
			  $statement = $pdo->prepare($sql);

			fgetcsv($handle); // read and ignore header
			
			while($data = fgetcsv($handle))  {
				  

				  $statement->execute([
					':material_name' => $data[0],
					':material_type' => $data[1],
					':material_manufacturer' => $data[2],
					':material_model' => $data[3],
					':material_width' => $data[4],
					':material_height' => $data[5],
					':material_depth' => $data[6],
					':material_height_ru' => $data[7],
					':material_ports' => $data[8]
				   ]);

			  }

			fclose($handle);

			header("location: upload_materials_success.php");

			}			
	}