[RESOLVED] Edit MYSQL entries with PHP

[drumbum360]

I have this code that is supposed to edit entries in my database but cannot figure out why it will not update the entry. All it does when I run it is erase the opclo and projectassigned mysql fields for the latest entry and it displays my success message. Can you please help me figure out what I have done wrong?

<? 
//connect to mysql
//change user and password to your mySQL name and password
include("include/session.php");

mysql_connect(DB_SERVER,DB_USER,DB_PASS) or die(mysql_error()); 
    
//select which database you want to edit
mysql_select_db(DB_NAME) or die(mysql_error()); 

//If cmd has not been initialized
if(!isset($cmd)) 
{
   //display all the projects
   $result = mysql_query("select * from projects order by id"); 
   
   //run the while loop that grabs all the project     scripts
   while($r=mysql_fetch_array($result)) 
   { 
      //grab the title and the ID of the projects
      $projectname=$r["projectname"];//take out the title
      $id=$r["id"];//take out the id
     
     //make the title a link
      echo "<a href='edit.php?cmd=edit&id=$id'>$projectname - Edit</a>";
      echo "<br>";
    }
}

if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST['submit']))
   {
      $id = $_GET["id"];
      $sql = "SELECT * FROM projects WHERE id=$id";
      $result = mysql_query($sql);        
      $myrow = mysql_fetch_array($result);
      ?>
      
      <form action="edit.php" method="post">
      <input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
   
      projectname:<INPUT TYPE="TEXT" NAME="projectname" VALUE="<?php echo $myrow["projectname"] ?>" SIZE=30><br>
      assigned to:<INPUT TYPE="TEXT" NAME="projectassigned" VALUE="<?php echo $myrow["projectassigned"] ?>" SIZE=30><br>
      status:<INPUT TYPE="TEXT" NAME="opclo" VALUE="<?php echo $myrow["opclo"] ?>" SIZE=6><br>
   
      <input type="hidden" name="cmd" value="edit">
   
      <input type="submit" name="submit" value="submit">
   
      </form>
<?   
}
   if ($_POST['submit'])
   {
      $pname = $_POST["projectname"];
      $pass = $_POST["projectassigned"];
      $status = $_POST["opclo"];
      $id = $_GET["id"];

      
      $sql = "UPDATE projects SET projectname='$projectname',projectassigned='$projectassigned',opclo='$opclo' WHERE id='$id'";
      //replace news with your table name above
      $result = mysql_query($sql); 
      echo mysql_error();
      echo "Thank you! Information updated.";
    }
}
?>

[SyCo]

To debug a problem like this turn error handing to E_ALL and fix the errors you get. You ought to test for the existence of vars in logic but you can add a @ to suppress the error and let it return false.

eg

 if ($_POST['submit']) to  
if (@$_POST['submit']) 

Next print_r($_POST) to see the values are as you expect.

Next read up on SQL injection. You script is currently vulnerable to a very simple attack. Renaming vars for no reason serves no purpose. It does make missing errors like this easy.

[drumbum360]

I believe you are talking about making sure that E_ALL is enabled in my PHP.ini file? If so, I believe it is. Upon checking it says this:
error_reporting = E_ALL & ~E_NOTICE

I'm confused about the @$_POST you are saying I should do as well as the print_r($_POST)
Thank you SyCo and I will look at SQL injection prevention methods

[SyCo]

If error reporting is set to E_ALL then this

if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit") 

and this

if ($_POST['submit']) 

would both report an undefined variable. Check also the setting for display_errors() it should be set to 'on'.

You can check the ini settings using phpinfo(); it's possible you're looking at the wrong ini file. phpinfo() will show the path under "Configuration File (php.ini) Path "

print_r($_POST); will display all the posted variables so you can see what is being received after the form is posted. You say the vars are updated to empty so you need to see where that values are being lost. First check they are received by the script and look at you would expect. You can add

echo '<pre>'.print_r(@$_POST).'</pre>'; to the top of the script. You'll see

array(
)

when the page first loads as the POST array is empty.
Submit the form and you'll see the submitted values displayed.

Then echo the query to the page and see who it looks. Copy it and run it on a command line like putty and see if you get any errors.

[SyCo]

Oh yea, you can set error_reporting() in the script too, not just the ini.

[drumbum360]

I have run it with the revisions and now this comes up.

First is this which looks correct to me. It holds the proper revised variables:
Array ( [id] => 1 [projectname] => Ghost [projectassigned] => cooper [opclo] => Open [cmd] => edit [submit] => submit )

1

Then this error is displayed (Does that mean I have something wrong in my session.php file?):
Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/content/somemore/edit.php:3) in somemore/manage/include/session.php on line 46

Third I have this error at the bottom of the page and I'm not sure why:
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

[drumbum360]

I got rid of the third error, simply was missing ' ' around a variable

[SyCo]

Array ( [id] => 1 [projectname] => Ghost [projectassigned] => cooper [opclo] => Open [cmd] => edit [submit] => submit )

This is the array with the vars. Becaue it's right at the top it's outputted beor the headers were sent. You can move it below the session include and...

Then this error is displayed (Does that mean I have something wrong in my session.php file?):
Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/content/somemore/edit.php:3) in somemore/manage/include/session.php on line 46

This error will go away.

Third I have this error at the bottom of the page and I'm not sure why:
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

Echo $sql to the page and examine your query for syntax errors.

[drumbum360]

Heh, so I resolved the errors and have been able to update the database. Problem now is that when I do update any of the entries in the database it takes my revision and updates the bottom entry which is the latest one. It doesn't apply my revision to the correct entry. What direction should I go on this one?

[SyCo]

again echo the sql to the page and examine the query. You might want to give the SQL vars unique names so you're sure you're looking at what you think you are. or somehting like

echo 'sql1 - '.$sql; 

The GET id is the suspect. Pass it into a hidden form field and resubmit it so it become part if the POST.

[drumbum360]

SyCo, You're awesome! Thank you for the help once again. I have solved it with your help.

[SyCo]

cool, happy to help. Glad it's solved.