Method for extracting and recording the exchange rate in a database?

Started by Vanesill, Apr 12, 2023, 12:18 AM

Previous topic - Next topic

VanesillTopic starter

Can you assist me in completing the cryptocurrency exchange rate parser and storing the data in a MySQL database every minute? Once this is done, I plan to create a graph based on the information from the database.

I have already created the parser, as shown below:

$btc = json_decode(file_get_contents(''), true);
$ticker = $btc['ticker'];
echo "Sell: ".$ticker['sell']." Dol.";
echo '</b> <br>';
echo "Buy : ".$ticker['buy']." Dol.";
echo '</b> <br>';
echo '<b><font color=" #00CC33" size="3">Updated: <font color="#00CC33">';

$timestamp = $ticker['updated'];
echo strftime('%H:%M:%S',$timestamp);


However, I am unsure how to proceed with writing the parsed data to the database. I have been stuck on this for some time and would appreciate any help you can provide. Please note that this task must be done in PHP.


Instead of learning PHP, SQL, etc., I approached the task differently. I installed a Munin and included a plugin, which yielded great results. =)


Do you require assistance in linking mysql and inputting the parser's findings into it? It seems like you may not be familiar with php or mysql. I can offer an illustration of my btc/ltc parser as reference.


if(!flock($lock_file = fopen($file, 'w'), LOCK_EX | LOCK_NB))
  die("Already runninng\n");

mysql_query("SET names utf8");


mysql_query("INSERT INTO btc_charts_live (mark, date, low, high, buy, sell, volume, last) VALUES ('btce',$timestamp,{$btc->low},{$btc->high},{$btc->buy},{$btc->sell},{$btc->vol},{$btc->last})");
mysql_query("INSERT INTO ltc_charts_live (mark, date, low, high, buy, sell, volume, last) VALUES ('btce',$timestamp,{$ltc->low},{$ltc->high},{$ltc->buy},{$ltc->sell},{$ltc->vol},{$ltc->last})");



MySQL can handle 8 million rows easily as long as proper indexes and composite indexes are implemented. To optimize search data, the date of addition can be divided into columns such as year, month, and day, and added to composite indexes. The week number can also be added in INT and reduced for faster search.

Outdated data that is no longer needed can be stored separately in a database like Mongo, which is fast but uses up disk space quickly. However, it's worth noting that over time, Mongo can become bloated and difficult to manage. This method has been successfully tested in a billing system processing 2k records/updates per second.


 To store the parsed data in a MySQL database, you will need to establish a connection to the database and then insert the data into a table.

Here's an example of how you can modify your code to achieve this:

$btc = json_decode(file_get_contents(''), true);
$ticker = $btc['ticker'];

// Connect to MySQL database
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check if connection is successful
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

$sell = $ticker['sell'];
$buy = $ticker['buy'];
$updated = strftime('%H:%M:%S', $ticker['updated']);

// Prepare and execute SQL statement to insert data into table
$sql = "INSERT INTO exchange_rates (sell, buy, updated) VALUES ('$sell', '$buy', '$updated')";
if ($conn->query($sql) === TRUE) {
    echo "Data inserted successfully.";
} else {
    echo "Error inserting data: " . $conn->error;

// Close the database connection

echo "Sell: ".$sell." Dol.";
echo '</b> <br>';
echo "Buy : ".$buy." Dol.";
echo '</b> <br>';
echo '<b><font color="#00CC33" size="3">Updated: <font color="#00CC33">'.$updated;


Make sure to replace "localhost", "your_username", "your_password", and "your_database_name" with the appropriate values for your MySQL database.

With this code, the parsed data (sell, buy, and updated) will be inserted into a table named "exchange_rates" in your database. You can modify the table structure to fit your requirements.