SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict Philip Toews's Avatar
    Join Date
    Dec 2001
    Location
    Kuala Belait, Brunei
    Posts
    367
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please help with a table join

    I am having some difficulty getting this script to display results properly. I am sure I need to specify a table join in the select statement, but I am a complete newbie and have no idea how to do this. In the end what I want is a select statement that grabs all the records in the tip_summary table and then sorts the results by the author name which is contained in the authors table (authors.name). The tip_summary also has a column called name (tip_summary.name), but I want that to appear in a separate column in the html table that is output by the script. The script currently outputs a straight cross-join. There are multiple repetitions of each record. How do I use a join?

    Here is the script:

    PHP Code:
    <!-- user_tiplist.php -->
    <html>
    <head>
    <title> Teaching Tips </title>
    <link rel="stylesheet" href="styles.css" type="text/css">
    </head>
    <body>
    <h1>Teaching Tips</h1>
    <p><a href="searchtips.php">Try a new search...</a></p>
    <?php

    $dbcnx 
    mysql_connect("localhost""root""password");
    mysql_select_db("teachingfish");

    // The basic SELECT statement

    $select "SELECT DISTINCT tip_summary.ID, 
    tip_summary.name, authors_ID, LEFT(short_text,100), 
    date, tip_summary.tip_categories_ID"
    ;
    $from   " FROM tip_summary, authors";
    $where  " WHERE tip_summary.ID >= 0";

    if (
    $aid != "") { // An author is selected
      
    $where .= " AND authors_ID=$aid";
    }

    if (
    $cid != "") { // A category is selected
      
    $from  .= ", tip_categories_lookup";

      
    $where .= " AND tip_summary.ID=tip_categories_lookup.tip_summary_ID AND tip_categories_lookup.tip_categories_ID=$cid";
    }

    if (
    $searchtext != "") { // Some search text was specified
      
    $where .= " AND long_text LIKE '%$searchtext%'";
    }

    if (
    $sort != "") { // a sort order was specified
      
    $where .= " ORDER BY $sort";
    }else{

    $where .= " ORDER BY authors.name";
    }
    ?>

    <table border="0">
    <tr><th><a href="<?=$PHP_SELF?>?sort=name">Tip Name</a></th>
    <th><a href="<?=$PHP_SELF?>?sort=short_text">Description</a></th>
    <th><a href="<?=$PHP_SELF?>?sort=authors_ID">Author</a></th>
    <th><a href="<?=$PHP_SELF?>?sort=date">Date</a></th><th>Options</th></tr>

    <?php
    $tips 
    mysql_query($select $from $where);
    if (!
    $tips) {
      echo(
    "</table>");
      echo(
    "<p>Error retrieving tips from database!<br />".
           
    "Error: " mysql_error() . "</p>");
      exit();
    }

    while (
    $tip mysql_fetch_array($tips)) {
    // table row colors alternate
        
    if ( $i%2==) {
            
    $bgcolor "'#C0E0FF'";
        } else {
            
    $bgcolor "'#EFEFEF'";
        }
      echo(
    "<tr bgcolor =$bgcolor valign='top'>\n");

      
    $id $tip["ID"];
      
    // If the tip text is 100 characters long, add "..." to the end of it
      // to indicate that it is actually longer. strlen() returns string length!
      
    $tiptext $tip["LEFT(short_text,100)"];
      if (
    strlen($tiptext)) $tiptext .= "...";
      
    //$tiptext = htmlspecialchars($tip["short_text"]);
      
    $tipdate $tip["date"];
      
    $tipname $tip["name"];
      
    $authorID = ($tip["authors_ID"]);
      
    $authorname mysql_query("SELECT name from authors WHERE ID=$authorID");
      
    $authorname mysql_fetch_array($authorname);
      
    $aname  $authorname['name'];
      echo(
    "<td>$tipname</td>\n");
      echo(
    "<td><a href='tip.php?id=$id'>$tiptext</td>\n");
      echo(
    "<td>$aname</td>\n");
      echo(
    "<td>$tipdate</td>\n");
      echo(
    "<td >[<a href='edittip.php?id=$id'>Edit</a>|".
           
    "<a href='deletetip.php?id=$id'>Delete</a>]</td>\n");
      echo(
    "</tr>\n");
      
    $i++;
    }
    ?>

    </table>
    </body>
    </html>
    any help will save me a lot of frustration and earn my eternal gratitude.

    p
    Last edited by Philip Toews; Jan 22, 2002 at 09:28.
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com

  2. #2
    SitePoint Addict Philip Toews's Avatar
    Join Date
    Dec 2001
    Location
    Kuala Belait, Brunei
    Posts
    367
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I fixed it

    I took a look at another thread that I started and figured out what I was being told to do (I hang my head in newbie shame). At any rate I figured it out:

    Here's the code that works:

    PHP Code:

    // The basic SELECT statement

    $select = "SELECT DISTINCT tip_summary.ID, 
    tip_summary.name, authors_ID, LEFT(short_text,100), 
    date, tip_summary.tip_categories_ID";
    $from   = " FROM tip_summary, authors";
    $where  = " WHERE tip_summary.ID >= 0 AND authors.ID = tip_summary.authors_id";

    if ($aid != "") { // An author is selected
      $where .= " AND authors_ID=$aid";
    }

    if ($cid != "") { // A category is selected
      $from  .= ", tip_categories_lookup";

      $where .= " AND tip_summary.ID=tip_categories_lookup.tip_summary_ID AND tip_categories_lookup.tip_categories_ID=$cid";
    }

    if ($searchtext != "") { // Some search text was specified
      $where .= " AND long_text LIKE '%$searchtext%'";
    }

    if ($sort != "") { // a sort order was specified
      $where .= " ORDER BY $sort";
    }else{

    $where .= " ORDER BY authors.name ASC";
    }
    ?>

    <table border="0">
    <tr><th><a href="<?=$PHP_SELF?>?sort=name">Tip Name</a></th>
    <th><a href="<?=$PHP_SELF?>?sort=short_text">Description</a></th>
    <th><a href="<?=$PHP_SELF?>?sort=authors.name">Author</a></th>
    <th><a href="<?=$PHP_SELF?>?sort=date">Date</a></th><th>Options</th></tr>

    <?php
    $tips 
    mysql_query($select $from $where);
    if (!
    $tips) {
      echo(
    "</table>");
      echo(
    "<p>Error retrieving tips from database!<br />".
           
    "Error: " mysql_error() . "</p>");
      exit();
    }

    while (
    $tip mysql_fetch_array($tips)) {
    // table row colors alternate
        
    if ( $i%2==) {
            
    $bgcolor "'#C0E0FF'";
        } else {
            
    $bgcolor "'#EFEFEF'";
        }
      echo(
    "<tr bgcolor =$bgcolor valign='top'>\n");

      
    $id $tip["ID"];
      
    // If the tip text is 100 characters long, add "..." to the end of it
      // to indicate that it is actually longer. strlen() returns string length!
      
    $tiptext $tip["LEFT(short_text,100)"];
      if (
    strlen($tiptext)) $tiptext .= "...";
      
    //$tiptext = htmlspecialchars($tip["short_text"]);
      
    $tipdate $tip["date"];
      
    $tipname $tip["name"];
      
    $authorID = ($tip["authors_ID"]);
      
    $authorname mysql_query("SELECT name from authors WHERE ID=$authorID");
      
    $authorname mysql_fetch_array($authorname);
      
    $aname  $authorname['name'];
      echo(
    "<td>$tipname</td>\n");
      echo(
    "<td><a href='tip.php?id=$id'>$tiptext</td>\n");
      echo(
    "<td>$aname</td>\n");
      echo(
    "<td>$tipdate</td>\n");
      echo(
    "<td >[<a href='edittip.php?id=$id'>Edit</a>|".
           
    "<a href='deletetip.php?id=$id'>Delete</a>]</td>\n");
      echo(
    "</tr>\n");
      
    $i++;
    }
    ?>
    Philip Toews Professional esl Educator and ASP.NET wannabe

    http://www.philiptoews.com
    philip@philiptoews.com


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
  •