3 Steps Simple Auction Bidding With PHP MYSQL

Welcome to a tutorial on how to create a simple bidding or auction system in PHP and MYSQL. Want to auction off some products on your website like eBay? Or want to open jobs for bidding?

The raw mechanics for an auction or bidding system consists of:

  1. A database table to store the auctioned products (or services/jobs/items), another table to store the bids.
  2. A library to manage the processing.
  3. Lastly, a page to display the auctioned item, and to place bids.

That covers the basic structure, read on for the example!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes Auction Bidding Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

ASSUMPTIONS

For this tutorial, I am going to assume:

  • There is an existing user and login system, you just want to build an auction mechanism on top… If you are starting from scratch, things will get rough. I will leave more links below.
  • You are already comfortable with full-stack development. If not, follow up with your own basic studies.

 

QUICK NOTES

  • Create a dummy database and import 1-bid.sql.
  • Access 3-bid.php in the browser.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

AUCTION & BIDDING WITH PHP MYSQL

All right, let us now get into the mechanics of an auction and bidding system in PHP MYSQL.

 

STEP 1) DATABASE TABLES

AUCTION ITEMS

1-bid.sql
-- (A) AUCTION ITEMS
CREATE TABLE `auction_items` (
  `item_id` bigint(20) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_desc` text DEFAULT NULL,
  `bid_end` datetime DEFAULT NULL,
  `bid_min` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `auction_items`
  ADD PRIMARY KEY (`item_id`);

ALTER TABLE `auction_items`
  MODIFY `item_id` bigint(20) NOT NULL AUTO_INCREMENT;
Field Description
item_id Primary key, auto-increment.
item_name Name of the item.
item_desc Item description.
bid_end Date and time, when the bid ends. Leave this blank if you want to manually close. I.E. Set this to “now” to immediately close the auctioning.
bid_min Minimum bid increment… Or you will get irritating $0.01 increments.

First, we are going to need a table to hold the items that are being auctioned off. Of course, feel free to add more fields as required – The starting amount, a “buy now” price, etc…

 

 

AUCTION BIDS

1-bid.sql
-- (B) AUCTION BIDS
CREATE TABLE `auction_bids` (
  `item_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `bid_amount` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `auction_bids`
  ADD PRIMARY KEY (`item_id`,`user_id`),
  ADD KEY `amount` (`bid_amount`);
Field Description
item_id Partial primary key.
user_id Partial primary key, and foreign key to the users’ table.
bid_amount The amount that the user bid.

Now, this table should be self-explanatory. But take note that it does not hold the bidding history, only the latest bid by the user. If you want to store the history, you will need to create your own auction_bids_history table to store every bid – That is pretty much a clone of this table, with an extra date field.

 

TEST ITEM

1-bid.sql
-- (C) DUMMY TEST ITEM
INSERT INTO `auction_items`
  (`item_id`, `item_name`, `item_desc`, `bid_end`, `bid_min`)
  VALUES
  (NULL, 'Foo Bar', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nulla id mauris quam.', NULL, '1.5');

Finally, a dummy test item… Remove this if you don’t need it.

 

 

STEP 2) PHP BIDDING LIBRARY

2-bid-lib.php
<?php
class Bid {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    try {
      $this->pdo = new PDO(
        "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
        DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
      );
    } catch (Exception $ex) { die($ex->getMessage()); }
  }
 
  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }
 
  // (C) GET ITEM
  // $id : item id
  function getItem ($id) {
    // (C1) GET ITEM
    $this->stmt = $this->pdo->prepare("SELECT * FROM `auction_items` WHERE `item_id`=?");
    $this->stmt->execute([$id]);
    $item = $this->stmt->fetch();
    if (!is_array($item)) { return null; }
 
    // (C2) LAST HIGHEST BID
    $this->stmt = $this->pdo->prepare("SELECT MAX(`bid_amount`) FROM `auction_bids` WHERE `item_id`=?");
    $this->stmt->execute([$id]);
    $item["highest"] = $this->stmt->fetchColumn();
    if (!is_numeric($item["highest"])) { $item["highest"] = 0; }
 
    // (C3) MINIMUM AMOUNT TO BID
    $item["min"] = $item["highest"] + $item["bid_min"];
 
    // (C4) RETURN RESULT
    return $item;
  }
 
  // (D) UPDATE BID AMOUNT
  // $iid : item id
  // $uid : user id
  // $amount : bid amount
  function setBid ($iid, $uid, $amount) {
    // (D1) CHECK IF ITEM EXIST
    $item = $this->getItem($iid);
    if ($item===null) {
      $this->error = "Invalid Item";
      return false;
    }
 
    // (D2) CHECK IF BIDDING ENDED
    if (isset($item["bid_end"]) && strtotime("now") >= strtotime($item["bid_end"])) {
      $this->error = "Bidding has ended";
      return false;
    }
 
    // (D3) CHECK MINIMUM BID AMOUNT
    if ($amount < $item["min"]) {
      $this->error = "Please bid at least " . $item["min"];
      return false;
    }
 
    // (D4) UPDATE HIGHEST BID
    try {
      $this->stmt = $this->pdo->prepare("REPLACE INTO `auction_bids` (`item_id`, `user_id`, `bid_amount`) VALUES (?,?,?)");
      $this->stmt->execute([$iid, $uid, $amount]);
      return true;
    } catch (Exception $ex) {
      $this->error = $ex->getMessage();
      return false;
    }
  }
}
 
// (E) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (F) NEW TO-DO OBJECT
$_BID = new Bid();

Yikes. This looks confusing at first, but keep calm and look carefully:

  • (A & B) The constructor will automatically connect to the database when $_BID = new Bid() is created, the destructor closes the database connection.
  • (C & D) There are only 2 library functions!
    • getItem() Get the specified item, the details, highest bid, and minimum bid price.
    • setBid() Save the bid price for the specified item and user.
  • (E & F) Self-explanatory.

 

 

STEP 3) BID PAGE

3-bid.php
<?php
// (A) LOAD LIBRARY
require "2-bid-lib.php";
$iid = 1; // fixed item id for this demo
$uid = 999; // fixed user id for this demo
 
// (B) UPDATE BID AMOUNT
if (isset($_POST["bid"])) {
  $ok = $_BID->setBid($iid, $uid, $_POST["bid"]);
  echo "<div>". ($ok ? "Bid saved" : $_BID->error) ."</div>";
}
 
// (C) GET ITEM & DISPLAY
$item = $_BID->getItem($iid); ?>
<!-- (C1) ITEM DETAILS -->
<h1><?=$item["item_name"]?></h1>
<p><?=$item["highest"]?></p>
<p><?=$item["item_desc"]?></p>
 
<!-- (C2) BID -->
<form method="post">
  <input type="number" name="bid" min="<?=$item["min"]?>" step="<?=$item["bid_min"]?>" value="<?=$item["min"]?>"/>
  <input type="submit" value="BID!"/>
</form>

Finally, a page to show the item and open it for bidding.

  1. Load the library. Take note that the item ID and user ID are fixed for this demo. You will want to use $_GET["id"] or $_POST["id"] for the item in your own project, also have a proper $_SESSION["user"].
  2. When the bidding form is submitted, we update the bid amount set by the user.
  3. Get and show the item, the bidding HTML form.

 

 

USEFUL BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

FUTURE DEVELOPMENT

Yep, that’s all to the raw mechanics, but as you can see, there are plenty of blanks to fill in:

  • User registration and login system, if you do not already have one.
  • Open the bidding page to registered users only.
  • Admin panel and functions – Add item, update item, delete item, etc…
  • So what happens when the bid ends? Get the user to do an online payment before releasing the item? Send an email to the user that they got the job/contract? Manually schedule a meeting between the seller and bidder?

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *