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
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Selecting Rows From The Past 24 Hours
I have some data rows, and they contain date entries in a datetime formatted field - e.g.: 2006-07-05 01:30:03 2006-07-05 01:46:36 2006-07-05 01:20:41 I would like to select rows from the table for the past 24 hours It would be something like: SELECT fldID, fldName, fldDate FROM my_table WHERE .... fldDate is within the last 24 hours But I'm not sure how to do it. I've used the date_format function before, but that might not help. Then I found the date_add function - so if I did this: SELECT DATE_ADD(��-01-02 23:59:59', INTERVAL -24 HOUR); It would return: 1998-01-01 23:59:59 That looks good - but I'm not sure how I can build it into my statement to only return rows where the data_format field contains data less entered in the last 24 hours.
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 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;
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?
How To Calculate Weeks Elapsed In MySQL V4.0.25
Can anyone provide an example of the most straight-forward way to determine if a datetime field in a table is from the future, the current week, last week, or the 3 months previous in MySQL v4.0.25? The TIMESTAMPDIFF function looks promising but it's only available in v5.0. I'd like to pass something to the WHERE clause of the query.
Data Exist 52 Weeks From Today?
I have a database which has the date column. I am having trouble to come out with a query that can select the data which exists 52 weeks from today.
Grouping Weeks, Months Or Years
I have a database full of log information that has a record for every hour of every day. So I have 24 records all with the same 'dateCreated' field and then the 'timeCreated' field changes for each hour of that day. So grouping the query results of the database into days is very simple, just a quick 'GROUP BY dateCreated' does the trick, however i now have the requirement to group that data into weeks, months and years. What is the best way of achieving this? is it even possible?
PAST INSTALLATION
I know almost nothing about MYSQL. I can programme (more or less) in Paradox. I installed MYSQL 4.1 for Windows, but there is no explanation of how to get it to run like the databases I know. (Paradox/Access). All you get is a black-screen DOS-like dialogue box which is a command-line monitor. With Paradox/Access, you get a proper inferface to play with tables, and can either programme an application or not. MYSQL seems inpenetrable without special knowledge. If MYSQL has to be programmed by a programmer before you can even learn how to use it then it's not much use to me. Am I missing something?
PHP - Can't Add Rows Past A Certain Id Number (127)
I've got a table which has about 70 rows in. The rows' ids start at 1, have a large gap in the middle, then range up to 127. If I try to add a new row with an autoindexed new id, I get the following MySQL error: duplicate entry '127' for key 1. If I add a new row with a lower id, e.g. 6, there are no issues - it works fine. If I delete row 127, or give it a lower number, I can then add a new row as normal, but then the error happens again for all subsequent rows. Everything was working fine with the page that adds new rows until it got to 127. Is this some kind of magic number I don't know about?
Searching For Dates In The Past
I am relatively new to this. both the SQL Forum and SQL itself. I have searched through the FAQ's but am not sure if i'm looking in the right place. I need to know how to search for a date that is a set number of days in the past. eg. I need to find what the date was 61 days before 21/06/06.
Rows In Past Month
I know I'm not writing this SQL command properly, please correct it for me. sql Code: Original - sql Code SELECT newsid,title,posted,content FROM `news` WHERE NOW() - `posted` < 2678400 SELECT newsid,title,posted,content FROM `news` WHERE NOW() - `posted` < 2678400
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?
Most Products Flagged In The Past 24 Hours (was "Query Help")
I need a little help setting up the logic behind a query. Here is the situation: I have 2 tables. Product table. Flag table (flagID, productID, date_flagged). Users can flag products. *date_flagged is a INTEGER unix timestamp. I would like to be able to setup a query where I can pull up products that have been flagged the most in the past 24 hours. At first I figured I would COUNT() the flags and GROUP BY productID, but this of course will only give me the most flagged all time, and because of the grouping by productID will not allow me to filter it by date.
Count Related Records, But Show Records With NO Related Records Also
cl_items ======== it_id (pk) it_ownerid it_name cl_offers ======== off_id (pk) off_itid (fk) -> to it_id off_whoid My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers) I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also. Is there any way to do this with mysql only?
All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in MySQlL 5. In My first select statement I get all my records from Table B SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` In my second statement I get my records that match in this case phase 401 in Table B and all my Table A records for phase 401. SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` Where table_A.PHASE In ('401' ) Now I need to combine the Data of both recordsets. I need EVERYTHING in Table B, but I also need All Table A records that match the phase selection.... Can I write this one query or do I need to use a Temp table?
Selecting First 10, Then Next 10...
How do I SELECT the next 10 records after I've selected the first ten, and so on. SELECT * FROM mytable LIMIT 10 ...will show on one page, then I want to click 'next' and get the next ten, and so on. If I had a column with stepwise monotonic integers (1,2,3,4...) it would be easy but I don't have that.
Selecting Just One
I have a table organized like this: Events Event1 Event1 Event1 Event2 Event2 Event3 Event3 Event4 How can I query the database so that it only returns one of each? So it would return: Event1 Event2 Event3 Event4
Selecting
Supposing I have a database with customer and number of items wanted as 2 columns. If I have 1000 items to sell how would I find out at which customer is my 1000 items sold. i.e. Customer number 1 100 2 500 3 300 4 200 5 600 Here I would only be able to supply up to customer 4. Is there any quick syntax to find this out automatically?
Selecting Only The First Row
I have been using the following command to select future events from a table: $query = "SELECT * FROM shows WHERE date_short >= CURDATE() ORDER BY date_short ASC"; and displaying all results using the following loop: if ($result = mysql_query ($query)) { while ($row = mysql_fetch_array ($result)) { //display } } What can I use when I only want to display the first row, eg the nearest future event?
Selecting From A-Z
I have a query: "SELECT * FROM table" and I want to select if alphabetically, I've seen the code somewhere but cant remember.
SQL Selecting
I have two tables that have columns with similar data. product_line.prodline_num has 86 unique fields product.ProLine has 73 unique fields I'm trying to make a query that will identify the 13 unique fields that ProLine does not have. I just can't seem to figure it out.
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 )
Selecting A Specified Row
is it possible to retrieve the cookie with php and make its value ftech a certain table row./. for a say a username.. its so i can do a profile thing for my site???
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".
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
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())...
|
TRACKER
