help with join or nested query?
Moderator: General Moderators
help with join or nested query?
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
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
While waiting for the McGruff Links...
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.
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'Hope it helped.
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.
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.
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.
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.
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?
Link table results are $row[5] - $row[7]
Thanks!
Phil
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>');
}Thanks!
Phil
Well, perhaps this might help some more...
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.
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
)
)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.
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.
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.
Thanks again JAM and McGruff.
I managed to get what I want, but I ended up droping the join completely:
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
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]);
}
?>Phil
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.
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.
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.
$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.