[SOLVED] PHP/MySQL Group By & Select Count Question

[N1CK3RS0N]

Hello,

 

I'm trying to get a code to work but it isn't quite working. Its going to be a feedback system and it will show the positive, neutral and negative feedback over 1 month, 6 months, and 12 months. There is a script I saw that does something similar, though I couldn't quite get it to work. Not sure what is wrong.

 

Basically the goal of the code below is to find the total number of positive feedback (case 1), neutral feedback (case 2) and negative feedback (case 3) that occurred within the past 1 month.

 

$stats = $vbulletin->db->query_read("
SELECT feedback_rating, COUNT(*) AS feedback_count
FROM " . TABLE_PREFIX . "forumgold_feedback 
WHERE feedback_to = " . $vbulletin->userinfo['userid'] . " 
AND feedback_timestamp >= " . mktime(0,0,0,date('m')-1,date('d'),date('Y')) . " 
GROUP BY feedback_rating
");			

while ($vbulletin->db->fetch_array($stats)) {
switch ($stats['feedback_rating']) {
	case 1:
		$post['pos1'] = $stats['feedback_count'];
		break;
	case 2:
		$post['neu1'] = $stats['feedback_count'];
		break;
	case 3:
		$post['neg1'] = $stats['feedback_count'];
		break;
	default:
		// Just incase
}
}

 

Any idea's?

 

Worst come to worst I would just have to do a query for each type of feedback. Would have to do, WHERE feedback_rating = 1, etc. Would like to figure out how to utilize the group by though.

 

Thanks!

[redarrow]

What error you get, and your learning mysql joins i guess

[N1CK3RS0N]

What error you get, and your learning mysql joins i guess

 

No errors.

 

I'm posting $post[pos1] in the templates and its just outputting nothing.

 

EDIT:

 

Not sure MySQL forums is the right place for this. Trying to figure out how to turn the MySQL into a variable for PHP.

[Zane]

put this line here

while ($vbulletin->db->fetch_array($stats)) {
   echo "", print_r($stats), "\n";
   switch ($stats['feedback_rating']) {

 

If the array is empty it's because your MySQL statement isn't retrieving anything, which is why I moved this to MySQL...because most likely you have something wrong with your SQL

 

EDIT: then again..this is vbulletin code and you may just have the API usage wrong.

You probably have to attach the vbulletin_fetch_array to a variable the same as you do mysql_fetch_array

 

so something like

while ($row = $vbulletin->db->fetch_array($stats)) {
     switch ($row['feedback_rating']) {

[N1CK3RS0N]

put this line here

while ($vbulletin->db->fetch_array($stats)) {
   echo "<pre>", print_r($stats), "</pre>\n";
   switch ($stats['feedback_rating']) {

 

If the array is empty it's because your MySQL statement isn't retrieving anything, which is why I moved this to MySQL...because most likely you have something wrong with your SQL

 

EDIT: then again..this is vbulletin code and you may just have the API usage wrong.

You probably have to attach the vbulletin_fetch_array to a variable the same as you do mysql_fetch_array

 

so something like

while ($row = $vbulletin->db->fetch_array($stats)) {
     switch ($row['feedback_rating']) {

 

Thanks Zanus. I wasn't sure at first what was wrong, but I know now it isn't the query.

 

At first I did assign the query to a variable in the While Loop, but it didn't help. So I removed it and still no function.

 

I based it off of a different script that does something similar, as seen below.

 

	$itraders = $vbulletin->db->query_read("SELECT COUNT(*) AS usercount, rating 
										FROM " . TABLE_PREFIX . "itrader 
										WHERE rateduserid = " . $userinfo['userid'] . "
										 AND dateline >= " . mktime(0,0,0,date('m')-1,date('d'),date('Y')) . "
										GROUP BY rating
									");

while ($itrader = $vbulletin->db->fetch_array($itraders))
{
	switch ($itrader['rating'])
	{
	case 1:
	   $userinfo['tradepos30'] = $itrader['usercount'];
	   break;
	case 0:
	   $userinfo['tradeneu30'] = $itrader['usercount'];
	   break;
	case -1:
	   $userinfo['tradeneg30'] = $itrader['usercount'];
	   break;
	default:
	   // Just incase
	}
}

 

Theirs works, but I cannot get mine to work... Odd isn't it?

[N1CK3RS0N]

Not sure if it needs the $vbulletin-> before it. Most my code does $db-> without the $vbulletin->. Tried without and still no luck. Heres how the code looks so far...

 

$stats = $db->query_read("
SELECT feedback_rating, COUNT(*) AS feedback_count
FROM " . TABLE_PREFIX . "forumgold_feedback 
WHERE feedback_to = " . $vbulletin->userinfo['userid'] . " 
	AND feedback_timestamp >= " . mktime(0,0,0,date('m')-1,date('d'),date('Y')) . " 
GROUP BY feedback_rating
");			

while ($row = $db->fetch_array($stats)) {
switch ($row['feedback_rating']) {
	case 1:
		$post['pos1'] = $row['feedback_count'];
		break;
	case 0:
		$post['neu1'] = $row['feedback_count'];
		break;
	case -1:
		$post['neg1'] = $row['feedback_count'];
		break;
	default:
		// Just incase
}
}