PDA

View Full Version : joins


tony09uk
04-28-2012, 12:27 AM
I am having trouble accessing an array element when I add a join,

The below works fine:

$article = mysql_query("SELECT article_id, user_id, content, DATE_FORMAT(date, '%d %b %y')
AS date
FROM article
ORDER BY date
DESC");

while($row = mysql_fetch_array($article)){

echo "<p>" . $row['date'];
echo "<br/>";

$wstring = explode("\n", wordwrap($row['content'], 100, "\n") );
echo $wstring[0];
echo "<a href=\"fullarticle.php?id=" . $row['article_id'] . "\"> Read full acticle...</a>";

}

$row['date'] shows the date the article was added
$row['article_id'] shows the content of the post

When I add a join e.g.

$article = mysql_query("SELECT article.article_id, article.user_id, article.content, DATE_FORMAT(article.date, '%d %b %y'), user.user_id, user.username
AS date
FROM article
LEFT JOIN user
ON user.user_id = article.user_id
ORDER BY date
DESC");

while($row = mysql_fetch_array($article)){

echo "<p>" . $row['date'];
echo "<br/>";

$wstring = explode("\n", wordwrap($row['content'], 100, "\n") );
echo $wstring[0];
echo "<a href=\"fullarticle.php?id=" . $row['article_id'] . "\"> Read full acticle...</a>";

}

$row['date'] displays the username.

why does this happen?

edbr
04-28-2012, 02:34 AM
user.user_id, user.username
AS date

tony09uk
04-28-2012, 08:14 PM
so by using AS date is the last item in the list used? I thought that command would create an array, that would allow me to access whichever element I wanted?

tony09uk
04-28-2012, 10:51 PM
thanks for your advice ed, ive sorted it now. using the numeric arrays (if that's what they are called :s)

tony09uk
04-28-2012, 11:11 PM
Can you please advise me on this next issue too?

I have two pages, page one:

$months = mysql_query("SELECT DISTINCT(MONTHNAME(date)) as post_month
FROM article
LIMIT 12");

while($row = mysql_fetch_array($months)){
echo "<a href=\"montharticles.php?id=" . $row['0'] . "\">" . $row['0'] . "</a><br/>";
}

page two (montharticles):

$article = mysql_query("SELECT article_id, content, DATE_FORMAT(date, '%d %b %y')
FROM article
WHERE article_id = $_GET[date]");


while($row = mysql_fetch_array($article)){
echo $row . "</br>";
}

My aim is to display all articles on the montharticles page from the selected month on page one. I realise that $_GET[date] is incorrect but i cant work how to do it, I have tried a few ways now without any luck.

Hope im not to far off the mark