I recently was working on a project that required a variable amount of SQL queries to be performed on a variable table for variable fields. Didn’t seem like a hard concept at first. I constructed the queries with PHP variables and presto I had everything I needed. The only problem came when I actually wanted to work with the data I had cleverly extracted.
Since the field name could have been anything I didn’t know how to access the data from within the mysqli row object. I knew that the table name was stored in another mysqli object because I had used it to create the query in the first place. In the name of self deprecation, here was my initial “God let it be this easy” attempt.
$tableQuery = "SELECT table_name, field_name FROM TABLES WHERE id = ".$tid;
$tableResult = $con->query($tableQuery);
while($tables = $tableResult->fetch_object()){
//The Wrong Way
$dataQuery = "SELECT ".$tables->field_name." FROM ".$tables->table_name." WHERE id=".$did;
$dataResult = $con->query($dataQuery);
while($data = $dataResult->fetch_object()){
echo $data->$tables->field_name; //Worth a shot right? Nice try, but no.
}
}
Yeah, I thought I was pretty clever too. Not to worry though. If you run into this situation remember that MySQL allows you to define a field name via the AS keyword. This way no matter what fields you’re searching for they will always be saved under the same name in the mysqli object. Below is the corrected code. Instead of copying the whole thing I just provided the code inside the while loop.
//The Right Way
$dataQuery = "SELECT ".$tables->field_name." AS constant_field_name FROM ".$tables->table_name." WHERE id=".$did; //Note the AS
$dataResult = $con->query($dataQuery);
while($data = $dataResult->fetch_object()){
echo $data->constant_field_name; //Much better
}