Jump to content

I have a problem with my php code regarding the insertion into the database


chaithu7844

Recommended Posts

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>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.