PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jauson
Forum Contributor
Posts: 111
Joined: Wed Oct 05, 2011 12:59 am

PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Post 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:
jauson
Forum Contributor
Posts: 111
Joined: Wed Oct 05, 2011 12:59 am

Re: PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Post by jauson »

Anyone?
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Post 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.
jauson
Forum Contributor
Posts: 111
Joined: Wed Oct 05, 2011 12:59 am

Re: PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Post 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,
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: PHPEXCEL DATETIME to be imported in MYSQL using phpexcel

Post 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.
Post Reply