Query To Find Data For Previous Week
I am trying to get a query that can find if a Loandate falls in last week...so basically check if loandate is between sunday or monday of last to friday or saturday of lastweek...
Any clues:
Select * from loan where last weeks sunday<Loandate>last week's saturday.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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?
Week Ago Data
I'm writing a script that will alert me when a users birthdate is ONE week prior to NOW(); So, here is what I'm doing so far, I see my error... it's looking for the data exactly 7 days behind now(); Code: SELECT CuID, F_Name, L_Name, DOB FROM Customer WHERE DOB = DATE_ADD(NOW(), INTERVAL -7 DAY) Soo... how do I get it so it will query all dates within a week?
Using Data From Previous Row
I only want to select the current row if a field in the previous row is not null. Is it possible to write such a query?
Query For Last Week
I hope this is the right group for this: I am trying to query a db to return only values for the current week. My query currently look slike this: "SELECT {$iLabel[0]} FROM {$iTable} WHERE {$iLabel[0]} <= now() AND {$iLabel[0]} >= date_sub(now(), INTERVAL 1 WEEK) GROUP BY floor(unix_timestamp({$iLabel[0]})/{$AvgTime}) ORDER BY unix_timestamp({$iLabel[0]}) ASC",$iConnection); This returns a full week's worth of data. How can I get it to only return the current weeks data (say from Sunday on)?
Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles. based on this I now want to find all the individuals that are affiliated to those listed in the first query ....
Change Week Start Day When Selecting Week
I am building a tip tracking database to be used by restaurant employees. I need to allow users to choose their scheduling/pay period start day as they tend to vary in this business. I will be selecting shifts by week for each user and want to be able to define the start day of a week for any given user while querying. Is this something that can be done with MySQL?
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.
All Days In Current Week Query
I need all Entrys created in This Week. The Week starts with monday till friday. Can you give me an SQL Query command ? (SELECT * FROM MAIN WHERE DATE = {THIS WEEK}}
Count The Hits For Week And Month In One Query?
I have this query to count the amount of total clicks for a whole week, but i would also like to know the total amount of clicks for the whole month. Is it possible to do both of them with one query? Or do i really need to make 2 seperate querys for that? SELECT date FROM clicks WHERE date >= '$timestamps[0]' and date <= '$timestamps[6]' AND link_id = '{$row['ad_linkid']}'
Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?
Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17): "update user inner join events on user.username = events.username set user.LastMessageDate = '$current_time' where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'" but fails on my production server running v3.23.56. Anyone any ideas why it doesn't run and what I need to do to fix it?
A Query To Find Something From Last 30 Days
So basically I am trying to do a query to pull up a "request" that is over thirty days old. How do I setup the other half of the inequality to automatically adjust for what 30 days old is each day. Date is in this format: 2006-07-18 11:59:59 $result = mysql_query('SELECT * FROM requests WHERE reqtime < "2006-07-18 11:59:59" ');
How To Find Max Value Of Query Using Group By
When I run the following query select count( username) as count, username from friends where status="validated" group by username order by count; it gives me a list of each username in the and how many times it appears in the friends table Can anyone tell me how to rewrite this query so that it tells me which username appeared the most times in the friends table.
Find And Replace Query
recently i completed a forum move to a new URL, however there are many posts on my board that still use the old URL for reference. Is there a query that i could run on my database to find all the instances of the first URL and replace it with the second?
Need A Query To Find All Spam Entries In My DB
I need to tweak the following query to help weed out spammers who have created accounts. I want to try and: 1. Show accounts that have alpha characters in the zip field 2. Show accounts that use more than 5 numbers in the zip field select zip, country from users where country = 'United States'
How To Find Out How Many Times A Query Has Been Executed
How can you count the number of times your query has been executed? So I'm trying to set it up, so that I get a variable on every time someone views a review. I have two querys right now, but honestly don't really know what I'm doing. And I'm assuming I have to increment viewcount everytime that query gets executed. This query looks up the review:
Query To Find Friends Of A Friend
I'm experimenting with writing my own social network script but I'm struggling a bit with one of the queries. I have a users table, and a friendships table containing user_id and friend_id columns which are both foreign keys linking to users.id (sql dump at end of this post). if x is a friend of y, and the friendship is approved, then y is also a friend of x. I'm using this query to retreive all the friends of x (in this example, x has an id of 99):
Mysql Query Search And Find In String
I have following values in a field of mysql table. These values are stored as string [varchar] in the field. Values : 3,4,10,21,20,8,100,2,6 How can I check through MYSQL query that 100 or 21 or 4 exists in the string?
Query By Dates To Find History Records
what is the correct way to query on a date field in a table to pull a value that is older than 3 days? Date field format is 2007-04-04 I only want records where the date is 2007-04-01 or earlier.
Comma Delimited List Query Find
I am building an app. using ColdFusion along with Mysql. I need to create a select query that checks if certain Element ID's are contained in a comma delimited list which are stored in the MySQL database. Normally, this would be easy, just something along the lines of: select * from table_name where Comma_list like '%ID1%' The problem comes in the creating of the ID's there's nothing to stop the user from creating an ID that contains the entirty of another id...example:
Find Hightest Number After Finding First Query
i have this code but it does not display the highest number or anything. it should be searching the field 'name' for the string $name and only display the one that has the highest number in the 'number' field. what am i doing wrong? $res = mysql_query("SELECT MAX( number) FROM data WHERE name='".$name."'"); $high = mysql_result($res, 0, 'number') or die(mysql_error()); echo($high);
Getting This Week And Last Week
I need to display data as it was logged Mon-Fri, this week and last week, but can't work out the SQL. For this week, I can get WHERE TO_DAYS(NOW()) - TO_DAYS(Date) < 7 but of course this means that if it's only Tuesday I'll still get the Friday of last week. Any ideas?
Find All Games A User Has A Top 3 Score In (was "Need Help With A Query")
I am trying to create a query that will go through a scores table for me and find all the games that a user has a top 3 score in. Here are the table that are relevant I think... CREATE TABLE files ( fileid int(11) NOT NULL auto_increment, file text NOT NULL, icon text NOT NULL, filelocation int(1) NOT NULL default Ƈ', iconlocation int(1) NOT NULL default Ƈ', customcode text NOT NULL, title text NOT NULL, description text NOT NULL, keywords text NOT NULL, width int(11) NOT NULL default Ɔ', height int(11) NOT NULL default Ɔ', category int(11) NOT NULL default Ɔ', timesplayed int(11) NOT NULL default Ɔ', status int(1) NOT NULL default Ɔ', filetype int(2) NOT NULL default Ɔ', dateadded date NOT NULL default ��-00-00', rating int(11) NOT NULL default Ɔ', totalvotes int(11) NOT NULL default Ɔ', totalvotepoints int(11) NOT NULL default Ɔ', scores enum(Ɔ',Ƈ') NOT NULL default Ɔ', PRIMARY KEY (fileid) ) TYPE=MyISAM AUTO_INCREMENT=2285 ; CREATE TABLE scores ( scoreid int(11) NOT NULL auto_increment, fileid int(11) NOT NULL default Ɔ', score int(11) NOT NULL default Ɔ', username text NOT NULL, userid int(11) NOT NULL default Ɔ', usercomment text NOT NULL, dateadded date NOT NULL default ��-00-00', PRIMARY KEY (scoreid) ) TYPE=MyISAM AUTO_INCREMENT=16 ; CREATE TABLE users ( userid int(11) NOT NULL auto_increment, username text NOT NULL, password text NOT NULL, email text NOT NULL, status enum(Ƈ',Ɔ') NOT NULL default Ɔ', usergroup int(11) NOT NULL default Ƈ', joined date NOT NULL default ��-00-00', played int(11) NOT NULL default Ɔ', comments int(11) NOT NULL default Ɔ', avatar text NOT NULL, avatar_uploaded enum(Ƈ',Ɔ') NOT NULL default Ɔ', location text NOT NULL, website text NOT NULL, gender int(1) NOT NULL default Ɔ', favourite int(11) NOT NULL default Ɔ', receiveemails enum(Ƈ',Ɔ') NOT NULL default Ƈ', newpm enum(Ƈ',Ɔ') NOT NULL default Ɔ', confirmation text NOT NULL, PRIMARY KEY (userid) ) TYPE=MyISAM AUTO_INCREMENT=8 ; ( Basically the query would need to search through the scores table to find if the specified user $userid has the top 3 (by top 3 i mean 1st, 2nd, or 3rd highest score) scores of any game in the scores table. The end resukt would be a list that contained the fileid of the game the user has a top 3 score in, the actual score, and the position (1, 2, or 3) Im not sure if I am even explaining this right cause im kind of tired. I am a novice MYSQL user but this just has me stumped
Storing Day Of Week
I need to store the day of the week an item will occur on every week. I will then be able to sort by that column by the day. I was thinking about storing it in a datetime field while putting all items in the same week but changing the date so that it coincides with the proper day of the week. I'm not sure how to do this in either the mysql query or in php.
Week Function
I have a series of records that I need to sort by the week they were submitted. I'm trying to submit a query to do that, but it's not outputting any results: SELECT * FROM SessionInfo WHERE Date=WEEK(50); How do I get something like this to work? The dates are currently in the YYYY-MM-DD format.
Select Day Of The Week
I am working on a calendar storing dates and events in a MySQL db. Some of the events happen on a certain day of the week throughout the year (ex. every Tuesday). Does anyone know how I could store a day, say Tuesday, in the db and when I select a specific month and year the dates that "Tuesday" falls on for that month and year are returned?
GROUP BY Week
Here's a stumper...I'd like to be able to run a report on data and group it by week, starting on Monday and going through Sunday. I have a 'date' and a 'timestamp' field on the table. I can group data by *day* easily, like this: SELECT count( * ) AS RECORDS, date FROM mytable WHERE date BETWEEN '2007-01-01' AND '2008-01-01' GROUP BY date ...but can't figure out a way to do it by week. I could do it with a perl script, but I always try to figure out pure MySQL methods first
Select Previous And Next Id After Id=# ?
For a gallery I'm trying to query for a picture by its id and also get some images before and after that id (according to current sort) are there references like DOM previousNode or stuff like that to use in the WHERE statement? Or how could I do this? Currently I have: ($picId is a php variable) '(SELECT id, picUri, picUriSmall FROM pictures WHERE id < '.$picId.' ORDER BY id DESC LIMIT 2) UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id = '.$picId.') UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id > '.$picId.' LIMIT 2)' This works but doesn't seem to be the best way. I want to be able to have it working for other sorts than by id. And the two first images gotten by this query will be in reversed order (can be fixed in php). Is there a way to ask for my picIDs position in a query and then use another query to make the LIMIT 10,14 if my image was in position 12?
Paging With Only Next/previous
I'm running PHP + MySQL (innodb) and in some pages, the paging is slowing down the system a lot. Now I've seen on some sites that have paging implemented in an interesting way. They only let you go forwards and backwards one page at a time, and some don't even tell you how many results there are. For instance if you look at google search results, they only says "about XXX results". Plus they never actually let you jump to the last page. I'm thinking of implementing my system this way in certain pages as well, to cut down on the load times. However, I'm exactly sure how that cuts down the load. Can someone shed some light on this?
Previous To Last With A Certain WHERE Condition
How do i fetch the previous row record with the same WHERE when the last record has only a lately date. for example field CConsorcio CRubro ImporteA FechaA 1 105 1000 2006-05-10 2 110 30 2006-05-12 5 105 300 2006-05-13 4 104 600 2006-05-14 2 103 800 2006-05-15 the WHERE CConsorcio=2 the last record: 2,103,800,2006-05-15 and i want to select the row: 2,110,30,2006-05-12
Previous Record
I need to get a previous record from database - i need to retrieve records which differs from the previous one about 5s example: select eventDate,previousEventDate,eventDate-previousEventDate as delta from events having delta<5 order by eventDate; eventDate is normal column from table previousEventDate is an "alias" for the previous date ... how can I write it in SQL? Please help. I can use SELECT as subquery, but it would be probably really slow, the table will have 100000s maybe milions of records soon.
Previous Row Comparison
I have a table in this format: Column A | Column B 1 | 2 1 | 3 1 | 4 2 | 5 ..... ..... What I want to do is something like this: If in Column A there is a change in the number i.e. from the above table there were many 1s which transitioned into a 2. I want to record this change. I want to extract these two rows into another table. How can I make this possible?
Get Next And Previous Records
Our website has a portfolio database which uses a column entitled 'list_order' to set the order of how we want the projects to display in a list. Each project has a link to click into it to see the full details of the project. Inside this details page we would like to add a View Previous Project and View Next Project link which would allow the user to navigate through our projects. The problem is that we are using an order set by 'list_order' and not an auto incremented column. How would I setup the Query to grab the previous and next rows no matter which project the user clicked on? For example, our database has these records: id - list_order 102 - 100 89 - 200 7 - 250 30 - 300 97 - 400 20 - 500 62 - 600 88 - 650 103 - 700 85 - 800 112 - 900 95 - 1000 So, if someone clicked into id 97, we'd need a query that would grab id numbers 30 and 20 for the previous and next id to create the links.
ID Of Previous Insert
Im thinking i need a transaction here. I want to insert a record into a table, which has an autoincrement field as the primary id. Then use this id to insert into another table. In the time between the first insert and then getting the id, couldnt there possibly be another user that causes an insert and hence the id the first user gets back isnt correct ?transactions to the rescue ?
Subtract Previous Value.
I'm trying to get one column of numbers to subtract from another column and to keep a running total. Column A is a dollar amount from an invoice. Column B starts out with an opening amount ($10,000) and I want to have Column A to subtract from that. Code: COLUMN A--------COLUMN B $0.00 $10,000 $1000 $9,000 $500 $8,500 $2000 $6,500
Getting Dates From: Week, Year
Is it possible to retrieve the dates that belong to a certain week in a year? If I query for Week 23 and Year 2006...I'd like to get this returned. 5-6-2006 6-6-2006 7-6-2006 8-6-2006 9-6-2006
Select From Table Where Age Is One Week
I think the Subject line is slightly cryptic... I have a simple query that outputs the records from a table that have dates 'entered' after 2008-04-01: CODESELECT * FROM table_user w WHERE w.`entered` >= '2008-04-01';
Generate Table Of Week
Is there a way to generate a table automatically that looks like this: YearWeek StartDate EndDate ------------------------------------- 200701 2007-01-01 2007-01-06 200702 2007-01-07 etc. Ideally, the query would generate all the YearWeeks from year 2000 up until CURDATE, not including the current week (i.e. all "complete" weeks). Another option would be to simply store YearWeek in a table, and automatically select StartDate and EndDate accordingly. I guess another option would be not storing it in a table at all, simply generating it at runtime.
Count 1 Week Interval
I'm having some trouble patching up my referrer script. I want my PHP script to tell me how many visitors I had the following week, up to today. I've wrote the monthly, current and yesterday bits, but I can't manage to create a weekly one. can someone help out? // yesterday $yesterday = date("Y-m-d",mktime(0,0,0,date("m") ,date("d")-1,date("Y"))); $sql_yesterday_command='SELECT count(*) as total FROM visits where date="'.$yesterday.'"'; $query_yesterday=mysql_query($sql_yesterday_command, $connect); $fetch_array_yesterday=mysql_fetch_array($query_yesterday); $visitors_yesterday=$fetch_array_yesterday['total']; // this month $sql_month_command='SELECT count(*) as total FROM visits where date like "'.date("Y").'-'.date("m").'-%"'; $query_month=mysql_query($sql_month_command, $connect); $fetch_array_month=mysql_fetch_array($query_month); $visitors_month=$fetch_array_month['total'];
Get Previous And Next ID Of Current Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example: ID's: 4,10,7,9,13 I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9. I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.
Phase Previous And Next Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example: ID's: 4,10,7,9,13 I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9. I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.
Previous And Next Record Delimma
I have a main page that has a query to get all my clients in alphabetical order. "SELECT * FROM clients ORDER BY cname ASC" This obviously mixes up their IDs. So when I click on a client's name "Goofy" (id = 6), I need the next client "Mickey Mouse" (id = 2), and "Donald Duck" (id = 9) to be the previous client. And I would prefer this to happen on the same page where it displays my client's details rather than have to put those vars in the URL. To get my client, it's simply: $client = $_GET['c']; "SELECT * FROM clients WHERE clientid = '".$client."'"
|
TRACKER
