-1

SOLVED: Reason Can I parameterize the table name in a prepared statement?

I have a very simple Query to collect data from a two column table in MySQL. Normally it worked but for some reason I know receive the error: Undefined offset: 1

$query_select = ("SELECT ?, ? FROM _HOOFDRUBRIEK");
$stmt = $mysqli->prepare($query_select);
$stmt->bind_param("ss", $column1, $column2);
$stmt->execute();
$stmt->store_result();
//$count = $stmt->num_rows;
//echo $count;
/die();

 $stmt->bind_result( $key_hoofdrubriek          ,
                     $descr_hoofdrubriek        ); 

$stmt->fetch();         

$hoofdrubriek[] = array('key'      =>$key_hoofdrubriek  ,                           
                        'descr'    =>$descr_hoofdrubriek    ); 

//Here I request the variable, what occurs the error
$var = $hoofdrubriek[1]['descr'];


echo 'Show here what's in the var: '.$var ;

Does anyone know why I get this error, because from my point of view, a multidimensional array can be called by $array_name[row][column];

Community
  • 1
  • 1
Krooy_mans
  • 304
  • 3
  • 10

2 Answers2

1

You are mistinpreting how that works. Result bind parameters are just bound in order to the selected field. You still need to select normal fields as usual.

Moreover, you cannot specify field names as input parameters. In your situation, you select two constant values, namely the strings you pass as input parameters. This is why you get the field names in the result instead of the values of those fields. The parameters are just string values, so the query that is executed would look like this:

SELECT 'key_hoofdrubriek', 'descr_hoofdrubriek' FROM FROM RGS_HOOFDRUBRIEK

So, skip the question marks and the input bind parameters altogether and build the query like so:

$query_select = ("SELECT key_hoofdrubriek, descr_hoofdrubriek FROM RGS_HOOFDRUBRIEK");

Or, if you must, by using PHP variables in the statement:

$query_select = ("SELECT $column1, $column2 FROM RGS_HOOFDRUBRIEK");

For reading, you can of course still use bind_result.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • I know, see my update. I missed the column names variables in my snippet. $column1 ='key_hoofdrubriek'; $column2 ='descr_hoofdrubriek'; --> see this post: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Krooy_mans Sep 11 '15 at 10:07
  • I assumed those were missing. Point is, it doesn't work that way. You cannot pass column names as input parameters. – GolezTrol Sep 11 '15 at 10:08
  • You are confused. The name of the VAR is @COLUMN1 but it's just a variable which is set to the name of the column. – Krooy_mans Sep 11 '15 at 10:10
  • I'm not confused. I know what it is. It *just doesn't work that way*. What you try to do is impossible. – GolezTrol Sep 11 '15 at 10:12
  • you are still confused my friend. Because when I do this $count = $stmt->num_rows; echo $count; die(); I receive 25 the exact numbers of the rows in database – Krooy_mans Sep 11 '15 at 10:20
  • I updated the script in intitial post above, and it's // their but on test you can see it works. Their it's not commented. http://www.sbr-accounting.com/hoofdrubrieken.php – Krooy_mans Sep 11 '15 at 10:23
  • Indeed. Because you select from that table. So you select 25 times the constant strings `'key_hoofdrubriek'` and `'descr_hoofdrubriek'`. Just execute the first query in my answer and you'll get the same result. Try it and you'll see what I mean. Other example: If you select `SELECT 1, 'Hello' FROM RGS_HOOFDRUBRIEK`, you will get 25 times the values `1` and `Hello`. The 25 rows is related to the number of rows in the table, but in the select, you don't specify any fields from the table, so you just get the values you specified. I have to go now, so I'll let you ponder on it for a while. :) – GolezTrol Sep 11 '15 at 10:23
  • Good guy @GolezTrol! I found it, with thanks to your suggestions. Bottom line: In the strictest sense, at the database level, prepared statements only allow parameters to be bound for "values" bits of the SQL statement. http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement – Krooy_mans Sep 11 '15 at 11:39
0

You can't use placeholders for column names, they're always treated as expressions. So your prepared query is equivalent to writing:

SELECT 'key_hoofdrubriek', 'descr_hoofdrubriek' FROM RGS_HOOFDRUBIEK

This just returns those literal strings for each row in the table, not the values in the columns with those names.

If you need to determine the column names dynamically, you have to use variable substitution or concatenation, you can't use placeholders:

$query_select = "SELECT $column1, $column2 FROM RGS_HOOFDRUBRIEK";
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I don't do this. I know it looks weird, but I use a VAR with name $column1 ut ists set to the name of the column: 'key_hoofdrubriek'; – Krooy_mans Sep 11 '15 at 10:09
  • @Krooy_mans I've updated my answer to match all your variable names and values, I hope it's more understandable. – Barmar Sep 11 '15 at 10:11