Cleopatra
Posts: 73
|
| Posted: 04/21/2010, 11:29 AM |
|
Hi y'all,
I was wondering if anyone could help me. I'm trying to count the number of records per user
but it does not seem to work.
The statement looks like: Select count(answers) from results where msg_id= --> how do you write the parameter which follows here?
so the statement selects all where the msg_id= that message_id and how can you do this per user?
I've tried with distinct but to no avail.
I don't have reportbuilder otherwise, I could've done this easier.
What do you guys think I could do?
_________________
php newbie |
 |
 |
ckroon
Posts: 869
|
| Posted: 04/21/2010, 1:10 PM |
|
Make a grid of the users. Put the userID in a Hidden field
Then in the before Show Row.. count hte number of records for that userid and then place it in a label on the row.
If you need super details let me know.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/22/2010, 6:06 AM |
|
Thanks Ckroon,
I think I have an idea of what you mean. But could you explain it more detailed, just to be sure that I have grasped what it is that you mean.
_________________
php newbie |
|
|
|
datadoit
|
| Posted: 04/22/2010, 6:16 AM |
|
"SELECT COUNT(user_id) FROM results WHERE msg_id=" .
CCToSQL(CCGetParam("message_id"), ccsInteger) . " GROUP BY user_id"
|
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/22/2010, 11:38 AM |
|
ok datadoit,
I've tried the statement you posted for a label(count) in the before show event.
Looks like this:
$db = new clsDBOracle();
$SQL = "SELECT COUNT(user_id) From resutls WHERE msg_id= ".
CCToSQL(CCGetParam("msg_id"), ccsInteger) . " GROUP BY user_id";
$db->close();
//set the label value
$
_________________
php newbie |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/22/2010, 11:44 AM |
|
How do set the label value in the code that is written above? I've tried with:
$Container->count->SetValue->( )
What comes between the parenthesis?
It's should be the result of the count sql satement, but I'm getting something completely different.
_________________
php newbie |
|
|
|
datadoit
|
| Posted: 04/22/2010, 12:08 PM |
|
In your label's BeforeShow:
global $Oracle();
$db = new clsDBOracle();
$SQL = "SELECT COUNT(user_id) AS Count From results WHERE msg_id= ".
CCToSQL(CCGetParam("msg_id"), ccsInteger) . " GROUP BY user_id";
$db->query($SQL);
$Result = $db->next_record();
if ($Result) {
$Component->SetValue($db->f("Count"));
}
$db->close();
|
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/26/2010, 5:24 AM |
|
Now I'm getting no results at all and I have no idea what I'm doing wrong.
_________________
php newbie |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/26/2010, 5:52 AM |
|
OK, it seems to me that the (CCGetParam("msg_id"),ccsInteger) is not retrieving a value.
So the msg_id it gets = NULL.
How can I solve this problem.
_________________
php newbie |
|
|
|
Waspman
Posts: 948
|
| Posted: 04/26/2010, 6:11 AM |
|
is the parameter in the url?
_________________
http://www.waspmedia.co.uk |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/26/2010, 6:22 AM |
|
No, I don't see any parameters in the url
_________________
php newbie |
|
|
|
Waspman
Posts: 948
|
| Posted: 04/26/2010, 6:24 AM |
|
So how does the parameter get to the page?
_________________
http://www.waspmedia.co.uk |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/26/2010, 6:57 AM |
|
Good question.
I didn't realize that no parameters where shown in the url until you asked.
Using a grid and I want to count all the records by that user --> using the user_id, which is in a hidden field in the grid. I want to be able to see all the users and the number of records by the same users ( grouping by user_id gives me an ora- error) not just the user that is logged in.
_________________
php newbie |
|
|
|
Waspman
Posts: 948
|
| Posted: 04/26/2010, 7:11 AM |
|
So you want to show the all the users and alongside their name show the number of records they have created/added?
Sounds like a report job to me?
_________________
http://www.waspmedia.co.uk |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/26/2010, 7:41 AM |
|
Yes, I know but unfortunately I do not have a report builder now.
When I was using the trial, I tried it with report builder and it worked perfectly.
The question is now: how can I do it without a report builder?
_________________
php newbie |
|
|
|
ckroon
Posts: 869
|
| Posted: 04/26/2010, 10:27 AM |
|
Ok.
Make a grid from your users table.
Put their usersid in a hidden field.
So lets say you have a grid of users: Labels: First Name, Last Name
And the user ID in a hidden field: userid
Then have a blank Label: Label1.
In the Before Show Row event.
//get the value of the userid
$get = $Component->userid->GetValue();
// Set the total number of records for each user
global $DBConnection1;
$Container->Label1->SetValue(CCDLookUp('Count(*)', 'users_tablename', 'usersid='.$check , $DBConnection1));
Not tested but this should do it for you.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
Waspman
Posts: 948
|
| Posted: 04/26/2010, 11:29 AM |
|
Ah Ha...me too
$db = new clsDBConnection1();
$C = CCDLookUp("count(*)","message_table","UserID=".$Container->UserID->GetValue(), $db);
$Container->Label->SetValue($C);
$db->close();
(in "Before show" label though) 
_________________
http://www.waspmedia.co.uk |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/26/2010, 11:50 AM |
|
Thank you. I'll try it and let you know how it went 
_________________
php newbie |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/29/2010, 7:52 AM |
|
Yes!!!! 
Works like a charm. Thanks guys for all your help, I really appreciate it.
_________________
php newbie |
|
|
|
