Getting a 1064 Error in MySQL but query still completes? Not sure why!!

Question

Hi there,

I am trying to insert a lot of fields into a MySQL database, some are failing, so I am adding some code into my PHP script to try and track down what is occurring.

The replace seems to work as I can see the fields being populated in mysql, but I get this error:

1064: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 '1' at line 1

//insert query
if (strlen($link_name)>0)
{
$query = mysql_query("REPLACE into jos_mt_links
(link_id, link_name, alias, link_desc, user_id, link_approved, metadesc, link_created, link_modified, website, price)
VALUES ('$link_id','$link_name','$link_name','$description','63','1','$metadesc','$link_created','$link_modified','$website','$cost')");
echo $link_name . "has been inserted <br />";
print "SQL-Query: ".$query."<br>";

    if(mysql_query($query,$db))
   {
    echo "<center><font face='$fonts' size=2 color='$green'>";
    echo " OK !</font><br><br>\n";
   }
  else
   {
    echo "<center><font face='$fonts' size=3 color='$red'>";
    echo "Error<br><br>";
       echo mysql_errno() . ":" . mysql_error() . "</font><br>\n";
   }

Answer 1

There is nothing wrong with your SQL. You're assigning $query to the result of your mysql_query() call:

$query = mysql_query("REPLACE into jos_mt_links
    (link_id, link_name, alias, link_desc, user_id, link_approved, metadesc,
    link_created, link_modified, website, price)
    VALUES
    ('$link_id','$link_name','$link_name','$description','63','1','$metadesc','$link_created','$link_modified','$website','$cost')");

mysql_query() returns true or false as a result of the REPLACE query, but the more important thing is that you're assigning the result, not the SQL query that you're executing.

Furthermore, in here:

if(mysql_query($query,$db))

You're calling mysql_query() again on the same $query variable which now holds a value of true (since you said your query is working and your database is being updated normally). PHP interprets boolean true as string '1' and you're telling MySQL to run a query called 1, which gives that error.

You probably meant to assign $query like this instead so your if condition works properly:

$query = "REPLACE into jos_mt_links
    (link_id, link_name, alias, link_desc, user_id, link_approved, metadesc,
    link_created, link_modified, website, price)
    VALUES
    ('$link_id','$link_name','$link_name','$description','63','1','$metadesc','$link_created','$link_modified','$website','$cost')";

And calling your echo within the if statement too.

Another thing: as what Mark Baker has noted, please be sure your variables have been escaped with mysql_real_escape_string() before sticking them directly in your SQL like that.