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.
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',arra y('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 1370565728 at 1370565728 ---------------

oops nevermind it works!

You are the man!