SQL syntax Error

ponypony

New Member
Messages
26
Reaction score
0
Points
0
I've been working at this for quite a while now and cannot figure out what the error it..I am trying to insert something into the database and it keeps telling me this:
errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show SET perf = '5', showname = 'DNE', showid = '2511', totalsta' at line 1

This is the section of the coding thats giving me the problems..

PHP:
$id = $_GET['id'];
$fetchedplayer = mysql_query("SELECT * FROM shows WHERE id = '$id'") or die(mysql_error());    
$fetchedplayer = mysql_fetch_array($fetchedplayer);
$showname = $fetchedplayer['showname'];
$id = $fetchedplayer['id'];
$fee = $fetchedplayer['fee'];
$hosterid = $fetchedplayer['hosterid'];
                  $showname = $showname;
                  $showid = $showid;


mysql_query("UPDATE players SET money=money-$fee WHERE id='$sessid'") or die(mysql_error()); 
mysql_query("UPDATE players SET money=money+$fee WHERE id='$hosterid'") or die(mysql_error());
              $perf = mt_rand(1,15);

                    $petid = trim($_POST['petid']);
$fetchedpet = mysql_query("SELECT * FROM pets WHERE petid = '$petid'") or die(mysql_error());
$fetchedpet = mysql_fetch_array($fetchedpet);
    
$petname = $fetchedpet['petname'];
$totalstats = $fetchedpet['totalstats'];
$petid = $fetchedpet['petid'];

    $perf = mt_rand(1,15);
    $petname = $petname;
    $totalstats = $totalstats;
    $petid = $petid;


mysql_query("INSERT INTO show SET
                perf = '$perf',
                showname = '$showname',
                showid = '$id',
                totalstats = '$totalstats',
                petname = '$petname',
                petid = '$petid'") or die ('error' . @mysql_error());
    
                echo "<br>$petname was entered into $showname for a fee of $$fee.<BR><BR>";
                    
                        };
 

rstat1

New Member
Messages
5
Reaction score
0
Points
0
To my knowledge "SET" is only usable with "UPDATE". Also with an INSERT statement you have to list the column names followed by VALUES and all the names of the variables you're trying to insert into the table.

So for your query you'd have to have

INSERT INTO show (perf, showname, showid, totalstats, petname, petid) VALUES($perf, $showname, $id, $totalstats, $petname, $petid)

Hope this helps. :)
 
Last edited:

ponypony

New Member
Messages
26
Reaction score
0
Points
0
Thanks, I will try that once my site comes back up. :)
Edit:
=/ same things happening..I tried this
mysql_query("INSERT INTO show
(perf, showname, showid, totalstats, petname, petid) VALUES('$perf', '$showname', '$showid', '$totalstats', '$petname', '$petid') ")
or die(mysql_error());
 
Last edited:

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
INSERT INTO <tbl_name> SET <col>=<expr> ... is perfectly valid syntax. The problem is "show" is a SQL keyword. Enclose it in backquotes so the parser parses it as a table name:
Code:
INSERT INTO `show` SET
                perf = '$perf',
                showname = '$showname',
                showid = '$id',
                totalstats = '$totalstats',
                petname = '$petname',
                petid = '$petid'

Your script is open to SQL injection via the unfiltered $_GET['id'] and $_POST['petid'] variables. Use the filter functions or the DB driver's escape function (e.g. mysql_escape_string, mysqli_real_escape_string, pg_escape_string). Even better is to use prepared statements. Try the mysqli driver and mysqli_prepare:

PHP:
class DBException extends RuntimeException {
    protected $_logMsg;
    function __construct($logMsg, $msg='', $code=0) {
        parent::__construct($msg, $code);
        $this->_logMsg=$logMsg;
    }
    function getLogMessage() {
        return $this->_logMsg;
    }
}
try {
    $dbConn = new mysqli('localhost', $dbUser, $dbPassword, $dbName);
    if ($dbConn->connect_errno) {
        throw new DBException("DB connect error: {$dbConn->connect_error}", 
				  "Couldn't connect to database. Something is probably temporarily wrong with the server. Please try again later.", $dbConn->errno);
    }
    $stmt = 'SELECT showname, id, fee, hosterid FROM `shows` WHERE id = ?';
    if (! ($showQuery = $dbConn->prepare($stmt))) {
        throw new DBException("Prepare error: {$dbConn->error}; statement: '$stmt'", 
				  "Couldn't create database query.", $dbConn->errno);
    }
    if (! $showQuery->bind_param('i', $_GET['id'])) { // change 'i' to 's' if ids aren't integers
        throw new DBException("Bind param error: {$dbConn->error}; param: '$_GET[id]'.", 
				  "Couldn't create database query.", $dbConn->errno);
    }
    if (! $showQuery->execute()) {
        throw new DBException('Fetch error: ' . $dbConn->error,
				  "Couldn't fetch any information from database", $dbConn->errno);
    }
    if (! $showQuery->bind_result($showname, $id, $fee, $hosterid)) {
        throw new DBException("Bind param error: {$dbConn->error }; param: '$_GET[id]'.", 
				  "Couldn't create database query", $dbConn->errno);
    }
    if (FALSE === ($success = $showQuery->fetch()) ) {
        throw new DBException("Fetch error: {$dbConn->error }; param: '$_GET[id]'.", 
				  "Couldn't create database query", $dbConn->errno);
    } elseif (is_null($success)) {
        throw new RuntimeException("No results found for show '$_GET[id].'");
    }
    $showQuery->close();
    // $showname, $id, $fee, $hosterid now hold the appropriate values.
   //...
} catch (DBException $exc) {
    echo 'Error: ', $exc->getMessage(), '. ';
    if (error_log('[script ' . __FILE__ . '@' . __LINE__ . '] ' . $exc->getLogMessage())) {
        echo "An error has been logged. We'll look into it.";
    } else {
        // echo "I couldn't log the error. Please e-mail at $email and let us know the time and error message you're getting.";
    }
} catch (RuntimeException $exc) {
    echo $exc->getMessage();
}
You can tighten up the above code by extending class MySQLi and MySQLi_stmt to throw exceptions rather than returning FALSE.
 

ponypony

New Member
Messages
26
Reaction score
0
Points
0
Parse error: syntax error, unexpected T_STRING in /home/pony/public_html/horseshow.php on line 64

which is

mysql_query("INSERT INTO `show` SET
Edit:
Oh, got it to work now, thanks so much! I had forgotten to remove the previous insert >.<
 
Last edited:
Top