Reputation: 17

Getting "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version"

Can someone please run their eye over my coding to find why I am getting this:

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

I know it will be something really simple but I can not see it.

<body>
<?php
//connect to database//
$dbc = mysql_connect("localhost", "root", "***");
if (!$dbc)
die ('Could not connect: ' . mysql_error());

//select database//
$db_selected = mysql_select_db("tafe", $dbc );
if (!$db_selected)
die ('Could not connect: ' . mysql_error());

// initialise variables to store form control values
$Name = "";
$Address = "";
$Phone = "";
$Mobile = "";
$Email = "";


if($_SERVER['REQUEST_METHOD'] == "POST") // if form has been posted
{  
// initialise variables to store posted values
$ContactID = $_POST["ContactID"];
$Name = $_POST["Name"];
$Address = $_POST["Address"];
$Phone = $_POST["Phone"];
$Mobile = $_POST["Mobile"];
$Email = $_POST["Email"];

//build sql insert statement
$qry = "UPDATE contacts SET Name = '" . $Name . "', Address = '" . $Address . "', Phone = '" . $Phone . "', Mobile = '" . $Mobile . "', Email = '" . $Email . "' WHERE ContactID =" . $ContactID;

// run insert statement against database
$rst = mysql_query($qry, $dbc);

if ($rst)
{
    echo "<b><font color='green'>The contact has been updated.</font></b>";
    echo "</br></br>";
    echo "<a href=list-contacts.php>Continue</a>"; 
}

else 

{
    echo "<b><font color='red'>Error: ". mysql_error($dbc) . "</font></b>"; //alert if contact could not be added//
}

}


else // if form has not been posted
{
// build sql statement
$qry = "SELECT * FROM contacts WHERE ContactID = " . $_GET["ContactID"];


// run select statement
$rst = mysql_query($qry, $dbc);

if ($rst)
{
    $row = mysql_fetch_assoc($rst); // fetch row and place column values into respective place holder variable 

    $Name = $row["Name"];
    $Address = $row["Address"];
    $Phone = $row["Phone"];
    $Mobile = $row["Mobile"];
    $Email = $row["Email"];
}

else // in case of an error
{
    echo "<b><font color='red'>Error: ". mysql_error($dbc) . "</font></b>"; 
} // end of nested else statement ?>

<form name="editcontact" method="post" action="edit-contact.php"> 
<table border="1" cellpadding="2">
<caption> Caption 5</caption>

<!--Name Input-->
<tr>
<td><label for="Name">Name</label></td>
<td><input type="text" name="Name" value="<?php echo $Name ?>" size="30" maxlength="50" tabindex="1"/>
</td>
</tr>

<!-- Address Input-->
<tr>
<td><label for="Address">Address</label></td>
<td><textarea name="Address" cols="45" rows="5" tabindex="2"><?php echo $Address?></textarea></td>
</tr>

<!--Phone Input-->
<tr>
<td><label for="Phone">Phone</label></td>
<td><input type="text" name="Phone" value="<?php echo $Phone ?>" size="20" maxlength="20" tabindex="3" /> </td>
</tr>

<!--Mobile Input-->
<tr>
<td><label for="Mobile">Mobile</label></td>
<td><input type="text" name="Mobile" value="<?php echo $Mobile ?>" size="20" maxlength="20" tabindex="4" /> </td>
</tr>

<!--Email Input-->
<tr>
<td><label for="Email">Email</label></td>
<td><input type="text" name="Email" value="<?php echo $Email ?>" size="30" maxlength="50" tabindex="5" /></td>
</tr>

<!--Submit Button-->
<tr>
<td colspan="2" align="center"><input type="submit" name="Submit" value="Submit" tabindex="6"/>      
</td>
</tr>

</table>
</form>



<?php
} // end of main else statement

mysql_free_result($rst); //free memory//

?>

</body>
</html>`

Upvotes: 0

Answers (3)

klenium

Reputation: 2607

The $_POST["ContactID"] returns null, that's why you got that error.
Send the ContactID to the server:

<input type="hidden" name="ContactID" value="<?php echo $_GET["ContactID"]; ?>" />

There are sevenal problems with your code:

Do not use the mysql_* functions. They're outdated. Use the mysqli_* or PDO.
Always check the data that was send by the user, or the user may delete your database.
Do not use <b> and <font> tags. It's 2014. Use HTML5, and CSS3.
Use htmlspecialchars(), or the user will be able to attack your site (XSS)
If you use labels, you need to set the input's id.
Do not use tables to build up the site. Use floated divs.

This code will work well:

 <?php
try
{
    $db = new PDO("mysql:dbname=tafe;host=localhost", "root", "***");
}
catch (PDOException $e)
{
    die("Cannot connect to database.");
}
function post($name)
{
    return isset($_POST[$name]) ? $_POST[$name] : "";
}
function html($x)
{
    return htmlentities($x, ENT_QUOTES, "UTF-8");
}
if (post("id"))
{  
    $query = $db->prepare("UPDATE contacts SET Name = :name, Address = :address, Phone = :phone, Mobile = :mobile, Email = :email WHERE ContactID = :id");
    $query->bindParam(":name", post("name"));
    $query->bindParam(":address", post("address"));
    $query->bindParam(":phone", post("phone"));
    $query->bindParam(":mobile", post("mobile"));
    $query->bindParam(":email", post("email"));
    $query->bindParam(":id", post("id"));
    if ($query->execute())
        $message = '<span style="color: green; font-weight: bold;">The contact has been updated.</span><br /><a href="list-contacts.php">Continue</a>';
    else
        $message =  '<span style="color: red; font-weight: bold;">There was an error.</span>';
}
elseif (isset($_GET["ContactID"]))
{
    $query = $db->prepare("SELECT Name, Address, Phone, Mobile, Email FROM contacts WHERE ContactID = :id");
    $query->bindParam(":id", $_GET["ContactID"]);
    if ($query->execute())
    {
        if (!$query->rowCount())
            $message = '<span style="color: red; font-weight: bold;">This contact does not exists.</span>';
        else
        {
            $row = $query->fetch(PDO::FETCH_ASSOC);
            foreach ($row as $k => $v)
                $_POST[$k] = $v;
        }
    }
    else
        $message = '<span style="color: red; font-weight: bold;">There was an error.</span>';
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Contact</title>
        <meta charset="utf-8" />
    </head>
    <body>
        <?php
        if (isset($message))
            echo "<p>".$message."</p>";
        ?>
        <form action="edit-contact.php" method="post"> 
            <label for="name">Name:</label><br />
            <input type="text" name="name" id="name" value="<?php echo html(post("name")) ?>" /><br />
            <label for="address">Address:</label><br />
            <textarea name="address" id="address"><?php echo html(post("address")) ?></textarea><br />
            <label for="phone">Phone:</label><br />
            <input type="text" name="phone" id="phone" value="<?php echo html(post("phone")) ?>" /><br />
            <label for="mobile">Mobile:</label><br />
            <input type="text" name="mobile" id="mobile" value="<?php echo html(post("mobile")) ?>" /><br />
            <label for="email">Email:</label><br />
            <input type="text" name="email" id="email" value="<?php echo html(post("email")) ?>" /><br />
            <input type="submit" name="submit" value="Submit" />      
            <input type="hidden" name="id" value="<?php echo isset($_GET["ContactId"]) ? intval($_GET["ContactId"]) : "0" ?>" />
        </form>
    </body>
</html>

Upvotes: 1

Abhik Chakraborty

Reputation: 44844

Try this

$qry = "UPDATE contacts SET 
                Name = '" . mysql_real_escape_string($Name) . "', 
                Address = '" . mysql_real_escape_string($Address) . "', 
                Phone = '" . mysql_real_escape_string($Phone) . "', 
                Mobile = '" . mysql_real_escape_string($Mobile) . "', 
                Email = '" . mysql_real_escape_string($Email) . "' 
                WHERE ContactID =" . $ContactID;

MAKE SURE in your html form you have a hidden text box or text box with name "ContactID" Since you are using this in the query and I dont see that within the form.

$ContactID = $_POST["ContactID"];

NOTE : You are using mysql_* functions which are deprecated, start using mysqli_* functions or PDO

Upvotes: 0

echo_Me

Reputation: 37233

try this

 $qry = "UPDATE contacts 
         SET Name = '" . $Name . "', 
             Address = '" . $Address . "', 
             Phone = '" . $Phone . "', 
             Mobile = '" . $Mobile . "', 
             Email = '" . $Email . "' 
         WHERE ContactID = '" . $ContactID . "' " ;

and change to that query also

  $qry = "SELECT * FROM contacts WHERE ContactID = '" . $_GET['ContactID']."' " ;

nB:

1- you should escape your variables by mysql_real_escape_string()

2- you should use PDO or MYSQLI instead of MYSQL

Upvotes: 0

Getting &quot;You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version&quot;

Answers (3)

Related Questions

Getting "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version"