PHP RSS PHP RSS

View Indexed Field in another table

Reply

•
•
Join Date: Jan 2008
Posts: 100
Reputation: rickarro is an unknown quantity at this point 
Solved Threads: 1
rickarro rickarro is offline Offline
Junior Poster

View Indexed Field in another table

 
0
  #1
Feb 11th, 2009
I'm having trouble figuring out how to display data from another indexed table. What I have is one table (assets) with a majority of my data in it. I use a simple select statement to display the data. Within that data is a field of "typeid" which is an indexed field to my "assettype" table. With my simple select statement I see only the indexed field "assets.typeid". I want to see the "assettype.type" field from the assettype table.

In a nutshell, i want to change the typeid of "1" to be "printer", but "printer" is in a different table that is indexed by "typeid" number. Here is a snippet, hope it helps.
PHP Syntax (Toggle Plain Text) 
  1. $query = "SELECT * FROM assets WHERE tag = $tag";
  2. $result = mysql_query($query) or die('Sorry, could not find tag requested');
  3.  
  4. $row = mysql_fetch_array($result, MYSQL_ASSOC) or die('No records retrieved');
  5.  
  6. $tag=$row['tag'];
  7. $typeid = $row['typeid'];
Further down the script
PHP Syntax (Toggle Plain Text) 
  1. echo "<h3><u>Tag#</u><font color=\"#663300\" size=\"2\"> $tag</font></h3>"; 
  2. echo "<h3><u>Type</u><font color=\"#663300\" size=\"2\"> $typeid</font></h3>\n";
Where $typeid is echoed, i want to echo $type instead, but it is in a different table that is indexed with $typeid.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 470
Reputation: khess is on a distinguished road 
Solved Threads: 8
Staff Writer
khess's Avatar
khess khess is online now Online
Staff Writer

Re: View Indexed Field in another table

 
0
  #2
Feb 11th, 2009
You'll have to query both tables with a WHERE clause and set the indexes equal to each other:

PHP Syntax (Toggle Plain Text) 
  1. SELECT table1.blah, table2.blahz FROM table1, table2 WHERE table1.tag = table2.tag1;

Sorry, I didn't pay attention to your column names, etc. but you get the idea.
Ken Hess
Linux DaniWeb Staff Writer/Columnist/Host
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 100
Reputation: rickarro is an unknown quantity at this point 
Solved Threads: 1
rickarro rickarro is offline Offline
Junior Poster

Re: View Indexed Field in another table

 
0
  #3
Feb 11th, 2009
Thank you for the reply. I did fail to leave out some information that, as I see, is now pertinant. This page is coming from another page where its using $tag as the id in a $_GET function.
PHP Syntax (Toggle Plain Text) 
  1. $tag = $_GET['id'];
  2.  
  3. $query = "SELECT * FROM assets WHERE tag = $tag";
  4. $result = mysql_query($query) or die('Sorry, could not find tag requested');
  5.  
  6. $row = mysql_fetch_array($result, MYSQL_ASSOC) or die('No records retrieved');
  7.  
  8. $tag=$row['tag'];
  9. $typeid = $row['typeid'];

So would i then have to alter my WHERE something like
PHP Syntax (Toggle Plain Text) 
  1. $query = "SELECT assets.*, assettype.* FROM assets WHERE assets.tag = "$tag" AND assettype.typeid = "$typeid"
  2.  

That didn't work but that's where my mind is going. Or create a $query2 and put them together somehow?
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2008
Posts: 61
Reputation: TommyBs is an unknown quantity at this point 
Solved Threads: 11
TommyBs's Avatar
TommyBs TommyBs is offline Offline
Junior Poster in Training

Re: View Indexed Field in another table

 
0
  #4
Feb 11th, 2009
You need to use a join I think

PHP Syntax (Toggle Plain Text) 
  1. $q = @mysql_query("SELECT a.tag, a.field2, at.assettype FROM assets AS a INNER JOIN assettype AS at
  2. ON a.assettypeid = at.assettypeid
  3.  WHERE a.tag = $tag);
  4.  
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 100
Reputation: rickarro is an unknown quantity at this point 
Solved Threads: 1
rickarro rickarro is offline Offline
Junior Poster

Re: View Indexed Field in another table

 
0
  #5
Feb 11th, 2009
Thanks TommyBs, I'll give it a try. Other work is pressing so I'll have to get back to this a little later, but really do appreciate the help.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 100
Reputation: rickarro is an unknown quantity at this point 
Solved Threads: 1
rickarro rickarro is offline Offline
Junior Poster

Re: View Indexed Field in another table

 
0
  #6
Feb 11th, 2009
Ok, this is what I tried.
PHP Syntax (Toggle Plain Text) 
  1. $query = @mysql_query("SELECT assets.*, assettype.* FROM assets AS a INNER JOIN assettype AS at ON assets.typeid = assettype.typeid WHERE assets.tag = $tag");

I have an "or die" on my $result = and recieved an error of "sorry, could not find tag requested"
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 100
Reputation: rickarro is an unknown quantity at this point 
Solved Threads: 1
rickarro rickarro is offline Offline
Junior Poster

Re: View Indexed Field in another table

 
0
  #7
Feb 13th, 2009
Does anyone have any ideas?
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2008
Posts: 61
Reputation: TommyBs is an unknown quantity at this point 
Solved Threads: 11
TommyBs's Avatar
TommyBs TommyBs is offline Offline
Junior Poster in Training

Re: View Indexed Field in another table

 
0
  #8
Feb 13th, 2009
Well doesn't that mean the tag you passed in doesn't exist?
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 100
Reputation: rickarro is an unknown quantity at this point 
Solved Threads: 1
rickarro rickarro is offline Offline
Junior Poster

Re: View Indexed Field in another table

 
0
  #9
Feb 18th, 2009
No, the tag exsists. If I use the original query
PHP Syntax (Toggle Plain Text) 
  1. $query = "SELECT * FROM assets WHERE tag = $tag";
The script continues to run and is successful. Only when I try to pull data from 2 different tables in this query is when I'm running into problems.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the PHP Forum
Thread Tools Search this Thread



ajax apache api array arrays beginner binary broken cache cakephp checkbox class cms code confirm cron curl customizableitems database date display dynamic echo email error external file files folder form forms forum function functions google header headmethod howtowriteathesis href htaccess html iframe image include insert integration ip java javascript joomla limit link login loop mail malfunction menu method mlm multiple mysql neutrality oop paypal pdf php phpmysql play problem query question radio random recursion regex remote root script search select server sessions sms soap source space sql syntax system table tutorial update upload url validator variable video web xml youtube
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC