PHP/MySQL Multi table selections

Question

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?

This is not best way to execute query inside loop . So you have to use Joins. — vjy tiwari, Jul 27 '17 at 08:33

score 0 · Accepted Answer · answered Apr 17 '13 at 13:21

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);

PHP/MySQL Multi table selections

1 Answers1