Tracking The Total Time Each Day A User Spends On Site
My DB is currently setup like this:
SQL Code:
id   userid   timecheck      visit_type
1Â Â Â Â 255Â Â Â 1205896013Â Â Â Â Â IN
2Â Â Â Â 255Â Â Â 1205896017Â Â Â Â Â refresh
3Â Â Â Â 255Â Â Â 1205896025Â Â Â Â Â refresh
2Â Â Â Â 255Â Â Â 1205896150Â Â Â Â Â IN
I basically need to grab the difference between the time from the first IN of the day to the row marked refresh just before the next in, and then repeat wherever the value in the timecheck column = the same day.
PHP Code:
$sql= "select * from timetable where timecheck = '".date("M d, Y", $row['timecheck'])' and visit_type between 'IN' and 'refresh'";
View Complete Forum Thread with Replies
Related Forum Messages:
Time Tracking
I am having a hard time tracking to when it started. But basically in IE when I attempt to load a script for what it seems like the first time per session I get a login prompt open like what i see when I login in to MySQL, this script uses MySQL heavily.
View Replies !
Time Calculations:Call Tracking System
I am creating a call tracking system that includes start date/time and end date/time. They are being stored in yyyy-mm-dd hh:mm:ss format. An example of something I need to do is query for all open and on hold calls, as well as calls closed within 1 hour for the past week. Select call_num, xdate, caller, dept, problem, specialist1, specialist2, specialist3, specialist4, status, end_date, current from CALL where (ucase(status) ='OPEN' or ucase(status)='HOLD') or ((ucase(status) = 'CLOSED') and TimeDIFF(Now(),end_date) < '00:00:00') order by current, status What valuse should be placed in the TimeDIFF() function instead of '00:00:00'? Am I even heading in the right direction? Should I use some form of timestamp? I also intend to have dynamic reports that will query between certain dates as designated by the user. Oh... I haven't gotten around to changing the name of the status field yet... and I am MySQL 4.1.7.
View Replies !
Tracking User Specific Logins
Good evening, I am the ultimate newbie. I have a webpage running on an Apache server with MySQL installed. I use Dreamweaver 8 to construct webpages. I have constructed a login with password page that allows a person to view a report that corresponds to their business (their account). There are roughly 170 individual businesses and no one can login without ID number and password. These values are assigned and may not be changed by the individual. This "service" is not up and running yet, but I have tested and with phony data loaded in the database, I have had other test it out as well. Everything seems to work just fine. Before I begin sending out ID's and Passwords, I was thinking it would be interesting to add a column in the database to record (tally) each visit that an individual makes for each of the businesses. That way I can see who is using it and who is not. I am looking for help on how to put some code together that would tally each business'visit based upon their ID number.
View Replies !
Compute Time On Site
I have moderators and i need to compute their time spend on site. I have table moderators_actions(mod_id,action_date). I make up to count sum periods of time between moderate_actions, but only them which are shorter than 5 minutes(if it is longer than 5 minutes I assume the moderator made a break in his work). My queries: 1. The query finds for each moderators_action next action of this moderator, and time between the action. Choose only shorter than 5 miunutes.
View Replies !
Sum Total Of A Time Field
We have a table with a field of type TEXT storing the time for certain actions (ie. 0:30 - for 0 mins 30 secs, 1:20:00 - for 1 hr 20 mins 0secs). We've come to a point where we'd like to sum the entire field up and get back the total time. I wish we established this filed as a time data type rather than TEXT but that's the way its set up. How would you go about achieving this? Perhaps I need to convert the TEXT to some other format to get back a more usable value for my PHP code to use?
View Replies !
Site User To A Paid Member? Database Design HELP
I am currently working on a website where there are 2 types of users. The regular site user and a paid member. I am trying to figure out the best way to design the database (mysql) without redundancy issues. The site user can eventually become a paid member but a member does not necessarily have to be a regular site user (in terms of category). Information stored on a site user - first name, last name, address etc. The userID being the primary key. The member pretty much the same thing but new info will be memberID, join date, expiry date, status (pending, active, expired). I thought the easiest way to do this was to have separate tables. Do you think the best way? My thoughts are: When the regular site user becomes a member then store the userID in the members table. The issue I am faced with is how to store the other pieces of information. The address is not mandatory for the site user but is mandatory for the member. So, if the site user has an address then store it in the member table? that's redundancy there. Then a member do not necessarily have to be a site user in the sense of storing the info in that table. Design issues to think about - activation of membership once a confirmed payment has been received - Client is using Paypal. -What if the user clicks on membership and the user is also a site user? -What if the user is logged in as a site user and clicks membership? I would greatly appreciate your design recommendations.
View Replies !
Mysqldump Server Becomes Busy For Longer Time And Site Stops Working
When I'm making a backup of mysql databases, during the dump apache is unable to load pages because mysql is no longer responding to new requests. The backup is taking 5 minutes or so, which essentially brings down the website until it's finished. Has anyone got any suggestions on how to overcome this problem? I'm thinking maybe if I could quickly make a copy of the database to another database within mysql, then backup the copy that was just made.
View Replies !
Random Password Vs. User Created Password For Site Login
Are there any security issues or other concerns that make one preferable? The client is pushing for user created passwords. I'm mapping out the basic functionality and front-end for a MySQL/PHP back-end that will be completed by a third party. I'm a novice to MySQL but familiar with PHP and their interaction. Mainly looking for anything to support one method over the other.
View Replies !
Calcuate Row Total And Its Cummulative Total
1. I hava a table structure like. EID Day1 Day2 Day3 RowTotal CumTotal And Results will be EID Day1 Day2 Day3 RowTotal CumTotal 1 1 0 0 1 1 1 0 1 0 1 2 1 0 0 1 1 3 How to calculate RowTotal and CumTotal in INSERT or UPDATE or SELECT Statement 2. I hava a table structure like. EID Day1 Day2 Day3 1 1 0 0 1 0 1 0 1 0 0 1 But I want after SELECT Statements Executes in the Following EID Day1 Day2 Day3 RowTotal CumTotal 1 1 0 0 1 1 1 0 1 0 1 2 1 0 0 1 1 3 How to calculate RowTotal and CumTotal in SELECT Statement
View Replies !
First Time User Need Help Connecting To Local Db
I've worked a bit with creating and connecting to databases online using PHPAdmin but I'm trying to set up a local environment and I know NOTHING about that. I installed MySQL 5.0 yesterday and it seems to be OK. At least I can bring up the command line client and get the mysql prompt. (I'm on XP Pro, btw.) I really don't know what to do next, though. I've built my db model in DBDesigner4 and supposedly, you can use that program to create and synch your database. I tried doing that and I keep getting the invalid user name/pw error message. I don't know if I have to create an empty db first or if there's just something wrong with the user name/pw. I'm using the pw I created when I set up MySQL and using "root" as the username. Anyone have any idea what I might be doing wrong? Do I need to create the db on my machine first? If so, how do I do that? Any ideas why I keep getting the invalid username/pw message?
View Replies !
First Time Install On Windows - Admin User&password Problem
I have a problem with logging in to MySQL. Access denied. I think the issue is with the host, user and password settings. I assumed you had to add this information to the my.ini before you started the service for the first time. Is this only for WinMySQLAdmin? Is there a default? I have tried all the solutions I could find but they seem to all require user privileges in the first place. I simply need to have one main admin user and password and to refresh their values to be certain of the new settings in case there was one before.
View Replies !
Tracking Arrivals &
I need to create a simple database that tracks equipment arrivals (and departures) at my facility. What I want is to create a simple form, which the receptionist could fill out, every time someone delivers something at the front door. The form would simply have a place to put the time/date, delivery method, addressee, and a brief description. Once the receptionist filled it out and hit enter it would create a new record and then send a summary notification email to a group of people on site who would then process the equipment as needed. Is there relatively simple way to do this in PHP & MYSQL? Basically all I need to create a data entry form that also sends a notification email. I was thinking I could just create a simple form on our intranet site. I would also like a way to track items as they leave the building as well.
View Replies !
Tracking Changes In MySQL
I'm trying a simple task of synchronizing MySQL and OpenLDAP. As a part of it, I donno how to detect changes in MySQL. Please let me know how to do that or point me to any source regarding that. Please reply soon as this is a part of my project.
View Replies !
Tracking Database
Here's what I have: 1 database dedicated to this project. 1 table named "amount" 1 field named "donation" An input form for the site owner to add donations to the database (tested and working, but...) I cannot show the output of the fields as a total. If I do a query, it simply lists the donations. I want to display only the total so far (I may at a later date add a display of "who donated what" but that is not a consideration for this project). Some details that may or may not matter: Apache version 1.3.37 (Unix) PHP version 4.4.3 MySQL version 4.0.27-standard
View Replies !
Tracking Table Assistance
I am wondering if this is considered incorrect table design, and if so, would it be better to separate some of the records. I am going to use "School Districts" as an example. Tables that hold information for each part of the district would be: district_info [name/info of district] school_info [name/addr/etc of school] class_info [name/type of class] teacher_info [name/type of teacher] student_info [name/addr/etc of student] I want to create a "tracker" table that will not only hold current information for districts/classes/etc etc, but previous information as well district_tracker_table: district_id: int(NULL) school_id: int(NULL) class_id: int(NULL) teacher_id: int(NULL) student_id: int(NULL) status: int(0 = old, 1 = current) Each of these fields would have a FK to their corresponding table..
View Replies !
Indexing And Tracking Profile Changes
I have a history table that tracks the changes users make to their profile. It is basically a replica structure of my "profile" table with some slight changes. It writes a line for each user change to the profile. (Note: They can have several profiles. ) I have a history table. with the following columns. (id) | (profID) | (uID) ...|...|... (id) is Auto-Incremented and is Primary Key(I belive it has to be PK), the next column (profID) is the profile ID, The 3rd col is the user ID. So if userID 5 changes their profile #2 twelve times over the course of a month, there will be 12 entries for prof(ID) thoughout the table. This table will be used for analytics only not for user queries. So I want to INDEX this table based profID right? or would it be uID > profID? Also, do I need to remove the (id) column from the table? If it is Primary Key will it screw up my index? I see it listed in the INDEX section of phpMYAdmin.
View Replies !
Tracking Unique Hits
I've got a simple database to track who looks at any given sale item on my site. The table is set up as such: CREATE TABLE stats ( stats_id int(11) NOT NULL auto_increment, stats_create int(11) NOT NULL default 0, sale_id int(11) NOT NULL default 0, user_id int(11) NOT NULL default 0, user_ip int(11) NOT NULL default 0, PRIMARY KEY (stats_id) ); The `stats_create` field is a unix timestamp of when any given user looks at the sale. Currently, I came up with this to get the total number of hits over the last 7 days (today included) for any given $sale_id: SELECT DATEDIFF(CURDATE(), FROM_UNIXTIME(stats_create)) AS days_old, COUNT(stats_id) AS hits FROM stats WHERE sale_id = $sale_id AND DATEDIFF(CURDATE(), FROM_UNIXTIME(stats_create)) < 7 GROUP BY days_old What I would like to do is to come up with a query that will select the number of unique hits over the last seven days. In other words, if user #2 has looked at this sale previously, their visit will be counted either 0 or 1 times (1 for the first time they visit--which would show up if that falls within the 7-day range; but 0 for all times thereafter). But as you can see, I have no idea how to formulate such a query.
View Replies !
Tracking Number Of Hits
For a project I am working on I need to track the number of hits per profile view. Originally I just stored the hit count in the profile table. Except now the client wants to be able to view reports showing number of hits between any two dates for any profile. I really can't think of a good way to do this. This is all I can come up with for a table structure: profile_hits -date -hit_count -profile_id The problem is I will be inserting a LOT of records into this table. It will work but perhaps there is a better way of doing this?
View Replies !
Tracking The History Sql Statements
I have a system runnig with many different insert,update and select queries running. sometimes my databases get updated with wrong values and I cant track the sql statement which cause this. So I want to get a list of all the sql statement that has been executed on that server? How can I get this information ?
View Replies !
Tracking Record Insertion And Deletion
I am writing some software that, among other things, needs to track the state of database tables. This includes occasionally checking the table to see what records or added, modified, or deleted. The added and modified parts of this have not proven too difficult to implement, but is there an easy/elegant way to determine what records have been deleted from a mySQL table ?? I'm very to mySQL, and after reading through the docs about logs, the only thing I can come up with is to make a slave of the target table, and somehow catch the delete statements, but do a select instead before deleting them from the slave. Has anyone tackled this problem and can offer some advice, or pointers to advice..
View Replies !
Field/Record Revision Tracking
I am looking for a method to keep track of revisions. I have developed many engineering database applications. Keeping tack of changes to fields is often required and I have never implemented a method that looks/feels good. There are two basic types of revisions. Revisions to field values and revisions to groups of fields. An example of this is an application that manages datasheets for instruments used in chemical processes. There is data associated with the process itself and data associated with the instrument serviceing that process. When a datasheet is ready to be issued so the associated instrument can be purchased, a view of the related data is produced as a 'datasheet'. This datasheet has a revision designation. If any changes occur to the data associated to the datasheet after its initial purchase issue, a new 'revision' must be published. Additionally, once, say, the process data changes, the instrument develpement process may change data multiple times before the datasheet is ready to issue again. 'Most' of these revisions must be stored/managed before the next revision of the datasheet is issued.
View Replies !
Tracking MySQL Table Transactions
I am trying to understand how I could manage MySQL transactions. I have this database that contains a very important table. Its data changes a lot and contains student grades. I could have another table where I would keep track of the original table's transactions. I make a backup of this table on a weekly basis and I'm thinking that if I keep track of the sql queries I could recreate the data at any point. So, if a user tels me that he/she overwrote the data, I would restore the portion of the table from the weekly backup, and then go to the transaction table and issue the queries up the point where the data is valid. Is this a good way of restoring data? Is there a better way? Does MySQL have a buil-in system of restoing data?
View Replies !
Regarding Tracking Error Codes In MYSQL
how to retrive the errors in Mysql As i will do it in oracle like the below one: DECLARE code number; BEGINcode:= SQLCODE; SELECT * FROM table WHERE field = 1 and field_1 = 2; dbms_output.put_line('The ErrorCode is '||code); END;
View Replies !
Tracking All DML And DDL Statements In Database Tables
I want to know one thing, i have a database which contains few tables and few default records in that. Sometimes unusually some developer is truncating the tables or else dropping the table without my knowlege. How can i track these changes in my database? In Oracle, i heard that there are DDL and DML Triggers available but iam new to MYSQL. can anyone tell me is that any functionality available in MYSQL?
View Replies !
Tracking MySQL Outgoing Connections
I currently have this serious issue where a bunch of mysql connections are being sent from one of my servers to another. I think its a virus or some sort of hole in my scripting but my main server keeps on sending fake logins to my database server. I do not know how this is happening, but IS THERE A WAY for the mysql client (not the database server) to track all of the outgoing connections that are being made?
View Replies !
Site Down
Need help figuring out ohow to unlock tables. Everytime I try a repair with my Web Host Manager my whole site freezes up. I have one table that is "in use" a sessions table. I can use putty and get to the >Mysql prompt but I am lost from there... Is there a way to repair or fix this? I have tried using phpmyadmin and cpanel and neither work, they just time out.... what command should I use?
View Replies !
MySQL On A Web Site
I am working on a web page that is currently using a MySQL database to manage our product inventory on our web site.The problem is that I am not the person who developed the web page, but it is now my responsibility. I am going to continue using the existing database, but the management process of a MySQL database on a web server is a new process for me.I am going to assume that I should be using the phpMyAdmin tools to interface with the database, but the php programming is new to me, as well. I'm a fast learner, so what I'm really looking for is a bit of direction in how I can improve or reconfigure the output for the end-user.Anyway, I don't know if I have provided enough information, but if any of you guys can point or steer me in the direction that would make me an educated contributor to these forums, I would appreciate the introductory patience.
View Replies !
Connect My Site
Im trying to connect my webpages to the MySql database that i have as part of my hosting account, ive dropped a MySqldump file into the database and have been trying to connect my site to the database but im somewhat confused about how to do this, i understand ive got to use a little bit of code to make the connection but where do i place that code? and do i place it on every webpage, im trying to put together GET variables so that when someone clicks the "Buy" link it should then make the billing popup appear, but mainly at the moment my main confusion is about actually what to insert and where, in order to make the connection between my pages and the database
View Replies !
Remote Site.
i jus completed my first database site and ready to upload. but need to know 2 things. 1. where exactly is the database? ive got my site in the inetpub.. is the database in here C:Program FilesMySQLMySQL Server 4.1 ? 2. how do i upload it along with the site? is there just 1 file to upload or is there lots... ?
View Replies !
Huge Site
I wonder if MySQL should work with tables having milions of rows, e.g. you have image uploading site and you store daily around thousands of photos and regularly querying them. What are the techniques of this? Is it possible to do this with mysql? Do you know some books, articles about this? I thought about indexing or caching(of course if possible) and I want to learn something about this.
View Replies !
Web Site Form
I want to have a form on my website, almost like a feedback form but not for feedback. It will be for getting info from customers. It will simply be about 30 fields that will need to post to a database. How hard is this with mysql? How would I do this on the web page? Is there a certain language I would need to use? Also is it possible to get an email everytime a record is added via the web form?
View Replies !
Search From Web Site
I have a simple search function. Just an input field and a submit button that should search a table in mysql. I do some regexp stuff in mysql so i use the REGEXP syntax but you don't have to bother about that, since we'll just asume that the user have input "x y z" in the search field. Problem 1 I have three fields in a mysql table that i want to search. The simplest way I came up with to do this was: SELECT * FROM table WHERE CONCAT_WS(' ',field1,field2,field3) REGEXP 'x y z' But this won't work if field1 = z, field 2 = y and field3 = x . How can I achive this? It's quite impossible to ad an OR statement for every possible combination of x, y and z. (perhaps I will need to search more fields in a go in the future)... Can I change the regexp to be able to match words in any combination? Problem 2 How do I set up an index in mysql for the kind of search mentioned above? Is it usable to have an index on field1, field2, field3 if you're going to do a concatenation on them anyway?
View Replies !
AIX 5.1 Binary From UCLA Site?
I wonder if anyone would share your experience with the binary download from the UCLA library? I am thinking of an AIX 5.1 box (32 bit) first. If things go well, and if manager allows, I'd like to try another box (AIX 5.1, 64 bit). It'd be nice to hear a success story before taking the jump. Also, is UCLA's binary the same as the one at mysql.com site? If different, in what way? Is one of them 64bit and the other 3 2bit? Or are they both 32 or 64 bit?
View Replies !
Porting A MySQL Site
I am creating a site here on my local machine, mainly to learn Mysql and php. I am using Mysql 4.1.10, php4, apache 2.0 and windows xp home. After I get an appplication built and running well, what am I going to have to do to make it run on the internet? Will I simply have to change all occurances of 'locahost' to 'mydomain.com' ?
View Replies !
Redesigning ASP Site And MySQL
I am in the process of redesigning a web site in asp. I have scrapped the access database and we are now using mysql server, I am having troub le with a query which worked fine with access but nothing with mysql. rsUKClients.Source = "SELECT ProductCust.PRODUCTCODE, Cust.* FROM ProductCust INNER JOIN Cust ON ProductCust.CustCode = Cust.CUSTCODE WHERE (((ProductCust.PRODUCTCODE)='" + Replace(rsUKClients__MMColParam, "'", "''") + "') AND ((Cust.Country)='UK')); " The record set is apparently empty, but as I said before it works fine with access and the results are as expected.
View Replies !
Cms Site Lo My Local Server
i'm having problem running my cms in my local server, i've downloaded our company sms from our remote webhosting server and tried running it in my local server but a lot of errors popped-up? like: in index: Notice: Undefined index: pagsanjan_cookie in C:Program FilesApache GroupApache2htdocspagsanjanindex.php on line 568 and what's worst is as i logged in try to modify contents through its php cms alot of errors would pop up like: Notice: Undefined index: fcategory in C:Program FilesApache GroupApache2htdocspagsanjanDynamiclist.php on line 65 Notice: Undefined variable: sqlstring_temp in C:Program FilesApache GroupApache2htdocspagsanjanDynamiclist.php on line 118
View Replies !
One Database For Site And Forum?
I have a site and a forum and I want to use one database for them, so when somebody registers in the site - to be registered automatically in the forum. Is this possible and if it is - how?
View Replies !
Building A 1 Page Site?
I have a couple of review site and I'm thinking of building a new one, but along the lines of a 1 page (well, maybe 2 or 3) site. I think the database is pretty basic and the queries dead simple (okay, except for the "I'm thinking about")*, but I'm concerned about 2 things. 1 - site simplicity and fast page loads 2 - some duplicate content The reviews will have 2 separate text fields. One will be a description written by me .. unique text. Another will be a review = customer comments supplied by an affiliate .. duplicate content. Each text field (description and review) will be up to 10k in size. The database records will also have fields for : name, image link, affiliate link, and a few more .. very brief. Probably a total of 10 fields. What Im wondering: 1 - what problems should I watch out for, if any, by adding this much text. *I'm thinking about making the first query show up to 10 or 20 items with just maybe the first 30 or 40 words of each text field plus a thumbnail; and then a link in each that would produce a single page with the full text and larger image. 2 - I'm a little concerned that I may get a duplicate content penalty. Will having it in a database help? I'm pretty sure I could put the 'echo' in an iframe and the iframe in a directory and exclude SE bots from that directory with robots.text .. does that make sense? Finally, there are hundreds of items so I plan on breaking them into categories to keep the menus a reasonable length .. and using javascript to to show an *items* menu when the *category* menu is selected. So should I go ahead and use AJAX to query the database?
View Replies !
Db On Live Site And Replica On Development PC
I have a Db on my live site, I use DW to manage the site and phpMyAdmin to manage the mySQL Db, and I'd like to do the following: 1. Replicate the Db on my Dev PC, to modify then reset the live version on the site to match it. 2. Set phpMyAdmin config to look at the PC version of the Db when developing and the Live version when its up on the site. Hope these make some sort of sense - just new to the world of php and mySQL (actually an MS developer).
View Replies !
Database Design For Ecommerce Site
I am looking to design an ecommerce site with categorised products. Once built, I want my client to be able to add additional categories and be abel to add their products into their chosen category.Would I be best displaying this dynamnically after creating a table with the products category names in it or doing it static? Also, if I wanted to show a different header / meta tags for weach category how would I best tie this in?
View Replies !
Simultaniously Access Crashes Web Site
It still goes fast tho....faster then some single queries i used before...only this is on localhost... any idea how this is gonna hold when the website goes online and like..10 people come browsing it simultaniously (or 100!) or how i find it out to make sure before it crash...or even build it is 7 queries maybe acceptable or not for 1 website page?
View Replies !
Create Php Site From Existing Database
I have been contacted by one of my clients to construct a knowledge bank based on their ms access database. I have successfully converted the ms access database to MySQL using the GUI conversion tools supplied by MySQL.com . I am wondering, and I don't know whether this is the right site to do this, if I can easily create a php site based on the database tables that have been converted.
View Replies !
Capitilised Words (values Within Site)
I have have changed servers for my website and when I reuploaded my database backup some text within my site is capitalised. The text that is in highercase seems to be values for example the words I have noticed are SOME, TO, FROM etc it seems to be the same words. here is another example: "This is Kidderminster's 4th annual Rock Festival, this YEAR taking place ON 28th September 2007, FROM 6:30pm until Midnight AT Kidderminster Town Hall IN the West Midlands." Has anyone ever come across anything like this before? If I hav backed up wrong or imported wrong please could you provide steps with the DBtools program.
View Replies !
MySql 5 On Hoster's Remote Site
I set up an application using Flex, C# web services and MySql 5. On localhost the app works fine. No problems. However, when I uploaded the site to our hosters, C# can't find MySql db. I followed all the same directory structures layout as on the original. Using MySql Maestro as my tool I can connect to the remote MySql db.
View Replies !
|
TRACKER
