Select From MySQL PHP

Sir, I have one Database Table named "table1" with 9 column, that is id, Date, Time, Name, t1, t2, t3, t4, t5. I want to insert it to table2 as follows...

my existing table:-

id  Date        Time   Name    t1   t2   t3   t4  t5
 1   10/11/2010  08:00  bob
 2   10/11/2010  09:00  bob
 3   10/11/2010  10:00  bob
 4   10/11/2010  13:00  bob
 5   10/11/2010  10:00  john
 6   10/11/2010  12:00  john
 7   10/11/2010  14:00  john
 8   12/11/2010  08:00  bob
 9   12/11/2010  09:00  bob
 10  12/11/2010  10:00  bob
 11  12/11/2010  13:00  bob
 12  12/11/2010  10:00  john
 13  12/11/2010  12:00  john
 14  12/11/2010  14:00  john
 15  12/11/2010  16:00  john 
 16  12/11/2010  08:00  Tom
 17  12/11/2010  17:00  Tom

I want to Insert to table2 as follows :-

id  Date          Name    t1      t2      t3      t4     t5
 1  110/11/2010    bob     08:00   09:00   10:00   13:00
 2  10/11/2010    john    10:00   12:00   14:00
 3  12/11/2010    bob     08:00   09:00   10:00   13:00
 4  12/11/2010    john    10:00   12:00   14:00   16:00
 5  12/11/2010    Tom     08:00   17:00   

I want to 开发者_StackOverflowinsert the table1 value to table2 is it posible to insert like this please help me..

Liju

Of course its possible, you need to do a proper UPDATE SQL statement like so:

UPDATE table1 t 
SET t.t1 = '08:00', t.t2 = '09:00', t.t3 = '10:00', t.t4 = '13:00'
WHERE `Date` = '10/11/2010' AND `Time` = '08:00' AND `Name` = 'bob';

As you DON'T have a primary key (row id for instance) you need to use the WHERE on 3 columns to make your update.

Date / Time should be named differently, something discriptive entrydate / entrytime etc;

Read up on your MYSQL queries (UPDATE/INSERT/DELETE, etc) Go to: http://dev.mysql.com/doc/

From

Date Time Name t1 t2 t3 t4 t5 
10/11/2010 08:00 bob 
10/11/2010 09:00 bob 
10/11/2010 10:00 bob 
10/11/2010 13:00 bob 
10/11/2010 10:00 john 
10/11/2010 12:00 john 
10/11/2010 14:00 john 
12/11/2010 08:00 bob 
12/11/2010 09:00 bob 
12/11/2010 10:00 bob 
12/11/2010 13:00 bob 
12/11/2010 10:00 john 
12/11/2010 12:00 john 
12/11/2010 14:00 john 
12/11/2010 16:00 john

To

Date Time Name t1 t2 t3 t4 t5 
10/11/2010 08:00 bob 08:00 09:00 10:00 13:00 
10/11/2010 10:00 john 10:00 12:00 14:00 
12/11/2010 08:00 bob 08:00 09:00 10:00 13:00 
12/11/2010 10:00 john 10:00 12:00 14:00 16:00

Here is process step

• Select from the table and store all variables in an array
  • Where t1 IS NULL OR t2 IS NULL OR ....
• Do an UPDATE to the table and define the ones you want to update
• You might need to also update the table that you have processed, with a flag
• And remove the remaining table

This process will be highly unreliable and performance intensive. You may have your reasons for doing what you are trying to do, but I will consider a different approach. The above process step should really be a last resort to this problem

If you can explain a bit more in detail about why you are doing this we may be able to give you a better explanation

Try this. This will defiantly work

update table1 set 
time2 = SUBSTRING(DATE_ADD(CONCAT_WS(' ',CONCAT_WS('-',SUBSTRING(date1,7,4), SUBSTRING(date1,1,2),SUBSTRING(date1,4,2)), time1), INTERVAL 1 HOUR), 12, 5 ), 
time3 = SUBSTRING(DATE_ADD(CONCAT_WS(' ',CONCAT_WS('-',SUBSTRING(date1,7,4), SUBSTRING(date1,1,2),SUBSTRING(date1,4,2)), time1), INTERVAL 2 HOUR), 12, 5 );

try this

UPDATE table1 as t1
    SET 
    t1 = ( 
        SELECT
            `Time`
        FROM
            table1 as t2
        WHERE
            t2.Date = t1.Date 
            and t2.Name = t1.Name
        LIMIT 0,1
    ),
    t2 = ( 
        SELECT
            `Time`
        FROM
            table1 as t2
        WHERE
            t2.Date = t1.Date 
            and t2.Name = t1.Name
        LIMIT 1,1
    ),
    t3 = ( 
        SELECT
            `Time`
        FROM
            table1 as t2
        WHERE
            t2.Date = t1.Date 
            and t2.Name = t1.Name
        LIMIT 2,1
    ),
    t4 = ( 
        SELECT
            `Time`
        FROM
            table1 as t2
        WHERE
            t2.Date = t1.Date 
            and t2.Name = t1.Name
        LIMIT 3,1
    ),
    t5 = ( 
        SELECT
            `Time`
        FROM
            table1 as t2
        WHERE
            t2.Date = t1.Date 
            and t2.Name = t1.Name
        LIMIT 4,1
    );

i havn't run this query, but hopefully this will work.

And after running this query. Dont forget to remove the duplicate rows.

And their is also options that you can use php to load all the columns using with group by and then loop through it and update each row.

Or if you dont want you use php, then you can use a MySQL procedure to do this. Anyway let me know if this work. else i will write the php or SP version, which are obviously more efficient