joining two tables to get a value

[bradkenyon]

never joined two tables before.

 

i am looking to get 'fname' from table 'd_media', to add to $attachment

 

tables 'd_media' and 'library_docs' are related by column 'file_id' within table 'library_docs', which matches the 'id' in table 'd_media'

 

$docid = $_GET['id'];
$query = "SELECT id, id FROM d_media, library_docs WHERE id = $docid";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))
{
	$attachment = dirname(__FILE__)."/../../uploads/documents/"$row['fname'];
    	//print $attachment;
}			

 

to sum it up, it takes the id passed in, looks the id up in the library_docs table, then grabs the file_id from that table, then goes to d_media table w/ that file_id and grabs fname from that file_id, which is called id in d_media table.

 

thanks in advance!

 

 

[roopurt18]

A few items you need to know.

First:

SELECT id, id 

You are only going to get a single `id` column back in your result set since one will overwrite the other.  You need to alias one of them with something like:

SELECT `id`, `id` AS `theAlias`

 

Second:

FROM d_media, library_docs

This is an implicit INNER JOIN, however do not use this syntax.  Use explicit INNER JOINs instead and it also helps to alias the table.  You also have to specify which field to join the tables on:

FROM `users` u /* u is the alias */
INNER JOIN `user_profiles` p ON u.`id`=p.`user_id` /* specify which fields join the tables */

 

So your query might look like:

SELECT
  m.`fname`
FROM `d_media` m
INNER JOIN `library_docs` d ON d.`file_id`=m.`id`
WHERE d.`id`={$docid}

 

There are some examples of JOINs in the MySQL documentation, which you should take a look at:

http://dev.mysql.com/doc/refman/5.0/en/join.html

[bradkenyon]

stuck at this:

"SELECT *,library_docs.id as lid FROM library_docs,d_media WHERE d_media.id = library_docs.file_id AND library_docs.id = $docid";

 

 

[roopurt18]

That looks like it should work.  Try echo'ing the query and check it for syntax errors.  Or echo it and run it directly in phpMyAdmin.  In your OP you have a die(mysql_error()); does that print anything?

 

Also, a word of caution.  I went out of my way for good reason to bring up the difference between (what I called) implicit and explicit INNER JOIN syntax.  Use the explicit version.

[bradkenyon]

thanks, that works.

 

another question.

 

it seems to take 5-10 seconds to run this script, is there a way to make it run through the loop quicker, or just do it without a loop and go right to that row in the table and grab the fname without having to loop thru??

 

$docid = $_GET['id'];
$query = "SELECT *,library_docs.id as lid FROM library_docs,d_media WHERE d_media.id = library_docs.file_id AND library_docs.id=".$docid."";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))
{
	$attachment = dirname(__FILE__)."/../../uploads/documents/".$row['fname'];

}		

 

thanks!

[roopurt18]

I'm not sure what you mean.  The WHERE clause is supposed to be built so that the DB only returns records you plan to use.  If you're having to loop through additional unneeded records, then modify your WHERE clause to return only those you need.

 

And you still haven't fixed your JOIN syntax!

[bradkenyon]

i think it does the trick.

 

not sure what you mean to modify my join syntax, kind of new at this.

 

i was thinking you could find fname without having to loop thru the table.

[roopurt18]

Modify your query so that it follows this format:

SELECT
  m.`fname`
FROM `d_media` m
INNER JOIN `library_docs` d ON d.`file_id`=m.`id`
WHERE d.`id`={$docid}

 

Do you see how the query has INNER JOIN written in it, i.e. I am explicitly telling MySQL that this is an INNER JOIN.

 

This is different from the implicit method in which you comma-delimit the tables in the FROM clause:

/* DO NOT DO THIS - THIS IS IMPLICIT */
FROM table1, table2, table3, table4

 

i was thinking you could find fname without having to loop thru the table.

You can if you use the proper query.

 

Change:

while($row = mysql_fetch_array($result))
{
	$attachment = dirname(__FILE__)."/../../uploads/documents/".$row['fname'];

}	

 

to:

while($row = mysql_fetch_array($result))
{
                echo '<pre style="text-align: left;">' . print_r($row, true) . '</pre>';
	$attachment = dirname(__FILE__)."/../../uploads/documents/".$row['fname'];

}	

 

This should dump out all of the data received.  Copy and paste that data here and then tell me which record you are interested in retrieving.

[eddierosenthal]

select d.fname from d_media d, library_doc L where

d.fileid = L.fileid

 

 

should return fname in one swoop without further ado

[bradkenyon]

using this now:

$query = "SELECT
	m.`fname`
	FROM `d_media` m
	INNER JOIN `library_docs` d ON d.`file_id`=m.`id`
	WHERE d.`id`={$docid}";

 

runs much quicker.

 

thank you.