Page 1 of 1

PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Posted: Tue Feb 21, 2012 8:08 pm
by jauson
Hi I'm having trouble finding how to read a value from a cell which is formatted as a date.
Can anyone help me out and point me in the direction of some sample code please?


Assumed below is my datetime in excel to be imported in database(MySQL) using PHP.

id startdatetime enddatetime Name
1 1988/02/02 09:22:02 1988/02/02 06:00:00 Jon

Below is the result in database.

id startdatetime enddatetime Name
1 0000:00:00 00:00:00 0000:00:00 00:00:00 Jon

I have understand that excel date format (YYYYMMDD His) for example is not recognizing the datetime of MysQL even the field type of startdatetime and enddatetime is set to DATETIME. can someone help me?

more thanks, :banghead:

Re: PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Posted: Thu Feb 23, 2012 7:15 pm
by jauson
Anyone?

Re: PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Posted: Thu Feb 23, 2012 7:27 pm
by Eric!
Didn't we just go through all this? viewtopic.php?f=1&t=134267&hilit=+date

You need to reformat the strings. Can you repost the code you are using (please use

Code: Select all

 tags).  Also dump out the SQL query you are using to set and read the data so we can see the raw information...not just variables.

Also you need to make sure the fields in your tables are really DATETIME and not something else.

Re: PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Posted: Fri Feb 24, 2012 12:48 am
by jauson
Hi

<?php

require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('Book3.xls');

$conn = mysql_connect("localhost","root","root");
mysql_select_db("database",$conn);


for ($x = 0; $x <= count($data->sheets[0]["cells"]); $x++) {
$id = $data->sheets[0]["cells"][$x][1];
$empID = $data->sheets[0]["cells"][$x][2];
$timein = $data->sheets[0]["cells"][$x][3];
$timeout = $data->sheets[0]["cells"][$x][4];

$sql = mysql_query("INSERT INTO `mytable` (id, empID, timein, timeout) VALUES ('$id', '$empID', '$timein', '$timeout')");
//echo $sql ."\n";
if($sql){

echo 'okay <br />';

} else {
echo 'Data <font color="RED">'.$empID.'</font> not stored in database. <br>';
}
}
?>

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 24, 2012 at 07:46 AM
-- Server version: 5.5.8
-- PHP Version: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `database`
--

-- --------------------------------------------------------

--
-- Table structure for table `timesheet`
--

CREATE TABLE IF NOT EXISTS `timesheet` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`empID` int(10) NOT NULL,
`timein` datetime NOT NULL,
`timeout` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `timesheet`
--

thats all what ive got.

Regards,

Re: PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Posted: Fri Feb 24, 2012 7:57 am
by Eric!
Please Use CODE tags when posting code and text tags for posting pasted text...
[ syntax=php] PASTE YOUR CODE HERE [ /syntax] (without spaces in the tags.

Also if you read the original thread: viewtopic.php?f=1&t=134267&hilit=+date You MUST change the format of your date strings.

And can you echo out what you are writing to mysql? Change to

Code: Select all

$sql = "INSERT INTO `mytable` (id, empID, timein, timeout) VALUES ('$id', '$empID', '$timein', '$timeout')";
$result=mysql_query($sql);
echo $sql ."\n";
So we can see what you are dumping into the db.