Title: Useful Ultimate Auction SQL Queries???
Last modified: August 21, 2016

---

# Useful Ultimate Auction SQL Queries???

 *  Resolved [jpf](https://wordpress.org/support/users/jpf/)
 * (@jpf)
 * [12 years, 2 months ago](https://wordpress.org/support/topic/useful-ultimate-auction-sql-queries/)
 * If you have SQL access to your WordPress DB, or you know how to code pages that
   query and report, the following may be useful to you .. please let me know if
   there are any errors or if anyone thinks of other useful queries…
 *     ```
       #list INCREMENTAL values for ALL AUCTIONS
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-TABLE-PREFIX_posts p, YOUR-TABLE-PREFIX_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_incremental_val"
   
       #list OPENING bid for ALL AUCTIONS
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-TABLE-PREFIX_posts p, YOUR-TABLE-PREFIX_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_opening_bid"
   
       #list HIGHEST BID for ALL AUCTIONS
       select p.ID,p.post_title,b.name,max(b.bid) from YOUR-TABLE-PREFIX_posts p, YOUR-TABLE-PREFIX_wdm_bidders b where p.ID = b.auction_id group by p.ID order by p.ID asc, b.bid desc
   
       #MOST ACTIVE auctions by bid_count ALL AUCTIONS
       select p.ID,p.post_title,count(b.bid) as bid_count from YOUR-TABLE-PREFIX_posts p, YOUR-TABLE-PREFIX_wdm_bidders b where p.ID = b.auction_id group by p.ID order by bid_count desc
   
       #list 10 RECENT BIDS
       select p.ID,p.post_title,b.name,b.bid, max(b.date) as bid_date from YOUR-TABLE-PREFIX_posts p, YOUR-TABLE-PREFIX_wdm_bidders b where p.ID = b.auction_id group by p.ID order by bid_date desc LIMIT 10
   
       #list END DATES DESCENDING
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-TABLE-PREFIX_posts p, YOUR-TABLE-PREFIX_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_listing_ends" order by m.meta_value desc limit 200
   
       <strong>#BULK update listing END DATE for all LIVE auctions -- WARNING, THIS WILL EDIT DATA</strong>
       update YOUR-TABLE-PREFIX_postmeta set meta_value = "2014-03-14 23:59:59" where meta_key = "wdm_listing_ends" and meta_value > NOW()
   
       #CURRENTLY LIVE AUCTIONS ENDING within 24 hours
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-TABLE-PREFIX_posts p, YOUR-TABLE-PREFIX_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_listing_ends" and m.meta_value < DATE_ADD(NOW(), INTERVAL 24 HOUR) and m.meta_value > NOW() order by m.meta_value desc limit 200
   
       #CURRENTLY LIVE Auctions CREATED in last 24hrs
       SELECT p.ID,p.post_title,p.post_date from YOUR-TABLE-PREFIX_posts p,YOUR-TABLE-PREFIX_postmeta m where p.ID = m.post_id and p.post_date > DATE_SUB(NOW(), INTERVAL 24 HOUR) and m.meta_key = "wdm_listing_ends" and m.meta_value > NOW() order by p.post_date desc limit 200
   
       #currently LIVE Auctions MODIFIED in last 24hrs
       SELECT p.ID,p.post_title,p.post_modified from YOUR-TABLE-PREFIX_posts p,YOUR-TABLE-PREFIX_postmeta m where p.ID = m.post_id and p.post_modified > DATE_SUB(NOW(), INTERVAL 24 HOUR) and m.meta_key = "wdm_listing_ends" and m.meta_value > NOW() order by p.post_modified desc limit 200
   
       #AUCTIONS WITH NO BIDS
       select * from (select p.ID,p.post_title,count(b.bid) as bid_count,m.post_id from YOUR-TABLE-PREFIX_posts p INNER JOIN YOUR-TABLE-PREFIX_postmeta m on p.ID = m.post_id LEFT OUTER JOIN YOUR-TABLE-PREFIX_wdm_bidders b on p.ID = b.auction_id where m.meta_key = "wdm_listing_ends" and m.meta_value > NOW() group by p.ID,m.post_id order by bid_count desc) as no_bids where bid_count = 0 LIMIT 200
       ```
   
 * [https://wordpress.org/plugins/ultimate-auction/](https://wordpress.org/plugins/ultimate-auction/)

Viewing 4 replies - 1 through 4 (of 4 total)

 *  Thread Starter [jpf](https://wordpress.org/support/users/jpf/)
 * (@jpf)
 * [12 years, 2 months ago](https://wordpress.org/support/topic/useful-ultimate-auction-sql-queries/#post-4706704)
 * BTW, I forgot to mention, I would love for someone to work with me to create 
   shortcodes for most of these? I’m not very WP shortcode saavy..but I’m sure someone
   here else may be.
 *  Thread Starter [jpf](https://wordpress.org/support/users/jpf/)
 * (@jpf)
 * [12 years, 2 months ago](https://wordpress.org/support/topic/useful-ultimate-auction-sql-queries/#post-4706707)
 * Oh nevermind on the Shortcode coding .. this works Fantastic!!!
    [https://wordpress.org/plugins/elisqlreports/](https://wordpress.org/plugins/elisqlreports/)
 *  Thread Starter [jpf](https://wordpress.org/support/users/jpf/)
 * (@jpf)
 * [12 years, 2 months ago](https://wordpress.org/support/topic/useful-ultimate-auction-sql-queries/#post-4706863)
 * I noticed a few errors in the ABOVE SQL (specifically, HIGHEST BIDDER LIST and
   RECENT BID LIST) .. I’ve also improved on them a bit for use with the ABOVE recommended
   plugin. See updated list of SQL below…..
 *     ```
       #list incremental values
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-WP-DB_posts p, YOUR-WP-DB_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_incremental_val"
   
       #list opening bid
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-WP-DB_posts p, YOUR-WP-DB_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_opening_bid"
   
       #list RESERVES
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-WP-DB_posts p, YOUR-WP-DB_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_lowest_bid"
   
       #list HIGHEST BIDS + USERS
       select CONCAT("<A HREF=http://WPSITE.COM.ORG/PATH-TO-AUCTION/PAGE-FOR-AUCTION?ult_auc_id=",ibt.ID,">",ibt.Item,"</A>") as Item,b.name as Name, ibt.bid as Bid from (select p.ID as ID, p.post_title as Item, max(b.bid) as Bid from YOUR-WP-DB_posts p, YOUR-WP-DB_wdm_bidders b where p.ID = b.auction_id group by p.ID )as ibt, YOUR-WP-DB_wdm_bidders b where b.auction_id = ibt.ID and b.bid = ibt.bid order by b.bid desc
   
       #3 MOST ACTIVE auctions by bid_count
       select  CONCAT("<A HREF=http://WPSITE.COM.ORG/PATH-TO-AUCTION/PAGE-FOR-AUCTION?ult_auc_id=",ID,">",Item,"</A>") as Item, NumBids from (select p.ID as ID,p.post_title as Item,count(b.bid) as NumBids from YOUR-WP-DB_posts p, YOUR-WP-DB_wdm_bidders b where p.ID = b.auction_id group by p.ID order by NumBids desc) as bid_count LIMIT 3
   
       #list 10 RECENT BIDS
       select CONCAT("<A HREF=http://WPSITE.COM.ORG/PATH-TO-AUCTION/PAGE-FOR-AUCTION?ult_auc_id=",p.ID,">",p.post_title,"</A>") as Item ,b.name as Bidder,b.date as Date,concat("$",b.bid) as Bid FROM <code>YOUR-WP-DB_wdm_bidders</code> as b, YOUR-WP-DB_posts p where b.auction_id = p.ID order by b.date desc limit 5
   
       #list END DATES DESCENDING
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-WP-DB_posts p, YOUR-WP-DB_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_listing_ends" order by m.meta_value desc limit 200
   
       #BULK update listing END DATE for all LIVE auctions
       update YOUR-WP-DB_postmeta set meta_value = "2014-03-14 23:59:59" where meta_key = "wdm_listing_ends" and meta_value > NOW()
   
       #ENDING within 24 hours
       SELECT p.ID,p.post_title,m.meta_key,m.meta_value from YOUR-WP-DB_posts p, YOUR-WP-DB_postmeta m where p.ID = m.post_id and m.meta_key = "wdm_listing_ends" and m.meta_value < DATE_ADD(NOW(), INTERVAL 24 HOUR) and m.meta_value > NOW() order by m.meta_value desc limit 200
   
       #currently LIVE Auctions CREATED in last 24hrs
       SELECT p.ID,p.post_title,p.post_date from YOUR-WP-DB_posts p,YOUR-WP-DB_postmeta m where p.ID = m.post_id and p.post_date > DATE_SUB(NOW(), INTERVAL 24 HOUR) and m.meta_key = "wdm_listing_ends" and m.meta_value > NOW() order by p.post_date desc limit 200
   
       #currently LIVE Auctions MODIFIED in last 24hrs
       SELECT p.ID,p.post_title,p.post_modified from YOUR-WP-DB_posts p,YOUR-WP-DB_postmeta m where p.ID = m.post_id and p.post_modified > DATE_SUB(NOW(), INTERVAL 240 HOUR) and m.meta_key = "wdm_listing_ends" and m.meta_value > NOW() order by p.post_modified desc limit 200
   
       #AUCTIONS WITH NO BIDS
       select Item, CONCAT("$",OpeningBid) as OpeningBid from (select CONCAT("<A HREF=http://WPSITE.COM.ORG/PATH-TO-AUCTION/PAGE-FOR-AUCTION?ult_auc_id=",ID,">",Item,"</A>") as Item,m.meta_value*1 as OpeningBid from (select p.ID as ID ,p.post_title as Item, count(b.bid) as NumBids from YOUR-WP-DB_posts p INNER JOIN YOUR-WP-DB_postmeta m on p.ID = m.post_id LEFT OUTER JOIN YOUR-WP-DB_wdm_bidders b on p.ID = b.auction_id where m.meta_key = "wdm_listing_ends" and m.meta_value > NOW() group by p.ID,m.post_id) as no_bids ,YOUR-WP-DB_postmeta m where m.post_id = no_bids.ID and no_bids.NumBids = 0 and m.meta_key = "wdm_opening_bid" order by OpeningBid desc) as no_bid_price LIMIT 5
   
       #Current Winning Bids Total Value
       select  CONCAT("$",round(sum(Bid))) as CurrentBidValue, "$6500" as Target from (select ID, Item, User, Bid from (select p.ID as ID, p.post_title as Item,b.name as User ,max(b.bid) as Bid from YOUR-WP-DB_posts p, YOUR-WP-DB_wdm_bidders b where p.ID = b.auction_id group by p.ID order by p.ID asc, b.bid desc) as max_bid order by Bid desc) as bid_list
       ```
   
 *  Plugin Author [Nitesh](https://wordpress.org/support/users/nitesh_singh/)
 * (@nitesh_singh)
 * [12 years, 1 month ago](https://wordpress.org/support/topic/useful-ultimate-auction-sql-queries/#post-4706981)
 * Nice research, would help community.

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘Useful Ultimate Auction SQL Queries???’ is closed to new replies.

 * ![](https://ps.w.org/ultimate-auction/assets/icon-128x128.png?rev=1494738)
 * [Ultimate Wordpress Auction Plugin](https://wordpress.org/plugins/ultimate-auction/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/ultimate-auction/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/ultimate-auction/)
 * [Active Topics](https://wordpress.org/support/plugin/ultimate-auction/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/ultimate-auction/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/ultimate-auction/reviews/)

 * 4 replies
 * 2 participants
 * Last reply from: [Nitesh](https://wordpress.org/support/users/nitesh_singh/)
 * Last activity: [12 years, 1 month ago](https://wordpress.org/support/topic/useful-ultimate-auction-sql-queries/#post-4706981)
 * Status: resolved