(PDO) UPDATE doesn't update with WHERE variable

Question

My problem here is that as soon as i have WHERE with GuildName it does NOT work. It simply doesnt update anything at all.
As soon as i have the id=1 (there are 100 guilds, so setting the id=1 is not an option) it does work.

$form           = $_POST;
$boss           = $form['bossname'];
$gname          = $form['guildname'];
$screen         = $form['screenshot'];
$log            = $form['logs'];
$defeat         = $form['defeat'];

if(isset($_POST['edit-guild'])){

    $Statement = $conn->prepare("UPDATE $boss SET `Bossname` =:boss, `GuildName` =:gname, `Screenshot` =:screen, `Link` =:link, `KillTime` =:defeattime, `KillYN` =:kill WHERE `GuildName`=:gname");
    // EXECUTING ARRAY FOR ^GUILD INFORMATION
    $Statement->execute(array(
        "boss"          => $boss,
        "gname"         => $gname,
        "screen"        => $screen,
        "link"          => $log,
        "defeattime"    => $defeat,
        "kill"          => 'Yes'
        ));
}

As soon as i have WHERE id=1 it does work.

 $Statement = $conn->prepare("UPDATE $boss SET `Bossname` =:boss, `GuildName` =:gname, `Screenshot` =:screen, `Link` =:link, `KillTime` =:defeattime, `KillYN` =:kill WHERE id=1");

Is that right `UPDATE $boss SET` $boss as param and tabelname? — JustOnUnderMillions, Nov 18 '16 at 16:20
I'd recommend using passing through the id for the where instead of the gname, especially if the gname is going to change. Otherwise, why bother changing it? — aynber, Nov 18 '16 at 16:20
@u_mulder makes the hole prepared statement stuff useless here, when $boss is used in this way for the tablename. — JustOnUnderMillions, Nov 18 '16 at 16:23
When somebody changes the stuff and post it for upate, you should add a hidden input in the form that hold the unique refer like an id (or gildename). But we dont know from where your posted form comes (userinput,ajaxautosubmit,...), so we cant tell for real... — JustOnUnderMillions, Nov 18 '16 at 16:27
@JustOnUnderMillions the Guildleader has to login with his own credentials, without those logins he cant change it - so i know who did what - does this make sense? — pbahmann1, Nov 18 '16 at 16:31
That ok, but you have to give any data that can be manipulated an uniqeid (only internal use) to easer update it in the database (mostly an field called id with flag index and autoincrement in the database does that for you). — JustOnUnderMillions, Nov 18 '16 at 16:40
@JustOnUnderMillions I have a field id with autoincrement yea, is that what you mean? — pbahmann1, Nov 18 '16 at 16:48
Yes, that can you use for your WHERE part when updating the data. Because it will never change and is always the same for a dataset — JustOnUnderMillions, Nov 18 '16 at 16:53
@JustOnUnderMillions Do you have an example for me, or a "guide" on how to change my script? So i get the ID for $gname — pbahmann1, Nov 18 '16 at 16:54
If you pull it first time from the database `SELECT * FROM` you got it, but if you have problems on that, open a new question for that. Because: `Please avoid extended discussions in comments.` Have a nice day :) — JustOnUnderMillions, Nov 18 '16 at 17:03

score 1 · Accepted Answer · answered Nov 18 '16 at 16:21

1

As mentioned you cant reuse the same param. Try giving it a different name:

if(isset($_POST['edit-guild'])){

    $Statement = $conn->prepare("UPDATE $boss SET `Bossname` =:boss, `GuildName` =:gname, `Screenshot` =:screen, `Link` =:link, `KillTime` =:defeattime, `KillYN` =:kill WHERE `GuildName`=:gname2");
    // EXECUTING ARRAY FOR ^GUILD INFORMATION
    $Statement->execute(array(
        "boss"          => $boss,
        "gname"         => $gname,
        "gname2"         => $gname,
        "screen"        => $screen,
        "link"          => $log,
        "defeattime"    => $defeat,
        "kill"          => 'Yes'
        ));
}

answered Nov 18 '16 at 16:21

atoms

2,993
2
22
43

so simply having it with different names should do the job here? – pbahmann1 Nov 18 '16 at 16:24
omitting any other errors, yes it should work. Strange your table name is prefixed with a `$`. Is that intentional? – atoms Nov 18 '16 at 16:25
Ok worked like a charm, thank you! Would there be a better solution tho? You seem skeptical – pbahmann1 Nov 18 '16 at 16:26
no problem. I'm not too sure, you could look at automating the bindig process or to use ? params. However as you have done it is correct. One last thing to think of; if you are using the guildname to query why are you returning it from the db? You already have it – atoms Nov 18 '16 at 16:28
ignore that was a brain lapse, thought you were selecting not updating ;) – atoms Nov 18 '16 at 16:34

score 0 · Answer 2 · answered Nov 18 '16 at 16:21

Remove gname field from update set.

$Statement = $conn->prepare("UPDATE $boss SET `Bossname` =:boss, `Screenshot` =:screen, `Link` =:link, `KillTime` =:defeattime, `KillYN` =:kill WHERE `GuildName`=:gname");

if you thinks gname is important in update SET you can try another different name or try to change where condition another field like id

(PDO) UPDATE doesn't update with WHERE variable

2 Answers2