chaithu7844 Posted March 11, 2011 Share Posted March 11, 2011 Could any one help me out with this code please, I have to show it as my company deadline and which will help me assure my job else i will be fired 1)First of all i have a table ra with name log_connect with the following attributes `log_id` bigint(40) NOT NULL AUTO_INCREMENT, `login` varchar(40) NOT NULL, `account_id` varchar(40) NOT NULL, `universe_id` varchar(40) NOT NULL, `action` enum('connection','visit','deconnection') NOT NULL, `activity_qaq` varchar(40) NOT NULL, `service_qaq` varchar(40) NOT NULL, `datetime_log` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`log_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; with example values as (1, 'login', 'ACC001', 'UNI001', 'connection', 'ActivityA', 'ServiceA', '2011-03-11 11:25:41') (2, 'login', 'ACC002', 'UNI002', 'connection', 'ActivityB', 'ServiceB', '2011-03-11 11:30:41') (3, 'login', 'ACC001', 'UNI001', 'deconnection', 'ActivityA', 'ServiceA', '2011-03-11 12:45:41') (4, 'login', 'ACC002', 'UNI002', 'deconnection', 'ActivityB', 'ServiceB', '2011-03-11 12:56:41') 2) Second step is to fill the values from the above table ie i have to retrieve from the above table and fill in few tables, here is where the problem occurs: a) first i have to fill the following table activity_rating which has the format ie i have to fill the id_acti here which is nothing but the activity_qaq present in the above table along with the rating_acti as the time spent on a particular activity as shown above the first user spends time on ActivityA from 2011-03-11 11:25:41 to 2011-03-11 12:45:41, ie from the conenction to a disconnection of that particular ActivityA. ( i am not able to understand how we calculate the timing in this sense i mean we can retreive the values but how to caluculate how i said ) `activity_rating` ( `id` smallint(5) NOT NULL AUTO_INCREMENT, `id_acti` smallint(5) DEFAULT NULL, `rating_acti` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 3) similarly we have to calcuulate the time spent by an user on a particular Service for example Service A and fill in the following table of service_qaq which has the following format. Here id_acti is nothing but the activity_qaq from the first table of log_connect and id_service is nothing but service_qaq from the first table log_conenct. `service_acti` ( `id` smallint(5) NOT NULL AUTO_INCREMENT, `id_acti` smallint(5) DEFAULT NULL, `id_service` smallint(5) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_service` (`id_service`), KEY `id_acti` (`id_acti`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1128 ; -- 4) This step is the most difficult of all and i totally dont understand how to code this. here we fill in id_user as the account_id of our log_connect table, time_session is one one week and id acti is the activities from log_connect and time_acti is the total time spent is the total time spent by a particular user on a particular activity in that week, and similar is the serice and time_service. we have another table of the format `user_log_analyse` ( `id` smallint(5) NOT NULL AUTO_INCREMENT, `id_user` smallint(5) DEFAULT NULL, `id_session` smallint(5) DEFAULT NULL, `time_session` float DEFAULT NULL, `id_acti` smallint(5) DEFAULT NULL, `time_acti` float DEFAULT NULL, `id_service` smallint(5) DEFAULT NULL, `time_service` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_service` (`id_service`), KEY `id_acti` (`id_acti`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ; This is the general code i use for connecting to database and retrieving values <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr" > <head> <title> Login Details </title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> </head> <body> <?php try { $database = new PDO('mysql:host=localhost;dbname=Logindetails', 'root', 'root'); $bd = new PDO('mysql:host=localhost;dbname=BDQAQ', 'root', 'root'); } catch (Exception $e) { die('Erreur : ' . $e->getMessage()); } // First select the unique Account Ids,action,activity_qaq,service_qaq From our table log_connect $sqlListeAccountid="SELECT account_id as Account,action as actions,activity_qaq as activity,service_qaq as service FROM log_connect"; $Req_Liste_Account = $database->prepare($sqlListeAccountid); $Req_Liste_Account->execute() or die(print_r($Req_Liste_Account->errorInfo())); $ResultListeAccount =$Req_Liste_Account->fetchAll(PDO::FETCH_ASSOC); $nbrUser= count($ResultListeAccount); ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.