CakePHP sql queries

Question

I'm sort of new to CakePHP and can't figure out how sql queries work here. I have a delete button which erases news about players. What it's not doing (and it should) is also take care of number of news, which is stored in "news" column in "fc_players" table. In an old-fashioned way I'd go like:

("SELECT player FROM entries WHERE id = '" . $id . "'");

(returns $name of the player; can't just use $id because of how database is built; "entries" is a table where news are stored)

("SELECT news FROM fc_players WHERE name = '" . $name . "'");

(returns how many news about certain player is in the database)

If only one, delete $name from database:

("DELETE FROM fc_players WHERE name = '" . $name . "'");

I've tried to do: $this->FCPlayer->deleteAll(array('FCPlayer.name' => $name)); but for some reason it doesn't work.

If more, simply reduce it:

$news = $news - 1;

("UPDATE fc_players SET news = '" . $news . "' WHERE name = '" . $name . "'");

I can handle it in plain PHP but Cake is a bit harder and I can't find any useful examples. Can you help me to translate it from sql queries to CakePHP or guide me to articles which are actually explaining it?

Thanks!

EDIT:

Ok, Nik and Steve, thanks for you answers. Unfortunately it doesn't solve my problem. I think there's something wrong with handling sql queries.

$this->Session->setFlash($id, 'success'); shows me correct entry id, so $id is fine.

But when I'm trying to get player name with:

$player = $this->Entry->field("player", array("id" => $id));

or with

$this->Entry->id = $id; $player = $this->Entry->field('player');

or execute query

player = $this->Entry->query("SELECT player FROM entries WHERE id = '" . $id . "'");

the result is blank. I did manage to get player name by doing:

$player = $this->Entry->field('player');

but it simply returned name of the player from most recent entry, not the one I'm deleting.

I just can't figure out where I'm making mistake. Is it possible that it returns some kind od array or object that setFlash cannot process? If so, why last example works (sort of)?

which version of cake are you using?

toxicate20
– toxicate20

2012-11-18 19:18:33 +00:00
Commented Nov 18, 2012 at 19:18 — toxicate20
– toxicate20, Commented Nov 18, 2012 at 19:18

Nik Chankov · Accepted Answer · 2012-11-18 23:41:37Z

1

Start the debug from core.php and see why it doesn't remove the entry it's possible to say something in the messages.
Try with

$this->FCPlayer->deleteAll(array('FCPlayer.name LIKE' => $name));
Is it possible the name column to have extra spaces in the beginning or in the end?
You can always use: $this->FCPlayer->query("your-query-goes-here");, but it's not recomended. :)

answered Nov 18, 2012 at 23:41

Nik Chankov

6,0371 gold badge22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AnJ Over a year ago

Ad 3. Names are just names, no spaces at all.

AnJ Over a year ago

Ad 2. Can't try it since I didn't manage to get $name (see EDIT).

AnJ · Accepted Answer · 2012-11-20 16:44:27Z

1

Ok, finally I managed to do this. Correct answer was:

$player = $this->Entry->field('player', array('id' => $id)); // yeah, single '

$news = $this->FCPlayer->field('news', array('name' => $player));

if ( $gossips > 1)
{
$this->FCPlayer->updateAll(array('FCPlayer.news'=>$news - 1),
array('FCPlayer.name'=>$player));
}
else
{
$this->LFCPlayer->deleteAll(array('LFCPlayer.name LIKE' => $player));
}

answered Nov 20, 2012 at 16:44

AnJ

12510 bronze badges

Comments

Alvaro · Accepted Answer · 2012-11-18 20:44:13Z

0

When you delete a piece of news, CakePHP wont take care of the number of views in any other table. Is not so intelligent yet :)

You have to change it manually.

You can use something like:

$number_of_news = $this->Player->field("number_of_news", array("id" => $id));

$this->Player->set("number_of_news" => $number_of_news - 1);

edited Nov 18, 2012 at 20:44

answered Nov 18, 2012 at 20:01

Alvaro

41.7k32 gold badges175 silver badges352 bronze badges

1 Comment

AnJ Over a year ago

Thanks. That's why I was trying to do it step-by-step. I have an id, wanted to get name and deal with entries this way. In your method, how exactly Cake knows which player we are talking about? I miss "WHERE" option...

Collectives™ on Stack Overflow

CakePHP sql queries

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related