Select From MySQL PHP

Question

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 insert the table1 value to table2 is it posible to insert like this please help me..

Liju

• Savetheinternet: What exactly do you mean by "MySQL PHP"?
• Angel.King.47: Dude you need to edit your Existing table and Update table in better formatting
• Savetheinternet: You need to explain what t1, t2, t3, t4 and t5 are equal to for us to be able to help you.

Answer 1

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/

Answer 2

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

• Jakub: A primary key CAN be 3 different columns, it does not have to be a unique incremental column. The poster can still do updates but would need to query 3 columns as the identifier
• Angel.King.47: Oh im sorry you are right didnt notice the difference in date

Answer 3

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 );

Answer 4

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

• Liju: I got the massage" You can't specify target table 't1' for update in FROM clause" when I put this code