0

Context: I've established a connection with a MySQL database that has Stock Ticker, Open, High, Low, Close prices, interval (1d, 1wk and 1mo) and date. Now, I'd like to make it possible for a user to select the "ticker" name and "interval" in HTML and send this input as a MySQL search query with PHP (I'm trying to do this using the action and method="post"). This data would then be saved or pushed into the "data" variable in a javascript file (which will create a candlestick graph using google charts).

TL;DR - User selects ticker and interval, this is sent to MySQL with PHP to collect the data and this data is saved in a javascript variable called "data" to create a candlestick graph.

Problem: I'm not sure how I could create this interface between MySQL outcome from PHP and then push this data to the javascript variable. I was trying to do $data = array() and then somehow push this data in the data variable on my js file, but not sure how to do this (I'm new to PHP). I was also trying to make it so that on the PHP file ticker='$ticker' to grab the HTML user input (since it's id of the input is ticker and Interval), but not sure if this is the correct way of doing this

Any help is very welcome! HTML

<section class="no-padding-top no-padding-bottom">
        <div class="container-fluid">
          <div class="statistic-block block">
            <form name="query" method="post" action="connection.php">

              <div class="ticker">
                <label for="name">Ticker:</label>
                <input style="background: rgba(255,255,255,0.5);border-radius: 10px; border: none;  text-align: center;"
                  type="text" id="ticker" name="ticker" required minlength="4" maxlength="8" size="10" 
                  placeholder="TSLA"></input>
              </div>

              <div class="Interval">

                <label for="name">Period:</label>
                <select
                  style="background: rgba(255,255,255,0.5);border-radius: 10px; border: none;  text-align: center;"
                  name="Interval" id="Interval">
                  <option value="1d">1 day</option>
                  <option value="1wk">1 week</option>
                  <option value="1mo">1 month</option>
                </select>
              </div>
            </form>

Javascript

google.charts
  .load("current", {
    packages: ["corechart"],
  })
  .then(function drawChart() {
    var rows = [
      ["2021-01-01", 2722124.01, 2715937.44, 2724963.79, 2715937.44], //these are just dummy values to test if the chart was functional or not. The goal is to push the PHP data in here to create the graph
    ];
    var data = google.visualization.arrayToDataTable(
      rows.map(function (row) {
        var date = row[0];
        var low = row[3];
        var open = row[1];
        var close = row[4];
        var high = row[2];
        return [date, low, open, close, high];
      }),
      true
    );

var options = {
  legend: "none",
  candlestick: {
    fallingColor: { strokeWidth: 0, fill: "#a52714" }, // red
    risingColor: { strokeWidth: 0, fill: "#0f9d58" }, // green
  },
};

var chart = new google.visualization.CandlestickChart(
  document.getElementById("candlestick")
);
chart.draw(data, options);
  });

PHP

 <?php
    $servername = "...";
    $username = "...";
    $password = "...";
    $dbname = "...";
    
    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
    }
    
    $sql = "SELECT Date, Open, High, Low, Close FROM symbols WHERE ticker='$ticker' AND interval='$Interval'";
    $result = mysqli_query($conn, $sql);
    // $data = array(); possibly create a data array to store all the data and then push this data onto the javascript?
    if (mysqli_num_rows($result) > 0) {
      // output data of each row
      while($row = mysqli_fetch_assoc($result)) {
        echo "[" . $row["Date"]. "," . $row["Open"]. "," . $row["High"]. "," . $row["Low"]. "," . $row["Close"]. "],";
      }
    } else {
      echo "0 results";
    }
    
    mysqli_close($conn);
    ?>
  • 2
    `WHERE ticker='$ticker' Where did `$ticker` get created? – RiggsFolly Oct 04 '21 at 16:46
  • 2
    Dont try and write your own JSON. pass a built data structure (Array/Object) ro `JSON_ENCODE()` – RiggsFolly Oct 04 '21 at 16:47
  • I was hoping this would grab the user input from the HTML with the respective ID (in this case id=ticker) –  Oct 04 '21 at 16:47
  • 1
    $_POST is where data will be passed into the PHP, So `$_POST['ticker']` and `$_POST['Interval']` – RiggsFolly Oct 04 '21 at 16:48
  • So I would do something like this: `
     –  Oct 04 '21 at 16:55
  • 1
    You can submit multiple values from one form, if that's what you mean. Btw The "action" of the form should be the URL where the form is to be submitted to. Have you done any tutorials on PHP / html forms yet? – ADyson Oct 04 '21 at 17:05
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Oct 04 '21 at 17:17

0 Answers0