lammy
Posts: 49
|
| Posted: 04/24/2006, 12:27 PM |
|
I have created a variable named clubacc, I want to now filter the records from a table depending on accountno = clubacc (variable)
in the visual query builder the sql looks like below. when testing it asks for a value for clubacc then runs correctly.
SELECT *
FROM members INNER JOIN officers ON
members.officer_id = officers.officer_id
WHERE members.accountno = {clubacc}
when run in ccs program it does seem to work, the code generated by ccs is below.
{
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeBuildSelect", $this->Parent);
$this->CountSQL = "SELECT COUNT(*) FROM members INNER JOIN officers ON members.officer_id = officers.officer_id WHERE members.accountno = " . $this->SQLValue($this->wp->GetDBValue("1"), ccsInteger) . "";
$this->SQL = "SELECT * \n" .
"FROM members INNER JOIN officers ON\n" .
"members.officer_id = officers.officer_id\n" .
"WHERE members.accountno = " . $this->SQLValue($this->wp->GetDBValue("1"), ccsInteger) . " ";
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeExecuteSelect", $this->Parent);
if ($this->CountSQL)
$this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL, $this->Where, ""), $this);
else
$this->RecordsCount = "CCS not counted";
$this->query($this->OptimizeSQL(CCBuildSQL($this->SQL, $this->Where, $this->Order)));
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "AfterExecuteSelect", $this->Parent);
}
any help would be appreciated in getting it to work
Thanks
Lammy
|
 |
 |
Damian Hupfeld
|
| Posted: 04/24/2006, 4:16 PM |
|
When you say its not working - exactly what happens (or does not happen).
Without reading your code too hard I think that the first thing to try is:
In the visual Query Builder click the Show Data button and see if any
results show there. If not change your inner join to a left join and show
data again... if there is still no data displayed try a right join...
looking at your query I think that the inner join is not going to give you
the matches that you after.
regards
Damian
"lammy" <lammy@forum.codecharge> wrote in message
news:5444d26ae56b1a@news.codecharge.com...
>I have created a variable named clubacc, I want to now filter the records
>from a
> table depending on accountno = clubacc (variable)
>
> in the visual query builder the sql looks like below. when testing it asks
> for
> a value for clubacc then runs correctly.
>
> SELECT *
> FROM members INNER JOIN officers ON
> members.officer_id = officers.officer_id
> WHERE members.accountno = {clubacc}
>
> when run in ccs program it does seem to work, the code generated by ccs is
> below.
>
> {
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "BeforeBuildSelect", $this->Parent);
> $this->CountSQL = "SELECT COUNT(*) FROM members INNER JOIN officers
> ON
> members.officer_id = officers.officer_id WHERE members.accountno = " .
> $this->SQLValue($this->wp->GetDBValue("1"), ccsInteger) . "";
> $this->SQL = "SELECT * \n" .
> "FROM members INNER JOIN officers ON\n" .
> "members.officer_id = officers.officer_id\n" .
> "WHERE members.accountno = " .
> $this->SQLValue($this->wp->GetDBValue("1"), ccsInteger) . " ";
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "BeforeExecuteSelect", $this->Parent);
> if ($this->CountSQL)
> $this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL,
> $this->Where, ""), $this);
> else
> $this->RecordsCount = "CCS not counted";
> $this->query($this->OptimizeSQL(CCBuildSQL($this->SQL,
> $this->Where,
> $this->Order)));
> $this->CCSEventResult = CCGetEvent($this->CCSEvents,
> "AfterExecuteSelect", $this->Parent);
> }
>
> any help would be appreciated in getting it to work
>
> Thanks
>
> Lammy
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
lammy
Posts: 49
|
| Posted: 04/25/2006, 12:36 AM |
|
when I left, inner or right join it shows no records at all in the live page,
but once ive added a value to clubacc in visual query buider it shows the
right information.
Have I crossed over the variable correctly into the code using the brackets {}.
|
|
|
|
WKempees
|
| Posted: 04/25/2006, 1:58 AM |
|
Lammy,
You will have to also tell us in what context you are working.
I assume that you are building a GRID showing the records
The GRID has the SQL you gave us.
Now where do you intend to set the "clubacc" variable??
If it is in the URL like //localhost//my_grid.php?clubacc=10 then :
In the VQB (visual Query Builder)'s WHERE part of the screen (lower right
pane)
what does it say?
press the change button on that line
it should be something like:
Field condition Parameter
Name: members.accountno = clubacc
Type Integer URL
The Parameter Type URL can be a few other types see docs.
Walter
|
|
|
|
lammy
Posts: 49
|
| Posted: 04/25/2006, 2:58 AM |
|
Hi again
The variable is coming from a table in the before show on the grid form
global $DBdatabase;
global $clubacc;
$clubacc = CCDLookUp("AccNo", "clubinfo", "clubinfoid=".$DBdatabase->ToSQL("1", ccsInteger) , $DBdatabase);
there is only one record in the table so just getting the AccNo
then there is a grid which lists all the oficers whos members.accno = {clubacc}
SQL parameters windows says
Name Type Source Default
clubacc URL clubacc 0
where parameter window
members.accountno = {clubacc}
if I use form,url,session,application it lists all the records and doesn't filter them.
using expression I get
Notice: Use of undefined constant clubacc - assumed 'clubacc'
hope this is clearer
Cheers
lammy
|
|
|
|
|
