bradkenyon Posted March 7, 2008 Share Posted March 7, 2008 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! Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted March 7, 2008 Share Posted March 7, 2008 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 Quote Link to comment Share on other sites More sharing options...
bradkenyon Posted March 7, 2008 Author Share Posted March 7, 2008 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"; Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted March 7, 2008 Share Posted March 7, 2008 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. Quote Link to comment Share on other sites More sharing options...
bradkenyon Posted March 7, 2008 Author Share Posted March 7, 2008 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! Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted March 7, 2008 Share Posted March 7, 2008 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! Quote Link to comment Share on other sites More sharing options...
bradkenyon Posted March 7, 2008 Author Share Posted March 7, 2008 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. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted March 7, 2008 Share Posted March 7, 2008 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. Quote Link to comment Share on other sites More sharing options...
eddierosenthal Posted March 7, 2008 Share Posted March 7, 2008 select d.fname from d_media d, library_doc L where d.fileid = L.fileid should return fname in one swoop without further ado Quote Link to comment Share on other sites More sharing options...
bradkenyon Posted March 7, 2008 Author Share Posted March 7, 2008 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.