PHP Mysql左连接

Question

<?php
$q = mysql_query("SELECT sub_cat.*, links.*
                 FROM links
                 LEFT JOIN sub_cat
                 ON links.p_id = sub_cat.id
                 WHERE sub_cat.p_id = '$id'
                 ORDER BY name ASC") or die (mysql_error());

while ($r = mysql_fetch_assoc($q))
{
    $links_name = $r['name'];
    $link_h3 = $links_name != '' ? '<h3>' . $links_name . '</h3>' : '';
    //print $link_h3;
    print '<pre>';
    print_r ($r);
}
?>

I have two tables with rows like:

sub_cat

• id
• name
• p_id

links

• id
• links
• p_id

In sub cat i have movie categories, like foreign language movies, national movies, uncategorised movies and so on. In links table i have concrete movie links and depending on sub category.

The only thing is that i do not want dublicate titles (sub_cat.name). result is:

Without Category www.moviesite.com

Without Category www.moviesite2.com

Without Category www.moviesite3.com

Foreign Movies www.moviesite1.bla

Foreign Movies www.moviesite2.bla

I want to be

Without Category www.moviesite.com

www.moviesite2.com

www.moviesite3.com

Foreign Movies www.moviesite1.bla

www.moviesite2.bla

and do not have any idea how to do this :(

any help appreciated.

Answer 1

To do the job, you have 2 solutions:

The first solution is to process your data before showing it, in order to group all movies by category.

You can do for example:

$moviesByCategory = array();

while ($r = mysql_fetch_assoc($q))
{
    // Create the new sub array for the new category if necessary
    if (!isset($moviesByCategory[$r['name']]))
        $moviesByCategory[$r['name']] = array();

    // Add the movie in the category
    $moviesByCategory[$r['name']][] = $r['links'];
}

And then, you can now iterate on this new array like

foreach($moviesByCategory as $category => $movies)
{
    // Print the category name
    echo '<h1>' . $category . '</h1>';

    // Print all movies of the category
    foreach($movies as $movie)
        echo '<h3>' . $movie . '</h3>';
}

The second solution is to modify the SQL query to group directly all movies that have the same category. You just have to use a GROUP BY clause on sub_cat.id and then apply an agregate function on all other fields in the select.

For performance aspect, the best solution is the SQL solution, but doing it with PHP will give you more flexibility for the presentation.