From the mysqli::prepare:
Note:
The markers are legal only in certain places in SQL statements. For
example, they are allowed in the VALUES() list of an INSERT statement
(to specify column values for a row), or in a comparison with a column
in a WHERE clause to specify a comparison value.
However, they are not allowed for identifiers (such as table or column
names), in the select list that names the columns to be returned by a
SELECT statement, or to specify both operands of a binary operator
such as the = equal sign...
Also, from the same source, referring to the sql statement:
You should not add a terminating semicolon or \g to the statement.
So, if you want to provide the wanted column name, you must do it using PHP variables. I wrote a solution, involving all the steps you should use when running db operations. I know, it's a lot but it's easy to follow. The extended and documented version is in the link I provided you in my comment, earlier.
Good luck.
<?php
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception). They are catched in the try-catch block.
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
// Just test values.
$taxon_subtyp = 'abc';
$taxon_typ = 'def';
$taxon_nam = '123xyz';
/*
* Build the sql statement using the printf() function.
* Familiarize yourself with it (it takes 15 minutes),
* because it is a very powerfull function, to use especially
* in constructing complex sql statements.
*
* In principle, each "%s" represents a placeholder for each
* variable in the variable list, that follows after the sql statement string.
*/
$sql = sprintf('SELECT DISTINCT %s FROM refPlant WHERE %s = ?', $taxon_subtyp, $taxon_typ);
// Open connection.
$conn = openConnection();
// Prepare and validate statement.
$stmt = $conn->prepare($sql);
if (!$stmt) {
throw new Exception('Prepare error: ' . $conn->errno . ' - ' . $conn->error);
}
// Bind variables for the parameter markers (?) in the SQL statement.
$bound = $stmt->bind_param('s', $taxon_nam);
if (!$bound) {
throw new Exception('Bind error: A variable could not be bound to the prepared statement');
}
// Execute the prepared SQL statement.
$executed = $stmt->execute();
if (!$executed) {
throw new Exception('Execute error: The prepared statement could not be executed!');
}
// Get the result set from the prepared statement.
$result = $stmt->get_result();
if (!$result) {
throw new Exception('Get result error: ' . $conn->errno . ' - ' . $conn->error);
}
// Get the number of rows in the result.
$numberOfRows = $result->num_rows;
// Fetch data and save it into an array.
$fetchedData = array();
if ($numberOfRows > 0) {
// Use mysqli_result::fetch_all to fetch all rows at once.
$fetchedData = $result->fetch_all(MYSQLI_ASSOC);
}
// Print results (in a cool formatted manner), just for testing.
echo '<pre>' . print_r($fetchedData, TRUE) . '<pre>';
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*/
$result->close();
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*/
$stmtClosed = $stmt->close();
if (!$stmtClosed) {
throw new Exception('The prepared statement could not be closed!');
}
// Close db connection.
$connClosed = $conn->close();
if (!$connClosed) {
throw new Exception('The db connection could not be closed!');
}
} catch (mysqli_sql_exception $e) {
echo 'Error: ' . $e->getCode() . ' - ' . $e->getMessage();
exit();
} catch (Exception $e) {
echo $e->getMessage();
exit();
}
/*
* Disable internal report functions.
*
* MYSQLI_REPORT_OFF: Turns reporting off.
*/
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;
EDIT:
Since you posted your "white list", I thought you might want to see it in action in my code structure as well. Just for fun :-)
<?php
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception). They are catched in the try-catch block.
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* Put this somewhere, so that it fits in your global code structure.
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
function get_following_plant_group($taxon_typ, $taxon_nam) {
$taxon_order = ['class', 'ord', 'family', 'genera'];
if (in_array($taxon_typ, $taxon_order)) {
$taxon_subtyp = $taxon_order[array_search($taxon_typ, $taxon_order) + 1];
try {
/*
* Build the sql statement using the printf() function.
* Familiarize yourself with it (it takes 15 minutes),
* because it is a very powerfull function, to use especially
* in constructing complex sql statements.
*
* In principle, each "%s" represents a placeholder for each
* variable in the variable list, that follows after the sql statement string.
*/
$sql = sprintf('SELECT DISTINCT %s FROM refPlant WHERE %s = ? ORDER BY ?', $taxon_subtyp, $taxon_typ);
// Open connection.
$conn = getBdd();
$conn->set_charset('utf8');
// Prepare and validate statement.
$stmt = $conn->prepare($sql);
if (!$stmt) {
throw new Exception('Prepare error: ' . $conn->errno . ' - ' . $conn->error);
}
// Bind variables for the parameter markers (?) in the SQL statement.
$bound = $stmt->bind_param('ss', $taxon_nam, $taxon_subtyp);
if (!$bound) {
throw new Exception('Bind error: A variable could not be bound to the prepared statement');
}
// Execute the prepared SQL statement.
$executed = $stmt->execute();
if (!$executed) {
throw new Exception('Execute error: The prepared statement could not be executed!');
}
// Get the result set from the prepared statement.
$result = $stmt->get_result();
if (!$result) {
throw new Exception('Get result error: ' . $conn->errno . ' - ' . $conn->error);
}
// Get the number of rows in the result.
$numberOfRows = $result->num_rows;
/*
* Fetch data and save it into an array.
* Use mysqli_result::fetch_assoc to fetch a row at a time.
*/
$arr = [];
if ($numberOfRows > 0) {
while ($row = $result->fetch_assoc()) {
$arr[] = $row[$taxon_subtyp];
}
}
// Print results (in a cool formatted manner), just for testing.
// echo '<pre>' . print_r($arr, TRUE) . '<pre>';
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*/
$result->close();
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*/
$stmtClosed = $stmt->close();
if (!$stmtClosed) {
throw new Exception('The prepared statement could not be closed!');
}
// Close db connection.
$connClosed = $conn->close();
if (!$connClosed) {
throw new Exception('The db connection could not be closed!');
}
$arr = [$taxon_subtyp, $arr];
return(json_encode($arr));
} catch (mysqli_sql_exception $e) {
echo 'Error: ' . $e->getCode() . ' - ' . $e->getMessage();
exit();
} catch (Exception $e) {
echo $e->getMessage();
exit();
}
}
}
/*
* Disable internal report functions.
*
* MYSQLI_REPORT_OFF: Turns reporting off.
*
* Put this somewhere, so that it fits in your global code structure.
*/
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;
