Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Insert into select statement using 2 tables

    I have employee table for attendance and i have a test select statement to get the total hours of employee:

    Code:
    select  sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein))) AS totalhours from employee;
    and now i want to insert the sum of hours per employee in time database with the employee no.

    here is my code:
    [CODE
    INSERT INTO time (empno,total)
    SELECT EMP_NO,sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein)))
    FROM employee
    GROUP BY EMP_NO;
    [/CODE]

    no data inserted in time database. What's erong in my query?
    Thank you

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    One possible problem: time is a keyword. Maybe you simply need to put `...` (back ticks...shares the key with umlaut ~ character) around that name?
    Code:
    INSERT INTO `time` ( empno, total )
    SELECT EMP_NO,sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein)))
    FROM employee
    GROUP BY EMP_NO;
    If that doesn't fix it, double check to make sure the SELECT is working as it is in that INSERT.

    That is, do
    Code:
    SELECT EMP_NO,sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein)))
    FROM employee
    GROUP BY EMP_NO;
    Does that get the right values?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (10-24-2011)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    One possible problem: time is a keyword. Maybe you simply need to put `...` (back ticks...shares the key with umlaut ~ character) around that name?
    Code:
    INSERT INTO `time` ( empno, total )
    SELECT EMP_NO,sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein)))
    FROM employee
    GROUP BY EMP_NO;
    If that doesn't fix it, double check to make sure the SELECT is working as it is in that INSERT.

    That is, do
    Code:
    SELECT EMP_NO,sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein)))
    FROM employee
    GROUP BY EMP_NO;
    Does that get the right values?
    It only insert the empno..the total did not insert..but when I run the select statement it works. the datatype of total is datetime. So the insert data in total is 0000-00-00 00:00:00

    Thank you

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    When I change the datatype of total from datetime to time the sum of timein and timeout display...is it correct?But theres a row added and the total data is : 00:00:00
    and how can i display using php the total in hour and minutes only? Thank you
    Last edited by newphpcoder; 10-24-2011 at 06:16 AM.

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    here is the vardump of my database:
    Code:
    -- MySQL Administrator dump 1.4
    --
    -- ------------------------------------------------------
    -- Server version	5.1.41
    
    
    /*!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 */;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    
    --
    -- Create schema db_upload
    --
    
    CREATE DATABASE IF NOT EXISTS db_upload;
    USE db_upload;
    
    --
    -- Definition of table `db_upload`.`employee`
    --
    
    DROP TABLE IF EXISTS `db_upload`.`employee`;
    CREATE TABLE  `db_upload`.`employee` (
      `EMP_NO` varchar(50) NOT NULL,
      `timein` datetime NOT NULL,
      `timeout` datetime NOT NULL,
      `totalhours` datetime NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `db_upload`.`employee`
    --
    
    /*!40000 ALTER TABLE `employee` DISABLE KEYS */;
    INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`totalhours`) VALUES 
     ('100603','2011-10-01 05:35:00','2011-10-01 13:35:00','0000-00-00 00:00:00'),
     ('100603','2011-10-02 05:25:00','2011-10-02 13:55:00','0000-00-00 00:00:00'),
     ('100603','2011-10-03 05:40:00','2011-10-03 13:40:00','0000-00-00 00:00:00'),
     ('','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00');
    /*!40000 ALTER TABLE `employee` ENABLE KEYS */;
    
    
    --
    -- Definition of table `db_upload`.`time`
    --
    
    DROP TABLE IF EXISTS `db_upload`.`time`;
    CREATE TABLE  `db_upload`.`time` (
      `empno` varchar(15) NOT NULL,
      `total` time NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `db_upload`.`time`
    --
    
    /*!40000 ALTER TABLE `time` DISABLE KEYS */;
    INSERT INTO `db_upload`.`time` (`empno`,`total`) VALUES 
     ('100603','24:30:00'),
     ('','00:00:00');
    /*!40000 ALTER TABLE `time` ENABLE KEYS */;
    
    
    
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •