Re: postgresql csv import

On 13/09/2021 23:07, ourdiaspora wrote:

Readers, Please could someone help to explain why the following code fails: <?php $uploaddir = '~/tmp/cpac/cpactmp/'; $uploadfile = $uploaddir . basename($_FILES['userfile']['name']); $dbconnect = pg_connect("dbname=cpacweb user=cpaca"); $targetfilepath = $uploaddir . $uploadfile; if(isset($_POST["submit"])){ $csvfile=$_FILES["userfile"]["tmp_name"]; if($_FILES["userfile"]["tmp_name"] > 0) { $csvfileopen=fopen($csvfile, "r"); while(($csvfiledata=fgetcsv($csvfileopen, 10000, ","))){ $csvinsert = "INSERT into somedata(name, id, emailaddress, groupname) VALUES ('".$csvfiledata[0]."', '".$csvfiledata[1]."', '".$csvfiledata[2]."', '".$csvfiledata[3]."')"; } } fclose($csvfileopen); } $query = "SELECT * FROM somedata"; $result = pg_exec($dbconnect, $query); echo "Number of rows: " . pg_numrows($result); pg_freeresult($result); pg_close($dbconnect); ?> The trial csv file contains 2 rows of data, but the query result is zero.

It looks like you're creating the insert query string, but you're never doing anything with it, so the database _will_ be empty! If I may make some suggestions: You're checking to see if $_POST["submit"] is set, but you're not actually using anything from the POST data, you're directly using $_FILES instead, so it might be better checking that. Also, when you do end up executing the query, I'd avoid doing that from within a loop a single insert at a time, as that may be very slow. It's generally faster to do bulk inserts with your database. Finally, you're creating the insert query using standard string concatenation, with no sanitisation against bad input. Consider using PDO, which should automatically protect against SQL injection. It does also appear to have a method pgsqlCopyFromFile which might do what you're doing here. -- Ashley Sheridan https://www.ashleysheridan.co.uk