Page 1 of 2
help with join or nested query?
Posted: Sat Oct 11, 2003 3:00 pm
by PhilPond
Hello, all.
I have 2 tables, one for users and one for links for a portfolio type application. I am trying to select and display all of the data from each table (except the ids). the user table contains the following fields:
ID, Name, Photo, Email
...and the link table contains:
LinkID, ID, URL
The common field is ID, where the links table will point to the appropriate user. Users may have many Links. I am afraid of joins and am not sure how to create a nested query (if such a thing exsists?).
I would appreciate any help or direction.
Thanks,
Phil Pond
Posted: Sat Oct 11, 2003 5:02 pm
by McGruff
I'm working away from home right now so I don't have my bookmarks. If you like, pm me so I don't forget your question: when I get home I'll post a couple of links re JOIN queries & relational database design.
It's essential to learn how to do JOIN queries - but they really aren't hard.
Posted: Sat Oct 11, 2003 5:58 pm
by JAM
While waiting for the McGruff Links...
Code: Select all
select
user_table.*, link_table.*
from
user_table
inner join link_table on user_table.id = link_table.id
where
user_table.id = '1'
Here we are joining the tables using their id's. We are selecting everything from both tables, but that doesn't matter as you yourself choose what to show the public (thinking of the ID issue in the original post).
Hope it helped.
Posted: Sat Oct 11, 2003 6:24 pm
by McGruff
If each user will have many links and each link will have many users, that's a many-to-many relationship which requires a third join table.
A join table simply stores a row id for each of the linked tables - there's a good O'Reilly tutorial on this and other relational database matters which I'll post later if you remind me.
Posted: Sat Oct 11, 2003 6:30 pm
by JAM
McGruff wrote:...and each link will have many users...
Please explain.
Posted: Sat Oct 11, 2003 9:19 pm
by McGruff
Let's say Jo has scrofula, scabies and leprosy. Jenny has acne, piles and scabies. In the patients table, users can have any number of diseases. Diseases can also have any number of patients - there are two patients with scabies here.
The join table would have two cols: patient_id and disease_id.
Actually on reading the first post again it sounds like portfolio links are not in fact shared by other users ie this would be a one to many relationship where users would be a lookup table for the links table. That means a two-table JOIN as you have suggested.
A many to many relationship just adds another table to the join.
Posted: Sat Oct 11, 2003 10:29 pm
by PhilPond

Sorry guys, I wrote that down backwards. Each -user- will have many links.
Yes, it will be a one-to-many link. Thanks McGruff and Jam. I will try out that code.
Phil
Posted: Sun Oct 12, 2003 6:17 am
by JAM
No problems. I hope you understand it enough to be able to use it the next time you need something similiar.
McGruff:
Just making sure it wasn't me that misread it. But in fact, it's not impossible that two users share the same link so it might still be interesting.
Posted: Sun Oct 12, 2003 5:18 pm
by PhilPond
Thanks to your help it is now working! -- But Not quite as I had expected...
MySQL returns a full recordset for each link that is found. But what I really want is a list of links for each user. Is this a problem with my control stucture?
Code: Select all
while($row = mysql_fetch_array($result)) {
echo('<tr><td>' . $rowї0] . '</td><td>' . $rowї1] .
'</td><td>' . $rowї2] . '</td><td>' . $rowї3] .
'</td><td>' . $rowї4] . '</td><td>' . $rowї5] .
'</td><td>' . $rowї6] . '</td><td>' . $rowї7] .
'</td></tr>');
}
Link table results are $row[5] - $row[7]
Thanks!
Phil
Posted: Sun Oct 12, 2003 5:29 pm
by JAM
Well, perhaps this might help some more...
Code: Select all
// I tested using similiar data...
$result = mysql_query("select * from test");
$array = array();
while ($row = mysql_fetch_array($result)) {
$array[$row[0]][] = $row[1];
}
print_r($array);
Code: Select all
Outputs:
Array
(
їJAM] => Array
(
ї0] => http://example.com/1
ї1] => http://example.com/2
ї2] => http://example.com/3
)
їPHPDN] => Array
(
ї0] => http://example.com/4
ї1] => http://example.com/5
)
)
Explaining...
By adding using the name (here $row[0]) I can fill an array with all url he/she has stored (and more of course).
If you know something about arrays you will likely see what I made and what to do with it, if not we can elaborate further.

Posted: Sun Oct 12, 2003 7:08 pm
by McGruff
Promised db links:
http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf
http://www.w3schools.com/sql/sql_join.asp
http://www.devshed.com/Server_Side/MySQL
viewtopic.php?t=12234
http://asia.cnet.com/itmanager/netadmin ... 301,00.htm
On a side issue, it's better not to mix up php and html. Instead, you can define a bunch of vars in a php script and then include an html file where the vars are echo'd out (or use a template engine which does pretty much the same thing with some custom placeholder tags & a bit of regexing). Separate html files helps to keep things clearer and allows the html files to be edited in a wsiwyg editor. Html designers can then do their thing without having to touch the php.
With lists, like this, you could build an array and apply a template later, or include the template in each "while" row and use output buffering to create a single $list var containing a block of html code. The former is probably better from a strict n-tier point of view.
Posted: Sun Oct 12, 2003 7:24 pm
by PhilPond
Thanks again JAM and McGruff.
I managed to get what I want, but I ended up droping the join completely:
Code: Select all
<?php
$connect = mysql_connect($host,$user,$pass);
$dbconn = mysql_select_db($db, $connect);
$result_users = mysql_query("select gallery_people.* from gallery_people");
function getLinks($uid) {
$result_links = mysql_query("select gallery_links.* from gallery_links WHERE id = $uid");
while($linkrow = mysql_fetch_array($result_links)) {
echo '<a href="' . $linkrowї2] . '" target = "_blank">' . $linkrowї3] . '</a><br>';
}
}
while($row = mysql_fetch_array($result_users)) {
echo ('<b>' . $rowї2] . '</b><br>');
getLinks($rowї0]);
}
?>
I have a function that queries for the links based on the userid. I am going to change the function so that it returns an array instead of printing out the actual links. My only concern here is that it may not be very efficient..
Phil
Posted: Mon Oct 13, 2003 5:29 am
by JAM
If it works, then it's good.
Efficiency...
Selecting all people, then selecting link_id's based on the responce, will mean: 100 users = 100 queries, 1001 users = 1001 queries etc.
Putting it all in an array, 1 query.
Then think that there is 15 visitors to the site. Do the math, and *15 to the above...
The problems choosing between letting php and mysql handle the data is common. But have in mind, that you are not only using one of them to actually 'do' something. MySQL also needs a connection to the database, a connection that is using resources.
Posted: Mon Oct 13, 2003 11:52 am
by PhilPond
I guess the only way around it is to set up some sort of paging system.
On the other side of the coin, if we had 1001 users * 10 links = array[10000] * 10 users -- that sounds link a lot of memory.
Phil
Posted: Mon Oct 13, 2003 5:47 pm
by JAM
Your right. It does need resources. But lets look at your example above...
$results_users is still a variable that holds 1000's of users of data. You can view it (Resource ID#X-msg) but, still it contains alot of memory consuming data.
$result_links then gets the actual links for the users. Same as above, contains 10xdata a user.
And on top of that, there is 1 (all users) + 1000 (links*users) calls to the database...
If you really are serious about pro's or con's in the code you produce (or we), benchmark it and see what you get as results. It varies so much from code to code even if it's similiar. But, do you really need to worry about this? Are you really trying to show 1000*10 links on a page? Nah...
Good luck.