Re: Coding style: PHP with Complex MySQL statements

Thanks for the reply. No, there is no money at stake. :) Actually, I
am developing a web-based application for my Rotary International Club
(www.washingtonrotary.com). I am indeed striving for a neat-looking
result. One that will be easy to main by me, and others in the future.

This application is for presenting the results of an FM radio-based
auction live on the internet. People can find out what is currently
being auctioned by listening to the radio and/or following along on the
internet.

The public will have no ability to place bids via the internet. Again,
it is a totally radio-based auction. The web is just there to assist
the listeners with what they are hearing on the radio. There is a team
of admins powering the backend, entering in all bids as they come in.
Also managing which items are up for auction when and which auctioneer
will auction each item.

Here is the guts of the database schema involved:

Table: auctions
Fields: auction_id, item_id, donor_id, auctiondate, auctioneer, status
Table: items
Fields: item_id, item_name, item_value
Table: bids
Fields: bid_id, bidder_id, auction_id, bid_amount
Table: bidders
Fields: bidder_id, (bidder contact info fields: name, address,
phone,...)
Table: donors
Fields: donor_id, (donor contact info fields: name, address, phone,...)

The 'auctions' table and 'items' table could potentially be merged, but
the reason that I split them is that many items are donated SEVERAL
times, and it seems it would make more sense to just enter the item
once into 'items', and auction it SEVERAL times via 'auctions'. So,
the 'auctions' table is NOT a single auction with several items;
instead, it is a collection of auctions where each record in the table
is its own auction, and also its own item.

The 'bids' table is the history of ALL bids placed. It links an item
from 'auctions' to a bidder from 'bidders'.

An example of many similar but still different SQL queries is that I
need to return from the database: all items, all sold items, all closed
items, all queued items, all sold items within the past X seconds, all
items on a certain date, all bids on an item, the highest bid on an
item, ... And a large varying mixture of all the above.

Here is an example of an SQL query I have. The only thing that ever
really changes is the WHERE clause, but, since I am doing many
sub-selects, there are WHERE clauses all over the place so it's
difficult to determine via a function where a WHERE clause statement
should go. Here it is:

# Get max bid, item/bidder/donor information on a particular item
SELECT *, auction_auctions.auction_id AS auction_id,
auction_auctions.auctionnumber AS auctionnumber
FROM
(
SELECT *
FROM auction_auctions
LEFT JOIN
(
SELECT
*
FROM
(
SELECT
*,
TIMEDIFF(bid_time, NOW()) AS
timeago
FROM auction_bids
WHERE auction_id='%s'
ORDER BY bid_amount DESC
LIMIT 1
) AS auction_maxbid
JOIN auction_bidders USING (bidder_id)
) AS auction_maxbidder USING (auction_id)
WHERE auction_auctions.auction_id='%s'
) AS auction_auctions
JOIN (auction_items, auction_donors)
ON
(
auction_items.item_id=auction_auctions.item_id AND
auction_donors.donor_id=auction_auctions.donor_id
)
;

.

Relevant Pages

  • Re: Duhhhhh!
    ... First, although a single above-market bid can indeed end an auction, ... miss some bids due to the % rise in the amount bid for the offering. ... current price is above what bidders feel they need to pay. ... The market price is set by the highest bidder. ...
    (uk.rec.waterways)
  • Re: Duhhhhh!
    ... First, although a single above-market bid can indeed end an auction, ... auctions wouldn't produce the desired market data is ... miss some bids due to the % rise in the amount bid for the offering. ... current price is above what bidders feel they need to pay. ...
    (uk.rec.waterways)
  • Re: Question
    ... We've seen it here a lot, even when the auction description is very clear. ... If someone wins it again, doesn't pay, I'll neg them and move on. ... Search 'parts phone' and you will and dozens, any time, most with bids. ... that that phone powers up, since I could not verify it makes and recieves ...
    (alt.marketing.online.ebay)
  • Re: Cancelling an auction with a bid
    ... selling a similar product and want to see how your auction fares. ... Don't expect bids in the last 8 hours, ... price you are comfortable selling the product for. ...
    (alt.marketing.online.ebay)
  • Re: General discussion: the state of the art in slam bidding
    ... Everyone loves a good slam auction, ... reference to cue-bidding style and integrating cue bids with other conventions. ... Do you include controls already shown via cue-bids? ...
    (rec.games.bridge)