help with join or nested query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

PhilPond
Forum Newbie
Posts: 7
Joined: Sat Oct 11, 2003 3:00 pm

help with join or nested query?

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

McGruff wrote:...and each link will have many users...
Please explain.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
PhilPond
Forum Newbie
Posts: 7
Joined: Sat Oct 11, 2003 3:00 pm

Post by PhilPond »

:oops: 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
PhilPond
Forum Newbie
Posts: 7
Joined: Sat Oct 11, 2003 3:00 pm

Post 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&#1111;0] . '</td><td>' . $row&#1111;1] .
          '</td><td>' . $row&#1111;2] . '</td><td>' . $row&#1111;3] .
          '</td><td>' . $row&#1111;4] . '</td><td>' . $row&#1111;5] .
          '</td><td>' . $row&#1111;6] . '</td><td>' . $row&#1111;7] .
          '</td></tr>');
  &#125;
Link table results are $row[5] - $row[7]

Thanks!

Phil
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
(
    &#1111;JAM] =&gt; Array
        (
            &#1111;0] =&gt; http://example.com/1
            &#1111;1] =&gt; http://example.com/2
            &#1111;2] =&gt; http://example.com/3
        )
    &#1111;PHPDN] =&gt; Array
        (
            &#1111;0] =&gt; http://example.com/4
            &#1111;1] =&gt; 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. ;)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
PhilPond
Forum Newbie
Posts: 7
Joined: Sat Oct 11, 2003 3:00 pm

Post 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) &#123;
    $result_links = mysql_query("select gallery_links.* from gallery_links WHERE id = $uid");
      while($linkrow = mysql_fetch_array($result_links)) &#123;
        echo '<a href="' . $linkrow&#1111;2] . '" target = "_blank">' . $linkrow&#1111;3] . '</a><br>';
      &#125;
  &#125;

  while($row = mysql_fetch_array($result_users)) &#123;
         echo ('<b>' . $row&#1111;2] . '</b><br>');
         getLinks($row&#1111;0]);
  &#125;

  ?>
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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
PhilPond
Forum Newbie
Posts: 7
Joined: Sat Oct 11, 2003 3:00 pm

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply