Page 1 of 1

Problem w/ syntax.[SOLVED]

Posted: Tue Oct 28, 2008 10:37 am
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

Re: Problem w/ syntax.

Posted: Tue Oct 28, 2008 11:03 am
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.

Re: Problem w/ syntax.

Posted: Tue Oct 28, 2008 12:15 pm
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 )

Re: Problem w/ syntax.

Posted: Tue Oct 28, 2008 2:42 pm
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')";

Re: Problem w/ syntax.[SOLVED]

Posted: Tue Oct 28, 2008 4:59 pm
by VladSun
That should be considered a third issue with your code. Sorry, I haven't noticed it.