editing sql data with php

cocco123 posted this at 16:28 — 16th February 2012.

They have: 1 posts

Joined: Feb 2012

im busy making a page where i can edit the data in my database with php. i have a script and i cant find any mistake but i still get an error: 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 cant find what is wrong with it so i thought i would ask you guys.

here is the edit.php file:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>

</head>
  
  <body>
   <h1>Edit Form</h1>
   <p></p>

<ul>
<?php 
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("SERVER_NAME", "USER_NAME", "USER_PASS");
    
//select which database you want to edit
mysql_select_db("DATABASE_NAME"); 

//If cmd has not been initialized
if(!isset($cmd)) 
{

   //display all the cars
   $result = mysql_query ('SELECT * FROM testtable order by ID') or die ('sql 1 Error: '.mysql_error ()); 
   
   //run the while loop that grabs all the cars
   while($r=mysql_fetch_array($result)) 
   { 
      //grab the model and the ID
      $id=$r["ID"];//take out the model     
      $fname=$r["fname"];//take out the model
      $lname=$r["lname"];//take out the id
$phon=$r["phon"];//take out the id
     
     //show the make and model a link in a list
      echo "<li>";
      echo "<a href='edit.php?cmd=edit&id=$id'>Edit - $fname $lname $phon</a>";
      echo "</li>";
    }
}
?>
   </ul>

<?php
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
      $id = $_GET["ID"];
   
      $result = mysql_query("SELECT * FROM testtable WHERE ID=$id") or die ('sql 2 Error: '.mysql_error ());        
      $myrow = mysql_fetch_array($result);
?>

      <form action="edit.php" method="post">
      <input type=hidden name="id" value="<?php echo $myrow["ID"] ?>">
   
      fname:<input type="text" name="fname" value="<?php echo $myrow["fname"]; ?>" size=30 /><br />
      lname:<input type="text" name="lname" value="<?php echo $myrow["lname"]; ?>" size=30 /><br />
      phon:<input type="text" NAME="phon" value="<?php echo $myrow["phon"]; ?>" size=30 /><br />

   
      <input type="hidden" name="cmd" value="edit" />
   
      <input type="submit" name="submit" value="submit" />
   
      </form>

     <?php } 
     
   if ($_POST["$submit"])
   {
      $fname = $_POST["fname"];
      $lname = $_POST["lname"];
      $phon = $_POST["phon"];



      $sql = "UPDATE testtable SET fname='$fname',lname='$lname',phon='$phon' WHERE ID=$id";

      $result = mysql_query($sql);
      echo "Thank you! Information updated.";
   }
}
?>

</body>
</html>

i hope you guys can help me

[Edited by moderator to remove actual server logic credentials]

vasvigupt posted this at 12:47 — 7th March 2012.

They have: 11 posts

Joined: Mar 2012

First test this code on local host then see if you get the same error.

Greg K posted this at 01:02 — 8th March 2012.

He has: 2,145 posts

Joined: Nov 2003

Some notes to begin with:

Your site is very insecure and open to hacks. Look into using the mysql_real_escape_string (http://php.net/mysql_real_escape_string for explanation and examples)

Also, why you should aways wrap data with mysql_real_escape_string for use in queries, for display on a web page (and this include the value="whatever" for inputs), you should always use htmlspecialchars($myVar,ENT_QUOTES). Lastly, when you go to use a piece of data in a URL, use urlencode() (these last two you can get to them by adding http://php.net/ in front like the first one I gave you)

For these I refer to any data that can be changed by the end user, which includes, but isn't limited to, the following:
$_POST, $_GET, $_COOKIE, $_SERVER['HTTP_REFERRER'], $_SERVER['HTTP_USER_AGENT'] $_SERVER['PHP_SELF'], $_SERVER['QUERY_STRING'] also any data that may have came from these sources (is once came from here and was put into database and read back in)

I edited your post to remove your login information, hopefully it is locked down so it will not et remote users in, but since you have published the information on a high traffic forum, you will probably want to go change the password ASAP.

So, now back to you issue.. You didn't list which one is givng you the error, but anytime you get an error, it is a good idea to echo out the actual $SQL query so you can see what variable replacement was done, sometimes when you do this you can catch a misplaced quote.

If you can't see it from there, try manually running the SQL directly on the server (worse case, use phpMyAdmin to manually execute it), sometimes it will give you more details of what exactly is wrong. But in this case (based on what you posted) it is running into a doublequote somewhere it wasn't expected, and since the code at quick glance looks good, must be coming from a variable somewhere (again, where mysql_real_escape_string would help, and echoing it out you would probably see where it is.

Here is an example of some code to use:

<?php
 

define ('DEBUG_MODE', TRUE);  // change to false when done testing

// Connect to database

$SQL = 'SELECT `field1`, `field2` FROM `tblCars` WHERE `field3` = "'.mysql_real_escape_string($myVariable).'" ORDER BY `lastfield`';
$rsCars = mysql_query($SQL) 
   or die ('[ERR-'.__LINE__.'] '.((DEBUG_MODE) ? mysql_error()."<br />\n".$SQL : 'There was an error with a database query'));
if (mysql_num_rows($rsCars) > 0) {
    while ($aryRow = mysql_fetch_assoc($rsCars)) {
        echo 'Field1 = ',htmlspecialchars($aryRow['field1']),"<br />\n";
        echo 'Field2 = ',htmlspecialchars($aryRow['field2']),"<br />\n<br />\n";
    }
    mysql_free_result($rsCars);
}
else {
    echo "There were no records found...<br />\n";
}
unset($rsCars);
?>

You will notice in the first line, there is a constant that you set to TRUE or FALSE to display more error information. it is good practice that when a script goes "live" (intended for others to access it) you do not list mysql errors, as there could be times depending on the query, it could give out info to help a someone compromise your site. While you are working on it (and after it goes live, if you come across an error), you can set this back to TRUE to see more info.

Good luck with this.

-Greg