Error in my SQL syntax

PHPMaker General Discussion (PHPMaker)
1

Hi all,When mailing the data (1,2) that comes with a comma from the lookup table, I get the following error:C:\inetpub\wwwroot\app\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractMySQLDriver.php(98): An exception occurred while executing ‘SELECT ContactTypeFR FROM contacttypes WHERE ContactTypeID =1,2’: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘,2’ at line 1field where I get data from lookup table = ContactTypes (varchar,255 and sample field data= 1,2) (phpmaker lookup table field settings = select & multiple)
lookup table = contacttypes
ContactTypeID, ContactTypeEN, ContactTypeFR
1, Eng_item1, Fra_item2,
2, Eng_item1, Fra_item2,

mail_sending code:" . ExecuteScalar(“SELECT ContactTypeFR FROM contacttypes WHERE ContactTypeID = explode(‘’,” “. CurrentPage()->ContactTypes ->CurrentValue .”)") . "I know very well that I wrote this code wrong, which examples should I look for for the right method?

2

Double check your code:
ContactTypeID = explodeIt should be:
ContactTypeID = " . explodeand adjust the closing part of that explode function code, too.

3

I re-edited the code as you said. The page worked without any errors, but when sending mail it gave the following error." . ExecuteScalar(“SELECT ContactTypeFR FROM contacttypes WHERE ContactTypeID = " . explode(”,", CurrentPage()->ContactTypes ->CurrentValue). “”) . "Error:
C:\inetpub\wwwroot\app\src\DbTableBase.php(395): Undefined property: ContactTypes.

4

Because in Email_Sending server event, you should use the following code in order to get the value from the certian field, for example:$myValue = $args[“rsnew”][“ContactTypes”];or if it does not work, you may try the following one instead:$myValue = $args[“rsold”][“ContactTypes”];You may read Server Events and Client Scripts topic from PHPMaker Help menu, and see Email_Sending section for more info and example.You may post your complete code in Email_Sending server event for more discussion.

5 
// Email Sending event
function Email_Sending($email, &$args)
{
    //var_dump($email); var_dump($args); exit();
    return true;
}

	if (CurrentPageID() == "edit") {

		if ($args["rsnew"]["Contacted"] == "1") {

			$email->Sender = CurrentUserInfo("Email")";
			$email->Recipient = "group@argustour.com";
			$email->Subject = "Contacted";
			$email->Content = " . ExecuteScalar("SELECT ContactTypeFR FROM contacttypes WHERE ContactTypeID = " . explode(",", CurrentPage()->ContactType ->CurrentValue). "") . ";

		if ($args["rsold"]["Contacted"] == "1") return false;
			} else {
			return false;
	}
}

ContactType field CurrentValue= 1,2 (from contacttypes table with table lookup properties (select & multiple)I want data with id value 1 and 2 in contenttypes table to be sent to email.For example,contacttypes table data:
ContactTypeID | ContactTypeEN | ContactTypeFR
1 | Telephone | Téléphone,
2 | E-Mail | E-Mail,
3 | Visit | Visite,What I can’t do;
Email data I want: Téléphone, E-Mail

6

You should insert your code before or above return true; line.

7

I don’t have a problem with if, else, true false conditions. Mail function is working. The problem is I just can’t get the data in the type I want as I mentioned above.when i run it like this the error code is:C:\inetpub\wwwroot\app\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractMySQLDriver.php(79): An exception occurred while executing ‘SELECT ContactTypeFR FROM contacttypes WHERE ContactTypeID = Array’: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Array’ in ‘where clause’

8

You should not use this code in Email_Sending server event: CurrentPage()->ContactType->CurrentValueYou should use the code that I suggested to you above.

9

well,
Please assume that an email is sent each time the record is edited.

  • When I use $args[“rsnew”] it throws an error if the existing data is not updated.
  • The $args[“rsold”] code always just sends the old value of the data.

There is a possibility that the data of the field may be updated. I have to use “CurrentPage()->MyField->CurrentValue” to always send the current value of the data.Is there something I don’t understand about the $args thing? I can’t use the $args event to always send the current value of the field.

10

I did it again as you said.

// Email Sending event
function Email_Sending($email, &$args)

	if (CurrentPageID() == "edit") {

		if ($args["rsnew"]["Contacted"] == "1") {

			$myValue = $args["rsold"]["ContactTypes"];

			$email->Sender = CurrentUserInfo("Email")";
			$email->Recipient = "group@argustour.com";
			$email->Subject = "Contacted";
			$email->Content = " . ExecuteScalar("SELECT ContactTypeFR FROM contacttypes WHERE ContactTypeID = " . explode(",",$myValue) .  "") . "

		if ($args["rsold"]["Contacted"] == "1") return false;
			} else {
			return false;
	}
}

Error code:
C:\inetpub\wwwroot\app\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractMySQLDriver.php(79): An exception occurred while executing ‘SELECT ContactTypeFR FROM contacttypes WHERE ContactTypeID = Array’: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Array’ in ‘where clause’

11

Unfortunately I found such complex solution as I can’t generate syntax, string, query code.
I placed the field function created by phpmaker inside the email function and thus I was able to send the view value to the email.

  // Email Sending event
    public function emailSending($email, &$args) 
    	{

    	if (CurrentPageID() == "edit") {
    	
// ContactType	
        $curVal = trim(strval($this->ContactType->CurrentValue));
        if ($curVal != "") {
            $this->ContactType->ViewValue = $this->ContactType->lookupCacheOption($curVal);
            if ($this->ContactType->ViewValue === null) { // Lookup from database
                $arwrk = explode(",", $curVal);
                $filterWrk = "";
                foreach ($arwrk as $wrk) {
                    if ($filterWrk != "") {
                        $filterWrk .= " OR ";
                    }
                    $filterWrk .= "`ContactTypeID`" . SearchString("=", trim($wrk), DATATYPE_NUMBER, "");
                }
                $sqlWrk = $this->ContactType->Lookup->getSql(false, $filterWrk, '', $this, true, true);
                $rswrk = Conn()->executeQuery($sqlWrk)->fetchAll(\PDO::FETCH_BOTH);
                $ari = count($rswrk);
                if ($ari > 0) { // Lookup values found
                    $this->ContactType->ViewValue = new OptionValues();
                    foreach ($rswrk as $row) {
                        $arwrk = $this->ContactType->Lookup->renderViewRow($row);
                        $this->ContactType->ViewValue->add($this->ContactType->displayValue($arwrk));
                    }
                } else {
                    $this->ContactType->ViewValue = $this->ContactType->CurrentValue;
                }
            }
        } else {
            $this->ContactType->ViewValue = null;
        }
        $this->ContactType->ViewCustomAttributes = "";			
			
			if ($args["rsnew"]["Contacted"] == "1") {
				$email->Sender = CurrentUserInfo("Email")";
				$email->Recipient = "group@argustour.com";
				$email->Subject = "Contacted";
    			        $email->Content = " . $this->ContactType->ViewValue . ";
    		
    			} else {
    			return false; 
    		}
    	}	
    return true;
    }
12

You’d better learn about PHP explode() function.

13

I read the documentation to learn the explode() function. I looked at what can be done with the explode function with the obtained data.Moreover, I understood from the help document that I should do this with ExecuteRows, not ExecuteScalar.You’ve been very helpful up to this point. Thanks.

14

This is one of the example of usage of ExecuteRows: https://discourse.hkvstore.com/t/get-array-and-insert-another-table/3536/3