Guy
Posts: 60
|
| Posted: 12/29/2004, 8:33 AM |
|
Hello,
I use mssql and php.
I would like in browse grid, add a calculated field based on a sql statement with count(*)
exemple : select count(*) where fielda=xxxx
but i can't arrive to recup the result. I don't know how to do
I use this statement before show :
global $TRANSPORT;
// Write your own code here.
$db = new clsDBVentauto();
$camion = CCGetFromGet("camion", 0);
$SQL= "Select count(*) from VEHICULE where (pointveh is null or pointveh=0) and camion=".$camion;
$db->query($SQL);
$Result = $db->next_record();
// $transport->SetValue($result);
**
the name of my variable is nbveh
Can you help me cause i'm a newbie with php and CCS
Thanks you
Guy
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
 |
 |
Martin K.
|
| Posted: 12/29/2004, 8:49 AM |
|
Hello.
Try this.
global $TRANSPORT;
$Result = "";
$camion = CCGetFromGet("camion", 0);
if($camion > 0) {
$db = new clsDBVentauto();
$Result = CCDLookUp("COUNT(*)", "VEHICULE", "(pointveh IS NULL OR pointveh = 0) AND camion=".CCToSQL($camion, ccsInteger)."", $db);
$db->close();
}
$transport->SetValue($Result);
Greets Martin
|
|
|
|
Guy
Posts: 60
|
| Posted: 12/30/2004, 12:45 AM |
|
Thank you martin
I tried your code but i have a problem with this code
$transport->SetValue($Result);
it say me problem object or function
I don't understand one thing. I have create one label with name "nbveh" for the result of the code but i don't see it in my code
I'm lost
Greets Guy
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Martin K.
|
| Posted: 12/30/2004, 3:26 AM |
|
Hello.
Try this.
global $TRANSPORT;
$Result = "";
$camion = CCGetFromGet("camion", 0);
if($camion > 0) {
$db = new clsDBVentauto();
$Result = CCDLookUp("COUNT(*)", "VEHICULE", "(pointveh IS NULL OR pointveh = 0) AND camion=".CCToSQL($camion, ccsInteger)."", $db);
$db->close();
}
$transport->nbveh->SetValue($Result);
Martin
|
|
|
|
Guy
Posts: 60
|
| Posted: 12/30/2004, 5:26 AM |
|
it's the same martin !
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
peterr
Posts: 5971
|
| Posted: 12/30/2004, 12:03 PM |
|
You probably need to decide if you want to use $TRANSPORT or $transport. It should be the same as the grid name on your page.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Guy
Posts: 60
|
| Posted: 12/30/2004, 12:45 PM |
|
You say me that the case is sensitive ?
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
peterr
Posts: 5971
|
| Posted: 12/30/2004, 1:09 PM |
|
I didn't know, but just tested it and it looks like PHP is. Why don't you do the same test? I always use the correct case anyway - don't see a reason not to.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Guy
Posts: 60
|
| Posted: 12/30/2004, 11:55 PM |
|
Peter, when i say to Mike "it's the same" i just want to say "it's the same error"
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Guy
Posts: 60
|
| Posted: 12/31/2004, 12:02 AM |
|
Peter,
Ok the case is sensitive and i don't have error now, but i don't have the result too.
How can i display my variable during the runtime ?
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Martin K.
|
| Posted: 12/31/2004, 12:53 AM |
|
Hello.
I dont known how the code will not show you the count. The Code is right. Please call me the names of your Side / Form / Field. Is the Side an include Page or not? Where do you put the code. Before Show from the Field or the Form ...? Your Form are an Grid, record or search?
greets martin
|
|
|
|
Guy
Posts: 60
|
| Posted: 12/31/2004, 1:42 AM |
|
Hello,
i putted the code in "before show" in my label field
The name of my file is transport_list.ccp but i have 2 form one for search (name TRANSPORTSearch) and one for the grid record (name TRANSPORT)
I put all the code in my field
<?php
//BindEvents Method @1-79A3EC27
function BindEvents()
{
global $TRANSPORT;
$TRANSPORT->nbveh->CCSEvents["BeforeShow"] = "TRANSPORT_nbveh_BeforeShow";
}
//End BindEvents Method
//TRANSPORT_nbveh_BeforeShow @74-F87D516A
function TRANSPORT_nbveh_BeforeShow()
{
$TRANSPORT_nbveh_BeforeShow = true;
//End TRANSPORT_nbveh_BeforeShow
//Custom Code @75-8141CDCD
// -------------------------
global $TRANSPORT;
// Write your own code here.
$db = new clsDBVentauto();
$camion = CCGetFromGet("camion", 0);
$SQL= "Select count(*) from VEHICULE where (pointveh is null or pointveh=0) and camion=".$camion;
$db->query($SQL);
$Result = $db->next_record();
$TRANSPORT->nbveh->SetValue($result);
// $Result = "";
// $camion = CCGetFromGet("camion", 0);
// if($camion > 0) {
// $db = new clsDBVentauto();
// $Result = CCDLookUp("COUNT(*)", "VEHICULE", "(pointveh IS NULL OR pointveh = 0) AND camion=".CCToSQL($camion, ccsInteger)."", $db);
// $db->close();
// }
// $TRANSPORT->nbveh->SetValue($Result);
// -------------------------
//End Custom Code
//Close TRANSPORT_nbveh_BeforeShow @74-AAC54586
return $TRANSPORT_nbveh_BeforeShow;
}
//End Close TRANSPORT_nbveh_BeforeShow
?>
Be careful i stayed the code of peter in comments
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Martin K
|
| Posted: 12/31/2004, 2:59 AM |
|
Hello.
Try this Code.
<?php
//BindEvents Method @1-79A3EC27
function BindEvents()
{
global $TRANSPORT;
$TRANSPORT->nbveh->CCSEvents["BeforeShow"] = "TRANSPORT_nbveh_BeforeShow";
}
//End BindEvents Method
//TRANSPORT_nbveh_BeforeShow @74-F87D516A
function TRANSPORT_nbveh_BeforeShow()
{
$TRANSPORT_nbveh_BeforeShow = true;
//End TRANSPORT_nbveh_BeforeShow
//Custom Code @75-8141CDCD
// -------------------------
####################################################
global $TRANSPORT;
$Result = "";
$camion = CCGetFromGet("camion", 0);
if($camion > 0) {
$db = new clsDBVentauto();
$Result = CCDLookUp("COUNT(*)", "VEHICULE", "(pointveh IS NULL OR pointveh = 0) AND camion=".CCToSQL($camion, ccsInteger)."", $db);
$db->close();
}
$TRANSPORT->nbveh->SetValue($Result);
####################################################
// -------------------------
//End Custom Code
//Close TRANSPORT_nbveh_BeforeShow @74-AAC54586
return $TRANSPORT_nbveh_BeforeShow;
}
//End Close TRANSPORT_nbveh_BeforeShow
?>
You must write only the Code between the #### Lines. All other Code automaticly come from CCS. Sorry about my english.
greets martin
|
|
|
|
Guy
Posts: 60
|
| Posted: 12/31/2004, 5:42 AM |
|
Always the same no error now, but no value in nbveh.
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Martin K
|
| Posted: 12/31/2004, 9:52 AM |
|
Hello.
If in your URL transport_list.php?camion=1&
the Field nbveh must minimum 0. If there are no camion in the URL there are no Value in nbveh.
greets Martin
|
|
|
|
Guy
Posts: 60
|
| Posted: 01/01/2005, 8:42 AM |
|
Hello,
Martin the field camion is not in the url is in the record ! I think the problem is here.
How can i retrieve the value of camion in the record ?
Thank you
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Guy
Posts: 60
|
| Posted: 01/01/2005, 8:50 AM |
|
But i tested with nothing in the clause WHERE and it's the same nothing in nbveh
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Guy
Posts: 60
|
| Posted: 01/01/2005, 9:28 AM |
|
I think i found the problem but not the solution
If i use a echo statement i see my constant variable in the top of page. the same number like row but not in each row (records).
I think my custom code is not in appropriate embed.
What is the good statement to display a value in grid record and a each record ?
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Martin K
|
| Posted: 01/02/2005, 1:11 AM |
|
Hello.
What´s this?
$camion = CCGetFromGet("camion", 0);
This is the code to Get Value from URL.
If you write $camion = 1; there must a Value in nbveh. Yes or NO??
You do not write that camion in a Field in your Form.
If you want to get the Value from a Field you can write:
$camion = $TRANSPORT->camion->GetValue();
OR
$camion = CCGetFromPost("camion"); // From hidden Field
OR
$camion = CCGetParam("camion");
greets and happy new year,
martin
|
|
|
|
Martin K
|
| Posted: 01/02/2005, 1:21 AM |
|
Hello.
####################################################
global $TRANSPORT;
$Result = "";
$camion = $TRANSPORT->camion->GetValue();
if($camion > 0) {
$db = new clsDBVentauto();
$Result = CCDLookUp("COUNT(*)", "VEHICULE", "(pointveh IS NULL OR pointveh = 0) AND camion=".CCToSQL($camion, ccsInteger)."", $db);
$db->close();
}
$TRANSPORT->nbveh->SetValue($Result);
####################################################
martin
|
|
|
|
Guy
Posts: 60
|
| Posted: 01/02/2005, 1:36 AM |
|
Hello Martin,
First Happy New Year.
Second, you resolv my problem, effectively the field camion was not good and now it's OK.
Thanks you very much for your patience. You are good.
Sorry for my poor english i'm french.
Thanks
Greets
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
Martin K
|
| Posted: 01/02/2005, 1:47 AM |
|
Hello an fine that the Code happend now.
I wondering that you are working in the night, but if you are from france we are nearly in the same timezone.
Greets martin from germany
|
|
|
|
Guy
Posts: 60
|
| Posted: 01/02/2005, 2:19 AM |
|
Yes Exactly and i'm in north est from france in METZ nearly luxembourg and germany.
_________________
CCS 3 - PHP 4 - MS SQL 2000 - IIS 5
Guy |
|
|
|
klwillis
Posts: 428
|
| Posted: 01/02/2005, 9:00 AM |
|
When selecting aggregated values (i.e. sum, count, average, etc..),
be sure to use an alias within your SQL statement to refer to the value.
i.e. select count(*) as field_count . . .
Quote Guy:
Yes Exactly and i'm in north est from france in METZ nearly luxembourg and germany.
_________________
Kevin Willis, VP/CIO
HealthCare Information Technology Specialist
http://www.nexushealthcare.com
"Fast - Convenient - Quality-Care"
Medical Software Consulting Services
Email : klwillis@nexushealthcare.com
Skype : klwillis2006 |
|
|
|
Mo
|
| Posted: 01/12/2005, 4:55 PM |
|
Hi All!
i am having a big problem doing following query?
question is "List the titles and creation dates of all blogs, as well as the total number of blog entries associated with that blog?"
the query should look at the "Subject_Title" in the blog_Entries table and "PostComment" field from PostReply table. the count should count the Subject_title and how many post reply it has for that subject.
this is my code
"select BLOG_ENTRIES_ID,Creation_DATE, count(PostComment)
from Blog_Entries, PostReply
where Subject_Title=PostComment
group by BLOG_ENTRIES_ID,Creation_DATE;"
i have try everything i don what to do now? please help me
i have below list the two tables;
thanking u in advance
bye
Mo
========================================================
CREATE TABLE Blog_Entries
(BLOG_ENTRIES_ID VARCHAR2(20) PRIMARY KEY,
EMAIL VARCHAR2(20), CONSTRAINT Blog_Entries_FOREIGN_KEY FOREIGN KEY (EMAIL) REFERENCES Bloggers (EMAIL),
Creation_DATE DATE DEFAULT SYSDATE,
Subject_Title VARCHAR2(40),
Entries_Text VARCHAR2(160));
========================================================
CREATE TABLE PostReply
(POST_REPLY_ID VARCHAR2(8) PRIMARY KEY,
BLOG_ID VARCHAR2(10), CONSTRAINT PostReply_FOREIGN_KEY FOREIGN KEY (BLOG_ID) REFERENCES Blog (BLOG_ID),
Subject VARCHAR2(40),
PostComment VarChar2(1000),
REPLY_DATE DATE DEFAULT SYSDATE);
========================================================
|
|
|
|
