im trying to write a database query to populate two coloumns of a custom table of myn by using three other tables in the same db, but currently those coloumns return nothing, i will provide tables and my query below!!! can any body solve this problem
table_17
trackID MyID Rid panelID timestamp useragent_browser useragent_OS
1 17 123 25 2014-04-29 10:02:01 Default Browser unknown NULL
2 17 123 25 2014-04-30 05:11:01 Default Browser unknown NULL
3 17 123 25 2014-05-15 06:44:02 Default Browser unknown NULL
4 17 59595 25 2014-10-06 07:50:30 Default Browser unknown 0
5 17 59595 25 2014-10-06 07:51:29 NULL NULL 0
6 17 59595 25 2014-10-06 07:54:51 NULL NULL 0
7 17 789 25 2014-10-07 10:20:29 Default Browser unknown 0
8 17 789 25 2014-10-07 10:23:48 Default Browser unknown 0
9 17 1881 25 2014-10-27 05:16:11 NULL NULL 0
10 17 188 25 2014-10-27 05:18:29 NULL NULL 0
projects
MyID projectname accountID creationdate status launchdate closedate total_impr
1 Virgo mobile 1 2010-07-07 09:09:09 3 2010-08-18 09:23:51 2011-11-18 19:38:41 1999
2 sys test 1 2010-06-05 09:12:35 3 2010-06-16 09:10:33 2010-06-30 11:23:55 2
16 test2 1 2010-08-16 14:12:50 1 NULL NULL 0
7 testproject 1 2010-08-24 14:44:40 2 2014-03-21 22:55:46 NULL 27
18 system2 1 2010-09-21 13:57:46 2 2010-09-21 12:03:23 NULL 51529
19 Holidayland 1 2010-09-30 08:36:55 2 2010-10-03 09:23:55 NULL 6246491
20 thor_test 1 2010-11-17 17:41:11 2 2014-07-17 10:43:53 NULL 0
21 cima 1 2011-02-14 19:13:32 2 2014-07-17 05:07:40 NULL 0
my query
mysql_query("
INSERT INTO custom_table (`useragent_browser`, `useragent_OS`,`MyID`,`timestamp`,`projectname`)
SELECT `useragent_browser`, `useragent_OS`, 'b.timestamp', 'b.MyID',`projectname`
FROM $table a, projects b
WHERE a.timestamp > DATE_ADD(NOW(), INTERVAL -1 MONTH) AND a.MyID = b.MyID")
or die(mysql_error());
The result i get from the query
trackID projectname MyID Rid panelID timestamp useragent_browser useragent_Os
1 testproject 0 NULL 25 0000-00-00 00:00:00 NULL NULL NULL
2 testproject 0 NULL 25 0000-00-00 00:00:00 NULL NULL NULL
4 Test168-150 0 NULL 25 0000-00-00 00:00:00 Chrome Win7 NULL
5 Test168-150 0 NULL 25 0000-00-00 00:00:00 Chrome Win7 NULL
6 Test168-150 0 NULL 25 0000-00-00 00:00:00 Default Browser unknown NULL
7 Test-155 0 NULL 25 0000-00-00 00:00:00 Default Browser unknown NULL
8 Test-155 0 NULL 25 0000-00-00 00:00:00 Default Browser unknown NULL
9 Test-155 0 NULL 25 0000-00-00 00:00:00 Default Browser unknown NULL
10 Test-155 0 NULL 25 0000-00-00 00:00:00 Default Browser unknown NULL
14 test155 0 NULL 25 0000-00-00 00:00:00 Default Browser unknown NULL
The columns which i need to populate Myid and timestamp is zero.. can anybody help me to populate those two correctly
Edit: organized query for readability above
You are accessing them incorrectly (as strings) and you have them reversed.
In the INSERT you have:
`MyID`,`timestamp`
In the SELECT you have:
'b.timestamp', 'b.MyID'
It should be:
`b.MyID`, `b.timestamp`
Therefore you are not using quotes, so they are not evaluated as strings and they are in the correct order.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments