SELECT COUNT Related to an ID

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
aromedia
User
Posts: 7

SELECT COUNT Related to an ID

Post by aromedia »

Hi,
I have a table with ads, and a table with offers for that add.

The link between them is the add ID whic is: BC_RequestID

I would like to display in column the number of offers received for that add:

$BC_RequestID=$this->BC_RequestID->ListViewValue();

$sql = "SELECT * FROM Requests_Basic_Offers WHERE BC_RequestID = '$BC_RequestID'";
$rs = ew_Execute($sql);
$offers=$rs->RecordCount();

$this->ListOptions->Items["addetail"]->Body = "
Offers received: $offers";

However, it seems impossible to find online how to do it, if I put the syntax from normal php and mysql is asking me to login again on mysql. Please help.
Thank you.

PS: Is there any post with how to add substract count etc, in phpmaker?


mobhar
User
Posts: 11660

Post by mobhar »

Use ew_ExecuteScalar() global function to get the first column of the first record that returned by SQL. Read "Some Global Functions" under "Server Events and Client Scripts" topic from PHPMaker Help menu for more info.

In addition, use CurrentValue instead of ListViewValue.


aromedia
User
Posts: 7

Post by aromedia »

Thank you for your fast reply.
I've changed 12 to 13 as I have another version of PHPmaker and I've added:

include_once "ewdbhelper13.php";
$DB = DbHelper ();
$RequestID=$this->BC_RequestID->CurrentValue();
$sql = "SELECT * FROM Requests_BasicCabins_Offers WHERE BC_RequestID = '$RequestID'";
$sqlrep = $DB->ExecuteScalar($sql);

this->ListOptions->Items["addetail"]->Body = "Offers received: $sqlrep";

However when I try with CurrentValue I get this error:
Fatal error: Call to undefined method cField::CurrentValue() in /home/***list.php on line 2966

On line 2966: $RequestID=$this->BC_RequestID->CurrentValue();

Any idea? Thank you!

Is there any post with examples like how to do math, how to count etc. with phpmaker? I think this is the only thing that's missing from this great program.


mobhar
User
Posts: 11660

Post by mobhar »

Did you use it in Custom Files? If so, then you cannot use CurrentValue property that belongs to the Fields object, unless you created that object before. So, to get the value of BC_RequestID from Database in Custom Files, then you should use ew_ExecuteScalar(), too.


aromedia
User
Posts: 7

Post by aromedia »

Hi,
I've put it in a view, in ListOptionsRender. I only want to count how many records of a specific ID are in a different table...


mobhar
User
Posts: 11660

Post by mobhar »

No need to use dbhelper if you write your PHP code in "ListOptions_Rendered" server event.

As mentioned before, you just only need to call ew_ExecuteScalar() global function to get the recordcount based on $this->BC_RequestID->CurrentValue param, and NOT $this->BC_RequestID->CurrentValue().


aromedia
User
Posts: 7

Post by aromedia »

Hi,

I'm sorry but I don't understand and I couldn't find too many info in the forum or help for scalar

This is what I've got now in the view in List Page / ListOptions_Render

include_once "ewdbhelper13.php";

$RequestID=$this->BC_RequestID->CurrentValue;

$value = ew_ExecuteScalar ("SELECT * FROM Requests_BasicCabins_Offers WHERE BC_RequestID = $RequestID");

$sqlrep = $DB->ExecuteScalar($value);

$this->ListOptions->Items["addetail"]->Body = "Offers received: $sqlrep";

However, now I get 2 errors:

Fatal error: Call to a member function ExecuteScalar() on null in /home/r50137logs/public_html/r_request_basiccabinlist.php on line 2968 where is: $sqlrep = $DB->ExecuteScalar($value);

and

Failed to execute SQL. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I don't understand what I'm doing wrong. Sorry to be a pain. Thank you very much.


mobhar
User
Posts: 11660

Post by mobhar »

As mentioned before, you don't need to use dbhelper. Just use ew_ExecuteScalar() global function.


Post Reply