Problem w/ syntax.[SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Weasel5-12
Forum Commoner
Posts: 37
Joined: Tue Sep 16, 2008 6:58 am

Problem w/ syntax.[SOLVED]

Post by Weasel5-12 »

G'day guys.

Just a small syntax problem that i can't see whats wrong with.

I'm creating a personal movie DB, just for my own collection, and as I'm adding my synopsis i get the following 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 'retired master car thief must come back to the industry and steal 50 cars with h' at line 1

The Synopsis field in my table is a longtext type.

I've included my query and the synopsis itself. i still cant figure out whats wrong with it.

Code: Select all

 
$synopsis [color=#4040FF]= $_POST[[/color][color=#FF0000]'synopsis'[/color][color=#4040FF]][/color];
 
[color=#FF8000]/* Output would be as follows.[/color]
[color=#FF8000] * $synopsis = "A retired master car thief must come back to the industry and steal 50 cars with his crew in one night to save his brother's life.";[/color]
[color=#FF8000] */[/color]
....
....
 
$query [color=#4040FF]=[/color] [color=#FF0000]"INSERT INTO movie_list (Film_ID, Title, Genre, Length, ReleaseDate, MQuality, FQuality, AQuality, Synopsis, AdditionComments, Location) [/color]
[color=#FF0000]VALUES ('NULL' , '$title', '$genre', '$length', '$releaseDate', '$MQuality', '$FQuality', '$AQuality', $synopsis, '$comments', '$location')"[/color];

Cheerz, and thanks in advance
Last edited by Weasel5-12 on Tue Oct 28, 2008 2:43 pm, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problem w/ syntax.

Post by VladSun »

First, use mysql_real_escape_string() for every variable value you put in the query.
Second, don't surround NULL with quotes - it's not a string but a NULL value.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weasel5-12
Forum Commoner
Posts: 37
Joined: Tue Sep 16, 2008 6:58 am

Re: Problem w/ syntax.

Post by Weasel5-12 »

Cheerz m8, i've changed all of my code to the following...
But it still hasn't changed the error message output;
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 'retired master car thief must come back to the industry and steal 50 cars with h' at line 1

Code: Select all

 
<?php 
[color=#FF8000] /* Program: addMovie_process_2.php[/color]
[color=#FF8000]  * Description: retrieves data from addMovie_process_1.php and checks for duplicate entries before adding to table[/color]
[color=#FF8000]  */[/color]
 
    [color=#008000]include[/color]([color=#FF0000]"Misc\misc.inc"[/color]);
 
    $connection = [color=#0000FF]mysql_connect[/color]($host, $user, $password)[color=#0000FF] or die [/color]([color=#FF0000]"Couldn't connect to server"[/color]);
    $db = [color=#0000FF]mysql_select_db[/color]($database, $connection)[color=#0000FF]or die [/color]([color=#FF0000]"Couldn't connect to database"[/color]);
    
    $title [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'title'[/color]][color=#0000BF])[/color];
    $genre [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'genre'[/color]][color=#0000BF])[/color];
    $length [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'length'[/color]][color=#0000BF])[/color];
    $releaseYear [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'releaseYear'[/color]][color=#0000BF])[/color];
    $releaseYear [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'releaseMonth'[/color]][color=#0000BF])[/color];
    $releaseDay [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'releaseDay'[/color]][color=#0000BF])[/color];
    $location [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'location'[/color]][color=#0000BF])[/color];
    $synopsis [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'synopsis'[/color]][color=#0000BF])[/color];
    [color=#FF8000] // $synopsis = "A retired master car thief must come back to the industry and steal 50 cars with his crew in one night to save his brother's life.";[/color]
    $comments[color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'comments'[/color]][color=#0000BF])[/color];
    
    $MQuality [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'MQuality'[/color]][color=#0000BF])[/color];
    $AQuality [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'AQuality'[/color]][color=#0000BF])[/color];
    $FQuality [color=#0000BF]= mysql_real_escape_string([/color][color=#4040FF]$_POST[/color][[color=#FF0000]'FQuality'[/color]][color=#0000BF])[/color];
    
    
    
    $releaseDate [color=#0000FF]=[/color] $releaseYear[color=#0000FF].[/color][color=#FF0000]"/"[/color][color=#0000FF].[/color]$releaseMonth[color=#0000FF].[/color][color=#FF0000]"/"[/color][color=#0000FF].[/color]$releaseDay;
    
    [color=#FF8000]/* Checks if drink already exists in drinklist table. If not, add it to table */[/color]
    $query [color=#4040FF]=[/color] [color=#FF0000]"SELECT Title FROM movie_list WHERE Title='$title'"[/color];
 
    $result [color=#0000FF]= mysql_query[/color]($query)[color=#0000FF] or die [/color]([color=#0000FF]mysql_error[/color]());
    
    $ntype = [color=#0000FF]mysql_num_rows[/color]($result);
    [color=#408000]if[/color]($ntype [color=#FF0000]!=[/color] 0) [color=#0000BF]{[/color]
        
        [color=#0000BF]echo[/color][color=#FF0000] "<h4>NOTE: The following already exist in the database:</h4><hr>"[/color];
        [color=#008000]include[/color]([color=#FF0000]"Misc\dataOutput.inc"[/color]);
    
     [color=#0000BF]}[/color][color=#408000]else[/color] [color=#0000BF]{[/color]
        $query [color=#4040FF]=[/color] [color=#FF0000]"INSERT INTO movie_list (Film_ID, Title, Genre, Length, ReleaseDate, MQuality, FQuality, AQuality, Synopsis, AdditionComments, Location) [/color]
[color=#FF0000]VALUES (NULL , '$title', '$genre', '$length', '$releaseDate', '$MQuality', '$FQuality', '$AQuality', $synopsis, '$comments', '$location')"[/color];
    
        $result [color=#0000FF]= mysql_query[/color]($query)[color=#0000FF] or die [/color]([color=#0000FF]mysql_error[/color]());
        [color=#0000BF]echo[/color] [color=#FF0000]"<h2>Movie Information Input. Stage 3.</h2>[/color]
        [color=#FF0000]<h3>The following have been added to the database:</h3>[/color]
        [color=#FF0000]<br><hr><br>"[/color];
        
       [color=#008000]include[/color]([color=#FF0000]"Misc\dataOutput.inc"[/color]);       
        [color=#0000BF]echo[/color] [color=#FF0000]"<br><hr><br><p></p>"[/color];
     [color=#0000BF]}[/color]
?>
I have tested the output for $synopsis, and it is what I expected it to be.

Thx for your help dude, but the solution to the syntax error still is beyond me.
(sorry but the formatting took a while :D )
User avatar
Weasel5-12
Forum Commoner
Posts: 37
Joined: Tue Sep 16, 2008 6:58 am

Re: Problem w/ syntax.

Post by Weasel5-12 »

For anyone who reads this and has a similar issue w/ their syntax.

I've found that i was missing a set of ' ' around my $synopsis value for my query:

$query = "INSERT INTO movie_list (Film_ID, Title, Genre, Length, ReleaseDate, MQuality, FQuality, AQuality, Synopsis, AdditionComments, Location) VALUES (NULL , '$title', '$genre', '$length', '$releaseDate', '$MQuality', '$FQuality', '$AQuality', $synopsis, '$comments', '$location')";
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problem w/ syntax.[SOLVED]

Post by VladSun »

That should be considered a third issue with your code. Sorry, I haven't noticed it.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply