PHP/MySQL 如何从 LEFT JOIN 中删除重复信息?
我正在尝试从 MySQL 数据库中的一系列表生成一个简单的列表。我使用的 SQL 是:
$resource_sql = "SELECT prod_spec_sheets.spec_uri, prod_spec_sheets.spec_title,
prod_photos.photo_uri, prod_photos.photo_title,
prod_diagrams.diag_uri, prod_diagrams.diag_title,
prod_ies.ies_uri, prod_ies.ies_title,
prod_instruction_sheets.instr_sheet_uri, prod_instruction_sheets.instr_sheet_title
FROM products
LEFT JOIN prod_spec_sheets ON products.prod_id = prod_spec_sheets.prod_id
LEFT JOIN prod_photos ON products.prod_id = prod_photos.prod_id
LEFT JOIN prod_diagrams ON products.prod_id = prod_diagrams.prod_id
LEFT JOIN prod_ies ON products.prod_id = prod_ies.prod_id
LEFT JOIN prod_instruction_sheets ON products.prod_id = prod_instruction_sheets.prod_id
WHERE products.prod_code = '$product_code'";
我用来显示它的 PHP 看起来像这样:
if ($resource_num_rows > 1){
echo '<h2>Downloads</h2>';
while($row = mysql_fetch_array($resource_result, MYSQL_ASSOC)){
echo "<ul>";
$count = 0;
foreach ($row as $key => $value) {
if ($count % 2) {
echo '">' . $value . '</a></li>' . PHP_EOL;
} else {
echo '<li><a href="' . RESOURCES_URI . $value;
}
$count++;
}
echo "</ul>";
}
}
生成以下 HTML:
<ul>
<li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/is_pil.pdf">Instructions</a></li>
</ul><ul><li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/isu_pil.pdf">Instructions (ALT)</a></li>
</ul>
当我真正需要的是:
<ul>
<li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/is_pil.pdf">Instructions</a></li>
<li><a href="http://URL/isu_pil.pdf">Instructions (ALT)</a></li>
</ul>
每个表(产品除外)可以有许多与产品关联的项目,仅此示例显示如果 prod_instruction_sheets 表中存在多于一组指令时会发生什么情况。
我不确定答案是在更好的 MySQL 语句中,还是在 PHP 中对我的循环进行更改。任何帮助将不胜感激。
I am trying to generate a simple list from a series of tables in a MySQL database. The SQL I am using is:
$resource_sql = "SELECT prod_spec_sheets.spec_uri, prod_spec_sheets.spec_title,
prod_photos.photo_uri, prod_photos.photo_title,
prod_diagrams.diag_uri, prod_diagrams.diag_title,
prod_ies.ies_uri, prod_ies.ies_title,
prod_instruction_sheets.instr_sheet_uri, prod_instruction_sheets.instr_sheet_title
FROM products
LEFT JOIN prod_spec_sheets ON products.prod_id = prod_spec_sheets.prod_id
LEFT JOIN prod_photos ON products.prod_id = prod_photos.prod_id
LEFT JOIN prod_diagrams ON products.prod_id = prod_diagrams.prod_id
LEFT JOIN prod_ies ON products.prod_id = prod_ies.prod_id
LEFT JOIN prod_instruction_sheets ON products.prod_id = prod_instruction_sheets.prod_id
WHERE products.prod_code = '$product_code'";
The PHP I am using to display it looks like this:
if ($resource_num_rows > 1){
echo '<h2>Downloads</h2>';
while($row = mysql_fetch_array($resource_result, MYSQL_ASSOC)){
echo "<ul>";
$count = 0;
foreach ($row as $key => $value) {
if ($count % 2) {
echo '">' . $value . '</a></li>' . PHP_EOL;
} else {
echo '<li><a href="' . RESOURCES_URI . $value;
}
$count++;
}
echo "</ul>";
}
}
Which generates the following HTML:
<ul>
<li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/is_pil.pdf">Instructions</a></li>
</ul><ul><li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/isu_pil.pdf">Instructions (ALT)</a></li>
</ul>
When what I really need is:
<ul>
<li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/is_pil.pdf">Instructions</a></li>
<li><a href="http://URL/isu_pil.pdf">Instructions (ALT)</a></li>
</ul>
Each table (except products) could have many items associated with a product, this example only shows what happens if there is more than one set of instructions in the prod_instruction_sheets table.
I'm not sure if the answer is in a better MySQL statement, or a change in the PHP to my loop. Any help would be gratefully received.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
尝试 SELECT 仅您需要的字段并使用关键字语句中的DISTINCT:
编辑:好吧
,我明白你在做什么,在
PHP
中过滤此列的一种简单方法是将你正在写入的行放入关联数组中,并检查你是否已经写回此数据。如果您没有两个列表,请将
UL
移到while
循环之外。例子:
Try to SELECT only the field you need and use the keyword DISTINCT in your statement:
Edit:
Ok i see what you are doing, one easy way to filter this column in
PHP
is to put the line you are writting into an associative array and check if you have already write back this data.Move also your
UL
outside of thewhile
loop if you don't have two list.Example:
GROUP BY products.prod_id 乍一看似乎应该可以工作。
GROUP BY products.prod_id smells like it should work at first glance.