Ad:

PHP Discussion Thread
Unsolved
Views: 814

Feb 11th, 2009

View Indexed Field in another table

Expand Post »

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)
$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'];
$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

PHP Syntax (Toggle Plain Text)
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";
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.

Similar Threads

comment function in PHP
Urgent question about Updates in C#

rickarro

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

Feb 11th, 2009

Re: View Indexed Field in another table

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

PHP Syntax (Toggle Plain Text)

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.

khess

Reputation Points: 94

Solved Threads: 8

Practically a Master Poster

Offline

638 posts
since Apr 2008

Permalink

Feb 11th, 2009

Re: View Indexed Field in another table

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)
$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'];
$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

PHP Syntax (Toggle Plain Text)
$query = "SELECT assets.*, assettype.* FROM assets WHERE assets.tag = "$tag" AND assettype.typeid = "$typeid"
 
$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?

rickarro

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

Feb 11th, 2009

Re: View Indexed Field in another table

You need to use a join I think

PHP Syntax (Toggle Plain Text)
$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);
 
$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);

TommyBs

Reputation Points: 11

Solved Threads: 11

Junior Poster in Training

Offline

61 posts
since Mar 2008

Permalink

Feb 11th, 2009

Re: View Indexed Field in another table

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

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

Feb 11th, 2009

Re: View Indexed Field in another table

Ok, this is what I tried.

PHP Syntax (Toggle Plain Text)
$query = @mysql_query("SELECT assets.*, assettype.* FROM assets AS a INNER JOIN assettype AS at ON assets.typeid = assettype.typeid WHERE assets.tag = $tag");
$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

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

Feb 13th, 2009

Re: View Indexed Field in another table

Does anyone have any ideas?

rickarro

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

Feb 13th, 2009

Re: View Indexed Field in another table

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

TommyBs

Reputation Points: 11

Solved Threads: 11

Junior Poster in Training

Offline

61 posts
since Mar 2008

Permalink

Feb 18th, 2009

Re: View Indexed Field in another table

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

PHP Syntax (Toggle Plain Text)

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

rickarro

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Mp3 Uploader / Player

Next Thread in PHP Forum Timeline: paypal sanbox help

DaniWeb Message

Cancel Changes

User Name
Password