Items Per Month Query
If I wanted to query the DB and get all itemid's = 111 that are marked "shipped" displayed by month how can I do this?
View Complete Forum Thread with Replies
Related Forum Messages:
Get Category And Number Of Items In One Query
I have a `category` table with columns cat_id and cat_name. And another `items` table with columns item_id, item_name and cat_id. I want a query which will select display each category name and the number of items in that category. I could do this in two sql statements, is it possible to do it in one?
View Replies !
Ignore Duplicate Query Items
This formcode retrieves faxes from a DB but some are the same and I do not want those in my list... how do I tell it to ignore duplicates?PHP Code: $sql = "select c.fax as fax ". "from #__comprofiler as c ". "left join #__users as u on c.user_id = u.id ". " where (c.user_id = u.id) and (u.block =0) and (c.fax != '') and (u.usertype != 'Super Administrator') and (u.name != 'guest') ". " order by c.fax";
View Replies !
Birthday Query Per Month
I want to output all the birthday celebrants for every month. YYYY-MM-DD is the format is my birthday. Not just the present month birthday celebrant but, per month. I try to use Select * from tblSample where birthday between '' and ''; But no success for i don't have the start date and end date of every month.
View Replies !
Mysql Is Giving The Previous Month, Not This Month, Strange!
PHP Code: $myquery = @mysql_query("select year(sendon) as yr, month(sendon) as mth, count(*) as hits from ebook_user group by yr, mth"); while ($myrow = mysql_fetch_array($myquery)) { $mymonth = $myrow['mth']; $myyear = $myrow['yr']; $myhits = $myrow['hits']; print '<td bgcolor="#E7E7E5">'.date("M", mktime(0, 0, 0, $mymonth, 0, $myyear)).' '.$myyear.' </td><td bgcolor="#E7E7E5"> '.$myhits.'</td>' } The above code is printing the previous month instead of current month ... can any one help?
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 !
Count And Group By Month In 1 Query For A Date Range?
I have a table that has employees work history on it. Basically the only thing I am worried about is the start and end date. My boss wants a report that charts out the quantity of workers for the past 12 months group by the month. For example Employee--Start Date--End Date Charles-----2008-6-3---2009-2-1 Vicky-------2009-1-1---Present So in this case there would be a quantity of 1 from June 08 to December 08. Then their would be a quantity of 2 from Jan 09 to Feb 09. And Vicky would also be counted for March since she is still working. I can do this by creating PHP functions and putting it all together, but I was just wondering if there was a way to do this all in 1 query.
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 !
Month By Month Count Of Records
I need to generate record counts for each month in a year, just one year at a time, but a full year at a glance. I am thinking 12 hits to the DB is perhaps not the most idea way to do it, is there a way to rip this out in one go?
View Replies !
Collecting Month By Month Results
does anyone have thoughts on how to do queries that produce date bins? I do things like DATE_FORMAT( created_time , '%M %Y' ) AS date and then group by date thing is that when there is nothing in a particular month, no row is returned ... So a query like this: Code:
View Replies !
Design For Stock Items And Checkin/checkout Process (was "Query Help")
I'm currently in the process of trying to bodge together a simple(ish) stock system to be used on a company intranet but I've ground to a halt with a particular problem. Using a MySQL database I've got tables with 11 fields, including 'Quantity' (self explanatory) and BookOut. Now this BookOut field contains information on the user who has booked out an item including the users name, the date/time etc The problem I have is this. If an item has a quantity of 7 and a user books out 1, then his/her info is stored in BookOut but when another user books out another 1 of said item at a later stage then the previous BookOut info is overwritten. Is there a way in that everytime another item is booked out that a new line is created within a specific field? } if(!sizeof($error)){ $update_SQL = "Update joystick Set BookOut = '" . mysql_real_escape_string($BookOut) . "', PCserial = '" . mysql_real_escape_string($PCserial) . "', Quantity = Quantity - " . $Quantity . ", MachineNo = '" . mysql_real_escape_string($MachineNo) . "', Date = now() WHERE ID = " . $ID; if(! @mysql_query($update_SQL)){ $error[] = "Update SQL failed"; } The above is the query I am currently using. I'm not totally proficient with .php and have already had a lot of help getting this far.
View Replies !
Single Query For No Of Hits "Today", "This Month", "This Year"
Is it possible to construct a single query that fetches the no of pageviews "today", "this month", "this year" and "total since start"? The table is very simple, one field for Date (YYYY-MM-DD) and one for the no of pageviews this day. If not, would it be much slower/faster to divide the query into 4 different queries?
View Replies !
Last 3 Items
I'm not sure how to select the last 3 items in ascending order. This does the trick in descending order: select * from user_menu_main where deleted = 0 and hidden = 0 order by date desc limit 3
View Replies !
Select Items
I have two tables: TABLE_ARTICLE article_id******article_name article_content TABLE_ARTICLE_SECTION article_id *section_id Given an article_id, how do I select all articles from TABLE_ARTICLE that have the same section_id as that of a given article_id. Something like this but for mysql: SELECT table_article.* FROM table_article,table_section WHERE table_section.section_id IN (SELECT section_id FROM table_section WHERE article_id="something")
View Replies !
Count Items
How would I count the items on my order details table and verify if all are marked "SHIPPED" in one query. Code: SELECT * FROM ORDER_DETAILS WHERE orderid = "333333"
View Replies !
The Number Of Items
How can I tell how many members there are in a set of a given row? 0001 = 1 memeber 0110 = 2 members 1001 = 2 members really I just want to know if there are 2 or more.
View Replies !
Grouping By Items
Is it possible to group items by values not defined in the database? For example, I have a list of prices and I would like to count/group them in two groups, one > 2000 and one < 2000. I am on MYSQL 4.0.1, so no subselects for me i'm afriad.Is this going to be possible?
View Replies !
Counting Set Items
Is it possible to count a set of items? Example count("2,3,4,5",","); Would return 4 Count items based on the delimeter? Or just count set items.
View Replies !
Listing Available Items
I'm building a small internal equipment bookings system, the relevant tables are built up like this (I've ommited irrelevant fields just to try and keep this a bit simple): inventory (Holds item details) --------- InventoryID (Pk)(Int) Name (Varchar) bookings (Booking details) -------- BookingID (Pk) (Int) Start (Date) End (Date) bookings-inventory ------------------ BookingID InventoryID What I would like to do is produce a list of items but only show those available between two given dates. I just can't figure out how to effectively query across all three tables in this manner. I can do a query on the bookings table to list those bookings which clash but I havn't established how to move from this to what I'm after.
View Replies !
Find Items Near Another
I have a bunch of records in a database that have an ID that is indexed. The IDs are not necessarily contiguous - there may be missing IDs. Now say that given an ID, which may not even exist in the database, I need a result set of the n closest IDs. For example: My IDs (each one is a record) are: [1,2,3,7,15,16,22,25,42] I'm given the ID of 17 and need to find the 6 nearest IDs. I'd like to have a result set of [3,7,15,16,22,25]. I don't know how to search "around" a particular target. I've looked in the docs and don't see it.....
View Replies !
Showing The Last Five Items
I have a comment system where I want to show the last five comments. So say I have 20 comments. I would like to display comments 15-20 I have tried "ORDER BY id DESC LIMIT 5" but it shows the last comment at the top of the page and comment 15 at the bottom. I would like comment 15 at the t
View Replies !
Removing Old Items
I have a mysql table that stores temporary session items, but I don't want the table to get huge. What would be the best way to automatically delete old items that aren't being used anymore? I could use a timestamp, but this will only be good through the year 2038. I don't want this function to stop working 32 years from now. Any suggestions?
View Replies !
List Of Items In A Field
I have a table of products for a shopping cart. I want to give the user to ability to select, for example, a color for a certain product. How can I set up my database table so that certain records can have a number of options available to them. Is there a data type like a "list" where I can list the options? I thought adding a field that when set would indicate the presence of additional options, which would then indicate that I should check for additional options.
View Replies !
Select Array Items
How can I select an array of items in mysql. I would like to use a SELECT query. Instead of Select * from equipment WHERE itemid = 111 OR itemid=112 OR itemid=113. Can I just create an array of itemid's and use that array in the mysql statement. Is this possible??
View Replies !
Select Items NOT IN Order
I created an array of items that I want to select only the items that are not on the order. The below query grabs everything on the order BUT the items in the array. Code: $ITEMS = array ( "455", "456", "1101", "1100", "1588", "458", "452", "453" ); $ITEMS = implode(',', $ITEMS); $sql = "SELECT * FROM INVOICES WHERE orderid='$_GET[id]' AND equipmentid NOT IN ($ITEMS)";
View Replies !
Items Based On The First Letter
we have products database with CD's, and would like to create an alphabetical list of artists, then display the CD's that the artist has underneath them. The database has the products in them, with the artists name being called 'prod_artist_name'. the query i tried, though I knew it wouldn't work is: SELECT * FROM products WHERE prod_artist_name LIKE '%n%' Now, this displays all the artists with the letter 'n' in their name, obviously. just wondering if there was a way to display the artists with 'n' as the first letter of their name.
View Replies !
Update Selected Items
I have 2 tables: financas, cadastrados.I want to compare if the field matricula in both of them are equal to each other.Then I want to update financas grupo with the grupo from cadastrados Here is my non-working code Code: UPDATE financas SET grupo=(SELECT grupo from cadastrados WHERE cadastrados.matricula=financas.matricula)
View Replies !
Count Items Recursivly
Can I loop this to recursivly count total `num` with MySQL v4? SELECT COUNT(t2.`id`) num FROM `categories` t1 LEFT JOIN `links` t2 ON t1.`id` = t2.`category_id` WHERE t1.`parent` = '33' AND t2.`visible` = '1'
View Replies !
Counting Unique Items
I have a set of ids(for different items) in a table so when I do mysql> select viewer_id from observations; +-----------+ | viewer_id | +-----------+ | 1 | | 6 | | 1 | | 2 | | 1 | | 3 | | 3 | | 2 | | 2 | | 1 | +-----------+ 10 rows in set (0.06 sec) I get all, but I want to know how many unique viewers there are (there are only 4). I know I can do mysql> select distinct viewer_id from observations order by 1; +-----------+ | viewer_id | +-----------+ | 1 | | 2 | | 3 | | 6 | +-----------+ 4 rows in set (0.00 sec) But I don't know how to put them together (using a subquery). I tried mysql> count (*) from observations where viewer_id = ( select distinct viewer_id from observations);
View Replies !
Datatype For A List Of Items
I have a table called students and a table called classes. In the web page you create a student by typing in the classes he has taken. If the class doesn't yet exist in the classes database, it will be created. Somehow, I want to store what classes each student has taken. I to be able to find, for example, all the students who have taken courses 232 and 199 but haven't taken course 104. Is there a datatype similiar to an array/list, or is there a better way to store this information? (I want to be able to search quickly.)
View Replies !
Way To Delete Unmatched Items
I have a schema with three tables, blogitems, topicitems, and topics. Topics is a list of topics and their primary keys, blogitems is a bunch of blogposts and their primary keys. Topicitems relates the blogitems to the topics, so each entry contains three columns, a primary key, a blogitem key, and a topic key. I sometimes delete records from topicitems (because they're too old, not really about the topic, deleting a topic etc.) and need to clean up orphan blogitems. That is, I need to delete blotitems that are no longer connected to any records topicitems. I am doing it with a left join like so delete topicitems.* from topicitems left join blogitems on (topicitems.blogitemsid = blogitems.id) where blogitems.id is null The problem is this is really, really slow. Am I doing something wrong or is there a better way to accomplish this?
View Replies !
Get X Newest Items From Each Category
I'm new on forum so I'd like to introduce myself. My name is Michal, I come from Poland. I write PHP scripts using MySQL database from 4 years. Now I have a problem. My database structure looks like (only the most important columns): article (id, title, created_at) category (id, name) article_category (article_id, category_id) My problem is how to get X newest items (sorted by created_at column) from each category? For example for X=2 I'd like to get output: category.id | category.name | article.id | article.title --------------------------------------------------------- 1 Â Â Â Â Â | Category_1 Â | 100 Â Â Â Â | Article_100 1 Â Â Â Â Â | Category_1 Â | 99 Â Â Â Â | Article_99 2 Â Â Â Â Â | Category_2 Â | 100 Â Â Â Â | Article_100 2 Â Â Â Â Â | Category_2 Â | 98 Â Â Â Â | Article_98
View Replies !
Get A List Of Items That Are In 1 Table And Not The Other
I have a couple of tables, and would like to make a query to get a list of items that are in 1 table and not the other. A join command will get the list of items that are in each one. But I have not been able to figure out how to only pull out the ones that I need. Table 1 has invoice number, and code number. Table 2 has code number. I need a list of the items from Table 2 where the code numbers are not in Table 1.
View Replies !
Field With Multiple Items..
I am working on my first project using PHP and MySQL. I need some insight on database design, here is my issue: I have a database filled with items that I want to make searchable by people that are involved with each item. More than one person is involved with each item on all cases. So I have a table that is like this: TABLE search_data id item_id people location subject how I would deal with there being more than one person. Every other item in the tables are discrete pieces of information. Do I have them all entered in a comma delineated fashion? I also would like to search people and see what items they are involved with. So I think I would need to create another table called 'people' that has each person that has been added. I am just having trouble figuring out how to connect it all and make it work. It seems to me that there will have to be some field that has many entries in it somewhere in the database.
View Replies !
Select For Featured Items
I'm developping a web application similar to Youtube or Slideshare. I currently work on the home page but I dont know how to display the featured items. If you have a look at youtube homepage for example, the videos are displayed like randomly but I dont think it is. Do you have any idea how the select could be like?
View Replies !
Total Number Of Items
I am limiting a query to only one line to save code in perl because that's all I need. However, is there a way to have MySQL tell me how many lines there would have been without the limit and without running another query? By limiting, I can't use scalar(@lines); in perl. I'm sure Mysql has a way to calculate lines, but I can't seem to find it.
View Replies !
Displaying All Items When Order Has Not Been Placed.
I have this SQL (I'm using MySQL) SELECT tbl_cottages.id, tbl_cottages.cottage_name, tbl_calendar.calendar_date, tbl_bookings.id as booking_id, tbl_bookings.book_date, tbl_bookings.book_cottage, tbl_bookings.book_status FROM tbl_cottages, tbl_calendar, tbl_bookings WHERE tbl_bookings.book_cottage = tbl_cottages.cottage_name(+) AND tbl_bookings.book_date = '#URL.calendar_date#' tbl_cottages - includes all Cottage Name tbl_calendar - has dates tbl_bookings - this table contains any books that have been placed. So if I book 'Rose Cottage' on 'January 1' it goes into this database. What I am trying to do is, list all the cottages in tbl_cottages, but if an order has been placed and an entry has been entered into tbl_bookings for 'January 1' I would like to display the text in 'tbl_bookings.book_status' next to the cottage on the page. Obviously if we move on to a different date for eg 'January 2' the 'tbl_bookings.book_status' would show nothing unless an order has been placed.
View Replies !
Categories With Subcategories & Items?
I'm having more or less logical troubles on creating a database. (Without InnoDB) What I would want is to have main categories, which will contain subcategories and/or items. (Like this forum, "SitePoint Forums > Forum Index > Program Your Site > Databases") Quote: Main categories --------------- - Can contain X amount of items or/and subcategories. - Fields: MainCatID, MainCatName, SubCatID. (?) Categories --------------- - Can contain X amount of items or/and subcategories. - Fields: SubCatID, SubCatName, ItemListID. (?) ItemList (a helper table) --------------- - This table is the link between items and categories. - Fields: ItemListID, ItemID, SubCatID (?) Items --------------- - Fields: ItemID, ItemName.. other various fields. Is there a better way to accomplish this? And would this work? Or would you have any other suggestions or helpful links? edit, the ID -fields are of course primary keys with auto_increment.
View Replies !
Most Sold Items Per Buyer
I don't know how to create this query and I appreciate any help. I have this table "sales" with the following fields: id buyer_id item_id My goal is to answer this question: What did every buyer buy most? So, I need to show one item per buyer, the item that he bought most of the times.
View Replies !
How Can I Store Items In A Database Like
Okay lets say I want to store a poem in the database like: roses are red voilets are blue sugar is sweet and so are you And I want the spacing to be like that.. and when I call it with php it show like that with the spacing?
View Replies !
SQL: Fetch Most Clicked Items
SQL CREATE TABLE `feed_clicks` ( `clickID` int(11) NOT NULL AUTO_INCREMENT, `clickFeedID` int(11) NOT NULL, `clickDate` int(11) NOT NULL, `clickIP` varchar(255) NOT NULL, UNIQUE KEY `clickID` (`clickID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1031 ; And: SQL CREATE TABLE `feed_items` ( `feedID` int(11) NOT NULL AUTO_INCREMENT, `feedSubject` varchar(255) character SET utf8 collate utf8_swedish_ci NOT NULL, `feedURL` text character SET utf8 collate utf8_swedish_ci NOT NULL, `feedDesc` text character SET utf8 collate utf8_swedish_ci NOT NULL, `feedParentID` int(11) NOT NULL, `feedDate` varchar(255) NOT NULL, `feedGUID` varchar(255) NOT NULL, `feedCounter` int(11) NOT NULL, `feedValid` tinyint(1) NOT NULL DEFAULT Ƈ', UNIQUE KEY `feedID` (`feedID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=506 ; And now I'm going to sum up the most clicked items for a specific date (in this case 20071016), I'm storing the 'feed_clicks.clickDate' as Unix timestamp. I've got this far: SQL SELECT count(feed_clicks.clickID) AS 'sum', feed_items.* FROM feed_clicks, feed_items WHERE FROM_UNIXTIME(feed_clicks.clickDate, '%Y%m%d') = 20071016 GROUP BY feed_items.feedID LIMIT 3 But I don't know how to order by the 'sum'
View Replies !
Filtered Items In The Same Table?
I have a little SQL issue and need some guidance for a plan of attack. I have records which sort of look like the following (very loosely based on original) NAME SHOT DESCRIPTION DISTANCE JOE FAIRWAY 150 JOHN FAIRWAY 180 JOHN FAIRWAY 210 RICK BUNKER 190 TODD BUNKER 180 JOE BUNKER 220 JOE FAIRWAY 280 Now I have a statement that groups all the data by name and then gives me total number of shots (regardless of where it landed) and distance between 150-200. My dilemma is adding another column (BUNKER_SHOTS) with - of those filtered shots, how many were in the bunker. Seemed simple at the time. Do I use a subselect or inner join or what?
View Replies !
Showing Similar Items Only
My db is full of books and is community-driven. I have the book title, the author, publisher, etc.. Because it's community-driven, there is a bit of duplicate data. However, it's not an *exact* duplicate, just similar. For instance, the book titles may be the same, but the author is misspelled, or the publishing date is incorrect. I'm looking for a way to display and group similar book titles together so they can be compared by an administrator. It would be easy enough using LIKE, but I want this list to be automatic. No searching involved, because, well, they won't know what to search for. Just click on a "show duplicates' link and presto! Instant list of duplicates.
View Replies !
COUNT() Items In Group
I want to GROUP BY a field named 'type' in my database, and find out the total number of rows for each type. How do I achieve this in a single query? As an example of what I mean in case I didn't make it clear enough. Imagine this is the table... Code: TypeValue dogdata dogdata catdata dogdata catdata frogdata The query should return... dog = 3 cat = 2 frog = 1
View Replies !
Getting Items Related To All Grandparents
id | parent_id | name 1 | 0 | Root 2 | 1 | One 3 | 1 | Two 4 | 2 | One #1 5 | 3 | Two #1 6 | 3 | Two #2 7 | 6 | Two #2 #1 8 | 4 | One #1 #2 want my input to be, for example: 2 want to get as out all items that have a category_id of: 2, 4, 8 Another example, id being 6 Then the returned items should have a category_id of: 6, 7
View Replies !
Different Month
I have a table that has a column containing date and time information with format of 0000-00-00 00:00:00, I want to know how to write query to get rows that are in 2006 Jan, Feb, Mar, ..., return information on a monthly period. I use PHP to allow users to input the 2006-01, 2006-02, in my code, I check if value is 2006-01, then I set two variables to '2006-01-01' and '2006-01-31'; if the value is 2006-02, then set the values to '2006-02-01' and '2006-02-28'. But I hope there is better way instead of writing if condition one by one.
View Replies !
How To Add One Month To Now()?
Within my PHP5 application I need to set an expiry date when inserting/updating into the MySQL database - but honestly I've got no clue how... Specifically - it needs to be one month from the day of the insert/update (thus one month past Now()) Currently I've got the following working fine: UPDATE item SET expdate=Now() WHERE pid=1; Obviously this set the expdate to the current date, I've tried doing Now()+1 (didn't work), was looking into ADDDATE (couldn't get it to work), etc. How, in MySQL, can I add 1 month to Now()?
View Replies !
|
TRACKER
