Selecting Results From This Business Week
I have a table which has the column 'date_added'. I need a query which returns all of the results which were added in the current week. By this I dont mean today minus 7 days, I mean monday 00.00am to sunday 11.59pm.
Is there a way of doing this? I've looked into WEEK() and YEARWEEK() but cant think of a way to do this using PHP.
Heres what I've got (probably awfully wrong..
PHP
mysql_query("SELECT * FROM table WHERE date_added BETWEEN WEEK(now(), 0) AND WEEK(??)");
View Complete Forum Thread with Replies
Related Forum Messages:
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?
View Replies !
Selecting Mutual Results
I have a table such as this: friends user friend 1 2 1 3 2 4 2 1 how could i select results where user and friend are the same but in opposite rows (that is, in this example, user 1 and friend 2 (user 2 and friend 1))?
View Replies !
Selecting Non Mutual Results
I have a table, such as this: friends +--------+----------+ | userid | friendid | +--------+----------+ | 2 | 7 | | 2 | 3 | | 2 | 6 | | 2 | 8 | | 2 | 9 | | 2 | 34 | | 7 | 2 | | 34 | 2 | | 9 | 2 | | 2 | 47 | +--------+----------+ and Bill K kindly helped with the code to fine opposite column matches (2 and 7, 7 and 2 for example) but now I want to return the results for a specific user excluding these opposite column matches. How can I do this?
View Replies !
Selecting Distinct Results In A Join
SELECT p.post_title , p.post_name , c.cat_name , DATE_FORMAT(p.post_date, '%M %D, %Y') AS dateadded FROM wp_posts AS p LEFT JOIN wp_post2cat AS pc ON p.ID = pc.post_id LEFT JOIN wp_categories AS c ON pc.category_id = c.cat_ID WHERE MATCH(p.post_content, p.post_title) AGAINST(TRIM('merchant') IN BOOLEAN MODE) ORDER BY p.post_date DESC I am doing a fulltext search on a Wordpress database. Some posts are in multiple categories. When using the above query I will get a result for every category a post is in. I don't want that. I have tried using distinct with no effect. The wp_categories has a row for each category a post is in and how I am joining it seems to be the culprit.
View Replies !
Calculating Business Days
I need to calculate the number of business days between a pair of arbitrary dates.Some scrounging around online delivered me this query,which basically does the trick: Quote:SELECT d1, d2, @dow1 := DAYOFWEEK(d1) AS dow1, @dow2 := DAYOFWEEK(d2) AS dow2, @days := DATEDIFF(d2,d1) AS Days, @wknddays := 2 * FLOOR( @days / 7 ) + if( @dow1 = 1 AND @dow2 > 1, 1, if( @dow1 = 7 AND @dow2 = 1, 1, if( @dow1 > 1 AND @dow1 > @dow2, 2, if( @dow1 < 7 AND @dow2 = 7, 1, 0 ) ) ) ) AS WkndDays, @days - @wkndDays AS BizDays FROM dates ORDER BY d1,d2;
View Replies !
Jdbc Over SSL, SAP Business Connector 4.6
I have set up a connection between SAP Business Connector 4.6 and MYSQL. I'm currently using jdbc driver com.mysql.jdbc.Driver (mysql-connector-java-3.0.16-ga-bin.jar). Using DB URL jdbc:mysql://<ip>/<database> the connection is working fine. So far so good. Now my problem: I need to set up a SSL connection but in MySQL Connector/J Documentation is stated: 'For SSL Support to work, you must have the following: A JDK that includes JSSE (Java Secure Sockets Extension), like JDK-1.4.1 or newer. SSL does not currently work with a JDK that you can add JSSE to, like JDK-1.2.x or JDK-1.3.x due to the following JSSE bug: http://developer.java.sun.com/developer/bugParade/bugs/4273544.html' My SAP business connector is running under jdk 1.3.0 How do I get SSL running?
View Replies !
Selecting Filtered Results From A Table With 1 Million Rows
I am trying to write an interesting query here and I am not sure if it all can be done in one step in the same query. Here is my dilemma: I have a DB with two tables: mm_recipients and postal_codes. The first one has a list of users in my system and the second one a list of 1 million postal codes in Canada. My objective is to select a list of 50 users that live less than 10Km away from a central location and are between 25 and 50 years old. The MM_RECIPIENTS table has the following fields: ....
View Replies !
How To Reset Yahoo Small Business MySQL Database
I am a new user of "Yahoo Small Business" web hosting service and I changed a lot of settings in my "MySQL Database" using "phpMyAdmin" utility during my learning period. Now, I want to reset my Yahoo Small Business MySQL Database to default settings or to reinstall it (I want it to be the same as it was newly activated). Note: I tried to repair my database with Yahoo's "Database Setup" page and the message "Database scheduled for repair" appeared for a few days but nothing happened. And, I can not login to my database using "phpMyAdmin" utility. If you can help me to reset my "Yahoo Small Business - MySQL Database" I will be very pleased.
View Replies !
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?
View Replies !
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
View Replies !
Birthdays This Week
I want to find record of all the people whose date of Birth falls in this week i.e 2006-11-27 and 2006-12-04. basically I want to find out Date of Birth falling in current week
View Replies !
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?
View Replies !
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?
View Replies !
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)?
View Replies !
WEEK() And The Years
The following query returns the dates within a given week : SELECT WEEK(DateEvent),DateEvent FROM event WHERE WEEK(DateEvent) = WEEK('2008-09-18'); Since WEEK('2008-09-18') returns 37, the results obviously don't take in consideration the year so all the dates of the week 37 of every year are displayed. If I specify the year, then the results are narrowed down : SELECT WEEK(DateEvent),DateEvent FROM event WHERE WEEK(DateEvent) = WEEK('2008-09-18') AND YEAR('2008-09-18') = 2008;
View Replies !
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.
View Replies !
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.
View Replies !
Finding Out The Week Day Start..
I have a table with lots of records. The fields are: - date - amount Im trying to build a query that COUNTS the number of records and adds up the amounts grouped by week. However, I don't want to display just the week number as this isn't useful, I want to display the week start date (monday). So in this case: 19 01 2009 - 10 - 450 26 01 2009 - 5 - 120 etc. The only bit im not sure about is the start of week date, not sure I can use conventional DATE_FORMAT() function.
View Replies !
Update Column Week
I need to update column week from 1 to 2 when a certain time occurs. is this possible if so what or how do i go about doing it?
View Replies !
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.
View Replies !
Find First Date In Week?
This query returns the number of new user registrations per week for the past 30 days. SELECT WEEK(registerdate,2) as WeekNum, COUNT(*) as NewRegs FROM users WHERE registerdate> CURDATE()-INTERVAL 30 DAY GROUP BY WeekNum ORDER BY registerdate It works fine, but shows only a number for the week. I would like to be able to find the date range, or at least the first date, in the numbered week. For example, I would like to return 2008-01-13 as the first date in week 2 for this year (using mode 2 for WEEK function).
View Replies !
Date_format :: Average By Week
I have used the following query to try and average by week select date_format(calldate, '%Y-%m-%d 23:59:59'), avg(billsec) from Clovercdr where calldate Between '2006-02-01 00:00:01' and '2006-03-22 23:59:59' and branchname = 'Boksburg' and billsec > 0 and Network = 'SAMobile' GROUP BY date_format(calldate, '%x%v') I know that it should be averageing every week on monday: 1. is there any way I can average every week from the first day ie. first day is 2006-02-01 which is a wendsday, every wendsday for the given time period 2. I dont understand my results here ....
View Replies !
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'];
View Replies !
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
View Replies !
Week Number In Specific Month
I have a problem regarding the week number in specific month..... i would like to get the week number in that certain month by just providing the date...... for example : 2007-10-07 - week 1 (oct) 2007-10-14 - week 2 (oct) but i want it to return me 1 when i provide (2007-10-07 to 2007-10-13) and return me 2 when i provide (2007-10-14o 2007-10-20) ..... and so on.... it is possible ?
View Replies !
How To Get YEARWEEK() To See Sunday As The Start Of The Week?
I'm trying to get the YEARWEEK function to see Sunday as the 1st day of the week. An example date is: Sunday 1st Mar 2009 Here is my sql SELECT YEARWEEK('2009-03-01') and the result is -> 200909 Which is week 9. I believe it is telling me this is week 9 because it sees the sunday as the last day of the previous week. How would I get this function to see sunday as the 1st day of the week and therefore return as week 10? I have tried the following mode options but have had no success mysql> SELECT YEARWEEK('2009-03-01',0) -> 200909 mysql> SELECT YEARWEEK('2009-03-01',1) -> 200909 mysql> SELECT YEARWEEK('2009-03-01',2) -> 200909 mysql> SELECT YEARWEEK('2009-03-01',3) -> 200909 mysql> SELECT YEARWEEK('2009-03-01',4) -> 200909 mysql> SELECT YEARWEEK('2009-03-01',5) -> 200908 mysql> SELECT YEARWEEK('2009-03-01',6) -> 200909 mysql> SELECT YEARWEEK('2009-03-01',7) -> 200908
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 !
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?
View Replies !
Select Rows From Yesterday/Last Week
PHP //yesterday WHERE (month(articles.date) = month(now()) AND dayofmonth(articles.date) = (dayofmonth(now())-1) AND year(articles.date)=year(now())) //last week WHERE (week(articles.date)=(week(now())-1) AND year(articles.date)=year(now())) This works all good and well, EXCEPT: When the date is the 1st for yesterday, need to go back a month. What is a better way to write the query to grab yesterday, even if yesterday was in the previous month. What is a better way to write the last week, even if last week was the end of last year?
View Replies !
Querying Db For Current Week Items
I have a db where I store videos file names and the date they are posted in UNIX timestamp format. I need to extract just the videos which have been posted during the current week. so I'm using this as a test but I'm not getting anything: SELECT * FROM video WHERE WEEK(Video_PostedOn) = 37 do I have to first use a function to convert that?
View Replies !
DATE_SUB() :: Calculate Working Days Of A Week
I have a table for orders containg a delivery dates (DATE) and a delivery_duration (INT) in days. The problem is the delivery duration is specified in 'working days', i.e. weekends don't count. How can I calculate the shipping date, which would be the delivery date - duration weekdays? e.g. a monday - 2 working days => thursday not a saturday.
View Replies !
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']}'
View Replies !
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?
View Replies !
Date Format :: Display Week Days
I am trying to output a list of dates from mysql database into a table. I have created a variable $date which outputs in the format yyyy-mm-dd as per the default mysql format. How can I change this to display the list of dates in the format day (eg monday), date, month, year.
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 !
Group By "week Of"
Week of February 1 - item - item - item Week of January 25 - item - item - item Week of January 18 - item - item - item Is this something mysql can do for me or will I need to code this with PHP? My query so far: Code: $query="SELECT pkey, title, LEFT(title,60), start_date FROM `announcements` WHERE localnet=1 AND approved=1 AND visible=1 AND curdate()<end_date ORDER BY start_date DESC";
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 !
|
TRACKER
