Linux: phpAH v3

[abofox]

Hi, Thank you very much for the code but it seems the new php7 doesn't support mysql_connect anymore and mysqli doesnt seems to work too. Could you alter the code a little and update it to usd PDO instead? Thank you.

So a while back, msulli1355 had wanted an auction house tool for Linux, and I could sort of picture a way to do it in my head, but didn't feel motivated to create it. I also knew I was about the worst PHP coder in history, so the result wouldn't be pretty.

I got bored, though.

Versions:
v1 - Total crap
v2 - Better, but still lacking
v3 - Full restock, 10 of each item, 3 seconds. Ready for practical usage.

Change the variables at the beginning of the script to match your SQL server settings.
$price is how much to sell each item for.
$forsale is how many of every item you want to be for sale.
$debug = true; will just give you status messages so you have a rough idea of what the tool is doing, without being too noisy. 6 rows are output, +1 for every 20,000 sales if sales > 20,000.
$debugv = true; will tell you any time the script adds something to the database. This is a LOT more verbose, including a debug message for each line added to the queries.

php <filename> and away you go. Add to cron for regularly scheduled auction house restocks. It's safe now.

The tool lists everything that can be sold on the AH, so Judge's equipment will be available. There are probably ways to address that, but I'm unaware of phpAH running on a public server yet. The tool itself will make sure there's a sales history listing to give price hints, and will add up to $forsale sales at $price gil, defaults being 10 and 100 gil each. The gil for any purchased items is discarded by the auction_house triggers.

I learned a bit about arrays from this project, so even if no one uses it, I had fun and learned from it.

I also feel it's absolutely necessary to thank Stubbs for writing the queries that make this tool work. All I really did from there is connect the queries to the database, then use their outputs to stock things up. Otherwise, you'd be getting 10-minute long restocks that grind your server to a halt.

v3:

Code: Select all

#!/usr/bin/php
<?php
// Kudos to Stubbs for the query.  Miles beyond what I could have produced.
// History query is based on his work

define('DS_HOST', "localhost");
define('DS_USER', "root");
define('DS_PASS', "password");
define('DS_SCHEMA', "dspdb");

// Set the price for everything.
$price = "100";

// How many you want for sale at a minimum.  Directly increases script's running time.
$forsale = "10";

// Displays status messages and logic.
$debug = false;

// Tells you whenever the script does something, like add an item.
$debugv = false;

if (!defined('PHP_EXECUTABLE'))
	define('PHP_EXECUTABLE', '/usr/bin/php');
	
function db_connect($dbhost, $dbuser, $dbpass, $dbschema) {
	$link = mysql_connect($dbhost, $dbuser, $dbpass);
	if ($link) {
		$result = mysql_select_db($dbschema, $link);
		if (!$result) {
			die("Can't select schema: " . mysql_error($link));
		}
		return $link;
	} else {
		die("Unable to connect to database (as $dbuser to $dbhost, database $dbschema): " . mysql_error());
	}
}

// Submits $query to $link, dumps an error if no connection
function db_query($link, $query, $die_on_error = true) {
	$result = mysql_query($query, $link);
	if (!$result) {
		$query = htmlspecialchars($query);
		if ($die_on_error) {
			die("Query <i>$query</i> failed: " . ($link ? mysql_error($link) : "No Connection"));
		}
	}
	return $result;
}

function db_close($link) {
	return mysql_close($link);
}

// Send messages to the prompt
function _debug($msg) {
	$ts = strftime("%H:%M:%S", time());
	if (function_exists('posix_getpid')) {
		$ts = "$ts/" . posix_getpid();
	}
	print "[$ts] $msg\n";
}

// Open transaction
$link = db_connect(DS_HOST, DS_USER, DS_PASS, DS_SCHEMA);

// Fetch any items missing auction house history entries.
$sql = mysql_query("SELECT itemid, 1 as stackSize FROM item_basic WHERE aH > 0 AND itemid NOT IN
	(SELECT itemid FROM auction_house WHERE stack = 0 AND buyer_name <> '')
	UNION SELECT itemid, stackSize FROM item_basic WHERE  aH > 0 AND stackSize > 1 AND itemid NOT IN
	(SELECT itemid FROM auction_house WHERE stack = 1 AND buyer_name <>'')");
$history = array();
while($line = mysql_fetch_assoc($sql)) {
	$history[] = $line;
}
if ($debug) _debug("Missing history entries: " . count($history));

// Fetches items that need to be restocked; separate rows for individuals and stacks.
$sql = mysql_query("SELECT itemid, stackSize, COUNT(sale) AS saleCount
FROM (SELECT Item.itemid, Item.stackSize, AH.itemid AS sale
	FROM (SELECT itemid, 1 as stackSize
		FROM item_basic
		WHERE aH > 0
		UNION
		SELECT itemid, stackSize
		FROM item_basic
		WHERE  aH > 0 AND stackSize > 1) as Item
	LEFT JOIN auction_house AH
	ON Item.itemid=AH.itemid AND ((stackSize=1 AND AH.stack=0) OR (stackSize>1 AND AH.stack<>0)) AND AH.buyer_name IS NULL) AS T2
GROUP BY itemid, stackSize
HAVING saleCount < $forsale");
$sales = array();
while($line = mysql_fetch_assoc($sql)) {
	$sales[] = $line;
}
if ($debug) _debug("Sales to restock: " . count($sales));

if ($debug) _debug("Arrays are complete.  Generating queries.");
if ($debugv) _debug("Stack = 1 indicates a stack, 0 indicates individual.");

$rowsadded=0;
db_query($link, "BEGIN");

// Add missing history entries for price data.
$send = array();
if(count($history) > 0) {
	foreach($history as $row) {
		if($row['stackSize'] > 1){
			$stack = 1;
		} else {
			$stack = 0;
		}
		//itemid, stack, seller_name, date, price, buyer_name, sale, sell_date
		$send[] = "(".$row['itemid'].", ".$stack.", 'phpAH', 1325482385, ".$price.", 'phpAH', ".$price.", 1325482385)";
		if ($debugv) _debug("Adding history entry for item: ".$row['itemid']." where stack = ".$stack);
		$rowsadded++;
	}
	$sql = db_query($link,"INSERT INTO auction_house (itemid, stack, seller_name, date, price, buyer_name, sale, sell_date) VALUES ".implode(", ",$send));
}
if ($debug) _debug("History completed.  Adding sales.");

// Empty the variable
$send = array();

// Restock the auction house
if(count($sales) > 0){
	foreach($sales as $row){
		if($row['stackSize'] > 1) {
			$stack = 1;
		} else {
			$stack = 0;
		}
		$i = $row['saleCount'];
		while($i < $forsale) {
			//itemid, stack, seller_name, date, price
			$send[] = "(".$row['itemid'].", ".$stack.", 'phpAH', 1325482385, ".$price.")";
			if ($debugv) _debug("Adding sale for item: ".$row['itemid']." where stack = ".$stack);
			$rowsadded++;
			$i++;
		}
		if (count($send) > 20000) {
			// Max possible size for $send in default conditions is around 26315 rows.
			// Therefore, we send the INSERT immediately before that limit is reached.
			// Theoretically it still fails if you're listing more than 1,000 of each item, but I think you'll break DS or FFXI first.
			if ($debug) _debug("Approaching default max packet size for MySQL.  Sending INSERT and clearing variable.");
			$sql = db_query($link,"INSERT INTO auction_house (itemid, stack, seller_name, date, price) VALUES ".implode(", ",$send));
			$send = array();
		}
	}
	$sql = db_query($link,"INSERT INTO auction_house (itemid, stack, seller_name, date, price) VALUES ".implode(", ",$send));
}
if ($debug) _debug("Sales completed.");

if ($debug or $debugv) _debug("Final rows added: " . $rowsadded);

db_query($link, "COMMIT");
db_close($link);
?>

Possible Improvements?
- Modify queries and create an array to limit the tool to certain categories?
- Similar, but to exclude taboo items, like L75+ armor and GM gear.
- The history query might be dangerously close to the maximum size.
- Max still fail for large (6+ digit) $price values, or combinations of $price and $forsale.

Edit 3:
Neatened up the first post substantially Current performance is approximately 3 seconds to do a total auction house restock to 10 items.

Edit 3.1:
The 25k auto-insert would have left space for approximately 50,000 characters, but I kept worrying that some unforeseen price+forsale combo would break it. Lowered to 20k, which should leave room for 240,000 characters in total. Should be completely safe, unless impractical values are used, with minimal impact upon practical usage.

[kirionet]

Tbank you! This is awesome! This is what i ever wanted, a tool to get the AH median price!
There's only an issue, it gets only some items (probably only the items in stock on official AH).
There's a way to full with everything ??

Due to my lack of looking on the forums, I created another style of this... so it's just another take on the same thing.
This one however, is more command line.

The only major difference, is this will have the option to pull the median price live from FFXIAH.com for your entries. I do not recommend it for a hourly crontab tho as it takes a while.

(interesting to compare writing styles!)

Code: Select all

*************************************************
AH Generator
*************************************************

  Requirements:
	- Lynx or Links or eLinks
	- PHP 5.4.x or higher
	- MySQL Extensions for PHP
	- Internet Connection (For FFXIAH Parse)


  Usage:
syntax: php gen.php options
        baseprice=x     set the base price for the items.
        amt=x           set the amount of items you wish to add, default is 3
        ffxi=true       this will take the median price from ffxiah. SLOW.(~5500 seconds)
        history=1       history lines for price, set to 0 if you do not wish to have
                        history. default 1
        clearah=1       this will clear the AH.  [Non-Recoverable]
        duplication=1   this will duplicate the last sold values under ffxiAH name.
                        (used in crons for ffxiah prices)

example: php gen.php baseprice=100 amt=10 history=1
example: php gen.php ffxi=true amt=10 history=2
example: php gen.php clearah=1
example: php gen.php duplication=1 amt=7


	This script was designed for crontabs, however the ffxi mode is not recommended in crontabs.
	This script was designed for *nix, and require a textmode browser for ffxi mode.
	

If using on windows (untested):
	Download Lynx and Compile for Windows (http://lynx.isc.org/lynx2.8.7/index.html)
	Download PHP for windows with MySQL connector (http://windows.php.net/download/)
	Place compiled Lynx and php files (php.exe, php5ts.dll, ext\php_mysql.dll, ext\php_mysqli.dll) in same dir as the php script.
	Please note, you will need the VC9 Libraries installed on your machine (The download page for windows php has link)
	run using the options above from command line. (you can create a batch file for Windows Scheduler)

EDIT:
I found a bad bad bug... it was only adding to the history of the AH. I have since fixed it. I will release another ver when I have finished duplicating the entries from history (for those who need cron, but want FFXIAH prices.. this will be much faster) Attached new replacement ver.

2: This is now completed.