i have a problem with my query and one of the joins, the initial query works fine but when i try and join in the links table which has 4 matching links for each article the query returns 4 records for each article thus for displaying each article 4 times on my website?
so my tables are:
tbl_news_articles => **newsID**, newsTitle, newsBody, newsDate
tbl_images => **imageID**, filename
tbl_news_images => **imageID**, **newsID**
tbl_links => **linkID**, linkTitle, linkURL
tbl_news_links => **newsID**, **linkID**
and my query i am using is
SELECT n.newsID, n.type, n.title, n.body, n.date, ni.imageID, i.imageID, i.filename, l.linkID, l.linkTitle, l.linkURL
FROM tbl_news_articles AS n
LEFT JOIN tbl_news_images AS ni ON ni.newsID = n.newsID
LEFT JOIN tbl_images AS i ON i.imageID = ni.imageID
LEFT JOIN tbl_news_links AS nlink ON nlink.newsID = n.newsID
LEFT JOIN tbl_links AS l ON l.linkID = nlink.linkID
which returns something like the following
newsID, newsTite, newsDesc, newsDate, imageID, filename, linkID, linkTitle, linkURL
65,Article 1 Title,Article 1 description,2016-05-21,1,group.jpg,1,link1 title,linkURL
65,Article 1 Title,Article 1 description,2016-05-21,1,group.jpg,2,link2 title,linkURL
65,Article 1 Title,Article 1 description,2016-05-21,1,group.jpg,3,link3 title,linkURL
65,Article 1 Title,Article 1 description,2016-05-21,1,group.jpg,4,link4 title,linkURL
so each article is returned 4 times one for each link.
what is the correct way to resolve this? should i just run a separate query within my php code to get the links separately?
Appreciate your help
my php code is
<?php
//call the database to retreive the records
$sql = "SELECT n.newsID, n.type, n.title, n.body, n.date, ni.imageID, i.imageID, i.filename, l.linkID, l.linkTitle, l.linkURL FROM tbl_news_articles AS n
LEFT JOIN tbl_news_images AS ni ON ni.newsID = n.newsID
LEFT JOIN tbl_images AS i ON i.imageID = ni.imageID
LEFT JOIN tbl_news_links AS nlink ON nlink.newsID = n.newsID
LEFT JOIN tbl_links AS l ON l.linkID = nlink.linkID
ORDER BY date DESC LIMIT $offset, $rowsperpage";
$result = $conn->query($sql);
$i = 0;
if ($result->num_rows > 0)
{
// output data of each row
while($row = $result->fetch_assoc())
{
$id = $row['newsID'];
$link = "article.php?id=".$id;
$type = $row['type'];
$title = $row['title'];
$urltitle = strtolower($title);
$urltitle = str_replace(" ","-",$urltitle);
$body = $row['body'];
#$bodytext = (strlen($body) > 130) ? substr($body,0,130).'...' : $bodytext;
$pos= strpos($body, ' ', 140);
$bodytext = substr($body,0,$pos);
$bodytext .= "... <a href='$link' title='$title'>read more</a>";
$date = $row['date'];
$formated_date = date("d-M-Y", strtotime($date));
$imgID = $row['imageID'];
$filename = $row['filename'];
if($filename != "")
{
$imgLink = "images/news-articles/".$id."/".$filename;
}
else
{
$imgLink = "images/news-item-placeholder.jpg";
}
$i++;
if($i == 1)
{
echo "<div class='news-spotlight'>";
}
elseif($i >=2 && $i <=3)
{
if($i == "2")
{
$class = "first";
}
else
{
$class = "";
}
echo "<div class='news-highlight $class'>";
}
else
{
echo "<div class='news-item'>";
}?>
<a itemprop="url" href="<?php echo $link?>"><img itemprop="image" src="<?php echo $imgLink?>" alt="<?php echo $title?>" title="<?php echo $title?>"></a>
<div class='data'>
<h3><a href="<?php echo $link?>"><span itemprop="name"><?php echo $title?></span></a></h3>
<div class="article-date"><?php echo $formated_date?></div>
<span itemprop="startDate" content="<?php echo $date?>"></span>
<div itemprop="location" itemscope itemtype="http://schema.org/Place">
<span itemprop="address" content="England"></span>
<span itemprop="name" content="MayoShinDo Association"></span>
</div>
<p itemprop="description"><?php echo $bodytext?></p>
<?php if($i == 1)
{?>
<ul>
<li><a href="<?php echo $link?>"><span class="glyphicon glyphicon-ok" aria-hidden="true"></span> link 1</a></li>
<li><a href="<?php echo $link?>"><span class="glyphicon glyphicon-ok" aria-hidden="true"></span> link 2</a></li>
<li><a href="<?php echo $link?>"><span class="glyphicon glyphicon-ok" aria-hidden="true"></span> link 3</a></li>
<li><a href="<?php echo $link?>"><span class="glyphicon glyphicon-ok" aria-hidden="true"></span> link 4</a></li>
</ul><?php
}?>
</div><?php
echo "</div>";
}
}
else
{
echo "0 Results";
}?>
i have not set up the links in the php code yet as you can see from the code above, just want to get the query correct first
i have read you can possibly use GROUP_CONCAT? i tried the following
SELECT n.newsID, n.type, n.title, n.body, n.date, ni.imageID, i.imageID, i.filename, l.linkID, l.linkTitle, l.linkURL
GROUP_CONCAT(("|", l.linkID,l.linkTitle,l.linkURL)) as "fieldvalue"
FROM tbl_news_articles AS n
LEFT JOIN tbl_news_images AS ni ON ni.newsID = n.newsID
LEFT JOIN tbl_images AS i ON i.imageID = ni.imageID
LEFT JOIN tbl_news_links AS nlink ON nlink.newsID = n.newsID
LEFT JOIN tbl_links AS l ON l.linkID = nlink.linkID
but that is saying i have an error in my sql syntax? any ideas
thank you!