PHP Mysql左连接
<?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.
＆lt;？php
$ q = mysql_query（“SELECT sub_cat。*，links。* \ n FROM链接
LEFT JOIN sub_cat
ON links.p_id = sub_cat.id
WHERE sub_cat.p_id ='$ id'
ORDER BY name ASC“）或die（mysql_error（））;
while（ $ r = mysql_fetch_assoc（$ q））
{
$ links_name = $ r ['name'];
$ link_h3 = $ links_name！=''？ '＆LT; H3＆GT;' 。 $ links_name。 '＆LT; / H3＆GT;' ：'';
// print $ link_h3;
print'＆lt; pre＆gt;';
print_r（$ r）;
}
？＆gt;
code> pre>
我有两个表，其行如： p>
sub_cat h2>
- id li>
- 名称 LI>
- P_ID LI>
UL>
链接 H2>
- ID LI >
- 链接 li>
- p_id li>
ul>
在子猫我有电影类别，如外语电影，国家电影， 未分类的电影等。 在链接表中，我有具体的电影链接和取决于子类别。 p>
唯一的是我不想要公开标题（sub_cat.name）。
结果是： p>
没有类别 strong> www.moviesite.com p>
没有类别 strong> www.moviesite2。 com p>
没有类别 strong> www.moviesite3.com p>
外国电影 strong> www.moviesite1。 bla p>
外国电影 strong> www.moviesite2.bla p>
blockquote>
我想成为 strong> p>
没有类别 strong> www.moviesite.com p>
www.moviesite2.com p>
www.moviesite3.com p>
外国电影 strong> www.moviesite1.bla p>
www.moviesite2.bla p>
blockquote>
并且不知道如何做到这一点：（ p>
任何帮助表示赞赏 。 p>
div>
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.
Try something like:
$lastSubcatName = "";
while ($r = mysql_fetch_assoc($q))
{
$links_name = $r['name'];
if($lastSubcatName != $links_name)
{
echo "<h1>".$links_name."</h1>";
$lastSubcatName = $links_name;
}
echo '<h3>' . $r['links'] . '</h3>';
}