View Indexed Field in another table

[rickarro]

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.

$query = "SELECT * FROM assets WHERE tag = $tag";
$result = mysql_query($query) or die('Sorry, could not find tag requested');

$row = mysql_fetch_array($result, MYSQL_ASSOC) or die('No records retrieved');

$tag=$row['tag'];
$typeid = $row['typeid'];

Further down the script

echo "<h3><u>Tag#</u><font color=\"#663300\" size=\"2\"> $tag</font></h3>"; 
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.

[khess]

You'll have to query both tables with a WHERE clause and set the indexes equal to each other:

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.

[rickarro]

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.

$tag = $_GET['id'];

$query = "SELECT * FROM assets WHERE tag = $tag";
$result = mysql_query($query) or die('Sorry, could not find tag requested');

$row = mysql_fetch_array($result, MYSQL_ASSOC) or die('No records retrieved');

$tag=$row['tag'];
$typeid = $row['typeid'];

So would i then have to alter my WHERE something like

$query = "SELECT assets.*, assettype.* FROM assets WHERE assets.tag = "$tag" AND assettype.typeid = "$typeid"

That didn't work but that's where my mind is going. Or create a $query2 and put them together somehow?

[TommyBs]

You need to use a join I think

$q = @mysql_query("SELECT a.tag, a.field2, at.assettype FROM assets AS a INNER JOIN assettype AS at
ON a.assettypeid = at.assettypeid
 WHERE a.tag = $tag);

[rickarro]

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.

[rickarro]

Ok, this is what I tried.

$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"

[rickarro]

Does anyone have any ideas?

[TommyBs]

Well doesn't that mean the tag you passed in doesn't exist?

[rickarro]

No, the tag exsists. If I use the original query

$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.