I created a code that is working fine but I'm not sure if its 'legit'. I am using a sql query in a while loop from another sql query, that means that the (second) sql query is repeated the amount of rows the first query returns.
Can anyone tell me if I can use this or its just one complete mess?
the code:
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_feat = 'SELECT * FROM wp_wpl_dbst WHERE category = 105';
$result_feat = mysqli_query($conn, $sql_feat);
if (mysqli_num_rows($result_feat) > 0) {
while($row = mysqli_fetch_assoc($result_feat)) {
$filter_feat = $row["table_column"];
$filter_name = $row["name"];
$sql_feat2 = 'SELECT * FROM wp_wpl_properties';
$result_feat2 = mysqli_query($conn, $sql_feat2);
if (mysqli_num_rows($result_feat2) > 0) {
while($row2 = mysqli_fetch_assoc($result_feat2)) {
if (!empty($row2[$filter_feat])) {
echo $filter_name;
echo "<br>";
}
}
}
}
}
mysqli_close($conn);
First, you should always check the result of mysqli_query before assuming it represents a proper query result:
// you should check if this value is FALSE before using it
$result_feat = mysqli_query($conn, $sql_feat);
// and this too
$result_feat2 = mysqli_query($conn, $sql_feat2);
As for your code running a query inside the loop, it is pretty much always discouraged unless there is no other option. You should explore the possibility of a JOIN before running any queries inside a loop. Cases may exist where you have no other option, but in your code, I don't see any point at all to run this query over and over:
$sql_feat2 = 'SELECT * FROM wp_wpl_properties';
If it made reference to the value from $row, then maybe. But it doesn't.
Essentially you are doing dynamic sql so join would not be possible but adding where clause to second select would make it more efficient. something like - $sql_feat2 = 'SELECT ' + $filter_name + ' FROM wp_wpl_properties where ' + $row2[$filter_feat] + ' is not null';