moosey_man1988 Posted June 26, 2015 Share Posted June 26, 2015 (edited) Hi peeps I have been using mysql commands for most of my php coding, and i've been told it would be wise to move to mysqli im trying to create a csv upload to database page but its just adding blank fields can someone tell me where im going wrong? as my code wont tell me <?php include('header.php'); ?> <!DOCTYPE html> <head> <title>Upload New Numbers files to the database</title> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head> <body> <form action="importData.php" method="post" enctype="multipart/form-data"> <input type="file" name="uploaded_file"><br> <input type="submit" value="Upload file"> </form> </body> </html> <?php error_reporting(E_ALL); ini_set('display_errors', 1); if (isset($_FILES['uploaded_file'])) { //Make sure the file was sent without errors if ($_FILES['uploaded_file']['error'] == 0) { //connect to the database $mysqli = new mysqli ('localhost','root','','PDNumbers'); if (mysqli_connect_errno()) { die("Mysqli connection failed: ". mysqli_connect_error()); } //gather all the required data from the CSV $file= $_FILES['uploaded_file']['tmp_name']; $handle = fopen($file, "r"); $row= 1; while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { if ($row == 1) { //skip the first row } else { //csv format like this //$data[0] = mobile number //$data[1] = landline //$data[2] = title //$data[3] = forename //$data[4] = surname //$data[5] = Address1 //$data[6] = Address2 //$data[7] = Address3 //$data[8] = blank field //$data[9] = Town //$data[10] = County //$data[11] = postcode } } } //$import_query = "INSERT IGNORE INTO LepusData (mobile,landline,title,forename,surname,Address1,Address2,Address3,Town,County,postcode) //VALUES ( //'".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."','".$data[4]."','".$data[5]."','".$data[6]."','".$data[7]."','".$data[8]."','".$data[10]."','".$data[11]."' //)"; $import_query = "INSERT INTO LepusData(mobile) VALUES ('".$data[0]."')"; $result = $mysqli->query($import_query); if ($result) { echo "Success! The file was added into the database"; } else { echo "Error! failed to insert the file"; } // close the mysqli connection $mysqli->close(); } else { echo "Error! The file was not sent!"; } ?> any help is greatly appreciated thanks oh forgot to add, I am ignoring data[9] on purpose, its a blank field Edited June 26, 2015 by moosey_man1988 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted June 26, 2015 Share Posted June 26, 2015 Do some things that experienced programmers do everyday. Add error checking when you do things that could go wrong. Things like opening up a file, or running a query. How do you know it actually happened if you don't check? When you read the manual ( you did read the manual?) didn't you wonder why many of these functions return a Boolean value? It's there so you can check the success or failure of that function. Make your life easier by using the list() function to capture the csv fields. This will give you real field names instead of an array with numeric indices. Makes it easier to follow what you are doing especially when doing debugging. BTW - why are you inserting only one field when you have all that data? Quote Link to comment Share on other sites More sharing options...
moosey_man1988 Posted June 26, 2015 Author Share Posted June 26, 2015 Well i changed it to one field to see if it would insert instead of blank fields, sorry i'm no pro at php that's for sure, I am okay at debugging php errors, but when it comes to debugging the mysql commands within php its where i get stuck, when i am working on a script i tend to tail -f /var/log/apache/error.log which usually gives me the info, but this one is working its just not putting in the entries, i'll have a look into php list thanks Quote Link to comment Share on other sites More sharing options...
ginerjm Posted June 26, 2015 Share Posted June 26, 2015 Are you checking the query result to see if it failed? If not, why do you think that is the problem? Quote Link to comment Share on other sites More sharing options...
moosey_man1988 Posted June 27, 2015 Author Share Posted June 27, 2015 well i tested the query in phpmyadmin but of course took out the $data variables and put in the top line from my CSV and it went in just fine, I am pretty new to php so im not the best at finding out issues yet, I'm going to take a loooong look through the script on monday and see if i can echo out the error as i really need to get this working. I have about 550 csv files to import, and i thought this will make my life easier lol, i found this so easy to do with mysql but mysqli is prooving more tricky to me, maybe because i dont understand its functions as well. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted June 27, 2015 Share Posted June 27, 2015 My previous question re: testing your query result was not answered. Do you in fact have code that tests the result of your query function call? Show us that code and also the complete query statement that you are trying to execute. Use an echo if you have to in case you are building it with php values in it. Something like this code: $q = 'select (stuff) from (table) where (condition)'; $qresult = $pdo->query($q); // test if query ran if (!$qresult) { echo "Error - could not execute query #1. Query is<br> $q"; exit(); } // process valid query results here. Note: the parens in the query statement are only there for example. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 28, 2015 Share Posted June 28, 2015 your code that's not commented out, that's using the $data values, is after the end of the while(){} loop. at that point, $data is a FALSE value. there is no $data[0], ... for the code to use. you should be getting 'undefined offset...' php error messages. also, the code inside your while(){} loop is never incrementing $row, so the code inside the loop, will skip every row. Quote Link to comment Share on other sites More sharing options...
moosey_man1988 Posted June 29, 2015 Author Share Posted June 29, 2015 hi guys, i dont know why i got in such a cuffufle with that code... I decided to re-write it with a fresh head on and its owrking perfectly now just got to get it to remove the first row, but thats easy <?php include('header.php'); ?> <?php error_reporting(E_ALL); ini_set('display_errors', 1); if (!$_POST) { ?> <html> <body> <form action="" method="post" enctype="multipart/form-data"> Choose your file: <br /> <input name="csv" type="file" id="csv" /> <br /> <br /> <input type="submit" name="Submit" value="Submit" /> </form> </body> </html> <?php } else { $connect = new mysqli("localhost","root","blah","PDNumbers"); if ($_FILES[csv][size] >0) { //get the csv file $file = $_FILES[csv][tmp_name]; $handle = fopen($file,"r"); $info = 0; while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $import = "INSERT IGNORE INTO LepusData (mobile,landline,title,forename,surname,Address1,Address2,Address3,Town,County,postcode) VALUES ( '$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[10]','$data[11]')"; $connect->query($import); } $info++; } fclose($handle); print "import done"; echo $info." records imported"; } ?> Thank you for all your help Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.