Use a comma-separated field in another SQL query

Question

I was just wondering if there was a better way to write this block of code in php. I have a string with values separated by commas. I need to prepare that string to pass it on to the query using IN clause.

 $sql = "SELECT domain, items FROM rep_skills WHERE report_id = $id ";
 $data = $conn->query($sql)->fetch_object();
 while($data){
 // can this be done in one line? 
 $array = explode(",",$data->items); // string here is 39,40,41
 $items = implode("','",$array); // string here is 39','40','41
 // end of block
 $result = $conn->query("SELECT description 
 FROM ".$tables[$data->domain]." 
 WHERE item_id IN ('".$items."')");
 $description = '';
 while($row = $result->fetch_object()){
 $details .= '- '.$row->description;
 }
}

• 200_success: Where does $records come from? How can you be certain that it contains only comma-separated numbers?
• joanb: pulled from database. 100% certain. Data is validated before input.
• 200_success: In that case, why not write a JOIN query instead?
• joanb: How would that be? i'll update the full code so you can see
• 200_success: Oh, that is a very weird database schema: there's a separate table for each domain, and the items are stored as comma-separated lists rather than as a table. The bad schema design is forcing you to write bad code to query it.

Answer 1

Not withstanding what other have mentioned.

$array = explode(",",$data->items); // string here is 39,40,41
$items = implode("','",$array); // string here is 39','40','41

You can simply replace the comas with ',', Like this

$items = str_replace(',', "','", $data->items);

One thing to note is you should never concatenate data into SQL, because it opens the possibility of SqlInjection. There is maybe 3 points that you are open to that. Even if you think the data is safe, you should still be using prepared statements. You never know when code will need to be changed and by not doing it, later you may change where this data comes from and allow user input into those variables. Sometimes, even, this data could be set in a place far removed from your query.

Just my thoughts.

• joanb: Thanks for the input! I am learning about prepared statements and starting to implement those in my projects now. Thank you again!

Answer 2

It's a very weird database schema in the first place.

There should be never a thing like comma-separated values in one cell. Learn the database normalization and then create a link table wghich can be used to get all your records in a single query with JOIN.

There should be never a thing like multiple tables of the same structure. there should be only one table with a field to distinguish the kind of domain.

• joanb: OK I get the one table. There are 7 domains so table can go this table name -> lookup_descriptions, columns -> id, item_id, description, domain; then lookup based on domain. the dropdown is a multi select. How do I go about not storing comma-separate values