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>
