Selecting First Occurance In A Given Period Of Record (log Sessions And User)
I have a table to log sessions and user (connect_id) id's, start time etc - see below ...
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting First Occurance In A Given Period Of Record
I have a table to log sessions and user (connect_id) id's, start time etc - see below +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | connect_id | varchar(12) | | | | | | session_id | varchar(32) | | | | | | start_time | int(11) | | | 0 | | | client_ip | varchar(16) | | | | | +---------------+-------------+------+-----+---------+-------+ I need to create a report that will tell me, for instance, how many sessions were started today, that's easy I know but I also need another report that will tell me how many of those sessions were first-time visitors.
View Replies !
Selecting User,password From User
When I do: select User,Password from user in MySQL, I get encrypted password. How can I see the real value? And, what is the proper (secure) way to access MySQL database from PHP or other scripting languages?
View Replies !
Selecting ONLY The LAST Record
I need to run a check when displaying a record, that tests to see if the current record is the last record in the table. I need a way to select only the last record in a table. Can you reverse the LIMIT feature somehow to do this?
View Replies !
Selecting 1 Record Per Foreign Key
I am trying to query a MySQL table and retrieve either the earliest or the latest message in each thread. The messages are in a table with MsgID being the primary key and fThreadID identifying to which thread the message belongs. Example Table ============= MsgID (primary key) fThreadID ReceivedTimestamp (DateTime) 1 99 2004-01-02 11:10:15 2 99 2004-02-01 01:11:59 3 99 2004-05-17 18:15:01 4 102 2004-07-01 11:11:29 4 103 2004-07-01 11:11:11 Example Desired Result Set #1 (earliest msg from each thread) =============================== MsgID, fThreadID, ReceivedTimestamp 1 99 2004-01-02 11:10:15 4 103 2004-07-01 11:11:11 Example Desired Result Set #2 (most recent msg from each thread) ================================== MsgID fThreadID ReceivedTimestamp 3 99 2004-05-17 18:15:01 4 102 2004-07-01 11:11:29
View Replies !
Selecting A Record One Year Old
$query_articles = "SELECT * FROM ".db('prefix')."articles WHERE published = 1 AND category!='1' AND SUBSTRING(position, 1, 1) != '2' AND (date<=DATE_SUB(NOW(), INTERVAL 1 YEAR)) order by date desc LIMIT 1"; date=DATE_SUB(NOW(), INTERVAL 1 YEAR)
View Replies !
Selecting Random Record By Group?
There is probably a quick solution to this but I cant figure it out I have a table of products (id, products, group_id) like: 1, product a, 1 2, product b, 1 3, product c, 1 4, product d, 1 5, product e, 2 6, product f, 2 7, product g, 2 8, product h, 3 9, product i, 3 10, product j, 3 11, product k, 3 12, product l, 3 13, product m, 4 14, product n, 4 15, product o, 4 16, product p, 4 and want to be able to select one random product from each group. I know how to do a random record, and I know how to do a group by. I just cant figure out the combo. A result would be like: product a, product g, product j, product m
View Replies !
Selecting Record From Multiple Computers
multiple client computers and 1 server computer - server holds a 'jobs' database with records that need to be processed The client computer connects to server, and requests the next record. After it does the select statement to get the next record it does an update to mark that record as 'in progress' and no other client computer are supposed to take that record. Before that can happen the second client also requests a job and gets the same one (the update has not happened yet or too slow and each client gets the same record) and each client is supposed to get a unique record. Does anyone know if it's possible to prevent this? mysql table locking or row locking?
View Replies !
Selecting A Single Record According To Date
I have this SQL statement; "SELECT * FROM event ORDER BY month ASC, date ASC"; But i'd only like to select the next one event in order of the date field. Instead of selecting them all. How can i order by the current date? The date and month are seperate fields.
View Replies !
Selecting Values For This Record Based On Other Records
Is it possible in a select to reference the contents of a column in a different record to the one currently being assessed? Kind of like :- Select id,prev_id,title,flag FROM my_table WHERE flag != "S" AND flag(prev_id)="S"; What I'd like it to do is select records which have flag set "S", but the record referred to in prev_id do not have flag set "S". If it matter, I'm stuck on 4.0.
View Replies !
Need To Find 1 Occurance In A Table
I have a database storing photos. I want to display the first picture from each album on one page. The table in question has, say, 10 pictures, all with the same "album" id. I want to pull the first picture from each album. Any ideas?
View Replies !
User Needs To Delete Record
I'm working on a project for a client in which they want to add and delete events for a selected day on a calendar. I have built the calendar and added the events, but I'm having troubles deleting the event. I thought I could use the same code for adding the event and change some of the code. I get this error when doing so .....
View Replies !
JOIN -> Limiting One Record Per User
I have a database table called 'users' and an associated table called 'profile_pics' that has records for uploaded pictures for any associated user that chooses to upload one. The thing is, one user can have many profile pictures, but on this particular page I am making, I want only the most recent profile picture to display for each user. So I want my select query to retrieve all profile pictures from all members who have a profile picture(s), but only display one profile picture for each member (eventhough they may have more than one). How can I go about doing that in my SELECT statement? Any ideas how I can work this out?
View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
View Replies !
Php Sessions ...
I am wanting to store php sessions in the mysql database. What are the repracutions of this? Are there many writes and reads or only when it has to? I mean if we have 1000 users on the site at once will it do a 1000 reads every page load? Is it wise to store them in there or will it tax the server to much. I am basically interested in this method for a load balanced server, is it wise to use it?
View Replies !
Sessions On The Same Server?
I have a landing page. Lets call it a "mall". It is going to be connected to different online stores. Users will go to the landing page "mall" and click on a store that interests them. I want an id number from the mall they came from to go to the store and follow it through the checkout process. I want that id number to store into the database of the online store. Everything is going to be on the same domain and server and I am only using one store per product/ category. How would I get started on this?
View Replies !
Delete Inactive Sessions Every 60 Seconds?
I have a session table, where I stored user sessions in the database in a memory table. My question is, performance wise, would it be smarter to delete inactive sessions every 60 seconds via client http requests, or would it be wiser to select active sessions via a select that uses a where clause and searches a timestamp index column for active sessions, and then a cron job is scheduled to delete every 30 minutes. Which of these would be the better choice? On each page request I am going to have to select the number of sessions that exist and a second query that displays the active users. So if I go with my second choice, I will have to include a WHERE clause that scans the index on both those queries. But if I am issuing a delete every 60 seconds, then I won't have to do that, but then the delete is taking place every 60 seconds on a client request.
View Replies !
Database Sessions Lead To Clogged Connections
I had a system where when a user logs in, a CURL request is sent to the other http server which creates its own php session with the POST data (lets avoid all the security mumbo jumbo). This worked, but when one session expired on one server, then the other server had no idea of it. So I switched to putting sessions in my database for a cross-server solution (the two http servers share the same database). This solved the issue of mirrored session data, but then I started to get an excess amount of mysql connections (I'm using persistent connections) and as a result my website crashed. No matter by how much I increased the max_connections, the database server kept on reaching its limit (not a performance limit, but a connection limit). OK so now I've switched back to PHP sessions, but I made the session directory available to the other http server with NFS. This solution works fine.
View Replies !
Add A Period 5 Places From The Right?
I'm trying to modify some latitude/longitude columns which have the values like: 3482787, -11782842 They're stored in text fields currently. I basically just need to put the decimal in the proper place, which should always be 5 places from the right, so i need to make the above value (and the other 6.3 million rows) look like this: 34.82787, -117.82842 I cant seem to find the function syntax to do this in MySQL, tho I'm sure it exists. I dont want to write a PHP script to do this because it seems like PHP chokes on me if I work on more than 500 thousand rows at a time.
View Replies !
Time Period Results
Can anyome tell me the php script to return data from a table which is less than 24 hours old. I have a timestamp on my table when data is added.
View Replies !
Grouping By A Date Period
Let's say I have a table with dates that range from January to May in each record. Is it possible to group the records by a date range, for example: I want to group the records by a date period of 1 month, starting at the 3rd day of each month. So what I'd want to see is, five records for each month (jan 3 to feb 3, feb 3 to march 3, march 3 to april 3, april 3 to may 3).At the moment I have the following: select id, _date, sum(cost) from test where _date >= ' 2005-01-03' and _date <= date_add('2005-01-03', interval 1 month) group by extension union select extension, _date, sum(cost) from test where _date >= date_add(' 2005-01-03', interval 1 month) and _date <= date_add('2005-01-03', interval 2 month) group by extension The trouble with this is that each month interval has its own select query and that the increase in the month is hard coded (interval 1 month for example). Is it possible to use SQL to inrement the month?
View Replies !
Date Calculation For 24 Hour Period
I would like to automatically get the number of assignments received between a 24 hour period for the past 8 days. I am using "where received >= date_add( now() , interval -1 day)" to get yesterdays assignments....
View Replies !
Dumping Data From A Certain Time Period
Every time I make changes to my MySQL database on my local PC, I have to dump the whole table on the server. This can get a bit annoying when the table contains thousands of records.Is there a way for MySQL to log all the updates you've made since a certain time and then use this file to dump it on the server?
View Replies !
Select And Displaying 90 Day Interval Over A Period
SELECT DATE_ADD('2008-09-07', INTERVAL 90 DAY) What I'm basically after is to display a range of dates with a 90 day interval till a given date. As per the nultiple select statements below. SELECT DATE_ADD('2008-09-07', INTERVAL 90 DAY) SELECT DATE_ADD('2008-12-06', INTERVAL 90 DAY) SELECT DATE_ADD('2009-03-06', INTERVAL 90 DAY) SELECT DATE_ADD('2009-06-04', INTERVAL 90 DAY) SELECT DATE_ADD('2009-09-02', INTERVAL 90 DAY) SELECT DATE_ADD('2009-12-01', INTERVAL 90 DAY) SELECT DATE_ADD('2009-12-01', INTERVAL 90 DAY)
View Replies !
Removing Data After A Certain Period Of Time
What I want to do is run a query that inserts data into my database, but removes itself on a set date. Is there any way to do this? Basically, I have something like this: INSERT INTO table (blah, blah2) VALUES (blah blah, 1) I would like to do something like: INSERT INTO table (blah, blah2) VALUES (blah blah, 1) {{{ INSERT CODE TO MAKE VALUES GO AWAY WHEN CURRENT TIME = SET TIME TO GO AWAY }}} There is an alternative to this if I have to. This is all for a PHP script, and instead of having MySQL automatically remove the data, I could add a time to remove to the table, insert the value for it in the INSERT query, and run a query on the index page consisting of something like <?php $timeToRemove = SELECT_TIME_TO_REMOVE_FROM_DB; if($currentTime => $timeToRemove) { REMOVE; } ?> so that every time someone hit the index page, it checks if it is time to remove the values from the DB and if it is, it does so. But of course, that adds an extra query/queries, and I'd like to avoid that. Edit: It may be useful to note that this is for a message board system, and I am dealing with making usergroup changes temporary. So instead of removing the values, perhaps it would be more useful if I could simply have MySQL reverse itself after a certain time has passed. But that seems unrealistic for some reason now that I think about it, so the alternative may be my best choice. Any suggestions from you all would be nice as well, if you've any ideas.
View Replies !
Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this: WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00")) Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like: Day Min Max 2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59 2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68 where at this point i`m getting min=40 and max=68
View Replies !
Room Reservation: See If A Time Period Is Open For A Particular Room...
this is stumping me... i need to see if a time period is available for a something (in this case a room reservation). it's not as simple as starttime < end and endtime > start... for example a room is scheduled from 12:00 to 1:00pm and from 2:00 to 3:00pm. how can you check if a user entered: from 1:00pm to 2:30pm? i'm trying to UNION but that doesnt seem to work. subselect?
View Replies !
Automatically Record Date Of Record Entry
I have my database table set up and I have an HTML form that is PHP driven that will add the information entered into the form into to my database table. I have a local buy-sell-trade Website. The way it has worked is that people fill out a form and the results emailed to me. I then take the information and enter it into a Web page. I only want the ads to be displayed for 30 days. I keep the ad for a total of 6 weeks (displays for 30 days and sits in limbo for 2 weeks afterwords) and if not renewed within that 2 week limbo period - I delete it. Entering all the ads and keeping up with the dates manually has become a burden. I've only recently began looking into databases. My hosting company provides me with phpmyadmin and mysql 5.0. I'm new to all of this but I have managed to set up a database table and a HTML form that is PHP driven that allows ads to be automatically added to the database table. There is a lot I need to do to make this ideal, but one step at a time. First, I need to know the date (March 02, 2006) the ad was created or added to the database table. I know that I need to add some piece of code to my php form to record this information, but what code and where do I put it? I know I will need to create an extra field in my database table to house the date - I can handle this. I've read the date and time information here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html but there's a lot there and I don't know which is right for my needs. Plus, it doesn't tell me how or where to insert it into my php form (or does it?).
View Replies !
Getting Record Before Or After The Record That Meets The Criteria
In a query I want to get all the results and then order those results by last name, but then I want to filter those results down to only the record that comes before or after the record that has 'empno' = '1259'. I want to get all the results already ordered and then filter them down to one record either before or after (depending on what is needed) the record where 'empno' = '1259'.
View Replies !
How To Record The Current Record In A Table?
I'm looking at convert file formats to mysql. The original file format used an "ENTRY" conception to record the current record. But I cannot find a solution to emulate "ENTRY" conception in MySQL. After searching MySQL's tutorials, the AUTO-INCREMENT fields would only use "mysql_insert_id()" to obtain its value. But this value cannot identify the current record, Right? This value are only last record after inserting? Who can help me to find a better solution to emulate implementing what the "ENTRY" does. In other words, How to obtain the auto-increment value contains AUTO-INCREMENT columns in a record which generated by SELECT statement.
View Replies !
Error 1142 - Mes. The Following Occured: SELECT Command Denied To User Xx@hostx.com For Table 'user
When trying to do anything to do with "User Administration" in MySQL Administrator 5.1 connecting to a remote MySQL database, I get the following message when I click on the "User Administration" icon/text in top left frame. Verbatim quote apart from my connection details which I am anonymising): A MySQL error was encountered. The message is: Could not fetch user names. The following occured: SELECT command denied to user xxx@hostxxx.xxx.xxx.com for table 'user' (1142) No I have looked through search and found the following: http://lists.mysql.com/mysql/134369 Unfortunately that tells me that it has happened to someone else but that's about it. What is one supposed to do with the fix? I am wondering if this is why I cannot create ordrop any tables with this username at the moment, even though it is the Admin level username....
View Replies !
Root User Denied Access To Fetch User List.
I can still log onto MySQL administrator or the command line program but I somehow appear to have lost all priveliges to alter or view the user list from this account. Even under the command line interface I can login but it tells me access is denied when I try to alter "root" permissions on a database. I cannot seem to log onto any of the other accounts created but obviously if I could get into the user admin I could solve this.
View Replies !
Copy User Privileges To A Newly Created User.
I have just created a user with specific permissions to certain tables in different databases. Now I have to create serveral more users with the same permission. Is there a command in MySQL that allows me to create the new user by copying the privileges from a template user?
View Replies !
Record Creates Record
I have a table that holds inventory information...primary key'd with an asset#. I have another table that I hold certain information in that I don't want in the main inventory table. I would like the second table to key itself and automatically enter a record when a device is entered. For example, I enter a scanner in my main table, but I want the DB to then also enter that assetNumber into the second table for me.
View Replies !
Deleted Root User From 'user' Database!
I so stupidly deleted the root user from my mysql > user database. Now I can't login to mysql. I uninstalled the rpm and reinstalled it but it still won't let me setup a new root user. How can I delete the mysql data files so that I start again, fresh? Where are these files located? I can't find them anywhere after removing the RPM but they are obviously there because upon reinstalling the RPM I'm still in the same position--I try to do mysqladmin -u root password 'password' but it says "Access denied for user root@localhost"
View Replies !
Access Denied For User: 'user@172.22.0.1' (Using Password: YES)
I am currently running two servers one with Tomcat and the other with MySQL. They are both in different Windows domains and on different network segments. I have the following users on the MySQL server: user@localhost user@% I am getting a VERRRY SLOOOW connection between the two machines so I thought I'd add a user just for the Tomcat server. The IP address of the Tomcat server is 172.22.0.1 so naturally the user would be 'user@172.22.0.1' When I added this user I got the following error Access denied for user: 'user@172.22.0.1' (Using password: YES) Any ideas on what I can do to remedy the situation?
View Replies !
Deleted Root User From Mysql.user !!!
While trying to grant access to root user, i deleted all entries for root from mysql.user. I've tried to use root% (anonimous built-in administrator user) but to no good). Is there another admin user i could use to run mysql monitor and insert root records to mysql.user? I've read Could I use a copy of the mysql.user table? I'm using version 5.0.16-standard on Linux Fedora Core 3. Before ruinin the root user, i made a backup of a databasefrom the same server (not mysql database); i was wondering if that backup contains the mysql.user information somehow?
View Replies !
User@localhost And User@domain.com
I have been following a tutorial for a mail server which uses mysql, mainly postfix all through the tutorial i have been asked to put a user and password in the files for the database.. which i have done root and pass1 in the log files the error shows as warning access denied for user root@domain.com ( using password : yes ) when i log on from the shell, to mysql and purpously get the password wrong, I get access denied for user root@localhost ( using password : no ) i have checked the password is correct.. is the part after the @ the issue, and how can i tell mysql that root@domainname.com is a valid user???
View Replies !
User Problems... Only Root User "works".
i have been trying to figure this strange problem out for a week or so. here it is. i have a set of users with various permissions for different reasons. about 2 weeks ago, my hardware suffered a fairly nasty failure. i recovered everything. things seemed to work out. i didnt think to check my phpBB forums or my blog (or other mysql based stuff). never had any problems with the DB... so, maybe i am missing something simple here. i hope so. what my users can do: they can log in via command line, a gui i use on mac os x and it does not matter if it is local, or remote. they work just fine. what all but one users cannot do: none of my users can run phpbb or the blog stuff.. except for the root user. any other user returns the error message written in the code of which ever script i try to access telling me that "could not connect to DB" etc.. root works fine. now, i thought maybe it was a problem with the forums and other stuff. but i cant be. same thing happens with all things... even a game server i occasionally run that keeps account info in a DB. all these users are set up properly and have worked just fine in the past. nothing new has been installed, no conf changes..im at a loss...
View Replies !
Changing User From One User To Another
Currently i have 2 Users in My MySQL(root,user1)...Whenever i am clicking command prompt Client its directly taking me to root user. If i want to change user (to user1) from there how i need to proceed...(Like connect in Oracle what we need to be use in mysql.
View Replies !
Getting Record
I need to query my db and only display the record with the highest number or amount. Example Record 1 bob $250 Record 2 bill $350 Record 3 bud $500 Query would only show "bud $500" and not display the other 2. Once bob updates his record to $501 then it will display "bob $501".I have everything else I just need the query syntax.
View Replies !
Last Record
I want to find out what was the last record updated. If I wanted to know what the last record inserted I would use last_inserted(id). Would I use the same function?
View Replies !
A Record
I have a database of songs played on a particular radio station, along with the date and time they were played. As you can imagine each artist-songtitle pair are repeated quite a bit. I'd like to find a query that would leave me with the earliest dateplayed and timeplayed values for each uniqe artist-songtitle pair. I have a MYSQL table containing artist,songtitle,dateplayed,timeplayed. The dateplayed is a Mysql Date vatiable and the timeplayed is a mysql time variable, the other two are varchars.Essentially what I want in the end is to know the first time they played each particular song within the database.
View Replies !
Next Record
I want to compare the value in one field in a record to the value of the same field in the next record. This is something that is pretty easy in a spreadsheet, is it possible in mysql?
View Replies !
Certain Record
lets say i have 10 records in a table. i want to start at record 6 and select all records up to 10. how do i do this? i know i can select all where record equals 6 but that will only pull all the fields for that one records.. i need to pull all the fields for records 6 through 10.
View Replies !
Doing Changes To Every Record
i need to reaad all the data for echa record, do some calculations, and finally update the data to each record. i ask this because is a large database. whats the best way to do this?
View Replies !
|
TRACKER
