Re: Coding style: PHP with Complex MySQL statements
- From: Schraalhans Keukenmeester <nomail@xxxxxxxxxx>
- Date: Wed, 28 Sep 2005 09:41:05 +0200
s1037989@xxxxxxxxx wrote:
I'll have a closer look later today. Sofar I can imagine loading all in PHP tables may result in HUGE tables consuming lots of memory.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 )
;
I'll be back!
SH
.
- References:
- Coding style: PHP with Complex MySQL statements
- From: s1037989
- Re: Coding style: PHP with Complex MySQL statements
- From: Schraalhans Keukenmeester
- Re: Coding style: PHP with Complex MySQL statements
- From: s1037989
- Coding style: PHP with Complex MySQL statements
- Prev by Date: Re: Coding style: PHP with Complex MySQL statements
- Next by Date: Please hack my highscore table code
- Previous by thread: Re: Coding style: PHP with Complex MySQL statements
- Next by thread: How to login to htaccess box
- Index(es):
Relevant Pages
|
