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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
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 !
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 !
Get Last Record
i have table "test1" those the field and records : ----------- close_price ----------- 01/15/2007 02/15/2007 03/15/2007 04/15/2007 05/15/2007 06/15/2007 07/15/2007 08/15/2007 ---------- i want to get date "06/15/2007" where close_price="07/15/2007"
View Replies !
How To Get Only One Record ?
Suppose there is many records return by sql script： select * from student where age='20'; However ,I only want to get just one record,that is to say, it is not necessary to query the whole database, just get the first record meeting with the condition and return it. This operation can save lots of time if the database is very large.
View Replies !
Getting The 1st Record From Many
I am trying to output data to a web page that will show me the latest (date) activity for a specific field: The source info would be as follows: id, date, username, notes 1, 1/1/07, bill, broken screen 2, 1/1/07, mary, format hd 3, 1/2/07, bill, returned 4, 1/3/07, jane, lost document 5, 1/5/07, mary, returned 6. 1/6/07, mary, lost document I want to get the latest activity for each username. I would like the output to be: 3, 1/2/07, bill, returned 4, 1/3/07, jane, lost document 6. 1/6/07, mary, lost document I have seen the distinct option but every example I have found shows it working on just 1 field.
View Replies !
Get Every X Record?
I have an application loading my db with a new record for each weather station every 30 seconds. For a graphing application, I need to extract every 10th record for a station. (2880 records per station per day, but I only need 288 records for a 24 hour graph). How can I write a query to do this? FWIW, I have a timestamp on every record,
View Replies !
Against Next Record
I want to run a query to see if one field is a subset of another field. Is there a way to check if one record's field is a subset of the next record's field's value. Specifically, I'm checking for duplicates in the database, and want to find (eg) Rob Smith == Robert Smith. So I'm going to make one field with last name + first name to get SmithRob and SmithRobert. Once I order them on that new field, SmithRob will be right before SmithRobert, and I want to flag the records where one record is a subset of the next record. Is this possible?
View Replies !
Getting Next Record From DB
I got a database with 2 fields. ID (autoincrement) name (carnames) let's set it up like this: ID name 1 porche 2 mercedes 3 BMW I want to search for mercedes, but instead of showing me the mercedes record, i want it to show me the record after that (the BMW record). what i got so far is: SELECT * FROM car_names WHERE name LIKE '". $car ."' this works perfect if i wanted to see the requested car, but i want to see the record of the car that comes after the $car. ($car = in this case the mercedes) anyone got any idea ?
View Replies !
Getting The Last Record
I need to generate my primary key from my application bcos my PK has a specific format(e.g. Cl2006-09). Now to do this I need to know the last record/primary key of the last row(or only one record if exists only one)so dat I can generate my new PK by adding up to this last value. By which query I can know only the last PK of my table (or only one PK if only one exists.)
View Replies !
How To Get The Right Record?
I have 2 tables - property_active, and property_inactive. Both tables are identical in structure. I have a record_id, relating to a record in one or both of my tables. I wish to do a select, whereby: If the record relating to the given ID exists ONLY in property_active, that will be returned. If the record exists ONLY in property_inactive, that will be returned. If the record exists in BOTH tables, the version from property_inactive will be returned. I have made various attempts involving UNION, COALESCE etc, but to no avail.
View Replies !
Selecting 1
Here is a sample table id month name 1 1 James 2 3 James 3 5 James 4 1 Ted 5 2 Ted 6 5 Ted I want to select only 1 result with 'james' and only 1 result with 'ted' and so forth for how ever many names there is. i guess it would be something like SELECT * FROM tablename WHERE name = 'james' LIMIT 1 but i want to select one result of every name can I do this without performing heaps of queries
View Replies !
Selecting Every Nth Row
Is there a way how to get only every nth row using purely MySQL statement (no eg. PHP scripting) with where part (so I cannot use something like SELECT * FROM table WHERE MOD(id, 10 )=0 )
View Replies !
Selecting Max() Id
I need to select 2 columns in 1 query: id and name. I want to select the max(id) in the entire talbe, and then select the name for that id in the same query. What is the most efficient way to do that? And how?I'm running the most recent version of MySQL.
View Replies !
SELECTing Where
I want to pull a count regarding how many orders have been placed TODAY and how many have been placed this MONTH (and this year I guess too)...ok, three questions. Something like: SELECT count(id) as orders_today FROM orders WHERE DAY(when_entered) = DAY(CURRENT_DATE())...
View Replies !
Selecting 'where'
I know the solution to this is fairly simple but im having a complete mental block ! I have table "users" and table "link". I wish to select all users who are present in table "users" but absent from table "link".
View Replies !
SQL Selecting
I have two tables that have columns with similar data. product_line.prodline_num has 86 unique fields product.ProLine has 73 unique fields I'm trying to make a query that will identify the 13 unique fields that ProLine does not have. I just can't seem to figure it out.
View Replies !
Selecting Only The First Row
I have been using the following command to select future events from a table: $query = "SELECT * FROM shows WHERE date_short >= CURDATE() ORDER BY date_short ASC"; and displaying all results using the following loop: if ($result = mysql_query ($query)) { while ($row = mysql_fetch_array ($result)) { //display } } What can I use when I only want to display the first row, eg the nearest future event?
View Replies !
Not Selecting
For some reason it's not selecting my server. I am using the code: $database = "main"; $db = mysql_select_db($database,$connection) or die ("Couldn't select database"); and when you go to the site it says couldn't select database. I don't see anything wrong with my coding.
View Replies !
Selecting Just One
I have a table organized like this: Events Event1 Event1 Event1 Event2 Event2 Event3 Event3 Event4 How can I query the database so that it only returns one of each? So it would return: Event1 Event2 Event3 Event4
View Replies !
Selecting A Specified Row
is it possible to retrieve the cookie with php and make its value ftech a certain table row./. for a say a username.. its so i can do a profile thing for my site???
View Replies !
Selecting First 10, Then Next 10...
How do I SELECT the next 10 records after I've selected the first ten, and so on. SELECT * FROM mytable LIMIT 10 ...will show on one page, then I want to click 'next' and get the next ten, and so on. If I had a column with stepwise monotonic integers (1,2,3,4...) it would be easy but I don't have that.
View Replies !
|
TRACKER
