CODEWITHSUNDEEP
mysqlinsert
Daniel Delcore
Daniel Delcore

Reputation: 1

Update Else Insert MySQL query

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

Upvotes: 0

Views: 1023

Answers (3)

gahooa
gahooa

Reputation: 137332

Two things:

  1. Check the manual for INSERT ... ON DUPLICATE KEY UPDATE which should do this in one statement.

  2. 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!

Upvotes: 1

fzh
fzh

Reputation: 11

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.

Upvotes: 0

Godwin
Godwin

Reputation: 9907

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'

Upvotes: 2

Related Questions