SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2007
    Location
    Oslo, Norway
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with joining two tables

    Hi,

    I have 2 tables, CV and CVcats. CV contains id, year, text and cvcatsid CVcats contains catsid and title. cvcatsid refers to catsid.
    What I want is to make a table listing of all content from CV with title from CVcats as a category title on top.
    Category 1
    -year / text
    -year / text
    -year / text
    Category 2
    -year / text
    -year / text
    etc.

    PHP Code:
        $result_array = array();
    $counter 0;

    // View of cv content in tables
    $rows 1;

    $result mysql_query
    "SELECT p.id
    , p.year
    , p.text
    , p.cvcatsid
    , c.catsid
    , c.title 
    FROM cv AS p 
    LEFT JOIN cvcats AS c 
    ON p.cvcatsid=c.catsid 
    ORDER BY c.catsid, p.id desc" 
    );
    while( 
    $row mysql_fetch_array$result ) )
        {
        
    $result_array[] = "<td class='cvyear'>" $row[1] . "</td><td class='cvtext'>" $row[2] . "</td>";
        }
        
    mysql_free_result$result );    

        
    $result_final "<table class='cv'><tr>\n";
        
        foreach(
    $result_array as $cvtext)
        {
        if(
    $counter == $rows)
        {    
        
    $counter 1;
        
    $result_final .= "\n</tr>\n<tr>\n";
        }
        else
        
    $counter++;

        
    $result_final .= "\t<td>" $cvtext "</td>\n";
        }
        
        if(
    $counter)
        {
        if(
    $rows)
        
    $result_final .= "\t<td colspan='".($rows-$counter)."'></td>\n";

            
    $result_final .= "</tr></table>";
            }
        
        echo 
    $result_final
    My query lists all the content without the category title. How is it possible to get the title on top of each table?
    Is LEFT JOIN the correct join and do I need to use GROUP BY?
    Thanks!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    If you only want to show the categories that have some data in the cv table, then you can use an INNER JOIN.
    If you want to show all categories, even if there is no data in the cv table, then you use a LEFT JOIN, but you'll have to use the cvcats table as primary table, and left join the cv table to it.

    Your script doesn't show the category, because you don't put it in your output.

    Look at this to see the global logic of how to put a title on top of each category block: http://www.sitepoint.com/forums/show...79&postcount=3
    Of course, you'll have to modify the code to suit your specific needs.

    By the way, I don't understand why people use the numeric array keys instead of the associative ones. $row['id'] is so much easier to understand than $row[1]. At least, that's my opinion

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2007
    Location
    Oslo, Norway
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Giudo,

    Thanks for you reply.
    Regarding the join - I just want to show categories with data, so I have changed it to INNER JOIN.
    I tried implementing the code in your post, and now it shows the title, but I only get 1 row of data for each category.
    PHP Code:
    $result_array = array();
        
    $counter 0;

        
    // View of cv content in tables
        
    $rows 1;
        
    $saveTitle '';
        
    $result mysql_query
            
    "SELECT p.id
            , p.year
            , p.text
            , p.cvcatsid
            , c.catsid
            , c.title 
            FROM cv_ts AS p 
            INNER JOIN cvcats_ts AS c 
            ON p.cvcatsid=c.catsid 
            ORDER BY c.catsid, p.id desc" 
    );
                while(
    $row mysql_fetch_assoc($result)) {
                if (
    $row['title'] != $saveTitle) {
                
    $result_array[] = "<td class='head'>" $row['title'] . "</td><td class='cvyear'>" $row['year'] . "</td><td class='cvtext'>" $row['text'] . "</td>";
                
    $saveTitle $row['title'];            
                }
                
                
    $result_final "<table class='cv'><tr>\n";
        
                foreach(
    $result_array as $cvtext)
                {
                    if(
    $counter == $rows)
                    {    
                        
    $counter 1;
                        
    $result_final .= "\n</tr>\n<tr>\n";
                    }
                    else
                    
    $counter++;

                    
    $result_final .= "\t<td>" $cvtext "</td>\n";
                }
        
                if(
    $counter)
                {
                    if(
    $rows)
                
    $result_final .= "\t<td colspan='".($rows-$counter)."'></td>\n";

                    
    $result_final .= "</tr></table>";
                }
                }
        echo 
    $result_final
    I agree on the associative array, it's much easier.
    Thanks!

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code PHP:
    $result_array = array();
    $counter = 0;
     
    // View of cv content in tables
    $rows = 1;
    $saveTitle = '';
    $result = mysql_query( 
        "SELECT p.id
            , p.year
            , p.text
            , p.cvcatsid
            , c.catsid
            , c.title 
            FROM cv_ts AS p 
            INNER JOIN cvcats_ts AS c 
            ON p.cvcatsid=c.catsid 
            ORDER BY c.catsid, p.id desc" );
     
    while($row = mysql_fetch_assoc($result)) {
      if ($row['title'] != $saveTitle) {
        $result_array[] = "<td class='head'>" . $row['title'] . "</td><td class='cvyear'>" . $row['year'] . "</td><td class='cvtext'>" . $row['text'] . "</td>";
        $saveTitle = $row['title'];            
      } else {
        $result_array[] = "<td class='cvyear'>" . $row['year'] . "</td><td class='cvtext'>" . $row['text'] . "</td>";
      }
    }
    $result_final = "<table class='cv'><tr>\n";
    foreach($result_array as $cvtext) {
      if($counter == $rows) {    
        $counter = 1;
        $result_final .= "\n</tr>\n<tr>\n";
      }
      else $counter++;
      $result_final .= "\t<td>" . $cvtext . "</td>\n";
    }
    if($counter) {
      if($rows) $result_final .= "\t<td colspan='".($rows-$counter)."'></td>\n";
      $result_final .= "</tr></table>";
    }
    echo $result_final;

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2007
    Location
    Oslo, Norway
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW! Works perfectly.
    Thanks a lot!


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
  •