edit table / update information

misseether

Hello again everyone, I am continuing building my script, and I am creating a part where people can edit the information they insered in the database.

This part is seperated in 3 pages : alter.php ; alert1.php and alter2.php

Alter.php is used for the user to select the "contentor" number ( "contentor" is kinda like a box in my language ) that he wants to edit the details of, and it's working good.

alter1.php captures the actual information in a form, where people can change it and submit it...

alter2.php updates the database...

However, it isn't updating and I can't seem to find where I am going wrong... it gives me a specific error just lets me know that an error happened ... here's the information:

File1 - Alter.php - It's working fine!!!

<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mundo Maritimo - Editar contentor</title>
<link href="stylesheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
Selecione o contentor a editar<br /><br />

<?php
$host = "*****";               // Database server 
        $user = "*****";          // Database username 
        $pass = "*****";                // Database password 
        $db = "tracking";       // Database name 

$db1=mysql_connect("$host","$user","$pass"); 
mysql_select_db("$db"); 
$ok = mysql_select_db("$db"); 
if (!ok) 
{ 
    die("<br>" . mysql_errno().":".mysql_errno()."<br>"); 
} 

$sql="select * from script";
$resultado = mysql_db_query ("tracking", $sql);

if ($resultado) {
print ("<table width=\"95%\" border=1 align=center cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#333333\">");
print ("<tr><td width=\"40%\" align=center bgcolor=\"#999999\"><div align=\"center\">Contentor</div></td><td width=\"40%\" bgcolor=\"#999999\"><div align=\"center\">Tipo</div></td><td width=\"40%\" bgcolor=\"#999999\"><div align=\"center\">Sa&#237;da para</div></td><td width=\"40%\" bgcolor=\"#999999\"><div align=\"center\">Data de Sa&#237;da</div></td><td width=\"40%\" bgcolor=\"#999999\"><div align=\"center\">Local</div></td><td width=\"40%\" bgcolor=\"#999999\"><div align=\"center\">Descri&#231;&#227;o</div></td><td width=\"40%\" bgcolor=\"#999999\"><div align=\"center\">Data</div></td><td width=\"40%\" bgcolor=\"#999999\"><div align=\"center\">Navio</div></td><td width=\"40%\" bgcolor=\"#999999\"><div align=\"center\">Voyage</div></td></tr>");
while ($registo=mysql_fetch_array($resultado)) {
$id=$registo["id"];
$contentor=$registo["contentor"];
$type=$registo["type"];
$to=$registo["to"];
$dateon=$registo["dateon"];
$location=$registo["location"];
$description=$registo["description"];
$date=$registo["date"];
$vessel=$registo["vessel"];
$voyage=$registo["voyage"];
print ("<tr><td align=center><div align=\"center\"><a href=\"alter1.php?id=$id&contentor=$contentor&type=$type&to=$to&dateon=$dateon&location=$location&description=$description&date=$date&vessel=$vessel&voyage=$voyage\">$contentor</a></div></td><td><div align=\"center\">$type</div></td><td><div align=\"center\">$to</div></td><td><div align=\"center\">$dateon</div></td><td><div align=\"center\">$location</div></td><td><div align=\"center\">$description</div></td><td><div align=\"center\">$date</div></td><td><div align=\"center\">$vessel</div></td><td><div align=\"center\">$voyage</div></td></tr>");
}
echo ("</table>");

}else{
print ("n&#227;o h&#225; registos");
}
mysql_free_result ($resultado);

?>

</body>
</html>

File2 - Alter1.php - I think it's ok... but not sure

<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mundo Maritimo - Editar contentor</title>
<link href="stylesheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<p align="center">Contentor Selecionado - Edite Contentor</p>

<form method="POST" action="alter2.php">

<table width="450" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#333333" bgcolor="#CCCCCC">
 <tr>
    <td colspan="3" bgcolor="#999999"><div align="center" class="style11">Editar Contentor</div></td>
  </tr>
  <tr>
    <td colspan="3" bgcolor="#999999"><div align="center" class="style11">Contentor adicionado N&#186; - <?php echo $_REQUEST['id'];?></div></td>
  </tr>
  <tr>
    <td width="150" align="right" bgcolor="#333333"><span class="style16">Contentor:</span></td>
    <td colspan="2" bgcolor="#333333"><label>
      <input name="contentor" type="text" id="contentor" value="<?php echo $_REQUEST['contentor'];?>" size="47" />
    </label></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333"><span class="style16">Tipo:</span></td>
    <td colspan="2" bgcolor="#333333"><input name="type" type="text" id="type" value="<?php echo $_REQUEST['type'];?>" size="47" /></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333"><span class="style16">Sa&#237;da para:</span></td>
    <td colspan="2" bgcolor="#333333"><input name="to" type="text" id="to" value="<?php echo $_REQUEST['to']; ?>" size="47" /></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333"><span class="style16">Data de sa&#237;da:</span></td>
    <td colspan="2" bgcolor="#333333"><input name="dateon" type="text" id="dateon" value="<?php echo $_REQUEST['dateon']; ?>" size="47" /></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333"><span class="style16">Local:</span></td>
    <td colspan="2" bgcolor="#333333"><input name="location" type="text" id="location" value="<?php echo $_REQUEST['location']; ?>" size="47" /></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333"><span class="style16">Descri&#231;&#227;o:</span></td>
    <td colspan="2" bgcolor="#333333"><input name="description" type="text" id="description" value="<?php echo $_REQUEST['description']; ?>" size="47" /></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333"><span class="style16">Data:</span></td>
    <td colspan="2" bgcolor="#333333"><input name="date" type="text" id="date" value="<?php echo $_REQUEST['date']; ?>" size="47" /></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333"><span class="style16">Navio:</span></td>
    <td colspan="2" bgcolor="#333333"><input name="vessel" type="text" id="vessel" value="<?php echo $_REQUEST['vessel']; ?>" size="47" /></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333"><span class="style16">Voyage:</span></td>
    <td colspan="2" bgcolor="#333333"><input name="voyage" type="text" id="voyage" value="<?php echo $_REQUEST['voyage']; ?>" size="47" /></td>
  </tr>
  <tr>
    <td align="right" bgcolor="#333333">&nbsp;</td>
    <td width="126" bgcolor="#333333"><div align="center">
      <label>
      <input type="submit" value="Alterar" />
      </label>
    </div></td>
    <td width="166" bgcolor="#333333"><div align="center">
      <label></label>
      <label>
        <input type="reset" name="Limpar" id="Limpar" value="Limpar" />
        </label>
    </div></td>
  </tr>
</table>



<input type="hidden" name="id" value="<?php echo $_REQUEST['id'];?>">
</form>

</body>
</html>

File 3 - Alter2.php - the page where the error appears ( but can come from the page before)

<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mundo Maritimo - Editar contentor</title>
<link href="stylesheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<?php

$host = "************";               // Database server 
        $user = "***********";          // Database username 
        $pass = "*********";                // Database password 
        $db = "tracking";       // Database name 

$db1=mysql_connect("$host","$user","$pass"); 
mysql_select_db("$db"); 
$ok = mysql_select_db("$db"); 
if (!ok) 
{ 
    die("<br>" . mysql_errno().":".mysql_errno()."<br>"); 
} 


$sql="update script set contentor='".$_REQUEST['contentor']."',type='".$_REQUEST['type']."',to='".$_REQUEST['to']."',dateon='".$_REQUEST['dateon']."',location='".$_REQUEST['location']."',description='".$_REQUEST['description']."',date='".$_REQUEST['date']."',vessel='".$_REQUEST['vessel']."',voyage='".$_REQUEST['voyage']."' where id='".$_REQUEST['id']."'";

$resultado=mysql_db_query("tracking",$sql);

$num_afect=mysql_affected_rows();

if ($resultado) {
print ("Altera&#231;&#227;o de $num_afect registo efectuada com sucesso<p>");
print ("O registo alterado passa a possuir os seguintes dados <p>");

$sql="select * from script where id='".$_REQUEST['id']."'";
$resultado=mysql_db_query ("tracking",$sql);
$num_campos=mysql_num_fields($resultado);
$num_reg=mysql_num_rows($resultado);

print ("<table width=90% align=center border=3>");
for ($coluna=0;$coluna<$num_campos;$coluna++) {
$field=mysql_field_name ($resultado,$coluna);
$campo=mysql_result($resultado,0,"$field");
print ("<tr><td align=center bgcolor=FFFF00>$field</td><td>$campo</td></tr>");
}
print ("</table>");
}else{
print ("Ocorreu um erro , repita a opera&#231;&#227;o");

}
mysql_free_result($resultado);
mysql_close();


?>

</body>
</html>

Hoping you can help me with this.
Thanks

ohioman

What is the specific error you're getting? Is it timing out?

misseether

ohioman wrote:
What is the specific error you're getting? Is it timing out?

on the last part of the 3rd page ( alter2.php) the script I have there that if the process doesn't get complete to appear:

}else{ 
print ("Ocorreu um erro , repita a operação");

Which means, " An error happened... "

that's what I am getting

ohioman

Are you sure that this error is coming from alter2.php? The reason I ask is that the same generic error is on both alter pages. One thing I do when a situation like this comes up.. is I swich the errors to be unique to be sure exactly what instance threw the error. Makes thing much easier to track down. 😉

Iceman 🙂

misseether

ohioman wrote:
Are you sure that this error is coming from alter2.php? The reason I ask is that the same generic error is on both alter pages. One thing I do when a situation like this comes up.. is I swich the errors to be unique to be sure exactly what instance threw the error. Makes thing much easier to track down. 😉

Iceman 🙂

Hey, thanks for the reply,
I know it is from that page because I did change that part to a different text and the different text came up!!
EDIT: I have been working on this in the past hours and can't seem to make it work 🙁

I am sorry The alter2.php and the alter1.php were the same code here, I have updated it with the real one... sorry for the mistake

misseether

Hello,
I put the line

mysql_query($sql) or die( "Unable to UPDATE Table: " . mysql_error());

in the end of the script on alter2.php and when i tried to edit a table it gives me the error:

Unable to UPDATE Table: you have an error in your sql syntax near 'to='SINES, PT', dateon='17/01/2008', location='BOBADELA,11,PT', description='Emp' on line 1

Can someone tell me what can I change to make this right?

misseether

please? I have been trying all weekend and the same error occours... what am I missing here?

Lars_Berg

to is a reserved word in SQL. Choose another name or enclose it with ` sign

`to`

misseether

thank you very much...
I changed the "to" to "tov"

it is editing now but in a weird way... if I update numbers ( if I change the information that is edited for exemplo from "hELLO" to " 235" it updates, but it I change " hello " to " goodbye " , it gives me the same error it did before.
any idea why this is happening?

Lars_Berg

What does yor update query look like in the latter case?

misseether

thanks for replying, here's the page with all changes made:

<!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>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Mundo Maritimo - Editar contentor</title>
<link href="stylesheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<?php

$host = "*************";        // Database server 
        $user = "***********";          // Database username 
        $pass = "*********";          // Database password 
        $db = "tracking";          // Database name 

$db1=mysql_connect("$host","$user","$pass"); 
mysql_select_db("$db"); 
$ok = mysql_select_db("$db"); 
if (!ok) 
{ 
    die("<br>" . mysql_errno().":".mysql_errno()."<br>"); 
} 



$sql="UPDATE script SET contentor=".$_REQUEST['contentor'].",tipov=".$_REQUEST['tipov'].",tov=".$_REQUEST['tov'].",dateon=".$_REQUEST['dateon'].",location=".$_REQUEST['location'].",description=".$_REQUEST['description'].",datev=".$_REQUEST['datev'].",vessel=".$_REQUEST['vessel'].",voyage=".$_REQUEST['voyage']." WHERE id=".$_REQUEST['id']."";

$resultado=mysql_db_query("tracking",$sql);


if ($resultado) {
print ("Altera&#231;&#227;o de $num_afect registo efectuada com sucesso<p>");
print ("O registo alterado passa a possuir os seguintes dados <p>");
echo 'Contentor adicionado com a seguinte informa&#231;&#227;o: <br><br><table width="95%" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#333333">
  <tr bgcolor="#333333">
    <td bgcolor="#999999"><div align="center">Contentor</div></td>
    <td bgcolor="#999999"><div align="center">Tipo</div></td>
    <td bgcolor="#999999"><div align="center">Sa&#237;da Para</div></td>
    <td bgcolor="#999999"><div align="center">Data de Sa&#237;da</div></td>
    <td bgcolor="#999999"><div align="center">Local</div></td>
    <td bgcolor="#999999"><div align="center">Descri&#231;&#227;o</div></td>
    <td bgcolor="#999999"><div align="center">Data </div></td>
    <td bgcolor="#999999"><div align="center">Navio</div></td>
    <td bgcolor="#999999"><div align="center">Voyage</div></td>
  </tr>
  <tr>
    <td><div align="center">'.$contentor.'</div></td>
    <td><div align="center">'.$tipov.'</div></td>
    <td><div align="center">'.$tov.'</div></td>
    <td><div align="center">'.$dateon.'</div></td>
    <td><div align="center">'.$location.'</div></td>
    <td><div align="center">'.$description.'</div></td>
    <td><div align="center">'.$datev.'</div></td>
    <td><div align="center">'.$vessel.'</div></td>
    <td><div align="center">'.$voyage.'</div></td>
  </tr>
</table>';


echo "<br>Ser&#225; redirecionado para o campo inicial em 5 segundos";
echo "<meta http-equiv=Refresh content=5;url=alter.php>";


}else{
print ("Ocorreu um erro , repita a opera&#231;&#227;o");
mysql_query($sql) or die( "Unable to UPDATE Table: " . mysql_error());

}
mysql_free_result($resultado);
mysql_close();

?>
</body>
</html>

I also changed the variables from " to " to "tov " and "type" to " tipov" and from " date " to " datev " on the database and the other files.
Thanks.

Lars_Berg

if you update a column, that is defined as character or date, the value should be enclosed in apostrophes

 SET contentor= '". $_REQUEST['contentor'] . "'" .

You also need to make your code safe for sql injections. (You can search for this topic as there are articles in abundance on the subject.)