MySQL UPDATE query (PDO) inexplicably not working

Question

I have been working on a website and recently added some changes to it, despite attempting to comment out said changes, my UPDATE query is not working- I have been attempting to figure out why for the last 3ish hours to no avail.

if (isset($_POST['submit']))
{
    if (count($rated)<$_SESSION['id'])
    {
        $difference = $_SESSION['id'] - count($rated);

        $rated = implode(',',$rated);
        while ($difference >= 0)
        {
            $rated .= "0,";
            $difference--;
        }
        $rated = explode(',',$rated);
    }

    $rated[$_SESSION['id']] = $_POST['rating'];

    $ratings = 0;
    $ratingsadded = 0;
    foreach ($rated as $user => $rating)
    {
        if ($rating != 0)
        {
            $query = $db->prepare("SELECT id, active FROM accounts WHERE id = :id");
            $query->execute(array('id' => $user));
            $useractive = $query->fetch();

            if ($useractive['active'] == 1 || $user == 0 || $user == 50)
            {
                $ratings++;
                $ratingsadded += $rating;
            }
            else
            {
                $rated[$user] = 0;
            }
        }
    }
    $ratingtotal = $ratingsadded / $ratings;

    $query = $db->prepare("UPDATE accounts SET rating = :rating, rated = :rated WHERE id = :id");
    $query->execute(array('rating' => $ratingtotal, 'rated' => implode(",",$rated), 'id' => $user['id']));
    header('Location: ?user=' . $_GET['user']);
}

Note: The query at the very end is the one I am referring to.

Secondary note: I know that there are quite a few inefficiencies in my code.

Full code: http://pastebin.com/ybb71U6k

It seems as if this will work, my laptop just crashed so and I am rebooting it now so that I can test. — qwerty77asdf, Nov 20 '16 at 07:34
I've deleted my comment. I can see that you've used `$user` as array indices in the `foreach` loop, so it's not the problem with the execute statement. — Rajdeep Paul, Nov 20 '16 at 07:42

score 0 · Answer 1 · answered Nov 20 '16 at 07:25

0

I think you are supposed to write your execute like this

$query->execute(array(':rating' => $ratingtotal, ':rated' => implode(",",$rated), ':id' => $user['id']));

Note : in front of every named parameter.
Reference: http://php.net/manual/en/pdo.prepare.php

answered Nov 20 '16 at 07:25

Dmitry Zayats

474
2
6

1

No, `:` in front of named parameter is not required, it's optional. See this SO answer, [http://stackoverflow.com/a/17386503/5517143](http://stackoverflow.com/a/17386503/5517143) – Rajdeep Paul Nov 20 '16 at 07:30
Thank you for the answer but, I have always written queries like this and they have always worked. – qwerty77asdf Nov 20 '16 at 07:30
@RajdeepPaul on the "very rare" occasion have I seen where the colon needs to be used and will depend on some server configuration. – Funk Forty Niner Nov 20 '16 at 14:40
@Fred-ii- Oh, I see. Sever's default/custom configuration can play a role in this scenario. Thanks for pointing it out, I'll look into it. – Rajdeep Paul Nov 20 '16 at 16:48
@RajdeepPaul You're welcome. As I said, it's "very rare" but it does happen from time to time. This in remembering a few questions asked on Stack. – Funk Forty Niner Nov 20 '16 at 16:50

qwerty77asdf · Accepted Answer · 2016-11-25T00:15:00.943

0

Whilst Rajdeep has deleted his answers, I intended on responding with the following:

Thank you very much, whilst your answer was a tiny bit off, it pointed out the problem I had- I was using $user as a foreach key value and as a query result.

I have found the problem and it was a case of simple stupidity and assigning variables atop of other variables.

I am sorry for putting you all through the trouble of attempting to assist me for this.

edited Nov 25 '16 at 00:15

answered Nov 20 '16 at 07:42

qwerty77asdf

93
3
11

1

Yes, that's what I commented above. Glad your issue is resolved. :-) – Rajdeep Paul Nov 20 '16 at 07:44

MySQL UPDATE query (PDO) inexplicably not working

2 Answers2