MySQL Query Return Category Name Instead of #

[GeekyGirl]

I Have 2 MySQL tables of interest here. 

Table 1: categories

ID

name

 

Table 2: items

ID

category (this relates to the category ID in Table 1)

title

 

I want my table on my webpage to show the category name from Table 1 instead of the category number in table 2.

 

 

Currently my code looks like this:

<table summary="" class="white" border="0" cellpadding="3" cellspacing="0" width="100%">					<tbody>						<tr class="headerBackground">							<td class="inspirationHeader">{$items[k].ID} {$items[k].category}<br/> {$items[k].title}</td>							

 

So the webpage looks like this:

1 85

quote

 

And I want it to look like this:

1 Learning

quote

 

(where category #85 has the name Learning)

 

 

So how do I incorporate a MySQL query here to look up the value from Table 2 and return the corresponding name in Table 1?

[cmgmyr]

Try something like this:

$sql = "SELECT t1.ID, t2.category FROM table_1 t1 INNER JOIN table_2 t2 ON (t1.ID = t2.ID) WHERE t1.ID = $id";

[GeekyGirl]

Just need a point of clarification...

 

My tables are named categories (table 1) and items (table 2) so I am a bit confused with your references to t1, t2, table_1 and table_2.

 

Would the statement look like this using my table names?

 

$sql = "SELECT t1.ID, t2.category FROM items t1 INNER JOIN categories t2 ON (t1.ID = t2.ID) WHERE t1.ID = $id";

 

Also do I need to enclose the statement in {} in order to work in the .tpl file?

 

[darkfreaks]

 

you need it in a php file then call it using smarty functions.

 

http://devzone.zend.com/article/1238-PHP-Templating-with-Smarty

[GeekyGirl]

I don't know if this helps but I was already working in the smarty template file.  I also have the php file if I need to do something with that instead.

 

A part of the php file that corresponds to the same set of data is seen below:

$que = 'SELECT {ITEM} FROM '.dext('items').' WHERE confirmed=1 ';if(isset($search['search_key'])) {	$search['search_key'] = addslashes($search['search_key']);}if(count($search)!=0&&$search['submitted']==1) {	$s_que = array();	if($search['search_cat']) {//category is definged where to search		$s_que[] = 'category="'.$cat.'"';	}		if($search['search_match_type']=='exact') {// no need to explode by word		$sword = trim($search['search_key']);		switch($search['search_type']) {			case 'title':				$s_que[] = '( title LIKE "%'.$sword.'%" )';			break;			case 'text':				$s_que[] = '( text LIKE "%'.$sword.'%" )';			break;			case 'author':				$s_que[] = '( author LIKE "%'.$sword.'%" )';			break;			default:				$s_que[] = '( text LIKE "%'.$sword.'%" )';				$s_que[count($s_que)-1] .= ' OR ( author LIKE "%'.$sword.'%" )';				$s_que[count($s_que)-1] .= ' OR ( title LIKE "%'.$sword.'%" )';			break;		}	}else {		$swords = explode(" ",$search['search_key']);		if(count($swords)!=0) {			$connector = ($search['search_match_type']=='any')?'OR':'AND';			$sphrase = '';			foreach($swords as $sword) {				if(trim($sword)) {					switch($search['search_type']) {						case 'title':							$sphrase .= ' ( title LIKE "%'.$sword.'%" ) '.$connector;						break;						case 'text':							$sphrase .= ' ( text LIKE "%'.$sword.'%" ) '.$connector;						break;						case 'author':							$sphrase .= ' ( author LIKE "%'.$sword.'%" ) '.$connector;						break;						default:							$sphrase .= ' ( text LIKE "%'.$sword.'%" OR author LIKE "%'.$sword.'%" OR title LIKE "%'.$sword.'%") '.$connector;						break;					}        					}			}			if(trim($sphrase)!="") {				$s_que[] = str_replace($connector.'|',' ',$sphrase.'|');			}		}	}	$s_que = trim(implode(') AND (',$s_que));	if(trim($s_que)!="") {		$que .= ' AND ('.$s_que.')';	}}else {	if($cat!=0) {		$que .= ' AND category="'.$cat.'"';	}}

 

The category information is something I am trying to add to the page since it wasn't showing at all.  I haven't worked with the php file at all yet but was able to have the category show up simply by editing the .tpl file.

 

So what do I need to do in order to have the category name show up?

 

By the way, you can see the active web page by going to http://www.scrapuniversity.com/Library/Inspirations/index.php?cmd=1

[darkfreaks]

try using this tutorial as a reference:

 

 

http://www.phpbuilder.com/columns/ben_robinson20070413.php3

[darkfreaks]

so for example you would create a php file with a function that returns the SQL selection to the TPL file.

[GeekyGirl]

I looked at the example you posted but it still doesn't help me.

 

First off, I can't get the MySQL query to work by itself let alone integrated within the template.  I can easily select the category number directly from the items table but can't seem to translate that to the actual category name found in the cetegories table in the SELECT command.

 

Second, if there is already an existing php file that corresponds to the smarty template file, don't I need to integrate the code into the existing file?

[tippy_102]

You need a JOIN...

 

SELECT * FROM items, categories WHERE items.category = category.ID