featured article issue while getting vote count

[eluser]vtx220[/eluser]
I seem to have lost this round because my knowledge in the field isn't as extensive as it should. I try to learn but it's difficult. Anyway, here's my problem

I'm trying to build sorta like a digg site, nothing too fancy fairly small and people get to upvote posts. On the front-page there's a "featured" article which is based off of this:

Featured logic:
> Find all articles that have been posted in the past week.
> Within those results, which one has had the most votes and has never been featured before?
> display result 1

It seems fairly simple but here's how my database is setup :
DB: news_article
articleid
author
votecount // Ignore this because I don't want this entry anymore
other post information like body, title etc...

DB: news_votes
vid // vote id #, not used besides indexing
articleid // article id from news_article
uid //user id as previously defined
date // timestamp

Why do I have votecount in news_article when I have news_votes to tally?
The reason why I have it this way is because I can't seem to figure out how to get the vote_count sum for that particular article.

So basically I fetch the article with the following model function:

Code:

function get_newsfeatured() {
  $timeframe = date('Y-m-d H:i:s', time() - 86400 * 7);
  $this->db->select('b.user_id, b.username, p.narticleid, p.news_type, p.nvotes, p.full_url, p.l_title, p.author, p.l_desc, p.s_desc, p.m_thumb, p.time');
  $this->db->from('news_articles AS p');
  $this->db->join('phpbb_users AS b', 'p.author = b.user_id', 'left');
  $this->db->where('time >=', $timeframe);
  $this->db->order_by('nvotes', 'desc');
  $this->db->limit(1);
  
  $query = $this->db->get();
        return $query;
}

It works fine, though I'm not sure how efficient that is... Anyways, I got the article, but I don't have the amount of votes for that article. (this is why I was going to go by the route of using votecount in the article's row itself. messy, messy!

Within the article itself I have the following query:

Code:

function get_votecount($articleid) {
// This counts the amount of votes for a given article
  $this->db->where('aid', $articleid);
  $this->db->from('news_votes');
  
  return $this->db->count_all_results();
}

This works great at counting the votes inside the article's view because it already knows what article to pick.

So how can I adjust this whole thing to make it so I can display the total vote count on the "home page" where the article is featured? Thank you for your time, this has been plaguing me for a while

----------------------------------------------------------------

Results:

~*FEATURED ARTICLE*~
[Thumbnail] <-- Works
[Article Title] <-- Works
[Article Description] <-- Works
[Vote Count from news_votes] <-- Problematic
[Read Article Link] <-- Works

[eluser]Jason Stanley[/eluser]
Wouldn't something like this work?

Maybe I don't understand it. You are just trying to modify your query so it also gets the number of votes also right?

Code:

function get_newsfeatured() {
  $timeframe = date('Y-m-d H:i:s', time() - 86400 * 7);
  $this->db->select('b.user_id, b.username, p.narticleid, COUNT(v.vid) AS votes, p.news_type, p.nvotes, p.full_url, p.l_title, p.author, p.l_desc, p.s_desc, p.m_thumb, p.time'); // Edited
  $this->db->from('news_articles AS p');
  $this->db->join('news_votes AS v', 'v.articleid = p.articleid', 'left'); // New Line
  $this->db->join('phpbb_users AS b', 'p.author = b.user_id', 'left');
  $this->db->where('time >=', $timeframe);
  $this->db->order_by('nvotes', 'desc');
  $this->db->limit(1);
  
  $query = $this->db->get();
        return $query;
}

Added to line 3, also added the additional join which is more obvious to see.

Code:

COUNT(v.vid) AS votes,

[eluser]vtx220[/eluser]
Yes, that's exactly what I was looking for However, how do I echo out the total vote count for that article? I really appreciate your help on this!

[eluser]CroNiX[/eluser]

Code:

$row = $query->row();
echo $row->votes;

[eluser]vtx220[/eluser]
Thanks for the reply, but it gives me an error saying results_array is linked to a non-object, which started from the added join line by Jason.

$this->db->join('news_votes AS v', 'v.articleid = p.articleid', 'left'); // New Line

this is what I have in the view file:

Code:

<? foreach ($newsfeatured->result_array() as $row4): ?>
<?echo $row4->votes;?>
<? endforeach; ?>

[eluser]CroNiX[/eluser]
Well, yes, because you changed it to result_array() (I used row()), but you are still trying to access the variable as though it were an object. Also, since you are only returning 1 row (limit 1), it doesn't make any sense to use result_array(). Use row() or row_array(), instead. You shouldn't need a foreach() loop if you use that. Did you try it the way I wrote it?

[eluser]vtx220[/eluser]
My interpretation of all of this is that there's no point to display everything as an array if there's only going to be one row, so each object in the view would have to be displayed like: &lt;?= $newsfeatured->l_title;?&gt;

However, when I try this, it doesn't display anything. I have tried:

Code:

<?$row = $query->row();?>
<?echo $row->votes;?>

// and...
<?$newsfeatured = $query->row();?>
<?echo $row->votes;?>

// and then various other attempts to correct this.

I understand that the mysql query ends with "return $query;" which sends $query back to the controller and then it gets parsed into the view file. $row isn't defined in the controller, view, or model. In the controller I have the following:

Code:

$data['newsfeatured']  = $this->get_news->get_newsfeatured();
//and of course the template file parses $data

Sorry for all of this. I really am doing this to learn so I try to figure things out but sometimes it's difficult to find manuals that explain how things work, especially with frameworks like codeigniter which seems to be limited in certain ways. But, I don't blame them, I'm sure the documentations are written very well for seasoned programmers, which in my case, am just starting.

Thanks a lot for your help everyone!

[eluser]CroNiX[/eluser]
In get_newsfeatured(), change

Code:

return $query;

to

Code:

return ($query->num_rows() > 0) ? $query->row() : FALSE;
//if the query has a result, return the row or else return boolean FALSE

In your controller, continue to use

Code:

$data['newsfeatured'] = $this->get_news->get_newsfeatured();

In your view:

Code:

if ($newsfeatured !== FALSE)
{
  echo $newsfeatured->votes;
}

[eluser]vtx220[/eluser]
Thanks for your reply. What that code gave me ended up causing this:

Code:

Fatal error: Call to a member function num_rows() on a non-object in "...models/get_news.php"

It honestly doesn't make sense considering that num_rows() should be valid. been looking through the documentations and did a few searches but nothing really came up, except hints that it could be related to this:

Code:

$this->load->model('get_news','',TRUE); // <--probably this
$data['newsfeatured']  = $this->get_news->get_newsfeatured();

Also when you say to replace return $query; with what you said, do you mean to replace all of:

Code:

$query = $this->db->get();
  return $query;

// or just return $query; with

return ($query->num_rows() > 0) ? $query->row() : FALSE;
//if the query has a result, return the row or else return boolean FALSE

After lunch I'm going to grab a mysql book and see what I can find. I think picking this as my first school project was a little too ambitious. lol! The funny thing is that I have everything else working, including the voting system, comments, article publishing, and category view. it's just this one thing that doesn't want to work.

[eluser]vtx220[/eluser]
Stupid, stupid, stupid me. As soon as I finally learned what the error message meant (it means it probably can't find something) things started to make sense.

I looked through and there were things like "p.time" that didn't exist in the database, or "v.articleid" which was actually "v.aid" and the sort by etc... had stuff that didn't have a prefix.

I did the echo test thing since it links back to COUNT(v.vid) AS votes and it showed the vote count!

Now it displays 59 votes, which is EXACTLY the amount of votes that exist for this article. I feel so relieved. It's amazing what reading through some mySQL book can do for you during troubleshooting. You guys ARE amazing, thank you =) I have until tomorrow to finish this project up, and unfortunately I forgot to undo the "featured article" module after pasting the echo test, so when I exit I lost the entire code for the module. But you know what? I don't care! LOL

My next step is to do the same thing for the last 10 results module, which I believe shouldn't be a problem.

Thanks again, w00t!