BBLJ84 BBLJ84 - 2 months ago 8
MySQL Question

sql query with joins not returning certain data, can't seem to fix it

i have 3 tables

tbl_news_articles

1 newsIDPrimary int(4)
2 type int(3)
3 title varchar(40)
4 body medium text
5 date date


tbl_images

1 imageIDPrimary int(4)
2 filename varchar(40)


tbl_news_images

1 newsIDIndex int(4)
2 imageIDIndex int(4)


so tbl_news_article hold the article data, tbl_images holds the image data, and tbl_new_images holds the id of the article and the id of the image.

each news article will usually have 1 image but could have multiple images, however sometimes there are no images for a specific news article.

Its when there are no images for an article the problem persists.

My query is

$sql = "SELECT n.newsID, n.type, n.title, n.body, n.date, ni.newsID, ni.imageID, i.imageID, i.filename 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
ORDER BY date DESC


which returns the following records

newsID -> 61
type -> 0
title -> title 1
body -> article body text
date -> 2016-10-01
newsID -> NULL
imageID -> NULL
imageID -> NULL
filename -> NULL

newsID -> 62
type -> 0
title -> title 2
body -> article body text
date -> 2016-10-01
newsID -> 62
imageID -> 2
imageID -> 2
filename -> group.jpg


on my website i display a snippet of each article with a link to the full article, the problem is when trying to click on the full article link on an article which has no images linked to it in the database the newsID row returns NULL instead of the actual newsID value.

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.newsID, ni.imageID, i.imageID, i.filename 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
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'm sure the issue is with the joins but i've tried using inner join outer join and neither one returns the expected data.

i even tried using the following

$id = $row['n.newsID'];


to target the specific newsID field but that just broke things further
can anyone please help?

Thanks

Answer

The problem is that you're selecting both n.newsID and ni.newsID. When there's an image linked, these will be the same. But when there's no image linked, n.newsID contains the ID, while ni.newsID is NULL. If you select multiple columns with the same name, $row['columnName'] will contain the last column in the SELECT list, so in this case it contains ni.newsID. Table name prefixes are not included in the column name in the associative array.

Either remove ni.newsID from the SELECT list, or give it an alias like ni.newsID AS ni_newsID. Then $row['newsID'] will be n.newsID, while $row['ni_newsID'] will beni.newsID`.