Results 1 to 2 of 2

Thread: Insert into select statement using 2 tables

  1. #1
    Join Date
    Oct 2011
    Posts
    39
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Default 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. #2
    Join Date
    Oct 2008
    Posts
    60
    Thanks
    2
    Thanked 7 Times in 7 Posts

    Default

    Change the SELECT statement to VALUES

    INSERT INTO time (empno,total)
    SELECT EMP_NO,sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein)))
    FROM employee
    GROUP BY EMP_NO;
    PHP Code:
    $con mysql_connect("localhost","peter","abc123");
    mysql_select_db("my_db"$con);
    INSERT INTO time (empnototal)
    VALUES (EMP_NOsec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein))) 
    Also, make sure that your mysql database table is set to auto increment.

  3. The Following User Says Thank You to fobos For This Useful Post:

    newphpcoder (12-15-2011)

Bookmarks

Posting Permissions

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