PHP MySQL Query Syntax Error?

Question

I think i'm doing something wrong here, I'm very new to PHP and only using it to interface my database with my client software through a WWW call, I have a Insert script, which works, but as for my Update script im stumped... here are the queries I tried:

the newest one:

$query = "UPDATE accounts SET moonscore= ' " . $moonscore . " ', sunscore = ' " . $sunscore . " ' WHERE name = ' " . $name . "';";

and I also tried, which I figured was wrong after awhile.

$query = "UPDATE accounts SET moonscore = $moonscore, sunscore = $sunscore WHERE name =$name;

Would really appreciate the help from all you PHP gurus.

It would help us out a lot if you told us what the exact error was, what the values of the three variables are, and separated your query onto multiple lines — Explosion Pills, Feb 08 '13 at 00:23

score 0 · Answer 1 · edited May 23 '17 at 10:24

try,

$query = "UPDATE accounts
          SET    moonscore = '$moonscore', 
                 sunscore = '$sunscore'
          WHERE  name ='$name'";

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

score 0 · Answer 2 · answered Feb 08 '13 at 00:23

0

you should use single quotes around the variables ,try this

    $query = "UPDATE accounts SET moonscore = '$moonscore' , sunscore = '$sunscore' WHERE name ='$name';

tips: try to use PDO or MYSQLI instead of mysql

answered Feb 08 '13 at 00:23

echo_Me

37,078
5
58
78

score 0 · Answer 3 · answered Feb 08 '13 at 00:26

Your query is open for SQL Injections. I've added a simple function that always served me well.

function inject($value)
{
    // Stripslashes
    if (get_magic_quotes_gpc()) {
        $value = stripslashes($value);
    }
    // Quote if not integer
    if (!is_numeric($value)) {
        $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
}

$query = "UPDATE accounts SET moonscore = ".inject($moonscore).", sunscore = ".inject($sunscore)." WHERE name =".inject($name);

score 0 · Answer 4 · answered Feb 08 '13 at 00:37

Take a look at prepared statements to avoid having to think about protecting your queries against injection with some fancy functions. http://php.net/manual/en/pdo.prepared-statements.php

Here's a video that might give you more insight as a beginner: http://www.youtube.com/watch?v=_bw54BqS2UE

PHP MySQL Query Syntax Error?

4 Answers4