SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: Left Join

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Left Join

    Hi,

    I'm trying to display the total comments for a particular record. When I am on the article page (showing just one article), the comment record is correct. When I am showing more than one article on a page the comment record shows zero for all.

    Here is my code:

    PHP Code:
    <?php require_once('Connections/connNickToye.php'); ?>
    <?php
    $maxRows_rsHome 
    3;
    $pageNum_rsHome 0;
    if (isset(
    $_GET['pageNum_rsHome'])) {
    $pageNum_rsHome $_GET['pageNum_rsHome'];
    }
    $startRow_rsHome $pageNum_rsHome $maxRows_rsHome;
    mysql_select_db($database_connNickToye$connNickToye);
    $query_rsHome "SELECT date_format(pub_date, '%D %M %Y') as pub_date, home_id, title, picture, text FROM home";
    $query_limit_rsHome sprintf("%s LIMIT %d, %d"$query_rsHome$startRow_rsHome$maxRows_rsHome);
    $rsHome mysql_query($query_limit_rsHome$connNickToye) or die(mysql_error());
    $row_rsHome mysql_fetch_assoc($rsHome);
    if (isset(
    $_GET['totalRows_rsHome'])) {
    $totalRows_rsHome $_GET['totalRows_rsHome'];
    } else {
    $all_rsHome mysql_query($query_rsHome);
    $totalRows_rsHome mysql_num_rows($all_rsHome);
    }
    $totalPages_rsHome ceil($totalRows_rsHome/$maxRows_rsHome)-1;
    $maxRows_rsArticles 5;
    $pageNum_rsArticles 0;
    if (isset(
    $_GET['pageNum_rsArticles'])) {
    $pageNum_rsArticles $_GET['pageNum_rsArticles'];
    }
    $startRow_rsArticles $pageNum_rsArticles $maxRows_rsArticles;
    mysql_select_db($database_connNickToye$connNickToye);
    $query_rsArticles "SELECT a.article_id,a.title, a.text,a.picture, date_format(a.pub_date,' %D %M %Y') as pub_date, COUNT(c.article_id) AS total FROM articles a LEFT JOIN comments c ON a.article_id = c.article_id GROUP BY a.article_id, a.title, a.text, pub_date ORDER BY pub_date DESC";
    $query_limit_rsArticles sprintf("%s LIMIT %d, %d"$query_rsArticles$startRow_rsArticles$maxRows_rsArticles);
    $rsArticles mysql_query($query_limit_rsArticles$connNickToye) or die(mysql_error());
    $row_rsArticles mysql_fetch_assoc($rsArticles);
    if (isset(
    $_GET['totalRows_rsArticles'])) {
    $totalRows_rsArticles $_GET['totalRows_rsArticles'];
    } else {
    $all_rsArticles mysql_query($query_rsArticles);
    $totalRows_rsArticles mysql_num_rows($all_rsArticles);
    }
    $totalPages_rsArticles ceil($totalRows_rsArticles/$maxRows_rsArticles)-1;
    mysql_select_db($database_connNickToye$connNickToye);
    $query_rsRecentArticles "SELECT * FROM articles ORDER BY pub_date DESC";
    $rsRecentArticles mysql_query($query_rsRecentArticles$connNickToye) or die(mysql_error());
    $row_rsRecentArticles mysql_fetch_assoc($rsRecentArticles);
    $totalRows_rsRecentArticles mysql_num_rows($rsRecentArticles);
    mysql_select_db($database_connNickToye$connNickToye);
    $query_rsLinks "SELECT * FROM inspiration ORDER BY title ASC";
    $rsLinks mysql_query($query_rsLinks$connNickToye) or die(mysql_error());
    $row_rsLinks mysql_fetch_assoc($rsLinks);
    $totalRows_rsLinks mysql_num_rows($rsLinks);
    $colname_rsComments "-1";
    if (isset(
    $_POST['article_id'])) {
    $colname_rsComments = (get_magic_quotes_gpc()) ? $_POST['article_id'] : addslashes($_POST['article_id']);
    }
    mysql_select_db($database_connNickToye$connNickToye);
    $query_rsComments sprintf("SELECT * FROM comments WHERE article_id = %s"$colname_rsComments);
    $rsComments mysql_query($query_rsComments$connNickToye) or die(mysql_error());
    $row_rsComments mysql_fetch_assoc($rsComments);
    $totalRows_rsComments mysql_num_rows($rsComments);
    //line break function
    function formatParagraphs ($text)
    {
    $text ereg_replace("\r",'',$text);
    $text ereg_replace("\n",'<br />',$text);
    return 
    $text;
    }
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <!-- InstanceBegin template="/Templates/main.dwt.php" codeOutsideHTMLIsLocked="false" -->
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <!-- InstanceBeginEditable name="doctitle" -->
    <title>NickToye 2005</title>
    <!-- InstanceEndEditable -->
    <link href="/css/myStyle.css" rel="stylesheet" type="text/css" />
    </head>
    <body>
    <div id="container">
    <div id="inner">
        <div id="header"></div>
        <!-- InstanceBeginEditable name="nav" -->
        <ul id="nav">
         <li id="home"><a href="index.php" class="active">HOME </a></li>
         <li id="about"><a href="about.php">ABOUT</a></li>
         <li id="blog"><a href="blog.php">BLOG</a></li>
         <li id="work"><a href="work.php">WORK</a></li>
         <li id="contact"><a href="contract.php">CONTRACT ME</a></li>
         <li id="admin"><a href="admin/adminIndex.php">ADMIN</a></li>
        </ul>
        <!-- InstanceEndEditable -->
        <div id="content"> <!-- InstanceBeginEditable name="main" -->
         <span id="slogan">Freelance Web Designer, Chester, UK... </span>
         <dl>
            <dt><a href="/about.php"><?php echo $row_rsHome['title']; ?></a><br /><br />
            </dt>
            <dd><a href="/about.php"><?php echo $row_rsHome['text']; ?><br />
            </a></dd>
         </dl>
     
         <hr size="1" class="linebreak" />
     
    <p class="byline">Recent Articles</p>
    <?php do { ?>
    <span class="title"><a href="/viewArticle.php?article_id=<?php echo $row_rsArticles['article_id']; ?>"><?php echo $row_rsArticles['title']; ?></a></span>
            <br />
            <br />
             <span class="article"><?php echo formatParagraphs($row_rsArticles['text']); ?></span> <br /><br />
     
     
             <span class="updated"><?php echo $row_rsArticles['pub_date']; ?></span><br /><br />
        <span class="viewComment"><a href="viewComments.php?article_id=<?php echo $row_rsArticles['article_id']; ?>">View Comments [<?php echo $totalRows_rsComments ?>]</a></span> <span class="style3">|</span> <a href="addComment.php?article_id=<?php echo $row_rsArticles['article_id']; ?>" class="addComment">Add Comment </a>
             <br />
    <hr size="1" class="linebreak" />
                <br />
    <?php } while ($row_rsArticles mysql_fetch_assoc($rsArticles)); ?>
        <!-- InstanceEndEditable --> </div>
        <div id="sidebar"> <!-- InstanceBeginEditable name="sidebar" -->     
         <span class="sidebarHeader">Recent Articles </span>
    <ul>
    <?php do { ?>
    <li><a href="viewArticle.php?article_id=<?php echo $row_rsRecentArticles['article_id']; ?>"><?php echo $row_rsRecentArticles['title']; ?></a></li>
    <?php } while ($row_rsRecentArticles mysql_fetch_assoc($rsRecentArticles)); ?>
    </ul>
         <span class="sidebarHeader">Inspiration</span>
         </p>
         <ul>
            <?php do { ?>
            <li><a href="<?php echo $row_rsLinks['link']; ?>" ><?php echo $row_rsLinks['title']; ?></a></li>
            <?php } while ($row_rsLinks mysql_fetch_assoc($rsLinks)); ?></ul>
     
            <span class="sidebarHeader">Icons</span>
    <ul>
    <li>Internet Superhighway</li>
    <li>Home</li>
    </ul>
     
        <!-- InstanceEndEditable --> </div>
        <div class="clearer"></div>
    </div>
    </div>
    <div id="footer"><span class="style2"> Powered By <a href="http://www.macromedia.com/software/dreamweaver/">Dreamweaver MX 2004</a>, <a href="http://www.mysql.com/">MySQL</a> and <a href="http://www.php.net/">PHP</a>. Copyright Nick Toye 2004<br />
    <a href="http://validator.w3.org/check/referer">XHTML</a>, <a href="http://jigsaw.w3.org/css-validator/validator?uri=http://www.nicktoye.co.uk/css/myStyle.css">CSS</a> </span></div>
    </body>
    <!-- InstanceEnd -->
    </html>
    <?php
    mysql_free_result
    ($rsHome);
    mysql_free_result($rsArticles);
    mysql_free_result($rsRecentArticles);
    mysql_free_result($rsLinks);
    mysql_free_result($rsComments);
    ?>
    Can anyone see where I have gone wrong.

  2. #2
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My suggestion with any query problem in an application is to take php out of the equation to see where the mistake it.

    Echo the pertinent SQL in addition to running the query so you can look that those statements and see if they make sense and work. Then we'll be able to easily see if the problem is with your query or your php.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, can you see why my code isn't working then?

    Is it the way the query is being asked,

    I'm a bit of a newbie here.

  4. #4
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Insert this line:
    echo $whatever . '<br /><br />';
    before every line containing:
    mysql_query($whatever)

    For example:
    $all_rsHome = mysql_query($query_rsHome);
    becomes:
    echo $query_rsHome . '<br /><br />';
    $all_rsHome = mysql_query($query_rsHome);

    Then, tell us all about the output. :-)
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what I get at the top of my document:

    SELECT date_format(pub_date, '%D %M %Y') as pub_date, home_id, title, picture, text FROM home LIMIT 0, 3

    SELECT date_format(pub_date, '%D %M %Y') as pub_date, home_id, title, picture, text FROM home

    SELECT a.article_id,a.title, a.text,a.picture, date_format(a.pub_date,' %D %M %Y') as pub_date, COUNT(c.article_id) AS total FROM articles a LEFT JOIN comments c ON a.article_id = c.article_id GROUP BY a.article_id, a.title, a.text, pub_date ORDER BY pub_date DESC LIMIT 0, 5

    SELECT a.article_id,a.title, a.text,a.picture, date_format(a.pub_date,' %D %M %Y') as pub_date, COUNT(c.article_id) AS total FROM articles a LEFT JOIN comments c ON a.article_id = c.article_id GROUP BY a.article_id, a.title, a.text, pub_date ORDER BY pub_date DESC

    SELECT * FROM articles ORDER BY pub_date DESC

    SELECT * FROM inspiration ORDER BY title ASC

    SELECT * FROM comments WHERE article_id = -1

    Link

  6. #6
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The value of -1 in the last query is supsect. There isn't any article_id with the value -1, right?

    Trace your steps back, why did you end up with a -1 in that query?

    Side note, as long as you don't want to switch databases, you only need one mysql_select_db in your script.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That -1 comes in as an automatic value when I set up the variable in dreamweaver. So should I delete that value completely?

  8. #8
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also when I delete the extra lines of mysql_select_db it deletes the record sets in my bindings panel, do you know why?

  9. #9
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, so you are using Dreamweaver-created PHP? I have no familiarity with those Dreamweaver features so I can't say what's up with the code it creates.

    Have you tried running this query in phpMyAdmin or the MySQL command line?
    SELECT a.article_id,a.title, a.text,a.picture, date_format(a.pub_date,' %D %M %Y') as pub_date, COUNT(c.article_id) AS total FROM articles a LEFT JOIN comments c ON a.article_id = c.article_id GROUP BY a.article_id, a.title, a.text, pub_date ORDER BY pub_date DESC LIMIT 0, 5
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  10. #10
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have yes, it shouldn't make much difference. Basically I want to show the total amount of comments from one article. I have an article_id field in both articles table and comments table.

  11. #11
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And when you run the query on the command line do you get the correct number of comments?
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  12. #12
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I run if from phpMyAdmin and it shows up the correct number of comments.

  13. #13
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    fixed it, bloody easy in the end, a good nights sleep and everything is possible. So what do you use rather than DW?


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
  •