jeeves245

A lot of my $parts values will be empty. Is there a way to modify the condition so it only ignores it when an entire line is empty?

Sorry not too sure I get what you mean? In the CSV file when I open it in notepad++ the cursor always starts on a new line at the bottom of the page. When I press backspace and get rid of that line, it uploads fine. So I need a way to make PHP ignore that line completely.

Hey guys, I have this script to upload a CSV file into the database. The CSV file is saved from an Excel spreadsheet file manually (the script doesn't do this). But for some reason when adding the CSV file values to the DB, it creates a couple of extra rows each time (possibly because Excel is saving blank lines or something). Is there any way to make the script ignore any blank spaces/lines in the CSV file when uploading it? <?php $target_path = "./uploads/"; $target_path = $target_path . basename( $_FILES['uploadedfile']['name']); $con = mysql_connect("localhost","user","pass"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("db", $con); $file_handle = fopen("./uploads/" . basename( $_FILES['uploadedfile']['name'])."", "rb"); while (!feof($file_handle) ) { $sql="INSERT INTO smail (deliveryDate, psNumber, numItems, volume, customerName, address1, address2, address3) VALUES ('$datestore','$parts[2]','$parts[3]','$parts[7]','$parts[1]','$parts[4]','$parts[5]','$parts[6]')"; mysql_query($sql); } fclose($file_handle); ?> Thanks in advance.

Any other ideas?

Hm... The primary key is psNumber which already contains a unique value. Plus if I change it i'm going to have to rewrite code to suit.

(Sorry if this should be in the database forum) I've been testing my program out over the last few hours to make sure there's no errors, and unfortunately I found a big one that I can't explain. Hoping for a bit of help.. i'll explain the best I can. I have a database with the the fields psNumber (primary key) and estimatedDelivery (among others, but let's keep it simple). Values are put into the DB from a CSV file, then outputted back to the browser when the user requests it. When I go to upload the file, I get the error "Error: Duplicate entry '17273(or some other psNumber value)' for key 1" even though there are no duplicate values in the CSV file. But even with the error, all information still uploads correctly to the database. The real problem comes when outputting the values back to the browser. For some reason all rows are pulled out and put in the HTML table except 1. No idea why it's missing just 1 row. Hopefully someone can shed some light on this? This code opens the CSV file and puts everything into the database (obviously i'm not including ALL the code, just what should matter) $file_handle = fopen("./uploads/" . basename( $_FILES['uploadedfile']['name'])."", "rb"); while (!feof($file_handle) ) { $line_of_text = fgets($file_handle); $parts = explode(',', $line_of_text); $sql="INSERT INTO db (deliveryDate, psNumber, numItems, volume, estimatedDelivery, customerName, address1, address2, address3) VALUES ('$parts[0]','$parts[3]','$parts[4]','$parts[8]','$parts[1]','$parts[2]','$parts[5]','$parts[6]','$parts[7]')"; mysql_query($sql); And this is the code that brings it out: while($row = mysql_fetch_array($result)) { echo "<td>" . $row['estimatedDelivery'] . "</td>"; echo "<td>" . $row['psNumber'] . "</td>"; echo "<td>" . $row['customerName'] . "</td>"; echo "<td>" . $row['numItems'] . "</td>"; echo "<td>" . $row['volume'] . "</td>"; echo "<td>" . $row['address1'] . "</td>"; echo "<td>" . $row['address2'] . "</td>"; echo "<td>" . $row['address3'] . "</td>"; } And here is a sample line of the CSV file: 1/07/2009,16/07/2009,customername,170395,4,2,address1,address2,address3,0.46 So the basic problem is everything is output except for ONE random row... Any info appreciated. Thanks in advance.

Fixed, and program complete Had an error in the where clause. Thanks guys, much appreciated.

Wait.. sorry.. spelled it wrong. I get: Array ( [0] => 16-07-2009 [1] => 16-07-2009 [2] => 16-07-2009 [3] => 16-07-2009 [4] => 16-07-2009 [5] => 16-07-2009 [6] => 16-07-2009 ) Which is correct.

print_r($_POST['estimateDelivery']); gives me absolutely no output

Hmm.. When I use print_r ($date); to see what is in that array, it only gives me one date even though $_POST['estimatedDelivery'] contains about 20 dates. When I run the code you gave me, no changes are made in the DB at all. Bah, this is like the last line I need to complete the program and i've spent about 2 days on it

Wouldn't that just save one date and not the whole array?

$arr = explode("-", $_POST['estimatedDelivery']); $date = $arr[2] . '-' . $arr[1] . '-' . $arr[0]; Tried that but got an "--ArrayError" Any other ideas?

Yes, the values in $_POST['date'] are definitely 00-00-0000 format. Would this work: $string = explode('-',$_POST['estimatedDelivery']); $string = array_reverse($string); $string = implode('-',$string); Then in the update query substitute "UPDATE `smail` SET `estimatedDelivery` = '" . $_POST['estimatedDelivery'][$i] . "' WHERE ... for "UPDATE `smail` SET `estimatedDelivery` = '" . $string[$i] . "' WHERE ...

Ohhh, well I got the date in the correct format. It writes to the DB, but as 2036-04-21 which isn't right.. I used this: UPDATE `smail` SET `estimatedDelivery` = '" . date("Y-m-d",strtotime($_POST['estimatedDelivery'][$i])) . "' WHERE `psNumber` = '" . mysql_real_escape_string($_POST['id'][$i]) . "'"; Trial and error seems to be getting me somewhere.. slowly

Thanks. Yeah I know that's the function.. just having trouble using it. Tried this: $timestamp2 = $_POST['date']; $new_date = date("d-m-Y",$timestamp2); But the dates still get saved as 0000-00-00 in the DB.

I have a query - "UPDATE 'table' SET 'column' = '$_POST['date']' WHERE ........" But the date is in DD-MM-YYYY format. What's the best way to convert my dates to YYYY-MM-DD format before it goes into the DB? I know how to convert them when they're coming OUT of the DB, just not when they're going IN. Thanks

Never mind, works perfectly when psNumber is used in the where clause!! Thanks so much!!!

Thanks for that As for the WHERE clause.. hmm.. well, each estimatedDelivery value matches a psNumber value. Will the for loop cycle through the array in the same order as they are put into the array? If so, then it should work.

$id = $_POST['id']; $date = $_POST['estimatedDelivery']; $date holds one date (0000-00-00) $id holds a 5 digit number. But as the form input was in a while loop, they both hold an unknown number of the above values in the arrays. Does that help?

Anyone? Still struggling with this.. it's proving to be a real pain :-\

Hmm so I assume if I modify the form to pass the values through as arrays they'll stay in the right order so when the form is being processed it will be possible to save them back to the DB in the same order? Any tips on how to modify the form to do this? I haven't done it before..

Well.. i'll post the rest of the script (well the parts that matter). I don't know if it will work yet, i'm still fumbling my way through it with help from you guys Here's the bit that should be of interest: echo "<td> <input type='text' name='estimatedDelivery' value=".$new_date2."></td>"; echo "<td>" . $row['psNumber'] . "</td>"; echo "<input type='hidden' name='id' value=".$row['psNumber'].">"; echo "<td>" . $row['customerName'] . "</td>"; $new_date2 is just a date pulled from the database (how many values depends how many DB rows there are at the time).

Does date have more than one value? Yes it is pulled from the database and has a dynamic number of values.

Quick question For loops are confusing me a bit... can anyone tell me what I would need to do to the for loop in the code below to make it loop until the $date runs out of values? $id = $_POST['id']; $date = $_POST['estimatedDelivery']; foreach () { $sql2 = "UPDATE smail SET estimatedDelivery = '".$date."' WHERE estimatedDelivery = '".$id."'"; mysql_query($sql2); } Cheers

Actually one more question about for loops... I've changed things around a bit so the dates go into the correct DB rows.. so with the code below, how do I modify the for loop accordingly? $id = $_POST['id']; $date = $_POST['estimatedDelivery']; foreach ($_POST['estimatedDelivery'] ??????) { $sql2 = "UPDATE smail SET estimatedDelivery = '".$date."' WHERE estimatedDelivery = '".$id."'"; mysql_query($sql2); }