I am trying to modify some PHP code that was created ages ago in Codecharge, to include a Group By clause in a MYSQL statement.
I have tested my MYSQL query and it works brilliantly in phpmyadmin. When I bring it in to the php page it kicks back an error because the WHERE and ORDER clauses are coded in separate locations, and then called in to the query string. Obviously, when the GROUP BY is left in the SQL it breaks because it is inserting it before the WHERE in the $this chain, so I need to figure out where I can insert the GROUP BY part of the code so it strings it all together in the proper order and the page loads as it should. Below is the 3 sections that pull together the SQL and clauses, and the webpage loads fine. I commented out by version of the SQL as it doesnt work properly without the GROUP BY.
//SetOrder Method @5-0FECF370
function SetOrder($SorterName, $SorterDirection)
{
$this->Order = "book_name";
$this->Order = CCGetOrder($this->Order, $SorterName, $SorterDirection,
"");
}
//End SetOrder Method
//Prepare Method @5-C6449552
function Prepare()
{
$this->wp = new clsSQLParameters($this->ErrorBlock);
$this->wp->AddParameter("1", "urlshow_id", ccsInteger, "", "", $this->Parameters["urlshow_id"], "", false);
$this->wp->Criterion[1] = $this->wp->Operation(opEqual, "ttb_books.show_id", $this->wp->GetDBValue("1"), $this->ToSQL($this->wp->GetDBValue("1"), ccsInteger),false);
$this->Where = $this->wp->Criterion[1];
}
//End Prepare Method
//Open Method @5-33D1EC54
function Open()
{
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeBuildSelect");
$this->CountSQL = "SELECT COUNT(*) " .
"FROM (ttb_books INNER JOIN ttb_states ON " .
"ttb_books.state_id = ttb_states.state_id) INNER JOIN ttb_statuses ON " .
"ttb_books.status_id = ttb_statuses.status_id";
$this->SQL = "SELECT * " .
"FROM (ttb_books INNER JOIN ttb_states ON " .
"ttb_books.state_id = ttb_states.state_id) INNER JOIN ttb_statuses ON " .
"ttb_books.status_id = ttb_statuses.status_id ";
//$this->SQL = "SELECT ttb_books.book_id, ttb_books.book_name, ttb_books.show_id, ttb_states.state_name, COUNT(ttb_imgs.img_id) AS images " .
//"FROM (ttb_books INNER JOIN ttb_states ON ttb_books.state_id = ttb_states.state_id) " .
//"INNER JOIN ttb_statuses ON ttb_books.status_id = ttb_statuses.status_id " .
//"LEFT JOIN ttb_imgs ON ttb_books.book_id = ttb_imgs.book_id ";
$this->Group = "ttb_books.book_id, ttb_books.book_name, ttb_books.show_id, ttb_states.state_name";
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeExecuteSelect");
$this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL, $this->Where, ""), $this);
$this->query(CCBuildSQL($this->SQL, $this->Where, $this->Order));
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "AfterExecuteSelect");
$this->MoveToPage($this->AbsolutePage);
}
//End Open Method
The original version of the code just lists all the book titles, and sorts by book name. My version also includes a COUNT, because I want to not only see all the book titles, but also a page count for each book in the list. Like I said, my new SQL works fine in phpmyadmin, and I’m sure it would on the page too if I could get the Grouping to work properly, too. Thank you for your help!
2
Answers
Ok, here is what I did. I stripped out the $this->Where part of the CCBuildSQL line, and put the WHERE directly in to the sql statement, which allowed me to use my GROUP BY and get the results I was looking for.
It looks like you’re not in the IDE and directly altering the generated code. Go into the CodeCharge IDE, choose the control you want to modify, then create a Custom Code Event and put your adjusted SQL there.
http://docs.codecharge.com/studio50/html/index.html?http://docs.codecharge.com/studio50/html/ProgrammingTechniques/HowTo/CustomizingDataSource/ModifyOrderBy.html?toc