SQL query is returning the literal SQL query and not executing it in CakePHP 4

Testing
1

I don’t know what any of this is called so I don’t know how to google for it. The query I’m trying to execute in CakePHP 4 is returning the SQL query literally and not executing it. The send_email method in UsersController is being called in the RecipesController. The email I receive from the send_email method is printing out the SQL query as follows:

Hi, Fruit Salad was added at https://projectpotluck.com/recipes/view/73 by contributing cook SELECT Users.contributor AS Users__contributor FROM users Users WHERE Users.id = :c0

is the result of
$user_id = $recipe → user_id;
$cooks_name = $this->Users->find()->select([‘contributor’])->where([‘Users.id’ => $user_id]);

The entire method is shown below:

public function send_email($recipe) {	
		$mailer = new Mailer('default');	
        $users = $this->Users->find('all');
		$recipename = $recipe -> recipename;
		$user_id = $recipe -> user_id;
		//also tried setting $user_id = 1 to test but got the same result
		$cooks_name =  $this->Users->find()->select(['contributor'])->where(['Users.id' => $user_id]);
		$id = $recipe -> id;	
		$mailer->setTransport('gmail'); 
		$email = "myname@website.com";
		$mailer->setFrom(['myname@googleworkspace.com' => 'Recipe Added'])
		->addTo($email)
		->setEmailFormat('html')
		->setSubject('Recipe added at Project Potluck')
		->deliver("Hi, $recipename was added at <a href=\"https://projectpotluck.com/recipes/view/$id\">https://projectpotluck.com/recipes/view/$id</a> by contributing cook {$cooks_name}");
			
}
}
2

Try

$cooks_name =  $this->Users->find()->select(['contributor'])->where(['Users.id' => $user_id])->first();

But still this returns a User Entity, not only that specific field.
I would do something like this so its clearer whats happening:

$entity =  $this->Users->find()
    ->select(['contributor'])
    ->where(['Users.id' => $user_id])->first();
$cooks_name = $entity->contributor;

For your explenation: You only created a query instance but didn’t tell cake to execute it.
In my example the ->first(); call tells cake to execute the query and return the first result (if one is available).
You will always get back an entity object which represents that row of data in your database, so you can’t just output that row.
This object has fields inside it which represents your database column values.

3

Thanks, KevinPfeifer. I had to look up entity in the documentation. I see that it’s a single result row and not a specific field like you also mentioned.
Tomorrow I’m going to add a recipe to the website and see if I can send an email about it to every user with the following code:

		foreach($users as $user) {
			$email->addTo($users -> email);
		}

I’m hoping it works!

4

I’m not sure this is going to work because up to now, I’ve only used foreach in the templates. Is the following going to work?

foreach ($users as $user) {
			$email = $users -> email;
			}
			$mailer->setTransport('gmail'); 
			->addBcc($email)
5

The code above didn’t work!

6

Maybe this will work:

$email = array();
foreach($users as $user)
{
$email[] = $users -> email;
}
7

That didn’t work either. There was some error message about $email should be a string.

8

I tried using ->addBcc($email[]) and got the error: Cannot use [] for reading

9

You’re making a couple of basic PHP mistakes here.

In every foreach example, you’ve made the same error by using the plural variable (which contains the array to iterate over) inside the loop where you should be using the singular variable (which represents the current, individual array element).

This may be a simple typo or you may need to look again at how foreach loops work.

In the final snippet you’ve tried to you’ve tried to look at an array element without specifying the index of the element you want.

Again, this may be a typo or you may still be grappling with the use of arrays.

Here are the two relevant PHP manual pages if the concepts and their use are still a bit fuzzy:
https://www.php.net/manual/en/control-structures.foreach.php
https://www.php.net/manual/en/function.array

10

Thanks, Don
I don’t think I need to specify the index of the element I want in CakePHP, only in PHP. I put the addBcc in the loop as follows and got the error: The email set for “bcc” is empty

$users = $this->Users->find('all');
$mailer = new Mailer('default');
foreach ($users as $user) {
	$email = $user -> email;
	$mailer->addBcc($email);
	}
11

Do all of your user records actually have the email field filled in?

12

Yes, all of the email fields are filled in.

13

Some extremely basic debugging for this would be:

foreach ($users as $user) {
	$email = $user->email;
	try {
		$mailer->addBcc($email);
	} catch (\InvalidArgumentException $ex) {
		debug($user);
		throw $ex;
	}
}
14

Thanks Zuluru. There was only one record missing the email address but it was enough to throw the same error again: The email set for “bcc” is empty. So I just had to delete that record and then it worked.

15

One record is all it would take, obviously.

You could also do things like exclude anyone that doesn’t have an email address from the query where you get the list of users, or skip over such records in your loop.

16

I put the following in my function and it works:
$users = $this->Users->find()->where(['email IS NOT NULL'])->all();
instead of
$users = $this->Users->find('all');

17

This is an entirely different question, and should have an entirely new post. Otherwise, many people will just see that there is new activity on a question they couldn’t answer, and not even bother looking.

When you make that new post, include a little more information, what we call “minimum reproducible example”. You’ve said here that you “tried” ->notEmptyString('category_id'), but you give no context to that, so we cannot copy your code and see what’s not working about it. Lots of people have used exactly that and it’s worked great for them, so the problem is not present in the code you’ve shown, hence it’s no reproducible. But don’t paste in the whole table class like some people do, because then there’s loads of unrelated code that just makes it hard to wade through and people will give up, this breaks the “minimum” part.

18

Okay, zuluru. I deleted the new question from my last post.