0

I am new to PDO, really new, I'm trying to read documentation from several site (this, for example) but I just don't fully understand it.

Still, I wish to update an internal website I'm using in my company because this project is becoming bigger and important and I want to rewrite it to shape it better.

I was able to "convert":

<?php
$sql = "SELECT name FROM table order by name ASC";
$result1 = pg_query($conn, $sql);
while ($row = pg_fetch_row($result1)) {
  echo "<option value='$row[0]'>$row[0]</option>";
}
?>

to:

<?php
$sql = "SELECT name FROM table order by name ASC";
$stmt_menu = $pdo->prepare($sql);
$stmt_menu -> execute();
foreach ($stmt_menu as $row){
  echo "<option value=".$row['name'].">".$row['name']."</option>";
}
?>

Even though I would have preferred to continue using the row[0] for simplicity, but Apache's logs complained about PHP Notice: Undefined offset: 0 in /var/www/pdo-test-website/left-menu.php on line 20

Following other tutorials (like this one), I am trying to replace the current datalist SQLs into a PDO based statements.

Before I had:

<input type="text" list="test_list" class="form-control input-sm" name="test_name" id="test_name_id" />

<?php
echo '<datalist id="test_list">';
$test_name = pg_query("SELECT DISTINCT ON (FOO) FOO FROM table;");
while ($row = pg_fetch_row($test_name)) {
  echo "<option value='$row[0]'>$row[0]</option>";
}
echo '</datalist>';
?>

Now:

<?php
$datalist_foo = "SELECT DISTINCT ON (:value) :value FROM table;";
$stmt_datalist = $pdo->prepare($datalist_foo);


echo '<datalist id="test_list">';
$stmt_datalist->execute(['value' => "FOO"]);
foreach ( $stmt_datalist as $row ) {
    echo "<option value=".$row['FOO'].">".$row['FOO']."</option>";
}
echo '</datalist>';
?>

I am trying my best to have it working but I keep reading errors in the Apache's error log file. I tried rewriting this statement in many different versions, it would be not so useful if I would paste the log file for your reviewing because I should also write, for each error row, its full php code.

The last error, the one that I get after trying the code I posted above, is:

PHP Notice:  Undefined index: FOO in /var/www/pdo-test-website/datalist.php

I just want to note, that the non-pdo version works, the (test) database have data inside and I am able to fetch them using the "old" pg_query method.

I hope you can give me some advice, I also tried the solution at this link: returning multiple rows from postgre PDO but I can't get it working.

aPugLife
  • 989
  • 2
  • 14
  • 25
  • 1. on order to use numeric indices you should use a [corresponding fetch mode](https://phpdelusions.net/pdo/fetch_modes#FETCH_NUM). 2. Assuming FOO is a field name, you cannot parameterize a field name. Dunno why did you decide to use prepare/execute for a constant query at all. Why not to keep it "SELECT DISTINCT ON (FOO) FOO FROM table;"? – Your Common Sense Feb 14 '18 at 14:04
  • @YourCommonSense thanks for the link! This explains a lot already! - 'FOO' is a real column name, of course I am using a fake name for it. - I have around 20 Datalists to manage, I thought that preparing a single SQL and then executing it with different parameters, one for each datalist case, would make sense - EDIT: FOO changes ~20 times or more, with this, also TABLE. I will parametize 'TABLE' when I will be able to execute it with 1 argument only – aPugLife Feb 14 '18 at 14:12
  • you cannot parameterize a table name either. parameters are for the data only – Your Common Sense Feb 14 '18 at 14:32
  • @YourCommonSense yeah, I just realized that, was searching it few minutes ago.. It is like that this question will never receive its answer because it just can not work. Thanks for letting me notice that. If you like, I can upvote your answer if you want to reply as to why one can not parameterize column names and tables – aPugLife Feb 14 '18 at 14:41

0 Answers0