Selecting Records From Database Entered Less Then 30 Minutes Ago?
I have tryed everything to select records from the database that have been entered less then 30 minutes ago. I have the time/date stored as a TIMESTAMP, so i need to find a way of returning the records.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Retrieving Records Entered In Last Couple Days
How can i retrieve records that have been entered in the last couple days or even last week? I know i will first need a field called "entry date". How do i match the date to the entry date and count backwards for the past couple days?
Selecting Date Time Field, Difference In Minutes
I wonder if is there any way to select from a table all the records which has a Date Field that is at least five minutes old? In other words, I have a table with a date field and I need to select all the records that are older than five minutes, has their date field updated before five minutes. Usually I do it with days by using the to_days() function and comparing the date to now: (to_dayss(now()) - to_days(somedate)).
How To Get 15 Minutes Old Records
Scenario : My system enables a record on zero minute of every hour [through cron job]. Lets suppose right now time is 10:10 AM. Query: My query is that -> 1- How can I get all records which are enabled before (1 hour and 15 minutes) of the current time?
Selecting Top Records
I was posed a question in an interview that required me to select the top 3 records of table after doing a sort. I have been studying MySQL for the last 6 months or so and don't recall that capability in MySQL. I know it is available in Oracle and SQL Server. Is this possible in MySQL?
Selecting Many Different Records
I have table A_1 which has one of its fields called ID (a unique integer field). Now if I wanted to select let say the records with the ID matching these numbers 5, 7, 8, 9, 15, 22, 26, 29 how would I do this? I would imagine there is a better way to do this than using many AND statements to do this?
Selecting Known Records
Say I have a big list of IDs of the records I want to select. What is the best way to do this? 1. A select statement with multiple OR statements eg. SELECT * FROM table WHERE ID = 2 OR ID = 5 OR ID = 10 etc... 2. Multiple separate mysql queries each selecting an ID each. 3. A select statements like obove but all the queries sent in a single query using union. I dont know how important it is but with lots of queries a second it could make a difference. If there is other better way please tell.
SELECTing Records
I want to select records for several events in the future. Here's my problem - I need the next seven days that events occur on - but these days are not nessicarily consecutive. I had the idea to write a query like this: SELECT PROGRAMME.PROG_TITLE, COMPANY.CO_NAME, VENUE.VEN_CITY, PERFORMANCE.PERF_DATE_TIME, COUNT(DISTINCT DATE_FORMAT(PERFORMANCE.PERF_DATE_TIME,'%D-%m-%Y')) AS NUM_DAYS FROM PROGRAMME JOIN COMPANY ON COMPANY.CO_ID = PROGRAMME.CO_ID JOIN PERFORMANCE ON PERFORMANCE.PROG_ID = PROGRAMME.PROG_ID JOIN VENUE ON VENUE.VEN_ID = PERFORMANCE.VEN_ID WHERE NUM_DAYS <= 7 ORDER BY COMPANY.CO_NAME, PROG_DATE_TIME Only to realize that I can't use a column alias in the WHERE clause. I know that there must be an easy way to do this but I can't seem to figure out how.
Selecting All Records
How would I go about selecting all records in a table with a date field of "date" -- "0000-00-00", "yyyy-mm-dd" -- that are 3 days older then the current date?
Selecting Records
I have one table containing itmes each having its own id, and a another table containing some of these numbers in one of its columns.I need to to build a query that returns the itmes from the first tables whose numbers are not present in the second table. (SELECT t1.* FROM t1, t2 WHERE < the value in t1.item_id is nowhwere in t2.item_id >) How do I do that?
Selecting Records
This is a bit of a forlorn hope, but are there any timestamp values automatically associated with records eg can I select all records created after a certain date
Selecting Records
my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code. Code: SELECT DateOfStats FROM table1 WHERE (DateOfStats NOT IN (SELECT dateofstats FROM table2)) and (DateOfStats NOT IN (SELECT dateofstats FROM table3)
Selecting Non Matching Records
How do I go about selecting records from two tables that do not match? example: I have two tables (Table1 and Table2) linked by field1. Table1 will have records added right away where as Table2 will get its records written at a later date. Thus having it in some cases where Table2 will not have a matching record for Table1. What I want to do is generate a list of those items in Table1 that do not have a record written in Table2 as yet.
Selecting Specific Records
I have two tables. I want to select the last record for a specific type from one table and look up info from the other table. Is there a command that will select go to the last record of a query?
Selecting Records Only X Days Old
Using PERL and MySQL. I have the select statement working for all records, is it possible to select only records (in this case it will only return one) that is as old as say 30 days?
Selecting Date Records
What is the most efficient way to select records based on a field called NOW which contains a date or a datetimestamp I use: SELECT * FROM `TBL` WHERE DATE_ADD( `TBL`.`NOW` , INTERVAL 3 MONTH ) > CURDATE( ); Is this the best way? TIA [color=blue] > Nicolaas[/color]
Selecting Non Duplicate Records Without Using DISTINCT
how not to display duplicate records without using DISTINCT. Say i have a database: ID Channel Description 41 Africa News Latest news from africa 42 Technology News Latest Tech News 43 PC News Latest PC related news 44 Africa News Latest news from africa I run the query 'SELECT DISTINCT * FROM Mytbl' and obviously get the results as shown above with 'Africa News' appearing twice as the primary key ID makes it unique. How can i return only one of the Africa entries in the results while still having the primary key value returned as well? Unfortunately with this particular app it is inevitable that duplicate values will occasionally be added to the database but i never want duplicates to be returned from a search query. Can this be done?
Selecting Past 12 Weeks Records
Im trying to grab the past 12 weeks records in a table. here is my code: $query1 = "SELECT * FROM table2 WHERE compdate > DATE_SUB(NOW(), INTERVAL 12 WEEK) ORDER BY compdate"; if($result1 = mysql_query($query1)) { $amount = 0; while($row = mysql_fetch_array($result1)) { $compdate = $row['compdate']; $compdate = substr($row['compdate'],8,2); $compdate = ltrim ($compdate, "0"); $days_array[$compdate] = 0; } } else { echo mysql_errno().": ".mysql_error()."<br>".$query1; } And here is the error: 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WEEK) ORDER BY compdate' at line 1 SELECT * FROM table2 WHERE compdate > DATE_SUB(NOW(), INTERVAL 12 WEEK) ORDER BY compdate
Selecting Records Containing Multiple Values
I want to query a database for records with which the field 'network_letter' contains G,R,M, and Q and display those results back. Is there an easier way than doing the following: $query1 = mysql_query("SELECT * FROM global_pops WHERE network_letter='G'"); $query2 = mysql_query("SELECT * FROM global_pops WHERE network_letter='R'"); $query3 = mysql_query("SELECT * FROM global_pops WHERE network_letter='M'"); $query4 = mysql_query("SELECT * FROM global_pops WHERE network_letter='Q'"); and then merging those results into one variable and printing them?
Selecting The Correct Row In A Table Which Records Changes
I have an "employees" table which records which department each employee has worked in, and the date that each employee started working in their departments. Since it is possible for an employee to change departments, a single employee can have several records in this table. For example, consider the following history for Employee 42: 1. On Jan. 15, 2007, Employee 42 is hired to work in Department 21 2. On Feb. 1, 2007, Employee 42 changes departments to Department 5 3. On Feb. 14, 2007, Employee 42 changes departmets to Department 22 4. On Mar. 3, 2007, Employee 42 changes departments to Department 18 Then, Employee 42's records in the "employees" table would look like this: CODEselect * from employees where employee_id = 42;
Detecting If User Has Already Entered Info
I have a simple checking system I want to use. However I am stuck on saying if they have or have not entered information or not. Here's why. I want to use code that basically says... $result= mysql_query("SELECT * FROM table WHERE $row['userid']= $_SESSION['id']") or die(mysql_error()) if ($result == "false"){ Do this; } else if ($result == "true"){ Do this; } Basically I want to say if a user hasn't got any information in that table then do something else if they haven't do this. Will my way work?
Getting Data Entered In The Previous Week
When a product is added to the database it has three seperate fields storing the date added in(01 - 31 format) month (January-December) and year( xxxx). Is there anyway that I can run a mysql/php query that will just retrieve those products entered within the last xx weeks?
Selecting Records Younger Than 24 Hours Using SQL Query?
I have a TIMESTAMP(14)-field in my db and I need a quick way to select records younger than 24 hours, WITHOUT using PHP to convert around. I'm pretty sure this one should be quite easy, played around with something like: SELECT fields FROM db WHERE 'timestamp'>NOW()-INTERVAL 1 DAY) for hours right now (with DATE, EXTRACT, BETWEEN..AND etc.), and I either got error in my queries or bogus results.
Buliding A Query:selecting Unique Records
I am nearly positive that there is some way to have mysql select only the first occurance of a data entity on a select. Maybe a better explanation is given by example. Think of having a table of employees, with one of the columns being their bosses email address. Now I want to do a select statement on the bosses email addresses that only returns one entry for each bosses email, even though that bosses email address will occour > 1 in the table. (more than 1 employee has the same boss...) I could parse out the duplicates when I get to PHP but I would rather have mysql do the work for me.
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
Selecting All Records In A Parent/child Tree Structure
I have a tree menu and I am filtering it so that it shows only nodes in the tree that have products against them. I have two tables: 1. Tree table Name: 'shopCategory' Fields: cat_pk category_text parent_pk url 2. shop items Name: 'shopItems' many fields but key field of TDCategoryID links to cat_pk in the shopCategory table I have got as far as: SELECT * from shopCategory WHERE cat_pk in (Select TDCategoryID from shopItems); This gives me all the nodes that have items against it: +--------+--------------------------+-----------+------------------------------- | cat_pk | category_text | parent_pk | url +--------+--------------------------+-----------+------------------------------- | 89 | Food and drink | 1 | ***** | 93 | Vitamins and supplements | 89 | ***** | 94 | Wine | 89 | ***** | 100 | Other wine | 94 | ***** | 121 | Home and garden | 1 | ***** +--------+--------------------------+-----------+------------------------------- What I need to do now is follow the parent/child relationship and return all the records to the recordset until parent_pk is '0' i.e. the top of the tree. THe problem is the length of the tree varies so it may be folder|items or folder|folder|items is this possible?
Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?
Selecting From Database
I want to select mysql results from the database based on the YEAR value of the DATE stoted in the table.The date is stored in this format: YYYY-mm-dd I would like to know how a query would look like if i were to get all results for say year 2005
Selecting A Certain Rows From A Database
if i wanted to select a certain range of rows from a database,is it possible?For example,i have a column in my database which is also a primary key and i wanted to select rows from where the primary key column is 21 - 40,would this sql statement be valid? SELECT * FROM tbl1 WHERE id=21 TO id=40;
Selecting From External Database
Assuming I have 2 database servers, A and B. I want to insert rows selected from a table in a database on B into a table on a database on A.
How To Add X Minutes, Myql Way
i using LOCALTIME() in my mysql codings... how can i add in mysql X minutes to this time? mySQLde="UPDATE DTIMES SET TIME=NULL WHERE TIME <" &DATE_ADD(LOCALTIME(),INTERVAL -30 MINUTE) Microsoft VBScript compilation error '800a03ee' Expected ')' mySQLde="UPDATE DTIMES SET TIME=NULL WHERE TIME <" &DATE_ADD(LOCALTIME(),INTERVAL -30 MINUTE) ---------------------------------------------------------------------------------------------^
DATETIME Was How Many Minutes Ago?
I have a DATETIME stamp in a mysql database and wish to find out how many minutes ago it was... looked across mysql.com for this but no luck.
MySQL Restarting Every 8:30 Minutes
My MySQL service seems to be restarting itself in intervals of 8 minutes and 30 seconds. There is, at most, a variation of a second or two. I can't seem to find out what is causing it. There are no cron jobs that I can see, the error log is only showing the stopping / starting messages, and the mysqld log doesn't seem to be showing anything peculiar around the time of the restarts.
How To Do An Action In Mysql Every 15 Minutes
i want to get the values from a web and add it to my database. but i don't know how to call a process every 15 minutes and i don't know how to get the data from the web. The web data is stored on a table, i thin i can do something with it.
Selecting Limited Items From A Database And Displaying On A Webpage
So I've got this PHP/MySQL program that someone wrote for me and it's working great. However, I'm trying to figure out the SQL (and subsequent PHP) to select only champion and reserve champion from the database and to display it on the page. The achievements are all listed in the php_achieve table in my database with RelPlaceID, etc, etc. Can anyone help? Or let me know what other information I need to include for you? Basically, I want my website to display the most recent five champion and reserve champion results (if there are five, otherwise, to list them all) for a specific horse (each horse already has an id). Help? I'm so dumb at this stuff. :-X
Working Out The Number Of Minutes Between Two Dates
Using MySql 3 (deliberately using an old version) how can I work out the difference between a date in a database field (stored as a unix time) and now. I'm actually using PostNuke and want to identify how long open items have been in my phpbb forum. I've tried various forms and the last effort was : select to_days(now())-to_days(from_unixtime(p.post_time)) from nuke_phpbb_posts p but that gives my a lowest number of 176 (I know people have been using it today).
Build An Automated PHP Gallery System In Minutes
Kia Ora From New Zealand I get the following warning on both these tables PRIMARY and INDEX keys should not both be set for column `category_id` also I have built a script to add a new catorgory to the gallery but the only thing that happens is the Category_id increments is this becouse PRIMARY and INDEX keys should not both be set for column `category_id` CREATE TABLE gallery_category ( category_id bigint(20) unsigned NOT NULL auto_increment, category_name varchar(50) NOT NULL default Ɔ', PRIMARY KEY (category_id), KEY category_id (category_id) ) TYPE=MyISAM; CREATE TABLE gallery_photos ( photo_id bigint(20) unsigned NOT NULL auto_increment, photo_filename varchar(25), photo_caption text, photo_category bigint(20) unsigned NOT NULL default Ɔ', PRIMARY KEY (photo_id), KEY photo_id (photo_id) ) TYPE=MyISAM;
Calculating Time Difference In Minutes Between 2 Times
I have two columns in a table start_time and end_time (stored as datetime). I wish to work out the time difference in minutes between these two values. If the difference is 1hr 30 minutes I wish to get 90 (mins) I have tried: SELECT TIME_FORMAT(TIMEDIFF(start_time, end_time), '%i') FROM table ... This returns justs the minutes (e.g. 30) and SELECT TIME_FORMAT(start_time - end_time, '%i') FROM table .... no such luck.
Find Days, Hours And Minutes Between 2 Dates
I have a datetime field in a mysql database and i want to output how many days, hours and minutes are left between the current date and the future date. So far i just convert the datettime to a timestamp like so: $start = strtotime($began); Where $began is the current time, basically i just need to know how to convert a timestamp to days, hours ect If i was to minus the current timestamp from the future timestamp.
How To Substract Minutes From A Time Field In Mysql Query?
I have a problem, i want to substract minutes from a time field in a mysql query, i'm trying with the next query: UPDATE TblOrders SET TmHours = TmHours - '00:09:00'; In the query I want to substract 9 minutes from all time fields named TmHours in the table, the datatype for the field TmHours is time.
Inserting Records Into A Database
I am using mysql 4.1 and I am trying to insert a sql file into a database. I have the mysql> prompt but I cannot figure out the script to insert the sql file. I have tried using th database name followed by < then the file name but this doesnt work.
|
TRACKER
