Newcastlefan Newcastlefan - 2 years ago 85
MySQL Question

PHP/MySQL Multi table selections

I'm trying to select multiple rows from one table, depending on the ID given from another table.

I've got it half working with the code below, however it echo's out each blog multiple times depending on how many different tags are assigned to it, how would I go about so it displays multiple tag's on one copy of the blog post?

$sqlCommand = "SELECT blogid, blogtitle, content, blogtime, category, blogseourl, author FROM blog ORDER BY blogtime DESC";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$blogDisplay = '';
while ($row = mysqli_fetch_array($query)) {
$blogid = $row["blogid"];
$blogtitle = $row["blogtitle"];
$content = $row["content"];
$blogtime = $row["blogtime"];
$category = $row["category"];
$blogseourl = $row["blogseourl"];
$author = $row["author"];
$contentshort = substr($content, 0, 250);
$sqlCommand2 = "SELECT tag FROM blogtags WHERE blogid='$blogid'";
$query2 = mysqli_query($myConnection, $sqlCommand2) or die (mysqli_error());
while ($row = mysqli_fetch_array($query2)) {
$tag = $row['tag'];

$blogDisplay .= '<h1><a href="/blog/'. $blogseourl .'"> ' . $blogtitle . ' </a></h1> ' . $contentshort . '... <a href="/blog/'. $blogseourl .'">Read More...</a><br /><br /> ' . $author . ' posted on ' . $blogtime . ' &#124; Category: ' . $category . ' &#124; Tags: ' . $tag . ' &#124; <a href="/blog/'. $blogseourl .'#disqus_thread"></a>';
}
}
mysqli_free_result($query);


So everything is working correctly apart from it echoing multiple $blogDisplay's for each tag.

Anyone got any ideas?

Answer Source

You have to divide the blogDisplay into two sections, and list the tabs in between. Or you have to buffer up the taglist and insert it as a parameter into $blogDisplay

The first one is easiest:

$sqlCommand = "SELECT blogid, blogtitle, content, blogtime, category, blogseourl, author FROM blog ORDER BY blogtime DESC";
    $query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$blogDisplay = '';
        while ($row = mysqli_fetch_array($query)) {
        $blogid = $row["blogid"];
        $blogtitle = $row["blogtitle"];
        $content = $row["content"];
        $blogtime = $row["blogtime"];
        $category = $row["category"];
        $blogseourl = $row["blogseourl"];
        $author = $row["author"];
        $contentshort =  substr($content, 0, 250);
    $sqlCommand2 = "SELECT tag FROM blogtags WHERE blogid='$blogid'";
    $query2 = mysqli_query($myConnection, $sqlCommand2) or die (mysqli_error());        

/*first part, all the html before the taglist */
$blogDisplay .= '<h1><a href="/blog/'. $blogseourl .'"> ' . $blogtitle . ' </a></h1> ' . $contentshort . '... <a href="/blog/'. $blogseourl .'">Read More...</a><br /><br /> ' . $author . ' posted on ' . $blogtime . ' &#124;  Category: ' . $category . ' &#124;  Tags: ';

        while ($row = mysqli_fetch_array($query2)) {
        $tag = $row['tag'];
/**add the taglist*/
$blogDisplay .= $tag.' ';
        }

/**last part, all the html after the taglist*/
$blogDisplay .= ' &#124; <a href="/blog/'. $blogseourl .'#disqus_thread"></a>';

        }
mysqli_free_result($query); 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download