Loading a csv file into myphpadmin using php

Beginners - Learning PHP
#1

I have looked around a few sites but I am struggling to get a site set up where a csv file that has been created by another program can be loaded into phpmyadmin when a user browses to the csv file using php. This could be due to my lack of knowledge and understanding. I understand looking at other bits of code that you can check the file has a csv extension and if not it generates an error.Can it be in sqli object oriented format as this is the way I have be taught php

Any help would be much appreciated and an idiots guide to explain what the code is doing would be great so I can may helpful comments on my site.

#2

Let’s be clear about what you want to do. Is this for YOU to be able to browse to a csv file and load it via phpmyadmin or for anyone on your website to do it? What is the end result you want to accomplish? Are you trying to populate a table? Create a temporary table? I don’t think you’ll be able to use the data directly from phpmyadmin as it is designed to interface with a mysql database.

#3

This is for anyone to load a csv via my website. The headings on the csv will be the same titles, it will just be the data and the size that will change. The end reason for this, is to run various queries to get reports, that can be saved in excel for emailing as attachments later.

#4

How do you intend to validate the data being imported? What database role have you assigned to these anonymous users? Website logins aren’t sufficient to be used by phpmyadmin. How are you going to deal with multiple users importing data at the same time to run these queries? These are all questions that need to be ironed out before you start worrying about how “anyone” can import a csv file so it can be processed.

#5

OK, so looking at various forums, I have managed to get read a csv file using php. The only thing I cannot work out is how to import the data into a table so I can sort it accordingly

the code I have so far is

<?php
$db	=	new mysqli('localhost','root','','test');
if ($db->connect_errno) {
  echo "Failed to connect to MySQL: " . $db->connect_error;
  exit();
}
?>

<table align="center" width="800" border="1" style="border-collapse:collapse; border:1px solid #ddd;" cellpadding="5" cellspacing="0">
	<thead>
		<tr bgcolor="#FFCC00">
			<th>Colour</th>
			<th>StartsDateOnly</th>
			<th>StartsTimeOnly</th>
			<th>EndsDateOnly</th>
			<th>EndsTimeOnly</th>
			<th>Calendar</th>
			<th>Description</th>
			<th>Starts</th>
			<th>Ends</th>
			<th>AllDay</th>
			<th>Notes</th>
			<th>Location</th>
			<th>Private</th>
			<th>Contacts</th>
			<th>CreatedDate</th>
			<th>CreatedbyUser</th>
			<th>UpdatedDate</th>
			<th>UpdatedbyUser</th>
			<th>ReminderEmailRecipients</th>
			<th>ReminderSMSRecipients</th>
			<th>ReminderPopupRecipients</th>
			<th>ReminderSubject</th>
			<th>ReminderBody</th>
			<th>ReminderSendDue</th>
			<th>Categories</th>
			<th>Categorieswithgroups</th>
			<th>UniqueID</th>
			<th>test1</th>
			<th>test2</th>
			<th>test3</th>
			<th>test4</th>
			<th>test5</th>
			<th>test6</th>
			<th>test7</th>
			<th>test8</th>
			<th>test9</th>
			
			
			
		</tr>
	</thead>
	<tbody>
	<?php
if(($handle		=	fopen("csv3.csv", "r")) !== FALSE){
	$n			=	1;
	while(($row	=	fgetcsv($handle)) !== FALSE){
		tried insert statement here but nothing is shown in the table
		if($n>1){
					?>
				<tr>
					<td><?php echo $row[0];?></td>
					<td><?php echo $row[1];?></td>
					<td><?php echo $row[2];?></td>
					<td><?php echo $row[3];?></td>
					<td><?php echo $row[4];?></td>
					<td><?php echo $row[5];?></td>
					<td><?php echo $row[6];?></td>
					<td><?php echo $row[7];?></td>
					<td><?php echo $row[8];?></td>
					<td><?php echo $row[9];?></td>
					<td><?php echo $row[10];?></td>
					<td><?php echo $row[11];?></td>
					<td><?php echo $row[12];?></td>
					<td><?php echo $row[13];?></td>
					<td><?php echo $row[14];?></td>
					<td><?php echo $row[15];?></td>
					<td><?php echo $row[16];?></td>
					<td><?php echo $row[17];?></td>
					<td><?php echo $row[18];?></td>
					<td><?php echo $row[19];?></td>
					<td><?php echo $row[20];?></td>
					<td><?php echo $row[21];?></td>
					<td><?php echo $row[22];?></td>
					<td><?php echo $row[23];?></td>
					<td><?php echo $row[24];?></td>
					<td><?php echo $row[25];?></td>
					<td><?php echo $row[26];?></td>
					<td><?php echo $row[27];?></td>
					<td><?php echo $row[28];?></td>
					<td><?php echo $row[29];?></td>
					<td><?php echo $row[30];?></td>
					<td><?php echo $row[31];?></td>
					<td><?php echo $row[32];?></td>
				</tr>
				<?php
			}
			$n++;
		}
		fclose($handle);
	}
	?>
	
	
	
	</tbody>
</table>
#6

You would need to post the code you tried to get any specific help with it and you would need to use a prepared query to insure that any sql special characters in the external, unknown, dynamic data values won’t break the sql query syntax, which is how sql injection is accomplished.

If you have more than about 2-3 similar meaning values, that will all be operated on in the same way, don’t write out code/sql for each value. Instead, use general purpose code to operated on the data as a set and dynamically build the sql query statement or output all the elements in the set by looping over a definition of the expected data.

Also, how are the csv files getting onto the server? You mentioned browsing to them? Are they being uploaded via a html form?

#7

An additional point for the above - the formatting of the lines in csv file can be anything. You should trim() the values before using them. This can be done with a single line of code, using array_map().

#8

For the moment, the csv file is being manually placed on the server for testing purposes in the htdocs folder. I will be adding code for the user to browse to where the file is located using a HTML form.

#9

When you mention about preparing a query if I understand correctly we are looking at this example from w3 schools?

https://www.w3schools.com/php/php_mysql_prepared_statements.asp

#10

Yes. That would be how you would do a prepared query using the hideously bad mysqli extension. If you switch to the much simpler and more consistent PDO extension, a prepared query is simper because you can just prepare it, then supply an array of the input values to the ->execute([…]) call.

#11

Any good example of the PDO?

#12

I guess I didn’t scroll down on the w3fools page. It contains a PDO example, but don’t do it that way either. Their examples, notes, and tips are a case study in how not to do things and contain wrong information.

If you use the PDO extension, here’s a bunch of best practices -

  1. When you make the database connection -
    1.1 Set the character set to match your database table(s).
    1.2 Contrary to the Note on the w3fools site, you don’t need to specify a database when you make the connection. You should however select a database if you have a specific database you want to use. You either need to select a database before running a query that requires a database to be selected or you need to use the database.table syntax in a query.
    1.3 Set the error mode to exceptions (this is the only thing the w3fools site is doing that is worth keeping.)
    1.4 Set emulated prepared queries to false. You want to run real prepared queries.
    1.5 Set the default fetch mode to assoc, so that you don’t need to specify it in every fetch statement.
  2. Don’t have try/catch blocks for database exceptions UNLESS there’s a possibility of an error that the visitor to a site can do something about, such as when inserting/updating duplicate or out of range values, and don’t unconditionally echo the raw database errors onto a web page, since this just gives hackers useful information when they do things that can intentionally trigger errors.
  3. In most cases, there’s no need to close prepared queries, close database connections, or free up result sets, since php destroys all resources when the script ends.
  4. Build the sql query statement in a php variable. This makes debugging easier, you can echo the sql to see what it is, and by separating the sql syntax as much as possible from the php statements, you will have less typo mistakes and you will see that there are common sets of php statements that you can then move into user written classes/methods/functions to help eliminate repetition in the code.
  5. Use the simpler ? prepared query place-holders.
  6. Don’t use explicate binding, with bindParam/bindValue statements written out for every value. This is just a waste of typing and resources. Instead, use implicit binding, by simply supplying an array of the values to the execute() call.
#13

So I found this example

I see some code which reads

// Read and throw away the header row
fgetcsv($file);

$stmt = $pdo->prepare(“INSERT INTO members (name, residential_address, mailing_address, precinct, age, ethnicity, gender, party, race, phone) VALUES (:name, :residential_address, :mailing_address, :precinct, :age, :ethnicity, :gender, :party, :race, :phone)”);

while (! feof($file)) {
$row = fgetcsv($file);
$stmt->bindParam(’:name’, $row[0]);
$stmt->bindParam(’:residential_address’, $row[1]);
$stmt->bindParam(’:mailing_address’, $row[2]);
$stmt->bindParam(’:precinct’, $row[3]);
$stmt->bindParam(’:age’, $row[4]);
$stmt->bindParam(’:ethnicity’, $row[5]);
$stmt->bindParam(’:gender’, $row[6]);
$stmt->bindParam(’:party’, $row[7]);
$stmt->bindParam(’:race’, $row[8]);
$stmt->bindParam(’:phone’, $row[9]);
$stmt->execute();

}

fclose($file);

?>

following the thread of the conversation, Is this what I am looking to do with my insert statement?

#14

Only in the most general terms, yes, it will look something like that. That OP has a current thread, 9 months later, where he is trying to use that same code, but because it is using the feof() as the loop condition and has no checking if the file was opened, it is locking up and only stops when the maximum runtime is exceeded. Reproducing and trying to make things work that you have found somewhere isn’t effective programming. It will take you 10, 50, 100 times longer to make code like this work than it will take you to actually learn the fundamentals and write just the code that you need.

#15

So to clarify the prepare statement and the bindparam should go between my while , if loop including the execute?

#16

Did you read my reply. You already have an array of the values, in $row, inside the loop. All you need to do inside the loop is call the ->execute(…) method with $row as the parameter.

#17

Forgive my lack of understanding, do you have code example of what you are say?

Sponsor our Newsletter | Privacy Policy | Terms of Service