Syntax Problem, again...need help!


#1

I got this error message once before and it turned out I had used “group” as a field in my database, which was protected. Its possible I chose another word that I should not have, although this error is new and I"m not sure what has changed.

I have created a form to submit to a database. When a user submits, they get an e-mail with a confirmation URL. When the access that URL they get to the page where the problem is. It is supposed to find their record in the database and move it to the “Live” table so that it becomes visible.

Here is the Error message I am getting:

Error - Query Error: 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 'sidewater@mindspring.com','yes')' at line 2 

Here is the PHP code from the page itself. I am not sure what the problem could be though.

[code]if (mysql_select_db ($db, $cn))
{

$sql = "SELECT * FROM unpublished WHERE email = '$email'";
$result = mysql_query($sql,$cn);	

//echo " <P>Error - Query Error1: " . mysql_error() . "xx<P>"; 
while($row = mysql_fetch_array($result))
{

//Get data

$id = $row[‘id’];
$sex = $row[“sex”];
$name = $row[“name”];
$partner = $row[“partner”];
$city = $row[“city”];
$state = $row[“state”];
$length = $row[“length”];
$children = $row[“children”];
$num_children = $row[“num_children”];
$adopted = $row[“adopted”];
$denied = $row[“denied”];
$child = $row[“d_child”];
$health = $row[“d_health”];
$death = $row[“d_death”];
$gov = $row[“d_gov”];
$employment = $row[“d_employment”];
$immigration = $row[“d_immigration”];
$other = $row[“d_other”];
$legal = $row[“legal”];
$legal_cost = $row[“legal_cost”];
$story = $row[“story”];
$email = $row[“email”];
$list = $row[“list”];
$created = $row[“created”];
$country = $row[“country”];

$sql = “INSERT INTO live(id,created,sex,name,partner,length,city,state,country,children,num_children,adopted,denied,d_child,d_death,d_health,d_gov,d_employment,d_immigration,d_other,legal,legal_cost,story,email,list)
VALUES (’$id’,’$created’,’$sex’,’$name’,’$partner’,’$length’,’$city’,’$state’,’$country’,’$children’,’$num_children’,’$adopted’,’$denied’,’$child’,’$health’,’$death’,’$gov’,’$employment’,’$immigration’,’$other’,’$legal’,’$legal_cost’,’$story’,’$email’,’$list’)”;

$insert = mysql_query($sql,$cn);

if ($insert == TRUE)
{

$sql2 = "DELETE FROM unpublished WHERE email = '$email'";
$delete = mysql_query($sql2,$cn);
if ($delete == TRUE)
{
print "<h2>Your story has been posted to our website</h2>Thank you for your support.  Don't forget to <a href="/gay_marriage_site/tell_a_friend.php">tell your friends about us.</a>";
}
else
{
print "Database update could not be completed";
echo " <P>Error - Query Error: " . mysql_error(); 
}
}
else
{
print "There was a technical problem, your story could not be posted.  Please e-mail the <a href="/gay_marriage_site/contact.php">Contact Us</a>.";
echo " <P>Error - Query Error: " . mysql_error(); 
}

}

}
else
{
print “Could not connect to database. Please Contact Us.”;
echo "

Error - Query Error: " . mysql_error();
} [/code]


#2

first off I don’t know which query is throwing the error… so here is a general debugging tip… Echo out your SQL before you run it (The SQL will echo out with the error message right after it.) so you can varify it is EXACTLY as you planned (I usually use double quotes around strings).


#3

It looks like the problem is with the “insert” statement. I added code to spit out the error and that is where I think its coming from.

does that help?

-Peter


#4

Just a coding suggestion - maintain your tabing of logic blocks it makes it much easier to read.

if (mysql_select_db ($db, $cn))
{   

   
   $sql = "SELECT * FROM unpublished WHERE email = '$email'";
   $result = mysql_query($sql,$cn);   
   
   //echo " <P>Error - Query Error1: " . mysql_error() . "xx<P>";
    while($row = mysql_fetch_array($result))
   {
   
       //Get data
        $id = $row['id'];
        $sex = $row["sex"];
        $name = $row["name"];
        $partner = $row["partner"];
        $city = $row["city"];
        $state = $row["state"];
        $length = $row["length"];
        $children = $row["children"];
        $num_children = $row["num_children"];
        $adopted = $row["adopted"];
        $denied = $row["denied"];
        $child = $row["d_child"];
        $health = $row["d_health"];
        $death = $row["d_death"];
        $gov = $row["d_gov"];
        $employment = $row["d_employment"];
        $immigration = $row["d_immigration"];
        $other = $row["d_other"];
        $legal = $row["legal"];
        $legal_cost = $row["legal_cost"];
        $story = $row["story"];
        $email = $row["email"];
        $list = $row["list"];
        $created = $row["created"];
        $country = $row["country"]; 

         /* watch this SQL statement... I broke it up for easier reading with
         the concatinator and I think it should work as is but you may have
         to put it back to its original form. */

        $sql = "INSERT INTO live( id, created, sex, name, partner, length, city, state, country, "
                    . "children, num_children, adopted, denied, d_child, d_death, d_health, d_gov, d_employment, "
                    . "d_immigration, d_other, legal, legal_cost, story, email, list)"
              ."VALUES ('$id', '$created', '$sex', '$name', '$partner', '$length', '$city', '$state', '$country', "
                    . "'$children', '$num_children', '$adopted', '$denied', '$child', '$health', '$death', '$gov', "
                    . "'$employment', '$immigration', '$other', '$legal', '$legal_cost', '$story', '$email', '$list')"; 
    
        // debugging SQL
        echo $sql;
        // debugging SQL
        
        $insert = mysql_query($sql,$cn);

        if ($insert == TRUE)
        {

            $sql2 = "DELETE FROM unpublished WHERE email = '$email'";
            $delete = mysql_query($sql2,$cn);
            if ($delete == TRUE)
            {
                print "<h2>Your story has been posted to our website</h2>Thank you for your support.  Don't forget to <a href="/gay_marriage_site/tell_a_friend.php">tell your friends about us.</a>";
            }
            else
            {
                print "Database update could not be completed";
                echo " <P>Error - Query Error: " . mysql_error();
            }
        }
        else
        {
        print "There was a technical problem, your story could not be posted.  Please e-mail the <a href="/gay_marriage_site/contact.php">Contact Us</a>.";
        echo " <P>Error - Query Error: " . mysql_error();
        }

   }
}
else
{
print "Could not connect to database.  Please <a href="/gay_marriage_site/contact.php">Contact Us</a>.";
echo " <P>Error - Query Error: " . mysql_error();
}      

Please note above that I am having the SQL statement echo out. This will allow us to see the SQL statement that is being sent to MySQL. This with the mysql_error() message is what we need.


#5

[code]INSERT INTO live(id,created,sex,name,partner,length,city,state,country,children,num_children,adopted,denied,d_child,d_death,d_health,d_gov,d_employment,d_immigration,d_other,legal,legal_cost,story,email,list) VALUES (‘9’,‘05-31-2004’,‘Fem’,‘Peter Sidewater’,‘test’,‘3’,‘boston’,‘ma’,‘US’,‘yes’,‘0’,’’,‘yes’,‘yes’,’’,’’,’’,’’,’’,’’,’’,’$1,000 - $’,’

Error4 - Query Error: You have an error in your SQL syntax near ‘sidewater@mindspring.com’,‘yes’)’ at line 2 [/code]

Does this help?


#6

well looks like the error isn’t coming from that SQL statement. I can’t find ‘sidewater@mindspring.com’,‘yes’ supposed to be at the end of that SQL statement? Could you be getting 2 single quotes?