SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2001
    Location
    New York
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Improving Query use a Join?

    I've worked on a very short piece of code and am very glad to report it works! My only reservation is that it may use too many calls to the database and wonder if there is a more efficient way to accomplish the same. I'm still new at using joins, etc. and polishing off those programming skills.

    The skinny: My script allows users to upload files and put them into public folders/categories, e.g. a folder/category called "Letters" contains files like "welcome" and "thankyou" that may be uploaded by the user. This code is what pulls up to 4 files the user uploaded and the folders they were uploaded in as well as the last 4 folders the user

    Table: USERS contains fields user_id and user_name
    Table: FILES contains fields file_id, user_id, category_id, thumbnail
    Table: CATEGORIES contains fields category_id, category_name and last_file

    I have the user's user_name but I need to (1) query the USERS table to get the user's user_id attributed to the user_name to extract information from other tables, (2) query the FILES table the user_id to get the categories the user has posted in, and (3) query the CATEGORIES table to get the category_name of the categories the user has posted in and the last_file posted in that category and the thumbnail associated with that file for display.

    I think these loops might be a little too much for one page and was wondering if I overdid it. Help in pulling this together would be MUCH appreciated.


    $theusername = ($this->{PULLSUSERNAME}); // USERNAME of USER

    $useridquery = "SELECT user_id FROM users where user_name = '$theusername'";
    $theresult = @mysql_query ($useridquery);
    $theuserid = mysql_fetch_row($theresult);
    $theuserid = $theuserid[0]; // GETS USER ID OF THE USER'S PROFILE BEING VEIWED

    $foldernamequery = "SELECT category_name FROM categories where cat_id = '$theuserid'";
    $folderresult = @mysql_query ($foldernamequery);
    $thefoldername = mysql_fetch_row($folderresult);
    $thefoldername = $thefoldername[0]; // GETS THE CATEGORY NAMES OF ALL CATEGORIES A USER HAS POSTED A FILE IN

    $folderfilequery = "SELECT lastfile FROM categories where id = '$theuserid'";
    $fileresult = @mysql_query ($folderfilequery);
    $thefileresult[] = mysql_fetch_row($fileresult);
    $thefileresult = $thefileresult[0]; // GETS THE ENTRY IN THE LAST_FILE FIELD MEANING LAST FILE UPLOADED TO THE CATEGORY

    echo "<div class=\"blue-header\"><b>My folders</b>

    // GET LIST OF CATEGORY IDS WHICH ARE THE SAME ID AS THE USERID AS THE USER BEING VIEWED

    $categoriesresult = mysql_query("SELECT DISTINCT category FROM FILES WHERE user_id = '$theuserid'") or die(mysql_error());
    while($catsresultlist = mysql_fetch_array( $categoriesresult)) {

    // GET THE NAMES OF THE CATEGORY IDS YOU JUST PULLED AND THE LAST_FILE UPLOADED TO THAT CATEGORY

    $result = mysql_query("SELECT cat_id,category_name, last_file FROM categories WHERE id = '$catsresultlist[cat]' ORDER BY category_name ASC") or die(mysql_error());
    while($row = mysql_fetch_array( $result )) {

    // GET THE THUMBNAIL THAT IS CREATED AND ATTRIBUTED TO THE FILE/FOLDER AND DISPLAY IT

    $result3 = mysql_query("SELECT file_id,category,thumbnail FROM files WHERE cat = '$catsresultlist[cat]' LIMIT 1" ) or die(mysql_error());
    while($row3 = mysql_fetch_array( $result3 )) {
    print "<a href=\"http://www.mysite.com/showfile.php?file=" . $row3['id'] . "\"><img src=\"/file/data/" . $row3['cat'] . "/thumbs/" . $row3['thumgnail'] . "\" border=\"0\"></a>";

    }

    // PRINT OUT THE CATEGORY NAME UNDERNEATH

    print '<a href="/showfile.php?cat=' . $row['id'] . '">' . $row['category_name'] . '</a>
    My law forum and legal information website -- I also buy websites you may wish to sell

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i had a hard time understanding your php logic (i don't do php)

    there seemed to be a number apparently redundant queries

    does this help you get started?
    Code:
    SELECT categories.category_name
         , categories.lastfile 
      FROM users
    INNER
      JOIN categories
        ON categories.cat_id = users.user_id
     WHERE users.user_name = '$theusername'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2001
    Location
    New York
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhh... I see where you are going. Thank you! I'll see if I can make this work more efficiently.

    To be quite honest, I'm not sure I understand the first part of the php either and why I have to go through all that trouble with the first part and pulling the variable from the first space in the array.
    My law forum and legal information website -- I also buy websites you may wish to sell


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
  •