How to add daily stock price history col in mysql table in php using cron job?

@bernice_zulauf 

To add daily stock price history in a MySQL table using PHP and a cron job, you can follow these steps:

1. Create a table in your MySQL database to store the stock price history. This table should have columns for the stock symbol, the date, and the price. CREATE TABLE stock_price ( id INT AUTO_INCREMENT PRIMARY KEY, symbol VARCHAR(10) NOT NULL, date DATE NOT NULL, price DECIMAL(10, 2) NOT NULL );
2. Create a PHP script that fetches the stock price data from an API or any other source. ://api.example.com/stocks'; // Fetch the stock price data $data = file_get_contents($apiUrl); $stocks = json_decode($data, true); // Insert the stock price data into the database $pdo = new PDO('mysql:host=localhost;dbname=your_database_name', 'your_username', 'your_password'); foreach ($stocks as $stock) { $symbol = $stock['symbol']; $date = $stock['date']; $price = $stock['price']; $query = "INSERT INTO stock_price (symbol, date, price) VALUES (?, ?, ?)"; $stmt = $pdo->prepare($query); $stmt->execute([$symbol, $date, $price]); } ?>
3. Save the PHP script on your server, for example, as update_stock_price.php.
4. Configure a cron job to execute the PHP script daily. Edit your crontab file using the command crontab -e, and add the following line to run the PHP script every day at midnight: 0 0 * * * php /path/to/update_stock_price.php Make sure to replace /path/to/update_stock_price.php with the actual path to your script.

Now, the cron job will run the PHP script daily at midnight, fetching the stock price data and inserting it into the MySQL table stock_price.

@bernice_zulauf 

Additionally, you might want to consider adding error handling and logging to your PHP script to ensure that any issues with fetching data or inserting it into the database are captured and logged for later review.

Here is an updated version of the PHP script with error handling and logging:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

<?php
// API URL to fetch stock price data
$apiUrl = 'https://api.example.com/stocks';

// Fetch the stock price data
$data = file_get_contents($apiUrl);

// Check if data was fetched successfully
if ($data === false) {
    error_log("Failed to fetch stock price data.");
    exit;
}

$stocks = json_decode($data, true);

if ($stocks === null) {
    error_log("Failed to decode stock price data.");
    exit;
}

// Insert the stock price data into the database
try {
    $pdo = new PDO('mysql:host=localhost;dbname=your_database_name', 'your_username', 'your_password');
    foreach ($stocks as $stock) {
        $symbol = $stock['symbol'];
        $date = $stock['date'];
        $price = $stock['price'];
        $query = "INSERT INTO stock_price (symbol, date, price) VALUES (?, ?, ?)";
        $stmt = $pdo->prepare($query);
        $stmt->execute([$symbol, $date, $price]);
    }
} catch (PDOException $e) {
    error_log("Database error: " . $e->getMessage());
    exit;
}
?>

In this updated version, error handling is added to capture any failures in fetching data or inserting it into the database. Error messages are logged using PHP's error_log function.

By including error handling and logging in your script, you can ensure that any issues that arise are properly handled and that you have visibility into what went wrong during the execution of the cron job.