|
|
Posted by mantrid on 06/21/07 22:37
"mantrid" <ian.dandav@virgin.net> wrote in message
news:ojBei.2247$_14.155@newsfe2-gui.ntli.net...
> Hello
>
> I used phpmyadmin to run an sql to insert a record into the table. The
only
> one that worked was
>
> INSERT INTO transactions
>
(transactionid,userid,companyid,aimlisted,amount,price,stamp,comm,datetime)
> VALUES ('',1, 2, 1, 11111, .76, 3.54, 7, '2002-04-10')
>
> ie Using a date in the correct format as the mysql table and in quotes
(hrs
> mins secs were not needed), leave out the quotes or in any other format
and
> it doesnt work.
>
> ian
>
>
> "Lars Eighner" <usenet@larseighner.com> wrote in message
> news:slrnf7lkh9.1qie.usenet@goodwill.larseighner.com...
> > In our last episode,
> > <mv7l73l0pdv6bo8mnq1kqrv6djubcjnoon@4ax.com>,
> > the lovely and talented Michael Fesser
> > broadcast on comp.lang.php:
> >
> > > .oO(mantrid)
> >
> > >>Is it best to do that in Excel or use a php date format function?
> > >>In Excel it is dd/mm/yyy
> > >>but it wont allow me to save as a CVS file in the form yyy-mm-dd
> hh:mm:ss,
> > >>it just changes it back.
> > >>ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the
sql
> > >>statement but its not working, am I using this function wrongly?
> >
> > > Yep, but doesn't matter in this case.
> >
> > >>Do you know a php function that does a similar thing? I know the date
> format
> > >>characters (eg d M Y etc) but can only see examples of their use with
> > >>date(), which is the current date, not a date from my CVS file.
> >
> > > Try strtotime(). If it doesn't work as expected (for example
dd/mm/yyyy
> > > vs. mm/dd/yyyy), use string functions to split the Excel date and
create
> > > your own date string in MySQL format.
> >
> > Some additional notes: You used yyy twice in your post, so I wonder
> > if it is a typo. MySQL will accept 2 or 4 digit years, but I have my
> > doubts about 3.
> >
> > Also DATETIME and DATE field will accept any separator, so if something
> goes
> > screwy with the date and you send 'HH:MM:SS' first it will try to
> interpret
> > that as the date HH-MM-SS. For MM over 12 and SS over 31 that will
always
> > produce an invalid date which will default to 0000-00-0000.
> >
> > I believe TIMESTAMP is stricter. Also, I note there have been changes
> > in the relevant functions / data structures of both PHP and My SQL, so
> > you should check the manuals of the version you are actually running.
> > Perfectly good advice may not work for you if you have a legacy system.
> >
> > But if you discover exactly what your MySQL wants, rolling your own
> function
> > to manipulate the date string is good advice that will always work. If
> you
> > have a MySQL client on the pertinent server and a sandbox database, the
> best
> > way to determine exactly what your MySQL wants is to try it from the
MySQL
> > client. You cannot expect to use PHP to write a query line that works
if
> > you cannot type in a query that works at the MySQL client prompt.
> >
> > --
> > Lars Eighner <http://larseighner.com/>
> <http://myspace.com/larseighner>
> > Countdown: 578 days to go.
> > Owing to googlegroups not screening users to eliminate spammers and
> other
> > USENET abusers, I do not see most posts from googlegroups.
>
>
ok got it
had to change
$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, "','")) !== FALSE) {
list($userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
$dateandtime) = $data;
to
$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000)) !== FALSE) {
list($userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
$dateandtime) = $data;
that will teach me not to just cut and paste others peoples use of the
functions before reading them properly.
and
$q = "INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,price,stamp,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,'".$dateandtime."')";
to
$q = "INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,price,stamp,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,'".$dateandtime."')";
the insertion of the quotes around $dateandtime allowed it to be accepted. I
also didnt need to manipulate $dateandtime to change it from dd/mm/yyyy to
yyyy-mm-dd, this appears to have happened automatically when using fgetcsv()
or possibly at the mysql insert stage.
Thanks all for the help
Ian
[Back to original message]
|