Update Else Insert MySQL query

Question

Hello i was here yesterday with this problem, i don't really know how to use this site well as i am new so i reposted. but I'm getting an error with this block of code and i think its the Update query which contains a syntax error.

// Updates if player's record already exists

    $result = mysql_query("UPDATE PlayerStat SET Position='$POS', Number='$NUM', Name='$PlyrName', Status='$Status', TDS='$TDS', INT='$INT', YDS='$YDS', RTG='$RTG', Team='$Team' WHERE Name='$PlyrName'");
    echo mysql_error();
    if (mysql_affected_rows()==0){
        // Populates table if record is non existent
        $result = mysql_query("INSERT INTO PlayerStat(`Position`, `Number`, `Name`, `Status`, `TDS`, `INT`, `YDS`, `RTG`, `Team`) VALUES ('$POS','$NUM','$PlyrName','$Status','$TDS','$INT','$YDS','$RTG','$Team')");
        echo mysql_error();
    }

The Error message

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT='1', YDS='86', RTG='52.5', Team='ARI' WHERE Name='Bartel, Richard'' at line 1

Godwin · Accepted Answer · 2011-11-30 03:07:46Z

2

INT is a keyword in mysql (declares and integer), if it's your column name you should surround it backticks (`) like so: `INT`.

It's good practice to put these in even though they're not necessary in all cases

UPDATE
    PlayerStat
SET
    `Position` =  '$POS',
    `Number` = '$NUM',
    `Name` = '$PlyrName',
    `Status` = '$Status',
    `TDS` = '$TDS',
    `INT` = '$INT',
    `YDS` = '$YDS',
    `RTG` = '$RTG',
    `Team` = '$Team'
WHERE
    `Name` = '$PlyrName'

answered Nov 30, 2011 at 3:07

Godwin

9,9576 gold badges44 silver badges62 bronze badges

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

2 Comments

Daniel Delcore Over a year ago

Hey yea that has definitely helped. but it isn't updating the old records in the DB, but just remakes them :(

Godwin Over a year ago

Your method inserts instead of updates? If that's the case it seems you may have further logic problem or perhaps you don't have the data set up like you think you do in your database. I would start by trying to execute this in your dbs UI (PHPMyAdmin or whatever) and see what you get for results. Better yet, start by converting this to a SELECT statement and executing it there.

gahooa · Accepted Answer · 2011-11-30 03:29:18Z

1

Two things:

Check the manual for INSERT ... ON DUPLICATE KEY UPDATE which should do this in one statement.
I suggest you take a hard look at mysql_real_escape_string() or similar in PHP to escape your data and prevent SQL Injections.

If you don't know what SQL Injections are, then google it and spend a bit of time reading NOW before it's too late and some cracker or script kitty has your database.

Hope this helps!

answered Nov 30, 2011 at 3:29

gahooa

138k14 gold badges101 silver badges104 bronze badges

Comments

fzh · Accepted Answer · 2011-11-30 03:10:35Z

0

You may want to check these websites. http://www.w3schools.com/php/php_mysql_update.asp http://www.tizag.com/mysqlTutorial/mysqlupdate.php

And you might also want to check your spelling mistake or the single quote or double quote. Other than that, check your database namings and data type.

answered Nov 30, 2011 at 3:10

fzh

111 silver badge4 bronze badges

Collectives™ on Stack Overflow

Update Else Insert MySQL query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related