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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
Time Value Calculation That Results In A Negative
here's my code: SELECT *, SEC_TO_TIME(TIME_TO_SEC(SEC_TO_TIME(SUM(TIME_to_SEC(phour)))-SEC_TO_TIME(SUM(TIME_to_SEC(mhour))))) AS Balance from toil.User join toil.toil on user.userid = toil.userid where name = 'X' FYI I'm working on a database to store details of Time Off In Leiu worked by staff (toil)
View Replies !
Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to display the results based on the user input. The results might be anywhere from 100 to 1000 per query and presently I am displaying them as 25 results per page. Problem: Each query is taking about 20-30 seconds. My solution: I have tried to optimize the table and also index the table. I have actually converted a MS access database to SQL database, so it wasn't previously indexed. Both optimization and indexing doesn't give any good results. I always get a timeout. ie. it takes longer after indexing and optimizing. 1. I was wondering if someone has a creative solution for this. ie. reduce the time from 20-30 seconds to atleast 10 seconds. 2. I have links of pages of results beneath the first page result. When each of these links are clicked it takes 20-30 seconds again. Is there a way I can reduce the time taken for the subsequent pages are reduced? I cannot use the LIMIT option in mysql, since I have a where clause which has to search through the whole table. I tried using views and using limits, but it takes as much time.
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 !
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 !
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 !
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 !
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 !
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
View Replies !
Minutes + TIME = TIME Function
I need some way to convert minutes (e.g. 80 mins) to SQL TIME (e.g. 01:20:00) in order to add it to another TIME var. Is there any way to do this? I really dont want to convert my whole db. Basically, I want to do: e.g. 80 + 10:00:00 = 11:20:00.
View Replies !
How To Retrieve Mean Time Between Time Stamps
I have a table that stores failure information of external application. The time stamp of every failure event is stored. Now I'd like to make some system performance & reliability statistics calculations based on time stamps in certain recordset; mean time between failure MTBF, down time DT, etc. Example: 2007-09-27 12:23:52 2007-09-27 08:50:23 2007-09-26 23:31:34 2007-09-26 21:45:33 Here the times between are (hours:mins): 3:33, 9:18, 1:46 and MTBF=4:52 Any effective possibility to make this example within a single query - or a procedure is needed?
View Replies !
Time Format Military Time
I know the time format it HH:MM:SS in mysql but can I input military time? reason I can is that I want to later extract that information for calculations and military time is easier to work with.
View Replies !
Date-time Overlaps Another Time
had a time question in mysql... i have a start and end time for a meeting table and i am comparing whether a new meeting conflicts with another meeting in same room on the same date. i was wondering if i could check whether the 'date-time duration' in anyway overlaps another meetings date-time duration. visualized below... ............|____compareToThisMeeting__| .....|_________meetingNewOverlaps__________| i was doing something dumb before i realized i needed soemthing more complex (checking whether the start time of new meeting was 'BETWEEN' the start and end times of a meeting and whether the end time time is as well but i forgot the duration/middle value hehe). (using asp) and just returning booleans with a mySQL SELECT statement is what i am looking for like... //within a loop in asp for meetings on same date/room "SELECT '"+ year+"-"+month +"-"+day +" " + startHourString +":" + startMinuteString +":00''"+ " BETWEEN '"+ thisMeetingStart +"' AND '"+ thisMeetingEnd +"'"; (this doesnt cover the 'middle' overlap areas though)
View Replies !
Display Results Within Results
1) I have already did a search for: "Results within results" on this site, in PHP & MySQL forums ( I think) properly...and one search resulted in over 100 pages etc. From the below structure of my DB, I would like to get the code from the below URL working on my existing data I have, but I am having trouble and I am just getting flustered.... I would eventually like to have the user "select" first a $State and then $County from a "drop down" for now, and eventually a "map" but the "drop down" for these (2) will be a must have....but I am just trying to use this function first. At present, I am unable to even get proper printed results, and I know it has to do with something on the variable call end that is screwing me up. 2) This is what I am using (learning) from: 3) This is my mySQL db structure: ....
View Replies !
Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row. I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.
View Replies !
Add Up Results
Not sure if I should ask this in PHP forum or this one? I think this is a simple request. I have a table with names and dollar amounts of pledges. What I need to do is grab all the amounts for the same person and then add it up to a total amount that they owe. ie: Table is like this. Don $500 Joe $100 Don $1250 Don $1250 Fred $300 I need to pull all the info that are for Don and then add up the amounts, for example this one Don owes a total of $3000.
View Replies !
Getting Results
i use mysql 4.0.22 which means i cant use subquery's right? Ive tried using them but it dident go well. This is what im trying to accomplish SELECT service FROM rate WHERE type = 'as' service must be equal to se SELECT se FROM autosurf where st = 0
View Replies !
Different Results
I have a nightmare of a problem, where in a Query I am attempting to calculate percentages. I had the query running on an earlier version of mysql and it worked fine, and it is now running on the latest version and doesn't return the correct value. This is the query : Select DISTINCT std.student_f_name, std.student_m_name, std.student_l_name, lt.time_from, lt.time_to, lt.day, TIME(rda.clockinTime), cast(($variable1/$variable2)*100 as decimal(3,2)) from students std, record_attendance rda, lecture_times lt where std.studentID = ? AND rda.studentID = std.studentID AND rda.lectureTime = lt.id AND lt.id=? This correctly returned 33.3 percent in the older version, however in the new version returns 9.9 %. Does anyone know how this can be fixed?
View Replies !
Multiple Results
I'm using PHP to display a list of statistic information about a site. Sometimes I need to retrieve mixed information from the database but I don't know which is the best method to do it. For example I need the top requested html pages and the most repeated value from a column. I dont know if I should make two different queries in the php file or it's more efficient to make only one query with an extra column like id | ip | date | page | max | --------+--------------+----------+----------+---------| 1 + 24.125.24.25 + 24/5/03 + index + 3 | 2 + 24.125.24.25 + 24/5/03 + top + 3 | 3 + 20.12.12.21 + 24/5/03 + index + 3 | 4 + 200.12.24.25 + 24/5/03 + left + 3 | 5 + 24.1.6.255 + 24/5/03 + left + 3 | 6 + 24.125.24.12 + 24/5/03 + index + 3 |
View Replies !
Displaying SQL Results
I have made a database with all different things (venue, team names etc), the team names display fine, but I am using different code for venues (counting) and these just won't display at all, I am really new to SQL so don't know what to do to fix it! The code for getting the SQL is $sql = 'select venue,count(venue) as frequency from matches group by venue limit 300'; which works fine in phpMyAdmin and displays what I want to, but I don't know how to display it on my page! I do have the sql connection info in the php page too, plus the code which is used for displaying all my other pages but doesn't want to work now! I would like a table with headers of Venue & Number if possible, then the venues and number next to them, as it displays in phpMyAdmin
View Replies !
Matching Results
Suppose you have two tables: A and B. Both tables have the same columns: col1, col2, col3, etc. So my records are distributed in two different tables that have the same format. I know, this is not good normalization design, but that is the way they are setup right now. The question is: how do I query both in a single sql statement, combine the results of the query get all the records from both, and list the result in alphabetical order? I can do: select * from A ORDER BY status; and I can also run: select * from B ORDER BY status; But how do I run both at the same time and get a single output from them?
View Replies !
Count Results
does there exist a less resource intensive way on how to count the results of a query? I mean I know that way: Doing a query on mysql and then using mysql_num_rows for fetching the amount of results, but I guess there should be an easyer way, that probably uses a mySQL syntax for that? Probably COUNT()? I read through the explanation of it and believe this could be the way how to do, but I can´t figure out how exactly the syntax would look alike
View Replies !
Mysql Results
What i'm trying to do is count is say I ran this sql query - SELECT DISTINCTROW length FROM list - it would show only one of each result (if there were rows with duplicate lengths). Is there a query which I could do that would also count how many duplicates there were if any
View Replies !
Counting Results
i'm trying to find a way of counting the results in a mysql table. Say I have the fields CAR_MAKE | CAR_MODEL | COLOUR | Is there are way of saying car_make=bmw car_model=z3 colour=black how many of these are there im my table
View Replies !
Results In Error
I'm trying to create a MySQL database using PHP with the following command: $create = mysql_query("CREATE DATABASE IF NOT EXISTS moviesite") or die(mysql_error()); This results in the following error: Access denied for user 'bp5am'@'%' to database 'moviesite' * I am able to connect to the database using: mysql_connect * I am able to create that very database manually using dos * Apache loads files as it should * PHP is parsed correctly
View Replies !
Getting NULL For Results
SELECT DATEDIFF('date_hired,'date_fired') from users and i get all NULL for results i think i should be getting number of days apart from the two dates date_hired and date_fired are both date columns so whats wrong with this
View Replies !
Getting Results From 3 Tables
I have three tables: Person with key nId plus other fields, volchar with fields nVolCharId, nId, nActivityNum and activitynames with two fields nActivityNum and txtActivityName. One person can be signed up for many activity so I want my output to look like: LastName, FirstName, etc plus a list of txtActivityNames (eg. Smith, Bob (activities: golf, swimming, basketball)). Is there a way to structure a query to get those kind of results so they can be displayed in table form?
View Replies !
Getting Search Results
I have a table of posts which contains parent post and replies (structure below). I am having difficulty returning search results from this. What I want to do it search all rows title and body fields for matches, however I only want to return a rows parent in the results regardless of weather the parent contains the search term or not. What I can't figure out how to do is remove child rows from the search results while also getting the parent row if it does not contain the search term.Structure: id parent_id -> is 0 if post is the parent user_id title body I am also joining the results to the users table so that I can display the username associated with the post in the results.
View Replies !
Getting Specific Results
my db looks like this: username - skill1 - skill2 - skill3 John - warrior - thief - warrior How can i only output the fields that are warrior? Ive tried this but it dident work: SELECT skillsM1, skillsM2, skillsM3 FROM ugd HAVING skillsM1 AND skillsM2 AND skillsM3 = 'warrior' Or is it necesary for me to change my db structure?
View Replies !
Group By Results In One Row
Take the following data Code: date | type 2005-04-02 | soft 2005-04-02 | soft 2005-04-02 | click 2005-04-02 | click 2005-04-03 | soft 2005-04-03 | soft 2005-04-03 | click 2005-04-03 | click Code: select count(*) as total, type, date from tbl group by type, date which would give me Code: total | type | date 2 soft 2005-04-02 2 click 2005-04-02 2 soft 2005-04-03 2 click 2005-04-03 I was wondering if there was a way,perhaps with a join, to get the data into the same row? Code: totalsoft | totalclick | date 2 2 2005-04-02
View Replies !
Order These Results
Lets say i have the following table (1 field): 1 1 1 2 3 3 3 3 4 4 So there's 3 1's 1 2's 4 3'd and 2 4's I want it to display the items like this 3 14 2 (the one with the most elements on top and so on) Is it possible to do it in a query or do i need some code to count one by one ?
View Replies !
Results W/ A LIMIT BY
I have a table where I need to pull out the 5 latest records only. There is an INT 10 field called category. I only want one record from each of the following items in category (1,3,4,5,7). I can't do a Distinct because the other fields are not distinct. If I do a LIMIT BY, I can't guarantee that I'll have one of each. How do I get out the data?Here is my not working query: SELECT username, title, category, price FROM mister WHERE category IN(1,3,4,5,7) ORDER BY laststamp DESC LIMIT 5
View Replies !
Round The Results
In another thread I got this query to work, but it comes up with 32.0513. How can I get it to round to the nearest whole number? This is the query: PHP Code: <?php global $database; $sql = "SELECT (100*SUM(c.cb_paiddues=1)/SUM(c.cb_paiddues =0)) as total_value from #__comprofiler as c left JOIN #__users as u on c.user_id = u.id WHERE (u.usertype != 'Super Administrator') and (u.block = 0) and (u.username != 'guest') "; $database->setQuery($sql); $row = $database-> loadObjectList(); return $row[0]->total_value; ?>
View Replies !
Limiting Results
I have two tables one with three rows one with several. I am left joining the second table with the first one based on shared data (a id number that matches in both tables). I run mysql_fetch_array until I have read all the data but heres the problem. Each row in table one is being returned more than once as it matches more than one row in table 2. For each row in table 1 I want to get only the first matching row in table 2. Does that make ANY sense?
View Replies !
Listing Results
how can I create a simple MySQL query from 2 tables (blog and news) where its just listing their results according to most recent datetime? Here's what I tried, but its vastly incorrect. Their structure is exactly the same; just 2 different tables. PHP Code: SELECT b.id, b.status, b.title, b.body, b.a_id, b.datetime, n.id, n.status, n.title, n.body, n.a_id, n.datetime FROM blog b, news n WHERE b.status=0 AND n.status=0 ORDER BY b.datetime,n.datetime DESC
View Replies !
Wrong Results
I have the following select statement Code $query_Recordset1 = "SELECT * FROM data where('$beds like $options')"; the $beds variable is a text field, and the $options variable is a drop-down menu. Technically the query works, but when i put in 775 and click ZIP in the drop-down box it shows zip codes that dont even start with a 7, much less a 775
View Replies !
More Accurate Results
I have a script with a text field that searches a table based on what is entered in the field...however I would like it to return more accurate results...I.E, if the letters "ham" are entered it returns records that contain the the term (ham) but I need it to FIRST return records that begin with the term... I.E "hamster" not "durham" PHP Code: $sql = "SELECT * FROM _table WHERE name LIKE '%".$searchTerm."%' ORDER BY name ASC LIMIT 0,30";
View Replies !
Correct Results From Db
I have a column of type text, in this column I am adding in numbers that relate to another column in the table. For example the column in question could end up having the following data in it: 1, 2, 6, 9, etc The problem I am having is if I want to return all the results that relate to lets say the number 1 and if the column in question has more numbers in it than the number 1, I don't get anything returned. Is this an error with my ASP script or the way the data is stored in the dababase. Do I need to change the data type,
View Replies !
Ordering By Most Results
In my database I have a table called imagebank and each row has field with the URL to a picture and I field that says what breed the picture is of. How can I make a query to my database that pulls all the info from that table, and displays them in order of what breed has the most pictures.
View Replies !
Ranking Results
Im trying to display baseball team stats, along with the ranking for that particular league. for example, there are 5 teams in the league, Team A has 12 HR, B has 15 HR, C has 3 HR, D has 3, and E has 20. I want to show on each individual team page their stat, with the rtanking for the league.
View Replies !
|
TRACKER
