How to use prepared statements with Postgres in PHP

The code below is my prep to eventually update a database with the prices of shares. What I am trying to do is get the value of the fund_name, based on its row number. The row is found, based on which row a value is entered on. The echo statements are just for me now, to see what is being returned before I modify it to update the DB.

The code below works somewhat. It will return the value amount I entered ($price[$i]) and the count ($x) it is on. The $x does correspond correctly to what row I entered the value on. However, when I try to execute the SELECT statement, nothing is returned if I have a variable in the select statement. If I change the $x in the SELECT statement to a number, ie 3, it will return the correct fund_name that is on line 3. I have tried by putting fund_line = '$x' and fund_line = $x directly in the SELECT statement. I have also tried fund_line = ? when using the $x as $update_prices->execute([$x]). I don’t get an error message when using a variable, just nothing is returned for the fund_name, while the other echo statements $price[$i] and $x return what I expect them to.

Is there something I am missing?

$x = 0;
$update_prices = $pdo->prepare ("SELECT fund_name FROM funds WHERE fund_name = (SELECT fund_name FROM
                                  (SELECT fund_name, ROW_NUMBER () OVER (ORDER BY fund_name ASC) AS fund_line
                                FROM (SELECT fund_name FROM funds) cost) AS cost_sorted WHERE fund_line = $x);");
$update_prices->execute();


if(isset($_POST['price_updates'])) {
    $price = $_POST['price'];
    $priceSize = count($price);
    for($i=0; $i < $priceSize; ++$i) {
      $x += 1;
      if ($price[$i] > 0){
        echo "x$: ".$x.' is the line #</br>';
        echo  '$'.$price[$i].' is the amount</br>';
        while ($row = $update_prices->fetch()) {
          echo $row['fund_name'].'</br>';
        }
      }
  }
}

Answer

You query is OK. you can pass the parameter like below with ?:

$x = 0;
$update_prices = $pdo->prepare ("SELECT fund_name FROM funds WHERE fund_name = (SELECT fund_name FROM
                                  (SELECT fund_name, ROW_NUMBER () OVER (ORDER BY fund_name ASC) AS fund_line
                                FROM (SELECT fund_name FROM funds) cost) AS cost_sorted WHERE fund_line = ?);");

$update_prices->execute(array($x));

Advertisement

Answer