My original post was focused primarily on monitoring Walmart prices. I’ve since expanded it to support four different stores including Target, Bestbuy, and GameStop. Originally, I only updated it to include Target and Bestbuy, and the article I wrote completely disappeared when I upgraded WordPress. As I added GameStop, I decided I had to change how the table structure worked if I was going to add more sites for some sadistic reason.
The table structure originally had only a column for each link and a column for each price for each store. As I decided to add GameStop, I realized that this table would become quite bothersome. As you can see below, if I planned to add more stores then it would become dreadful quickly. It’s like when someone uses 30 columns in Excel instead of understanding how to display data cleanly across multiple sheets.
In addition, it wasn’t tracking price changes over time. I had to use my memory to think, “Wait, is the price $39.99 for Luigi’s Mansion the cheapest it’s ever been? I don’t remember it being lower than that.” My software running on my VPS was only updating prices but never keeping a record of them. I thought I’d fix that by creating a price history table:
I’ll make this simple:
- ID = unique identifier for each row – this helps with indexing
- GameID = Gained from the stores/links table (i.e. GameID 4 = Super Mario Smash Brothers)
- SiteID = A designated ID for each Site (i.e. Walmart = 1, GameStop = 5)
- Price = I like monies
- PriceDate = When the price was updated
I wasn’t sure how to best get this data and I’m very positive I did it inefficiently, but it works and I don’t care right now. I created a procedure that would capture each video game and pull the most recent price, and which store that price is tied to.
BEGIN
CREATE TEMPORARY TABLE temp
SELECT wc.ID
,wc.Name
,(SELECT ph.ID
FROM pricehistory ph
WHERE ph.GameID = wc.ID
AND ph.Price > 0
ORDER BY ph.PriceDate DESC, ph.Price ASC
LIMIT 1) 'BestDeal'
,(SELECT ph.SiteID
FROM pricehistory ph
WHERE ph.GameID = wc.ID
AND ph.Price > 0
ORDER BY ph.PriceDate DESC, ph.Price ASC
LIMIT 1) 'SiteID'
,(SELECT ph.PriceDate
FROM pricehistory ph
WHERE ph.GameID = wc.ID
AND ph.Price > 0
ORDER BY ph.PriceDate DESC, ph.Price ASC
LIMIT 1) 'Date'
FROM walmartcheck wc
WHERE Active = 1;
Then I produce a table that says “This game is cheapest at this store”:
SELECT t.ID
,t.Name
,CASE
WHEN t.SiteID = 1 THEN wc.Walmart
WHEN t.SiteID = 2 THEN wc.Walmart
WHEN t.SiteID = 3 THEN wc.Target
WHEN t.SiteID = 4 THEN wc.Bestbuy
WHEN t.SiteID = 5 THEN wc.GameStop
END 'Link'
,ph.Price
,(SELECT ph2.Price
FROM pricehistory ph2
WHERE ph2.GameID = t.ID
AND ph2.Price > 0
AND ph2.SiteID <> 5
ORDER BY ph2.Price ASC
LIMIT 1) 'Best'
,t.Date
FROM temp t
INNER JOIN pricehistory ph ON ph.ID = t.BestDeal
INNER JOIN walmartcheck wc ON wc.ID = t.ID;
ENDFinally, I display it.
One thing to note is that I made the Price green to show “This is equal to or less than the cheapest recorded historical price.” Once a price meets this criterion then they’ll appear green or they’ll appear white if it’s a higher price. I added a “Best” column just to be clear what I should look for because a couple of dollar difference is still probably worth it. For historical prices, I used camelcamelcamel and looked at each game on Amazon to find its best price in history and gave it a date of January 1, 2021 for no particular reason. By the way, I highly recommend using that site when you purchase things on Amazon unless you need them right away. The newer games, as expected, have their best prices already met since they’re either unreleased or recently released.
The good news is that displaying this table was much simpler than what I had been doing.
$sql = "CALL `getprices`();";
$result = $conn->query($sql);
while ($row = mysqli_fetch_row($result)):
echo '<tr style="border:1px solid black;">
<td>'.$row[1].'</td>';
if($row[3] <= $row[4])
{
echo '<td style="background-color:green;"><a href="'.$row[2].'" target="_blank" style="color: white;">'.$row[3].'</a></td>';
} else {
echo '<td><a href="'.$row[2].'" target="_blank">'.$row[3].'</a></td>';
}
echo '<td>'.$row[4].'</td>';
echo '<td><a href="walmart.php?m='.$row[0].'">Hide</td></tr>';I don’t expect you to understand what all of that means, but it sure beats creating dynamic arrays and using array_multisort function to find the best price for each created dynamic array discussed in the previous article. Instead, I just use “CALL `getprices`();” from the procedure and it displays the table. With the pricing history table added, I can use a procedure to handle all the sorting in the database.
Gathering pricing on other websites worked almost the exact same. Target:
if (link.ToLower().Contains("target"))
{
Console.WriteLine("Capturing Target price...");
Int16 next = 0;
foreach (string str in getinputs)
{
if (next == 1)
{
Console.WriteLine("Updating Target price to " + str.Substring(1) + "...");
UpdatePrice(info[0], str.Substring(1), 3);
break;
}
if (str.Contains("PriceFontSize"))
{
next++;
}
}
}For Target, we’re looking at the HTML tag for “PriceFontSize”. Then the code waits until the next line to capture its price.
Then BestBuy works almost the exact same. For BestBuy, I look for “priceView-hero-price” and then look four lines later to find the actual price.
string bestprice = "";
if (link.ToLower().Contains("bestbuy"))
{
Console.WriteLine("Capturing BestBuy price...");
Int16 next = 0;
foreach (string str in getinputs)
{
if (next > 0 && next < 4) next++;
if(next == 4)
{
Console.WriteLine("Updating BestBuy price to " + str.Substring(1) + "...");
bestprice = str.Substring(1);
break;
}
if (str.Contains("priceView-hero-price"))
{
next++;
}
}One reminder is that I split lines by tags “<” or “>”, which is why the price for Target is on a separate line in the code, and BestBuy is four lines. Here’s the code from BestBuy’s website:
GameStop was the most complex and I had to use an IF statement. They’re different because they primarily sell used games. I don’t want to buy used games if they’re close to the same price as a new game. I set a limit of $35, so anything above that will get a ridiculous price of 99.99 just so I have some data.
string gsprice = "";
if (link.ToLower().Contains("gamestop"))
{
Console.WriteLine("Capturing GameStop price...");
Int16 next = 0;
bool primarydetails = false;
foreach (string str in getinputs)
{
if (str.Contains("primary-details-row")) primarydetails = true;
if (next == 1)
{
gsprice = str.Substring(str.IndexOf('$') + 1).Trim();
// don't update price if it's not cheap enough for used games
decimal gspriceint = 0;
try
{
gspriceint = Convert.ToDecimal(gsprice);
}
catch
{
gspriceint = 0;
}
finally
{
if(gspriceint <= 35 && gspriceint != 0)
{
Console.WriteLine("Updating GameStop price to " + gsprice + "...");
UpdatePrice(info[0], gsprice, 5);
}
else
{
UpdatePrice(info[0], "99.99", 5);
Console.WriteLine("Updating GameStop price to bullshit price...");
}
}
break;
}
if (str.Contains("actual-price") && primarydetails == true)
{
next++;
}
}
}When looking for the price I could only find “actual-price” as a class that could be used. The downside is they use this class religiously. Like here:
And here:
To be completely honest, I have no idea where these are on the webpage.
It’s like a honeypot for bots like mine to prevent price scrapping (I guess). What I do know is that the real price sits somewhere in a DIV with the class “primary-details-row”.
So, the way I built the bot was to look for “primary-details-row” first and then set the primarydetails boolean as “True”. Once primarydetails is set to true and it sees the class “actual-price”, then it captures the price.
I feel like this project is pretty much complete. What else should I build?