need some help guys

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

need some help guys

Post by jauson »

Hi Guys!

I want to import datetime data from excel sheet going to mysql.
everything works fine until datetime from mysql didnt read the data from my excel file.

it shows from mysql.

0000-00-00 00-00-00

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

Re: need some help guys

Post by Eric! »

I assume you're using PHP to import from excel. Can you post some code with some more data like, what are you reading from the file and how are you storing it in to mysql. Also is that field a DATETIME field? And what does PHP think the data is (echo it out) before it writes it to the database?
jauson
Forum Contributor
Posts: 111
Joined: Wed Oct 05, 2011 12:59 am

Re: need some help guys

Post by jauson »

below is my code to read excel files. xls i have just edit the columns to parse exactly on my data columns in excel.

<?php

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

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

for ($x = 2; $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 'Data successully stored in databbase. '.$empID.'<br>';
} else {
echo 'Data <font color="RED">'.$empID.'</font> not stored in database. <br>';
}
}
?>

Assume that below is my data on Book1.xls

id empID timein timeout
1 02062012A 2012-01-01 2012/01/11 17:29:49
2 02062012A 2012/01/13 07:56:56 2012/01/13 17:17:59
3 02062012A 2012/01/14 09:00:49 2012/01/14 16:14:11
4 02062012A 2012/01/16 08:19:48 2012/01/16 17:13:51
5 02062012A 2012/01/17 07:54:14 2012/01/17 17:19:45
6 02062012A 2012/01/18 08:13:16 2012/01/20 17:14:39
7 02062012A 2012/01/21 09:32:58 2012/01/21 16:07:42
8 02062012A 2012/01/23 08:26:30 2012/01/23 17:04:33
9 02062012A 2012/01/23 17:04:40 2012/01/23 17:09:52
10 02062012A 2012-01-24 08:23:02 2012/01/24 17:17:59
11 02062012A 2012/01/24 1/24/2012

now everything is fine except timein and timeout.
I heard that excel didnot support mysql datetime. really need help

mysql shows below:


id empID timein timeout
1 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
2 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
3 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
4 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
5 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
6 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
7 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
8 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
9 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
10 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
11 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00

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

Re: need some help guys

Post by Eric! »

Ok, and what does echo $timein or echo $timeout show?

And you'll need to convert those strings into the proper DATETIME format YYYY-MM-DD HH:MM:SS before mysql can store them properly:

Code: Select all

$timein=preg_replace('/\//', '-', $timein);//change / to - in date strings
(I think I have the regex right for finding / with /\//)

(PS please use the code tags when posting code!)
jauson
Forum Contributor
Posts: 111
Joined: Wed Oct 05, 2011 12:59 am

Re: need some help guys

Post by jauson »

I will explain further, below is my code in reading excel files. see Annex A

Annex A:
<?php

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

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

for ($x = 2; $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 'Data successully stored in databbase. '.$empID.'<br>';
} else {
echo 'Data <font color="RED">'.$empID.'</font> not stored in database. <br>';
}
}
?> Annex A is work perfectly, now assumed that below is my data on my excel files. Book1.xls. Annex B:

Annex B:
id empID timein timeout
1 02062012A 2012/01/13 07:56:56 2012/01/11 17:29:49
2 02062012A 2012/01/13 07:56:56 2012/01/13 17:17:59
3 02062012A 2012/01/14 09:00:49 2012/01/14 16:14:11
4 02062012A 2012/01/16 08:19:48 2012/01/16 17:13:51
5 02062012A 2012/01/17 07:54:14 2012/01/17 17:19:45
6 02062012A 2012/01/18 08:13:16 2012/01/20 17:14:39
7 02062012A 2012/01/21 09:32:58 2012/01/21 16:07:42
8 02062012A 2012/01/23 08:26:30 2012/01/23 17:04:33
9 02062012A 2012/01/23 17:04:40 2012/01/23 17:09:52
10 02062012A 2012-01-24 08:23:02 2012/01/24 17:17:59
11 02062012A 2012-01-24 08:23:02 2012/01/24 17:17:59

Now, when i try to execute the codes. it echoes see Annex C.

Annex C
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A
Data successfully stored in databbase. 02062012A

Now it shows that everything is well and stored in database. But assumed that below is the data stored in phpmyadmin, it show see Annex D:

Annex D:
id empID timein timeout
1 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
2 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
3 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
4 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
5 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
6 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
7 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
8 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
9 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
10 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00
11 02062012A 0000-00-00 00:00:00 0000-00-00 00:00:00

What else can i do? to read timein and timeout on my excel going to phpmyadmin.
what would be the code to read exactly the timein and timeout on my excel going to database. thank you.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: need some help guys

Post by Eric! »

echo $timein and echo $timeout to see if you're really reading what you think you are from the excel file. And I don't see where you have changed the date format as I described in my earlier post.

Also use the

Code: Select all

 tags when posting code and [text] tags when posting output here on this message board.
jauson
Forum Contributor
Posts: 111
Joined: Wed Oct 05, 2011 12:59 am

Re: need some help guys

Post by jauson »

Helo,

I have done what you advised here, I echoed $timein and $timeou theres no other error, except the datetime in excel doesnt read on mysql,

I want to convert datetime of excel to support the datetime of mysql.

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

Re: need some help guys

Post by Eric! »

and have you changed the format for your two variables like I suggested earlier:

Code: Select all

$timein=preg_replace('/\//', '-', $timein);//change / to - in date strings
jauson
Forum Contributor
Posts: 111
Joined: Wed Oct 05, 2011 12:59 am

Re: need some help guys

Post by jauson »

below is my code. where do i need to input the regex?

<?php

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

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

for ($x = 2; $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($timein){
echo 'Data successully stored in databbase. '.$timein.'<br>';
} else {
echo 'Data <font color="RED">'.$empID.'</font> not stored in database. <br>';
}
}
?>
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: need some help guys

Post by Eric! »

Before you use it in your msql_query. And you have to do it for both $timin and $timeout.

Also please use code tags when posting [ syntax=php ]PASTE CODE HERE[/ syntax] (without the spaces)
jauson
Forum Contributor
Posts: 111
Joined: Wed Oct 05, 2011 12:59 am

Re: need some help guys

Post by jauson »

nothings happen still. i tried to input the regex syntax but the problem occurs.
Post Reply