SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Dealing with multiple tables

    Hi, I apologise if this has already been covered, but I couldn't find anything that helped me when I did a search.

    Anyway, I have four tables:

    users
    id
    name
    email
    group_id

    group
    id
    name

    categories
    id
    name

    user_categories
    user_id
    category_id

    What I want to do is display users of a certain category (users belong to multiple categories) and group (Can only belong to one group) on a page. Unfortunately dealing with related information spread across multiple tables is slightly beyond me. Any help that can be provided would be greatly appreciated.

  2. #2
    SitePoint Evangelist Jhorra's Avatar
    Join Date
    Sep 2004
    Location
    Phoenix, Az
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You want what's called a join to link the tables together. You would end up with something like this:
    Code:
    $sql = "SELECT 
    u.name c.name 
    FROM
    users AS u
    INNER JOIN user_categories AS uc ON
    u.id = uc.user_id
    INNER JOIN categories AS c ON
    uc.category_id = c.id
    WHERE c.id = 'some category'";

  3. #3
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! It's printing out the correct number of records but doesn't seem to be displaying the users name. All I see is:


    Any ideas? This is the code I'm using.

    PHP Code:
    <?php
                
    $userlist 
    = @mysql_query(
    "SELECT u.name FROM users AS u INNER JOIN user_category AS uc ON u.id = uc.user_id INNER JOIN category AS c ON uc.category_id = c.id WHERE c.id='3' ORDER BY u.name ASC");
    if (!
    $userlist) {
    exit (
    '<p>Error performing query: ' mysql_error() . '</p>');
    }
                
    while (
    $user mysql_fetch_array($userlist)) {
    $user_name $user['u.name'];
                
    echo 
    "<p><span class='bullet'>&curren;</span> $user_name<br /></p>";
                
    }
                
    ?>

  4. #4
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Change this line:
    PHP Code:
    $user_name $user['u.name']; 
    To

    PHP Code:
    $user_name $user['name']; 
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  5. #5
    SitePoint Evangelist Jhorra's Avatar
    Join Date
    Sep 2004
    Location
    Phoenix, Az
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, you are basically giving the table an alias when you do users AS u

    when you give a table an alias, you have to specify which table you want the field from in your select statement. The results you get from your query only carry the field name though, not the table alias.

    If you are pulling two fields that have the same name, but different tables you have to alias the field like this

    SELECT c.name AS user_name


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •