insert failed ?

Trying to develop a script to import a csv file into mysql which seems to work so far.

however it does not insert any data into a field called log_date and cant seem to figure out why not ..
The name of the field is correct i tried a field definition of date and of varchar but that doesnt seem to affect it either.

here is the script

note: when testing it will prompt you for the csv file to import the file needs to be in the same directory as import.php and have read rights on it.

hope someone can help me out with this ..


<?php
include "includes/connect.php";
if(isset($_POST['submit']))
   {
     $filename=$_POST['filename'];
     $handle = fopen("$filename", "r");
     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
     {
      $import="INSERT into logins(user_id,user_name,logged,log_date,log_time) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
       mysql_query($import) or die(mysql_error());
     }
     fclose($handle);
     print "Import done";
 
   }
   else
   {
 
      print "<form action='import.php' method='post'>";
      print "Type file name to import:<br>";
      print "<input type='text' name='filename' size='20'><br>";
      print "<input type='submit' name='submit' value='submit'></form>";
   }
 
?>

Open in new window

mdeekAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

artmsCommented:
Please attach csv file to test  and table create script.
0
Ray PaseurCommented:
You might want to add a line to echo the $import variable after line 9 so you can see the query.

Also, what is the data type for your log_date field?  If it is a DATETIME type, you might want to have the data formatted into an ISO8601 date.  If the code below gives you dates in 1969, the date format is not readily understood by the programs.
$data[3] = date('Y-m-d', strtotime($data[3]));
echo "<br />$import\n";

Open in new window

0
mdeekAuthor Commented:
just 5 fields in the table

user_id, user_name, logged,log_date, log_time

xls  file attached due to restrictions on this site csv is not allowed

log-in-11-06-08.xls
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Ray PaseurCommented:
mdeek: What are the data types of these 5 fields in the table?
0
mdeekAuthor Commented:
user_id is int
user_name is text
logged is text
log_date is varchar
log_time is time

tried setting log_time to varchar since log_date works that way but no dice ..
0
Ray PaseurCommented:
Now that I've looked at your log, it looks like code I posted above should help you, if you have the DATETIME data type for the log_date.  If it were up to me, I would always use internal dates like YYYY-MM-DD so that they can be sorted.  You can reformat them to "pretty" dates for client display

Just curious - why are you keeping date and time in separate fields?  You can combine the into one timestamp.  That will let you do all kinds of easier arithmetic, take less storage, easy to translate with a query (no extra PHP needed) etc.
0
Ray PaseurCommented:
Try setting log_date to DATETIME.

Actually you could combine log_date and log_time into one DATETIME field.

Man Page Here: http://dev.mysql.com/doc/refman/5.0/en/datetime.html

HTH, ~Ray
0
Ray PaseurCommented:

$datetime = date('Y-m-d\TH:i:s', strtotime($data[3] . ' ' . $data[4]);

Open in new window

0
mdeekAuthor Commented:
problem is ray that we get the xls file from an outside source .. and im not that clever when it comes to combining stuff lol hence the reason i ask a bunch of questions here ;)

the idea what they want to do here is this

they want to be able to run a query and show when the user logged in and logged out
then take the first login of the user *sometimes the application hangs and you get double logins and logouts*
then take the last logout of the user  and calculate how much time is in between the two and display that

as for me combining the two fields into one i would have no idea how to do that at the moment i suspect with concatination , but if i can initialy get it to work then i can always come with the newer version afterwards with more bells and whistles in it.
for in stance i would also split out the logged field into 2 fields with a bolean value in them so all we have to do is check if they logged yes or no on either .

they have a second xls file that shows if the user was active which i will have to import later and check if the user was active during that time because if the user was not it means he didnt perform his duties .

u get the general idea i guess
0
mdeekAuthor Commented:
the date is given in the mm/d/yyyy format in the xls file
ehm where do i put that line of code u posted ?
and do i make any changes as to data type of the dbase field ??
0
Ray PaseurCommented:
Roger that.  Try the changes I posted here and then post back if you're still not getting the results you want. ~Ray
0
Ray PaseurCommented:
To change the data type, you want to create a new table in the data base with the corrected data type.

To add the code I posted, please take note of the comment that said "line 9" - that was based on the line numbers in your code snippet.
0
mdeekAuthor Commented:
get a syntax error unexpected ',' ?
should i change the data type to datetime ?
0
Ray PaseurCommented:
mdeek: it's almost impossible to know what you are talking about when you post a statement like this:

a syntax error unexpected ',' ?

Where?  In what line of code?  What is the context?  Etc.  If you have written some PHP you may have seen that error before.  It usually results from a typographical error, such as unbalanced parentheses.  But that is just a guess.

We are only experts, not mind readers.  Give us a little more to work with and maybe we can help.
0
mdeekAuthor Commented:
sorry ray :) its in the same line that u quoted to put below line 9
i cut and pasted it straight from here in there ..
0
Ray PaseurCommented:
IIRC, I posted TWO lines, showing here.

Please post the current script in its entirety.  Please also post the error message WITH THE LINE NUMBER.

I will take one quick look at resolving this, but you have to take some responsibility for the work, too.
$data[3] = date('Y-m-d', strtotime($data[3]));
echo "<br />$import\n";

Open in new window

0
mdeekAuthor Commented:
i commented the place of the error in the code now .
<?php
include "includes/connect.php";
if(isset($_POST['submit']))
   {
     $filename=$_POST['filename'];
     $handle = fopen("$filename", "r");
     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
     {
       $import="INSERT into active(type,date,account,record,info) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
 
// error occurs below this line unexpected ','
 
   $datetime = date('Y-m-d\TH:i:s', strtotime($data[3] . ' ' . $data[4]);
        mysql_query($import) or die(mysql_error());
     }
     fclose($handle);
     print "Import done";
 
   }
   else
   {
 
      print "<form action='importactive.php' method='post'>";
      print "Type file name to import:<br>";
      print "<input type='text' name='filename' size='20'><br>";
      print "<input type='submit' name='submit' value='submit'></form>";
   }
 
?>

Open in new window

0
Ray PaseurCommented:
mdeek, Two things here...

You did not make the two line change I recommended - that script does NOT contain the two line change I suggested above.  Go back up and look at your original post, and look at my post number 22976831.  

Second, please don't make changes or add lines to the failing script.  I can't help you debug a moving target!  Let's try this once again...

Please post the current script in its entirety.  Please also post the error message WITH THE LINE NUMBER.

Thanks, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdeekAuthor Commented:
I just changed the data type of the field to varchar 20 chars and it works now it inserts the values in there.

sorry ray hope i didnt frustrate you too much
0
mdeekAuthor Commented:
you gave me some very good reference and will try to combine those two fields sorry if it frustrated you :)
0
Ray PaseurCommented:
Hi, mdeek - no trouble, and thanks for the points.  For the most part, you will find the EE help to be quite literal - if we say "put this at line 9" we really mean it, and we expect to see a specific result from the suggestion.  Otherwise we would not waste your time.  Hope you've got the results you need!

best regards, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.