Jump to content

MySQL Query Return Category Name Instead of #


GeekyGirl

Recommended Posts

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?

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.