Here's my sql:
SELECT *
FROM products
LEFT JOIN bids
ON bids.bids_item = products.id
LEFT JOIN orders
ON orders.orders_product = products.id
WHERE products.username = ?
AND Now() > products.enddate
AND enabled = 1
OR bids > 0
GROUP BY products.id
ORDER BY bids.bids_amount DESC
I am trying select products from the products table, orders table, and bids table, pretend you're a user using my website, you want to buy 2 products, item 1, and item 2. You start out on item 1, that is an auction, you bid on that item and the following information goes in to the bids table:
bids_amount,
bids_id,
bids_item,
bids_timestamp
In the product table, I have an id, an enddate, and a bids rows when you bids, it adds +1 to the bids rows and inserts the product id to the bids_item. To gauge when an item has ended I am comparing the data vs enddate. Then I'm using the above sql to check sold items with those specifics. Anyway, now you know how the bids work, now let's pretend you're buying a buy it now item. You see product 2 which you like and decide to add it to your cart, and then decide to checkout (which adds the following info to the orders minus a few)
orders_id,
orders_item,
orders_username,
orders_status
So I technically need to have an sql query to check if bids > 0 or if there's a row in the orders and bids table. My table structures:
Bids
CREATE TABLE `bids` (
`bids_id` int(11) NOT NULL AUTO_INCREMENT,
`bids_item` int(11) NOT NULL,
`bids_username` varchar(100) NOT NULL,
`bids_amount` decimal(11,8) NOT NULL,
`bids_date` datetime NOT NULL,
`bids_timestamp` int(25) NOT NULL,
PRIMARY KEY (`bids_id`),
UNIQUE KEY `bids_id` (`bids_id`)
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=latin1
Orders
CREATE TABLE `orders` (
`orders_id` int(11) NOT NULL AUTO_INCREMENT,
`orders_product` int(11) NOT NULL,
`orders_username` varchar(100) NOT NULL,
`orders_status` int(11) NOT NULL,
`orders_firstname` varchar(100) NOT NULL,
`orders_lastname` varchar(100) NOT NULL,
`orders_address1` varchar(100) NOT NULL,
`orders_address2` varchar(100) NOT NULL,
`orders_zipcode` varchar(100) NOT NULL,
`orders_city` varchar(100) NOT NULL,
`orders_country` varchar(100) NOT NULL,
`orders_shipped` int(11) NOT NULL,
`orders_btcaddress` varchar(100) NOT NULL,
PRIMARY KEY (`orders_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1
Products
CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`description` text NOT NULL,
`shortdescription` varchar(100) NOT NULL,
`price` decimal(11,6) NOT NULL,
`username` varchar(50) NOT NULL,
`buyitnow` int(11) NOT NULL,
`enddate` datetime NOT NULL,
`purchases` int(11) NOT NULL,
`bids` int(11) NOT NULL,
`startingprice` decimal(11,6) NOT NULL,
`date` datetime NOT NULL,
`itemcondition` varchar(100) NOT NULL,
`international` int(11) NOT NULL,
`shipsto` varchar(100) NOT NULL,
`shipping` varchar(100) NOT NULL,
`shippingcost` decimal(11,6) NOT NULL,
`shippingtime` int(11) NOT NULL,
`location` varchar(100) NOT NULL,
`enabled` int(11) NOT NULL DEFAULT '1',
`category` varchar(50) NOT NULL,
`quantity` int(11) NOT NULL,
`offers` int(11) NOT NULL,
`returns` int(11) NOT NULL,
`images` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=latin1
What my sql is showing:
(84, 'balance', '', 'dfghgfds', '0.000000', 'admin', 0, '2015-03-19 17:40:24', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 1, 0, 0, ''),
(85, 'dfghdsdgb
', '', 'dfghgfds', '1.000000', 'admin', 1, '2015-03-19 17:41:06', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 1, 0, 0, ''),
What I need it to show:
Nothing because the bids rows = 0 and there's no rows in the bids or order matching that product.id
Here is my products rows etc:
CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL,
`title` varchar(100) NOT NULL,
`description` text NOT NULL,
`shortdescription` varchar(100) NOT NULL,
`price` decimal(11,6) NOT NULL,
`username` varchar(50) NOT NULL,
`buyitnow` int(11) NOT NULL,
`enddate` datetime NOT NULL,
`purchases` int(11) NOT NULL,
`bids` int(11) NOT NULL,
`startingprice` decimal(11,6) NOT NULL,
`date` datetime NOT NULL,
`itemcondition` varchar(100) NOT NULL,
`international` int(11) NOT NULL,
`shipsto` varchar(100) NOT NULL,
`shipping` varchar(100) NOT NULL,
`shippingcost` decimal(11,6) NOT NULL,
`shippingtime` int(11) NOT NULL,
`location` varchar(100) NOT NULL,
`enabled` int(11) NOT NULL DEFAULT '1',
`category` varchar(50) NOT NULL,
`quantity` int(11) NOT NULL,
`offers` int(11) NOT NULL,
`returns` int(11) NOT NULL,
`images` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`id`, `title`, `description`, `shortdescription`, `price`, `username`, `buyitnow`, `enddate`, `purchases`, `bids`, `startingprice`, `date`, `itemcondition`, `international`, `shipsto`, `shipping`, `shippingcost`, `shippingtime`, `location`, `enabled`, `category`, `quantity`, `offers`, `returns`, `images`) VALUES
(84, 'balance', '', 'dfghgfds', '0.000000', 'admin', 0, '2015-03-19 17:40:24', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 1, 0, 0, ''),
(85, 'dfghdsdgb
', '', 'dfghgfds', '1.000000', 'admin', 1, '2015-03-19 17:41:06', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 1, 0, 0, ''),
(86, 'desk table
', '', 'dfghgfds', '1.000000', 'admin', 1, '2015-03-25 13:21:40', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 0, 0, 0, ''),
(87, 'ps3
', '', 'dfghgfds', '1.000000', 'admin', 1, '2015-03-25 13:21:40', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 0, 0, 0, ''),
(88, 'xbox one', '<div class="fullDetails"> <p>Xbox One brings together the best exclusive games like Titanfall and Halo, the most advanced multi-player and entertainment experiences you won''t find anywhere else.</p><ul><li>Parental controls allow you to set-up profiles for children to prevent unauthorised purchases and ensure they only access age appropriate content.</li><li>Game DVR keeps a rolling video record of gameplay and with Upload Studio you can edit clips, add voiceover and share them with your friends.</li><li>500GB hard drive give you plenty of memory for your games, demos, movies, apps, music and more.</li><li>Snap a game, live TV and apps like Netflix or YouTube side-by-side and switch quickly between them.</li><li>Experience the action like never before with the Xbox One Wireless Controller. New Impulse Triggers, redesigned thumbsticks and D-pad provide greater intensity, precision and comfort.</li><li>Keep your in-game communications crisp with the comfortable new Xbox One Chat Headset. Hear friends and foes in crystal-clear digital audio.</li><li>Includes Xbox One console, wireless controller, chat headset, HDMI cable, power supply, 14-Day Xbox Live Gold membership.</li><li>Built in blu-ray disc drive allows playback of blu-ray discs and 3D blu-ray discs.</li><li>500GB hard drive capacity.</li><li>Size H8, W27.4, D33.2cm.</li><li>EAN: 885370808315.</li></ul> </div> <!-- : 15113product.longdescription <tr><td>Name:</td> <td> product.longdescription </td></tr> <tr><td>DataType:</td> <td> MarketingContent </td></tr> <tr><td>ActivityIdentifier UniqueID:</td> <td> 11503 </td></tr> <tr><td>ActivityIdentifier Name:</td> <td> pdp_proddesc2colleft </td></tr> <tr><td>CampaignName:</td> <td> </td></tr> <tr><td>ActivityFormat:</td> <td> web </td></tr> <tr><td>ActivityPriority:</td> <td> 0 </td></tr> <tr><td>Url Link:</td> <td> </td></tr> <tr><td>Marketing Text:</td> <td> prod.long.description.content </td></tr> <tr><td>Location:</td> <td> </td></tr> <tr><td>Format UniqueID:</td> <td> 10009 </td></tr> <tr><td>Format Name:</td> <td> Widget </td></tr> <tr><td>Content Mime Type:</td> <td> </td></tr> <tr><td>Name:</td> <td> </td></tr> <tr><td>Short Description:</td> <td> </td></tr> <tr><td>Image:</td> <td> </td></tr> --> <div class="additionalinfo" id="additionalinfo"> <h4>Additional Information</h4> <p><img src="/wcsstore/argos/en_GB/images/p8/icon_dagger.gif" alt="This item is excluded from the 30 day money back guarantee. See the Additional Information panel for full details." nmcxl33="1"> Please note that this product is excluded from our 30 Day Money Back Guarantee. This does not affect your statutory rights.</p> <p>Prices correct as displayed but are subject to change</p> </div>', 'dfghgfds', '1.000000', 'admin', 1, '2015-03-25 13:21:40', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 0, 0, 0, ''),
(89, 'xbox 360', '', 'dfghgfds', '1.000000', 'admin', 1, '2015-03-25 13:21:40', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 0, 0, 0, ''),
(90, 'camera', '', 'dfghgfds', '1.000000', 'admin', 1, '2015-03-25 13:21:40', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 0, 0, 0, ''),
(91, 'sd card', 'main descript blah blah blah blah blah blahblah blah blahblah blah blahblah blah blahblah blah blahblah blah blahblah blah blahblah blah blahblah blah blah', 'blah blah blah', '1.000000', 'demo', 0, '2015-03-26 17:21:00', 0, 0, '1.040000', '2015-03-18 13:21:40', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 1, 0, 0, ''),
(92, '22 Inch lg Tv lg22ma33d-pz', '<p>dfd</p>', 'dfghgfds', '0.000000', 'admin', 0, '2015-03-26 23:53:02', 0, 0, '99999.999999', '2015-03-19 23:53:02', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 1, 0, 0, ''),
(93, '22 Inch lg Tv lg22ma33d-pz', '', 'dfghgfds', '0.000000', 'admin', 0, '2015-03-27 14:42:47', 0, 0, '1.000000', '2015-03-20 14:42:47', 'new', 0, '', 'MyHermes', '1.000000', 0, 'United Kingdom', 1, '', 1, 0, 0, '');
