CODEWITHSUNDEEP
phpmysql
Kaka
Kaka

Reputation: 395

PDO query not returning expected result

I'm making a forum for my game. Currently I'm working on the edit function. What I'm trying to figure out how I could check that the logged in account owns the player that posted the message. You post with your player, not account. You can have several players. An user can have several "players" assigned to them. If they don't own the player that posted the message I want to return false, but if they do, true.

# table accounts
id | username | password

# table posts
id | posterid (this is the player id) | message

# table players
id | account_id | name

This is how far I have come. But this returns false no matter what. There is a post with ID 666 and the player that posted it is owned by account 34767. So it should work.

function can_we_edit_post($pid) {

global $db;

// Let's see if they have permission to edit
$stmt = $db->prepare("SELECT * FROM players pl a JOIN posts p ON p.id = $pid WHERE pl.account_id = 34767");
$stmt->execute(array(34767));
$row = $stmt->fetch();

// Check if we got any rows
if ($row) {
    return true;
} else {
   return false;
}

}

if (can_we_edit_post(666)) {
   echo "You may edit this post.";
} else {
  echo "You do not own this post.";
}

Upvotes: 0

Views: 78

Answers (2)

Sean
Sean

Reputation: 12433

You have an errant a after pl, so your query is probably failing

SELECT * FROM players pl a JOIN posts p ON p.id = $pid WHERE pl.account_id = 34767
                         ^

try something like this (using placeholders to prevent SQL injection) -

// Let's see if they have permission to edit
$stmt = $db->prepare("SELECT * FROM players pl JOIN posts p ON p.id = ? WHERE pl.account_id = ?");
$stmt->execute(array($pid, 34767));
$row = $stmt->rowCount();

take a look at this sqlfiddle - http://sqlfiddle.com/#!2/e282a1/2 - without the a the query returns a result, with the a the query fails with an error.

EDIT
It is probably returning true for every player, because you hardcoded the pl.account_id -

WHERE pl.account_id = 34767

and you are not verifying if the posterid matches the pl.id for the specific post.id which you can do by adding - AND p.posterid = pl.id to your JOIN 

function can_we_edit_post($pid,$aid) {

global $db;

// Let's see if they have permission to edit
$stmt = $db->prepare("SELECT * FROM players pl JOIN posts p ON p.id = ? AND p.posterid = pl.id WHERE pl.account_id = ?");
$stmt->execute(array($pid, $aid));

// Check if we got any rows
if ($stmt->rowCount()) {
    return true;
} else {
   return false;
}

}


if (can_we_edit_post(666,34767)) { // change 34767 to each player account_id ie. $player->account_id
   echo "You may edit this post.";
} else {
  echo "You do not own this post.";
}

Upvotes: 1

OptimusCrime
OptimusCrime

Reputation: 14863

You are using PDO wrong. You should do it like this:

$stmt = $db->prepare("SELECT * FROM players pl a JOIN posts p ON p.id = :pid WHERE pl.account_id = :id");
$stmt->execute(array(':pid' => $pid, ':id' => 34767));
return (($stmt->rowCount() > 0)? true : false);

PDO and execute is genius because you just supply keys you want to replace with values, and it will escape and make it harmless for you.

If the rest of the query is correct, this code should work. rowCount returns the number of rows returned by the query. If you just want to see if it returns anything, you can use this instead of using fetch.

Upvotes: 1

Related Questions