FOREACH sql table entry get result from another table

Question

I'm working with vBulletin forums and I'm trying to get results from one table if it exists in another table.

This is the creation of the table the data is from

$db->query("ALTER TABLE " . TABLE_PREFIX . "drc_settings ADD thread_ids varchar(50) NOT NULL default '0'");

I have created an input field meant for comma separated numbers (thread ID's). That submits to this column, so a result in this column would look like:

1,46,23

Another column in this table is threadid, this is the thread the post was submitted from.

So this is what we have in the drc_settings table

threadid  thread_ids
   5       1,46,23

Now another table that exists is thread, in the thread table we have:

threadid  title
   46     Some Title

What I need to know is, how do I get the title from thread if thread_ids or drc_settings match threadid of thread?

This is where I'm at with it, but I'm a little out of my element on this one.

$res = $post['thread_ids'];
$res = explode (',', $res);
$post['drcid'] = '<ul>';

foreach ( $res as $ret ) {
$db->query("
  SELECT
  thread.threadid, drc.threadid AS threadid,
  thread.threadid, thread.title AS threadtitle
  FROM " . TABLE_PREFIX . "thread AS thread
  LEFT JOIN " . TABLE_PREFIX . "drc_settings AS drc ON (drc.threadid=thread.threadid)
  WHERE thread.threadid VAR (" . $ret . ")
");  
$post['drcid'] .= '<li><a href="showthread.php?t=' . $ret . '">'.   $thread[threadtitle] .'</a></li>';
}
$post['drcid'] .= '</ul>';

If I remove the query from the foreach, I am able to get $res to return 1 46 23 which is stumping me why the query isn't working properly.

The end result will be a list of links to each thread specified in the input, I.E:

• Some Title
• Another thread
• Thread Title

This is the error returned with the current setup I have

Database error in vBulletin 3.8.9:

Invalid SQL:

                SELECT
        thread.threadid, drc.threadid AS threadid,
                    thread.threadid, thread.title AS threadtitle
                FROM thread AS thread
                LEFT JOIN drc_settings AS drc ON (drc.threadid=thread.threadid)
                WHERE thread.threadid VAR (1);

MySQL Error   : You have an error in your SQL syntax;

Answer 1

Using $ret (the parent threadid)...

$query="SELECT B.threadid,B.title 
    FROM drc.settings A 
    LEFT JOIN thread B 
    ON FIND_IN_SET(B.threadid,A.threadids) 
    WHERE A.threadid={$ret};"

Then loop through the resultset as needed to build the $post array.