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.

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.

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?

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);

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.

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"

Does anyone have any ideas?

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.