[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert failed ?

Posted on 2008-11-17
21
Medium Priority
?
361 Views
Last Modified: 2013-12-13
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

0
Comment
Question by:mdeek
  • 11
  • 9
21 Comments
 
LVL 2

Expert Comment

by:artms
ID: 22976662
Please attach csv file to test  and table create script.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22976831
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
 

Author Comment

by:mdeek
ID: 22977068
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22977136
mdeek: What are the data types of these 5 fields in the table?
0
 

Author Comment

by:mdeek
ID: 22977206
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22977209
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22977222
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22977246

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

Open in new window

0
 

Author Comment

by:mdeek
ID: 22977292
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
 

Author Comment

by:mdeek
ID: 22977318
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22977321
Roger that.  Try the changes I posted here and then post back if you're still not getting the results you want. ~Ray
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22977350
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
 

Author Comment

by:mdeek
ID: 22977402
get a syntax error unexpected ',' ?
should i change the data type to datetime ?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22977542
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
 

Author Comment

by:mdeek
ID: 22977595
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22977616
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
 

Author Comment

by:mdeek
ID: 22977672
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 22977836
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
 

Author Comment

by:mdeek
ID: 22977942
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
 

Author Closing Comment

by:mdeek
ID: 31517473
you gave me some very good reference and will try to combine those two fields sorry if it frustrated you :)
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22979919
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question