Page 1 of 1

url,varibles and left join

Posted: Thu Aug 20, 2009 9:36 am
by tippo27
Hi i am new to php and i am currently trying to develop a latest blog page with a read more link. However when i enter the following:

echo "<a href='posts.php?id=" .$row_post['id']."'>Read More....</a>;

it keeps returning the wrong id, the query has been done using a left join on users table id= author_id.
I need the blog_post id, but it returns the author id. Please help i ve been trying to crack this for ages, going slightly crazy!!!

<?php require_once("Connections/myblog.php"); ?>
<?php


$maxRows_post = 5;
$pageNum_post = 0;
if (isset($_GET['pageNum_post'])) {
$pageNum_post = $_GET['pageNum_post'];
}
$startRow_post = $pageNum_post * $maxRows_post;

mysql_select_db($database_myblog, $myblog);


$query_post = "SELECT *, DATE_FORMAT (`datePosted`,'%d-%m-%y')as newdate FROM blog_posts LEFT JOIN users ON users.id = author_id ORDER BY blog_posts.datePosted DESC";
$query_limit_post = sprintf("%s LIMIT %d, %d", $query_post, $startRow_post, $maxRows_post);
$post = mysql_query($query_limit_post, $myblog) or die(mysql_error());
$row_post = mysql_fetch_assoc($post);

if (isset($_GET['totalRows_post'])) {
$totalRows_post = $_GET['totalRows_post'];
} else {
$all_post = mysql_query($query_post);
$totalRows_post = mysql_num_rows($all_post);
}
$totalPages_post = ceil($totalRows_post/$maxRows_post)-1;


?>


And then on the page the php is:


<?php include ("includes/config.php");






do {


echo "<h2>" .$row_post['title']."</h2>";
echo "<div class='publish'> Posted By". " ".$row_post['username']. " " .$row_post['newdate']."</div>";
echo "<div class='blog'>";
echo "<p>" .$row_post['introtext']. "</p>";
echo "</div>";
echo "<a href='posts.php?id=" .$row_post['id']."'>Read More..</a>";
echo "<div class='blog_line'></div>";



}

while ($row_post = mysql_fetch_assoc($post));



?>

Re: url,varibles and left join

Posted: Thu Aug 20, 2009 11:09 am
by jackpf
Try putting users.id AS `id` in the select clause.

Re: url,varibles and left join

Posted: Thu Aug 20, 2009 11:34 am
by tippo27
do you mean blog_post.id as id?

Re: url,varibles and left join

Posted: Thu Aug 20, 2009 11:36 am
by jackpf
Sorry, yeah, I didn't read your post properly :P

Re: url,varibles and left join

Posted: Thu Aug 20, 2009 11:46 am
by tippo27
trying that and it tells me unknown coloumn blog_post.id

Re: url,varibles and left join

Posted: Thu Aug 20, 2009 11:50 am
by tippo27
Your a star, got it!! I have been playing with this for ages and could t figure it out!!

Thanks

Re: url,varibles and left join

Posted: Thu Aug 20, 2009 11:53 am
by jackpf
Cool, nice one. No problem :)

Re: url,varibles and left join

Posted: Thu Aug 20, 2009 7:52 pm
by McInfo
This is an inappropriate use of do-while. If there are no rows returned by the query, this code will throw "undefined index" errors.

Code: Select all

$row = mysql_fetch_assoc($result);
do {
    echo $row['field'];
} while ($row = mysql_fetch_assoc($result));
Use just while instead.

Code: Select all

while ($row = mysql_fetch_assoc($result)) {
    echo $row['field'];
}
Edit: This post was recovered from search engine cache.