select a category by name instead of id

[AndrewSimm]

currently I am using the following code to show the news for an entry in vbdynamics. I currently have to enter the category id into a custom field on the entry to get it to work. The title of the entry will be the exact same as the title of the category and I am looking to knock out a stop. Here is my code. I will have to change $entry['field97'] to $entry['title'] The area betweeen the comment is where I need to make the changes, I think.

PHP Code:

ob_start();  

// Set Your Section ID Here 
global $entry; 
$section1 = '242'; 
$category1 = $entry['field97']; 
// Set The Number of Articles To Display 
$limit1 = '9'; 

if($category1 == '')  
{ 
echo "<span style='margin-left:8px;'>There is no news for this recruit</span>"; 
} 
else 
{ 
$articlegrab1 = vB::$db->query_read(" 
SELECT ".TABLE_PREFIX."cms_nodeinfo.nodeid 
     , ".TABLE_PREFIX."cms_nodeinfo.title 
     , ".TABLE_PREFIX."cms_article.previewimage 
     , ".TABLE_PREFIX."cms_article.previewtext 
     , ".TABLE_PREFIX."cms_article.pagetext 
     , ".TABLE_PREFIX."cms_article.contentid 
     , ".TABLE_PREFIX."cms_node.nodeid 
     , ".TABLE_PREFIX."cms_node.parentnode 
     , ".TABLE_PREFIX."cms_node.contentid 
     , ".TABLE_PREFIX."cms_node.url 
     , ".TABLE_PREFIX."cms_node.publishdate 
     , ".TABLE_PREFIX."cms_category.category 
     , ".TABLE_PREFIX."cms_category.categoryid  
    , ".TABLE_PREFIX."cms_node.setpublish 
  FROM ".TABLE_PREFIX."cms_node 
INNER 
  JOIN ".TABLE_PREFIX."cms_article 
    ON ".TABLE_PREFIX."cms_article.contentid = ".TABLE_PREFIX."cms_node.contentid 
INNER 
  JOIN ".TABLE_PREFIX."cms_nodeinfo 
    ON ".TABLE_PREFIX."cms_nodeinfo.nodeid = ".TABLE_PREFIX."cms_node.nodeid 
INNER 
  JOIN ".TABLE_PREFIX."cms_nodecategory 
    ON ".TABLE_PREFIX."cms_nodecategory.nodeid = ".TABLE_PREFIX."cms_node.nodeid 
INNER
// LOOK HERE FOR WHAT I NEED TO CHANGE 
   JOIN ".TABLE_PREFIX."cms_category 
    ON ".TABLE_PREFIX."cms_category.categoryid = ".TABLE_PREFIX."cms_nodecategory.categoryid 
 WHERE ".TABLE_PREFIX."cms_category.categoryid IN ($category1) 
   AND (".TABLE_PREFIX."cms_node.setpublish != 0) 

// I DONT THINK YOU HAVE TO LOOK PAST HERE
ORDER  
    BY ".TABLE_PREFIX."cms_node.publishdate DESC LIMIT $limit1 
"); 




while($articleinfo1 = vB::$db->fetch_array($articlegrab1)) { 

    $title1 = $articleinfo1['title']; 
    $text1= $articleinfo1['previewtext']; 
    $nodeid1 = $articleinfo1['nodeid']; 
    $url1 = $articleinfo1['url']; 
    $pagetext1 = $articleinfo1['pagetext'];   
    $unixdate1 = $articleinfo1['publishdate'];  
    $date1 = date("F j, Y, g:i a", $unixdate1);   
    $fulltext1 = strip_bbcode($fulltext1); 
    $text1 = preg_replace('/\[ATTACH\=CONFIG\]\d\d\[\/ATTACH\]/', '', $text); 
    $text1 = strip_bbcode($text1); 
    $pagetext1 = strip_bbcode($pagetext1); 


    if($text1 == '') $pagetext1 = substr($pagetext1, 0, 120); 



    $centerhtml1 .="<div style='padding-bottom:10px;margin-bottom:10px; margin-left:8px; margin-right:8px;border-bottom:1px solid #cccccc''>"; 
    $centerhtml1 .="<b><a href='/content.php?".$nodeid1."-".$url1."'>".$title1."</a></b>"; 
    $centerhtml1 .="<p><span style='color:#6f6f6f'>".$date1."</span></p>"; 
    $centerhtml1 .="<p>".$pagetext1."...</p></div>"; 

     
} 
vB::$db->free_result($articlegrab1); 
$tabhtml1 = "<div><ul>".$tabhtml1."</ul>"; 
echo $tabhtml1.$centerhtml1."</div>"; 
//  

} 
$recruitnews = ob_get_contents();  
ob_end_clean();   
vB_Template::preRegister('ADV_DYNA_SHOWENTRY',array('recruitnews' => $recruitnews)); 


[kh99]

If I understand what you want to do, I think you would want to change the "WHERE" line to:

 WHERE ".TABLE_PREFIX."cms_category.category IN ('$category1')

and since you really should use escape_string on a string in case it contains any special characters, change the line that sets $category 1 to

$category1 = vB::$db->escape_string($entry['title']);

One other thing, it looks like the original code is written to allow field97 to be a comma separated list of category ids, and with the modified code you can only have one title.

[AndrewSimm]

I get
Warning: mysqli_query() [function.mysqli-query]: (42000/1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Yearby) AND (cms_node.setpublish != 0) ORDER BY cms_node.publishda' at line 28 in [path]/includes/class_core.php on line 1391

The title and category are 2 works with a space and I think that is causing the issue.

Thoughts?

--------------- Added [DATE]1370565728[/DATE] at [TIME]1370565728[/TIME] ---------------

oops nevermind it works!

You are the man!