Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows
SELECT * FROM news WHERE ((news.date)>$today ORDER BY date
where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.
View Complete Forum Thread with Replies
Related Forum Messages:
Creating New Table Based On Old Table
I have a MySQL database with a user table will lots of DUPLICATE email. I want to delete the rows completely having the duplicate email. I found a way out is to use some thing like this: Code: insert into newtable (email) select distinct email from oldtable But this way, only the email column is populated in the new table. I want all the other columns as well.
View Replies !
Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....
View Replies !
Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' with a field representing the topic author's username. I then have another table called 'ignorelist' which has two fields; one is an account number and the other a reference to the aforementioned author. There can be many ignored authors for any one account. For any given account number, I'd like all the rows from the forumtopics table where there is no match for (account, author) held in the ignorelist table. Obviously it's easy to check for existence but can the opposite be done? I run MySQL 4.0.17.
View Replies !
Sub Query - Aggregate Fields Based On Min N Max
In the Users table below there are duplicate users by email address +---------------------+------------------------------+-----------------+-----------+ | ts | email | field1 | field2 | +---------------------+------------------------------+-----------------+-----------+ | 2009-01-31 06:51:14 | user1@rediffmail.com | 05 | 03 | 2009-01-31 16:07:39 | user2@yahoo.com | 02 | 02 | 2009-01-31 16:15:02 | user2@yahoo.com | 09 | 04 | 2009-01-31 16:16:00 | user2@yahoo.com | 06 | 08 | 2009-01-31 16:19:52 | user2@yahoo.com | 01 | 09 | 2009-01-31 02:04:36 | user3@rediffmail.com | null | 01 | 2009-01-31 02:12:34 | user3@rediffmail.com | 01 | 03 | 2009-01-31 02:20:31 | user3@rediffmail.com | 08 | null +---------------------+-----------------------------+--------------+-----------+ I want to fetch one record per user ‘user1,field1,field2’ For user 1 select field1 where min(ts) select field2 where max(ts) the final output should be user1,05,03 user2, 02, 09 user3, 01,03 (max of ‘field2’ is null so it should pick the field value which matches the next min ‘ts’ val)
View Replies !
Query Based On Data Of 3 Tables
this is what I've been trying to implement: I have, say, three tables. One is a user table, with id, username, etc Second is a question table with Question ID, Question, Category etc And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns. What I want to do is this: Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.
View Replies !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query? Example result for ProductX:
View Replies !
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.
View Replies !
How To Query Multiple Tables Based On Value Difference
I 5 tables all with equal columns,but with different values. Per example: time_start,time_end,num_a,num_b,price etc. How can I retrieve the values of all columns `price` between tableX and tableY where time_start in table Y is higher as a datetime than time_start in X. So,the query is about finding the values of `price` between 2 dates in differnt tables.
View Replies !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
Howto Make A Query Based On Another Result?
I need to make query from another query result. tbl_A fields: ID and Date tbl_B fields: ID and FileName Code: Select ID FROM tbl_A where Date>=CURDATE() this 1st_query_result will be any ID with current date from tbl_A. Then I need this 1st_query_result to query the filename which store at tbl_B. something like this: Code: select FileName from tbl_B where ID = 1st_query_result
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
Time Based Reservation System - Only 1 Query Should Succeed
a user can select a time they want and submit the page * The code selects all the bookings for a range of time. * It then counts how many concurrent bookings there are for each hour. * If there's less than the total (4) it inserts another row ( the user's requested booking ) into the booking table. We also notify people if there has been a cancellation This has led to a situation where a few different people are trying to book the same session at the same time, and succeeding! We've ended up with 5 sessions booked In the course of 2 page requests this seems to happen: User1 - submits their booking site selects to see if there's availability ( there is!) User2 - submits their booking site selects to see if there's availability ( there still is!) site inserts User1's booking site inserts User2's booking
View Replies !
Query For Dates
Table Data: ----------- id__name___from_________to_______ ------------------------------------- 1___ABC___2006-10-01___2006-10-31 2___CDE___2006-11-01___2006-11-31 is there a syntax to select both rows at once? i.e. if I query for dates from 2006-10-15 to 2006-11-15, is there a possibility to have the two rows as a result of a single SQL Statement query?
View Replies !
Query Dates
I am trying to find records where my approved date is not NULL and my shipped date is NULL. Using phpMyAdmin the records show cells with italic NULL and others with dates ie. 2006-06-11, so I assume this is formated right. However using this query I get no records. SELECT * FROM records WHERE approved_date != NULL AND shipped_date = NULL I have used phpMyAdmin's search section with every conceivable way and I can't get any proper results.
View Replies !
Query Using Dates
I'm having trouble figuring out how to get two time periods in one query. Basically it's either one month at a time or a full year...So I need to get everything that starts and stops this month, or cases where this month falls between the start and stop dates. I couldn't figure out how to do the between bit, so I settled for calling an expiration date that is not older than this month.I get partially correct results. It's calling the five ads that start on 2006-12-01 and end 2006-12-31, and ignoring the ones that start 2007-01-01 and end 2007-01-31. However, I've got a test ad that starts 2006-01-01 and ends 2006-12-31 that it's ignoring...I think my timeframes are cancelling each other out or something. $sql = "SELECT ad_id, ad_filename,rally,affiliates,past,upcoming,start_date FROM ads WHERE Month(start_date)='$curmonth' AND Year(start_date)='$curyear' OR Month(end_date)>='$curmonth' AND Year(end_date)>='$curyear'ORDER BY rally='y' DESC";
View Replies !
Using Dates To Query
I have a table with a field for each record with a date in it. I want to get all records before September 12,2000 but I don't know what the query should be.
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 !
Dates NOT IN Query Confusion
I have a table of properties and a table of dates, each date relates to when a property is BOOKED. I am trying to write a search so that users can find available properties for their desired date range. I have a query that s working, but I think that it might need to change for other variables! Code: SELECT DISTINCT pid FROM properties WHERE pid NOT IN (SELECT pid FROM cal WHERE takendate between '2009-01-20' and '2009-01-29') AND property_publish='Y' AND property_sleeps >='1' cal table stores: pid (property id) takendate In this instance the dates 2009-01-20 -> , 2009-01-23 are TAKEN, however dates from 2009-01-24 -> 2009-01-29 are AVAILABLE. How would I flag it as available if only some of the dates are available? (eg. only hide property if ALL the date range is taken)
View Replies !
Select Query Between Two Dates
select * from table where Fri, 23 May between sdate and enddate Fri, 23 May is user given date table likes sno | sdate | enddate 1 | Mon, 19 May | Sat, 24 May 2 | Sat, 17 May | Mon, 19 May 3 | Fri, 23 May | Mon, 26 May so the above queries i need the 1 and 3 results only what is the problem in query and how to change it give solutions.
View Replies !
Looping An Array Of Dates In One Query
$newdates is a simple array of dates (like 2008-10-01) the 'comm' column is decimal 2 places 'trans_date' is a datetime column PHP Code: foreach($newdates as $newdate){ $linequery = "SELECT SUM(comm) AS `linecomm` FROM `table` WHERE DATE(trans_date) = '" . $newdate . "'"; $lineresult = mysql_query($linequery) or die("Could not execute comm totals query" . mysql_error()); $row2 = mysql_fetch_row($lineresult); array_push($data_1, $row2[0]); }
View Replies !
Query Dates Within 1 Week From A Date
I'm fairly new to PHP and have done some basic work with mysql but am not sure how to approach this. I'm trying to figure out how to create a query with PHP to get all rows with a datetime from 1 week ago to the current timedate. Note: I'm also not sure how to find a date for 1 week ago with PHP. Any help would be great.
View Replies !
Comparing Dates Y, M, D , Time In Sql Query
ok.. ive finally got my nice little calender setup to output my dates now how do i query my database for example ive got 2 variables $startdate = 2006-1-1 00:00:01 $enddate = 2006-1-1 23:59:59 these represent the 1st and last second of the first of january 2006 i have a cell in my table (table called challenges) ( cell called time) that contains a date in the same format for each entry how do i get all the entries between the start time and the end time.. can i use less than < and more than > as these are not really integar values the column type is datetime but there is also another column of type datetime
View Replies !
Creating An A To Z List From Query
I am creating an a to z list - basically a count of all results that start with the letter "A", "B", "C" .... and so on. I am pretty poor at SQL so I am sure some brains out there can do better than I have here. What I have is working, I just want to make sure that it is optomized. So let's assume I have some query "$query" that I want to run and get an A..Z list based on column "$column". Let's further assume that '$query" produces the following results, and that $column is equal to "last_name". last_name --------------- Anderson Bitmore brown Bogus My AZlist query would look like this: select * from (SELECT count(alist.$column) as a from ($query) as alist where alist.$column like 'a%' or alist.$column like 'A%' ) as a_result, (SELECT count(blist.$column) as b from ($query) as blist where blist.$column like 'b%' or blist.$column like 'B%' ) as b_result, .... (SELECT count(zlist.$column) as z from ($query) as zlist where zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result; And this retuns the following result: a | b |...| z -------------- 1 | 3 |...| 0 Meaning that $query has 1 result where the first letter in $column is "A" or "a", 3 results where the first letter is "B" or "b" and 0 results where the first letter is "Z" or "z". What I am afraid of here is that "$query" is being executed 26 times (once for each letter of the alphabet) . Is there a way to refine this, or is MySQL (4.x and 5.x) smart enough to optomize this on its own?
View Replies !
Creating An Optimal Query
What I am doing at present is displaying a list of topics such as on a forum main page. That data is in a table say "my_topics" for example The list I am displaying also references data in another table say "my_images". Now each item in my_topics could reference zero or more items from my_images and they would need to be displayed alongside that topic. So each item in my_images has an id which is an index into the my_topics table. At present I am dumping the entire my_images table into an array so I can access it without having to do multiple sql queries. I can't use "left join" as far as I know because the relationship between my_topics and my_images is one-to-many rather than one-to-one.Obvious downside is that as my_images table gets larger - dumping it to an array increases the memory usage of the script which eventually leads to it not working.
View Replies !
Creating Complex Query
its a booking form. user selects start date, finish date, and room type. on my table i have roomInfo(roomNo, type, NoOfBeds); occupancy(roomNo,DateStart,DateFinish,GuestNumber); i am trying to do something like: select roomNo from roomInfo where roomNO = single and roomNO not IN ... cant do subselect its mysql 4.x version... would i have to do an outerjoin/innerjoin?
View Replies !
Creating A Query From A Form
I'm struggling building a SQL query from the output of a form, i.e. the user inputs into a form which in turn decides the query. I have never done this before and was just wondering if anyone had any links tutorials of something like this!? I have searched but haven't found anything too useful yet. Basically all I want to do is for the user to pick from a drop down menu how they want a leaderboard displayed, i.e. top 50 results, bottom 50, 50 to 100 results, etc. Do I just tie a complete SQL statement with the corresponding LIMIT info inside to a variable. The variable being the value of the chosen item in the drop down menu. Pseudocode as follows:
View Replies !
Creating Table From A Query
I have just put a forum up on my web site. The database tables were readymade via phpbb. Because I want to send newsletters I need a table that just contains usernames and email addresses. The table for users in the phpbb forum is huge so i did sql query: SELECT user_id, username, user_email FROM USERS; Which gave me all the information I need to send the emails on mass when this table is linked to a newsletter application. however I am aware this is just a query. Can I create a new table from this query? So i just have a table of users and emails. If so how do I do it? I am using phpMyadmin.
View Replies !
Creating A Query That Will Only Return Records With Matching Counterparts
I'm using the table below as an example. I want to create a MySQL query that will return only records that have matching counter-parts where 'col1' = 'ABC'. Notice the 'ABC / GHI' record does not have a counter-matching 'GHI / ABC' record. This record should not be returned because there is no matching counter-part. With this table, the 'ABC / GHI' record should be the only one returned in the query. How can I create a query that will do this? id | col1 | col2 -------------------- 1 | ABC | DEF 2 | DEF | ABC 3 | ABC | GHI 4 | DEF | GHI 5 | GHI | DEF
View Replies !
Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?
My insert code is below. The MySQL server is on my local machine and is version 4.1.22. I am exporting from an access table with 6 fields to a mysql table with those 6 plus 5 more fields. The main problem I am having SO FAR, is converting dates. The data file dates are formated like: 2/2/2006 0:00:00, 12/20/2006 9:22:05 Any ideas how to format those into mysql friendly dates? Also, how do I convert currency fields in the load data process? I know you use SET by what kind of formula? =========================================== LOAD DATA INFILE 'C:Documents and Settingspath_to_filedata.txt' INTO TABLE auto (field2, field3, field4, field5, currency_field6, field7, date_field8, date_field9) SET id = MD5(UUID()) FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '
View Replies !
Update String Dates Into Mysql Dates
I've got a table that someone created with varchar fields for month, day, and year. I've added a DATE field to the table, and now I want to combine all three of these strings into one DATE and stick it into the DATE field. Once I've got this done, I can delete those three varchar fields and just have the nice DATE field. Here's the command I've tried in several variations but no luck--syntax errors every time: UPDATE SET OCR_Entry_Date = date_format(str_to_date(concat_ws('-', OCR_MM, OCR_DD , OCR_YYYY) ,"%b-%d-%Y"), "%m-%d-%Y") WHERE OCR_YYYY = '1974' OCR_Entry_Date is a DATE field and the others are the varchar fields. My logic is basically: 1. concatenate the separate field values into a single string 2. convert the string into a date 3. format the date in the way I'd like it in the table
View Replies !
MSSQL Dates -> MySQL Dates Automatically?
We're using data feeds that were originally meant for MS SQL and the dates in the data feed (tab delimited text files) are formated like: Oct 21 2007 In MySQL, the date fields are formatted as datetime fields and when we do an import the dates all come through as 0000-00-00 00:00:00. We're importing using the MySQL "LOAD DATA LOCAL INFILE" command and just dumping the text files right into freshly truncated tables. Is there a way when loading the files to find and replace the dates maybe? Can MySQL convert the dates? I have a shell script downloading an archive and uncompressing, then it runs a PHP script that loops through the files runningt he LOAD DATA command. Any advice would be appreciated... I'm just looking for the easiest (and least server-intensive) way to get the date issue fixed. We've already tried begging the vendor but they're not yet ready to start supporting other date formats (even though their MS SQL db can output a date format friendly to MySQL). Thanks!
View Replies !
List All Dates Between 2 Diff Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.
View Replies !
Dates Pre 1970 And Dates After 2050
i have MySQL version: 4.1.16-nt, when i try to insert a date pre 1970 or after 2050 into a timestamp field, the date is stored as all 0's, how can i save a date pre 1970 and after 2050 into a mysql field?
View Replies !
List All The Dates Between 2 Given Dates Using SELECT
how I can list all the dates between 2 dates (inclusive) using SELECT sql statement in mysql? I have a member table with the following columns: id int autoincrement name varchar(40) join_date date I want the following output (date, count of members joined on that date): 1 April 2009...... (2 members joined) 2 April 2009...... (0 members joined) 3 April 2009...... (3 members joined) .....so on ...upto 30 April 2009.....(1 members joined) For the above output I need the SQL command that is valid in mysql. Is there anyone who can produce the above result with a single SELECT command. Please don't suggest that is possible using php for loop or creating a mysqlorary table first and then inserting all the dates into it. Then make a join between mysql table and members table using date as GROUP by column etc. etc. I know this is possible using some user variables as I searched through the various SQL tutorial sites. But I am unable to figure out how I can produce a list of all dates between 2 given dates.
View Replies !
Stroring Dates Perior To 1 AD, BC Dates
I need to store some dates that can be BC, However I received a problem indicating Incorrect Datetime. I have read the documentation, and it says that Date&DateTime types can store from 1000-9999 AD, and Timestamp stores less year ranges. What other options do I have to store dates before 1000 AD !
View Replies !
How To List All Missing Dates Between 2 Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.
View Replies !
Dates, Dates, Dates - Syntax Help Please!
OK, I have a MySQL table which contains, among others, a field called date which stores dates in the format YYYY-MM-DD HH:MM:SS. What I'd like to do is select a list of all the months present (without repetition), preferably in the format YYYY-mm
View Replies !
Select Based On FK
I have two questions regarding the MySQL v4.1.16 scenario below: 1. What should the FK on update and on delete parameters be (i.e., default, cascade, restrict, no action, etc.)? 2. How can I insert the correct id’s as implied below? I have tried just about everything (including “Insert …select”, “LAST_UPDATE_ID();”, etc.) and whenever I think I am starting to get close I either get more errors or ‘0 records added’ with no error message; I would greatlyappreciate someone who could instruct me on how to accomplish this task.
View Replies !
Using A Web Based Database
My web site host allows me to create MySQL databases on its database server. I've created a database as a trial. Now I would want users of my website to supply data to the database and for me to be able to extract it for analysis.
View Replies !
Web-based Chat
I'm debating between 2 different table setups for a web-based chat system (gchat clone). The first is just one table labeled 'chat' which has the columns 'user_1' 'user_2' 'time' 'message'. The second would be dynamically creating/deleting tables with the user names as table names like 'user_1/user_2'. I don't know a whole lot about DB table organization but I was thinking the actual 'select' statement would be faster with seperate tables. Any ideas?
View Replies !
|
TRACKER
