MySQL UPDATE query Error in Web Page

Question

My UPDATE query is failing although the syntax looks fine to me (I have another update query that works fine on the same page).

mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("sitename") or die(mysql_error());

$id = $_GET['id'];

if (isset($_POST['submit'])){                           

        $b = mysql_real_escape_string(trim($_POST['body']));

        //**You have an error in your SQL syntax;** --> ?
        mysql_query ("UPDATE body SET body= $b WHERE id = $id") or die (mysql_error() );

        // $b is fine 
        echo "$b";          

    }

How the HTML review forms are rendered..

// Puts SQL Data into an array
$q = mysql_query("SELECT * FROM vote") or die (mysql_error());

// Now we loop through the database
echo "<br />";
while ($ratings = mysql_fetch_array($q))
{
    //This outputs the doctors's name
    echo "Doctor's name:" . $ratings['doctor_name'] ."<br />";

        //This outputs a textarea for the user to submit comments
        echo "<b>Your Experience: </b>";
        echo "<form method='post' action='review_doctors.php'> 

                <textarea name='body'></textarea>
                <input type='submit' name='submit' value='Send' id='submit'/>
             </form>
             "; 
        echo "<br />";

echo "<p> </p>";
}

Why am I getting a SQL syntax error whenever a comment is submitted?

Please, don't use mysql_* functions, They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO or MySQLi. You will also want to Prevent SQL Injection! — Jay Blanchard
– Jay Blanchard, Commented Nov 10, 2014 at 21:59

Ray · Accepted Answer · 2014-11-10 22:30:37Z

2

So, you're setting $id from the $_GET array which will probably not be set on submission of a form via post.

The update query you're running is inside a check for a POST (checking to see if $_POST['submit'] is set).

You probably want to send the value for the $id in the post body and pull it from the post array.

edited Nov 10, 2014 at 22:30

answered Nov 10, 2014 at 22:00

Ray

41.6k22 gold badges110 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

deedle Over a year ago

I fixed it to this based on your hints:

deedle · Accepted Answer · 2014-11-11 21:39:04Z

I fixed it to this:

// If submitted 
if (isset($_POST['id'])){       

            //Capture what was typed in textarea
            $b = mysql_real_escape_string(trim($_POST['body']));

            $id = $_POST['id'];
            mysql_query ("UPDATE vote SET body = '$b' WHERE id = $id") or die (mysql_error() );

            // $b and $id are still fine 
            echo "$b";  
            echo "$id";

        }

Also fixed the hidden input value:

while ($ratings = mysql_fetch_array($q))
{
    //This outputs the doctors's name
    echo "Doctor's name:" . $ratings['doctor_name'] ."<br />";

        $id = $_POST['id'];    

        //This outputs a textarea for the user to submit comments
        echo "<b>Your Experience: </b>";
        echo "<form method='post' action='review_doctors.php'> 

                <textarea name='body'></textarea>
                <input type='submit' name='submit' value='Send'/>


                <input type='hidden' name='id' value='$ratings[id]' />

             </form>
             ";

        echo "<br />";

Collectives™ on Stack Overflow

MySQL UPDATE query Error in Web Page

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related