Solved

Queuing system help

Posted on 2011-02-15
3
392 Views
Last Modified: 2012-05-11
I'm trying to build a kind of queuing table. We are offering items for sale on a daily basis, kind of like woot.com. It is called Daily QuickDeals. Now, I need a script to go through all the records in linv_quick_deals to determine:

1

Is the product not sold out

2

has it not been listed as a QuickDeal in the last 2 days

And if it meets the above criteria, return the inventory_id so I can insert a record into linv_todays_deal with today's date and the inventory_id.

The below isn't working as I expect because it keeps putting inventory_id 1 in when it is sold out.

I'm thinking that I may have a bad design. Should I just either, move the sold_out column to the linv_quick_deals table or remove the record from the linv_quick_deals table when it is sold out?

Any ideas or suggestions will be appreciated.

SELECT `inventory_id` 
FROM   linv_quick_deals i 
WHERE  `inventory_id` NOT IN (SELECT d.`inventory_id` 
                              FROM `linv_todays_deal` d
                              WHERE d.`inventory_id` = i.`inventory_id` 
                              AND   d.`sold_out` <> 0
                              AND   DATE_SUB(CURDATE(), INTERVAL 2 DAY) <= d.`deal_date`)

Open in new window

Data:
CREATE TABLE `linv_todays_deal` (
	`deal_id` INT(10) NOT NULL AUTO_INCREMENT,
	`inventory_id` INT(10) NOT NULL DEFAULT '0',
	`deal_date` DATE NULL DEFAULT NULL,
	`sold_out` TINYINT(4) NULL DEFAULT '1',
	PRIMARY KEY (`deal_id`),
	INDEX `idx_inventory_id` (`inventory_id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT;

deal_id 	inventory_id 	deal_date 	sold_out
1 		2 		2011-02-14 	1
2 		1 		2011-02-10 	0


CREATE TABLE `linv_quick_deals` (
	`inventory_id` INT(10) NOT NULL AUTO_INCREMENT,
	`user_id` INT(11) NOT NULL,
	`title` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
	`description` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
	`attributes` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
	`order_link` VARCHAR(500) NOT NULL COLLATE 'utf8_unicode_ci',
	`image_link` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`inventory_id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT;

inventory_id 	user_id title 	description 	attributes 	order_link 	image_link
1 		23 	Item 1 	<SNIP>  	<SNIP> 		<SNIP>		<SNIP>
2 		23 	Item 2 	<SNIP>		<SNIP>		<SNIP>		<SNIP>

Open in new window

0
Comment
Question by:EddieShipman
3 Comments
 
LVL 26

Accepted Solution

by:
jar3817 earned 400 total points
ID: 34897016
Your linv_quick_deals table doesn't have an "out of stock" column, so can't select products from that table based on whether they're in stock or not.
0
 
LVL 6

Assisted Solution

by:Ferrosti
Ferrosti earned 100 total points
ID: 34897231
Usually one would go for an item_master table and an item_stock table.
In lineitem one would have itemdetails, while stockitem holds information about reservation, quantity, qty_threshold etc.
Problem is that in case of phone calls an agent might tell the customer of a valid stock and two minutes later he could not book it to the customers order. For this case one would reserve an item as soon as a customer agent got its record open.
0
 
LVL 26

Author Closing Comment

by:EddieShipman
ID: 34901519
Decided to put the sold_out column in the linv_quick_deals table
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MSSQL 2008 with mySQL webservers 7 60
Duplicating MySQL Table columns 5 45
selecting date modified field from a table 2 52
mysql query for sum() 3 28
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question