Print Page - Output Transactions in CSV

I needed a list of transactions in CSV format for a given Bitcoin or Namecoin address, but I couldn't find anything that would directly give me the list (especially in the format I wanted), so I cooked up some code.

Requirements (or at least what is known good without code modification):

PHP
PostgreSQL with a fully populated bitcoin-abe (https://github.com/jtobey/bitcoin-abe/) database
bitcoin-php (https://github.com/mikegogulski/bitcoin-php/) library

I made one PHP file and a SQL function for the bitcoin-abe database based on the SQL in the bitcoin-abe python code.

txs.php:

Code:

<?php

include 'bitcoin.inc';

$addr = $_GET['address'];
// Can't do the following because Namecoin addresses will fail this
// if (Bitcoin::checkAddress($addr) == false) exit("ERROR: Not a valid address.");
exec("sudo -u nope /path/cgi-bin/abe");
$dbconn = pg_connect("host=nope dbname=nope user=nope password=nope");
$params = array(Bitcoin::addressToHash160($addr));
$result = pg_query_params($dbconn, "SELECT * FROM GetTransactions($1)", $params);
$arr = pg_fetch_all($result);
pg_close($dbconn);
if ($arr) {
   $out = fopen('php://output', 'w');
   fputcsv($out, array_keys($arr[0]));
   for ($i = 0; $i < count($arr); $i++) {
      fputcsv($out, $arr[$i]);
   }
   fclose($out);
} else {
   echo("ERROR: No results.");
}

?>

GetTransactions.sql:

Code:

CREATE OR REPLACE FUNCTION GetTransactions(char(40))
RETURNS TABLE(nTime numeric,
chain_id numeric,
height numeric,
is_in int,
blk_hash character,
tx_hash character,
pos numeric,
value double precision) AS
$$
SELECT nTime, chain_id, height, is_in, blk_hash, tx_hash, pos, value * 10^(-8) AS value
FROM (SELECT
b.block_nTime AS nTime,
cc.chain_id,
b.block_height AS height,
1 AS is_in,
b.block_hash AS blk_hash,
tx.tx_hash,
txin.txin_pos AS pos,
-prevout.txout_value AS value
FROM chain_candidate cc
JOIN block b ON (b.block_id = cc.block_id)
JOIN block_tx ON (block_tx.block_id = b.block_id)
JOIN tx ON (tx.tx_id = block_tx.tx_id)
JOIN txin ON (txin.tx_id = tx.tx_id)
JOIN txout prevout ON (txin.txout_id = prevout.txout_id)
JOIN pubkey ON (pubkey.pubkey_id = prevout.pubkey_id)
WHERE pubkey.pubkey_hash = lower($1)
AND cc.in_longest = 1
UNION
SELECT
b.block_nTime AS nTime,
cc.chain_id,
b.block_height AS height,
0 AS is_in,
b.block_hash AS blk_hash,
tx.tx_hash,
txout.txout_pos AS pos,
txout.txout_value AS value
FROM chain_candidate cc
JOIN block b ON (b.block_id = cc.block_id)
JOIN block_tx ON (block_tx.block_id = b.block_id)
JOIN tx ON (tx.tx_id = block_tx.tx_id)
JOIN txout ON (txout.tx_id = tx.tx_id)
JOIN pubkey ON (pubkey.pubkey_id = txout.pubkey_id)
WHERE pubkey.pubkey_hash = lower($1)
AND cc.in_longest = 1) u
ORDER BY nTime
$$ LANGUAGE SQL;

Example Usage: http://path/txs.php?address=insertaddresshere (http://) or "php txs.php?address=insertaddresshere"

Example Output for Bitcoin address 1HZRowLTkmF6M8V11vj2dmVf2r9VK7dVfo:

Code:

ntime,chain_id,height,is_in,blk_hash,tx_hash,pos,value
1318808407,1,149565,0,00000000000004d9894eea1ad808c9e671ff3bace453480b11300dbda337252b,af0140b834007df586b47571e6bd000a6396d275dd0afa169efba71f863acb5a,117,0.01
1319109613,1,149988,0,00000000000001f38546624afceafc53723cd4383b3668819ff58f94fe50a365,ebfcf2fc5018de28eaecef09af936658ca4afc68ee67da93d1448cce6d7ba954,78,1

EDIT: Added command to txs.php for updating the abe database before returning data and example output.

Bitcoin Forum

Bitcoin => Project Development => Topic started by: notawake on November 07, 2011, 08:23:22 AM