From Mysql database into a form for editting the data

[Phobia1]

Hi Guys
I got data entry sorted thanks you your excellent help.
Now I want to be able to modify the data and for the moment cannot display the data.
here is my feeble attempt so far.
<?
// modify.php
require("./resources/globals.php") ;
require("./resources/common.php") ;
require("./resources/foots.php") ;

$selectStmt = "SELECT * FROM Garant WHERE Ref = '$detail'" ;
$result=$selectStmt;
//Check data

// Connect to the Database
if (!($link=mysql_connect($location,$usr,$pwd) or die (mysql_error()))) {

DisplayErrMsg(sprintf("error connecting to host %s, by usr %s",
$location, $usr)) ;
exit() ;
}

// Select the Database
if (!mysql_select_db($dbname, $link)) {
DisplayErrMsg(sprintf("Error in selecting %s database", $dbname)) ;
DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;
exit() ;
}

// Execute the Statement
if (!($result =mysql_query($selectStmt, $link))) { //where Detail =?

DisplayErrMsg(sprintf("Error in executing %s stmt", $selectStmt)) ;
DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;
exit() ;
}

GenerateHTMLHeader( "Please modify fields") ;
if (!($row = mysql_fetch_object($result))){
DisplayErrMsg("Internal error: the entry does not exist") ;
exit() ;
}

while ($row = mysql_fetch_object($result)) {

echo $row->Ref . "<br />"; //this does not retrieve anything

Best regards from Bulgaria
F

[RonPK]

$selectStmt = "SELECT * FROM Garant WHERE Ref = '$detail'" ;

You'll need to define $detail. Is it in the query string? If so, use $_GET['detail'] and quote it with mysql_real_escape_string() [ph.net].

[Knucklehead00]

Also, as a rule of thumb, refrain from using SELECT *.

Identify which fields you want to pull out -- SELECT field1, field2, field3 FROM table1 WHERE condition=$this.

It is a better mode of documentation for future revisions as you will know EXACTLY what data you are pulling from the database.

It also cuts down on your memory allocation and helps with your array identification.

[edited by: Knucklehead00 at 4:35 pm (utc) on Oct. 23, 2008]

[Phobia1]

Hi, thanks for the replies.
I have managed to get the form populated with data from my Mysql database, However, some of the data is truncated. for example 'Very Good Access' becomes simply 'Very'.
Does anyone have any idea why?
Best
F
<?
// modify.php
require("./resources/globals.php") ;
require("./resources/common.php") ;
require("./resources/foots.php") ;

$selectStmt = "SELECT * FROM Garant WHERE Ref= '$detail'" ;

// Connect to the Database
if (!($link=mysql_pconnect($hostName, $userName, $password))) {
DisplayErrMsg(sprintf("error connecting to host %s, by user %s",
$hostName, $userName)) ;
exit() ;
}

// Select the Database
if (!mysql_select_db($databaseName, $link)) {
DisplayErrMsg(sprintf("Error in selecting %s database", $databaseName)) ;
DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;
exit() ;
}

// Execute the Statement
if (!($result= mysql_query($selectStmt, $link))) {
DisplayErrMsg(sprintf("Error in executing %s stmt", $selectStmt)) ;
DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;
exit() ;
}

GenerateHTMLHeader( "Please modify fields") ;

if (!($row = mysql_fetch_object($result))){
DisplayErrMsg("Internal error: the entry does not exist") ;
exit() ;
}

$resultEntry["Ref"] = $row->Ref;
$resultEntry["Portfolio"] = $row->Portfolio;
$resultEntry["Roadaccess"] = $row->Roadaccess;

$resultEntry["Town"] = $row->Town;
$resultEntry["Price"]= $row->Price;
$resultEntry["Location"]= $row->Location;

GenerateHTMLForm2( $resultEntry, "update.php?rowid=$detail", "MODIFY" );

mysql_free_result($result) ;
?>

// Generate the HTML form for add/modify/search
function GenerateHTMLForm2($formValues, $actionScript, $submitLabel ) {

printf("<FORM METHOD=post ACTION=\"%s\"><PRE>\n", $actionScript);

printf("<TABLE border=\"0\" width=\"100%%\">");

printf("<TD align=right><B>Our Reference:</B></TD><TD><INPUT TYPE=Text SIZE=20 NAME=Ref Value=".$formValues['Ref']."></TD></TR>");

printf("<TR><TD align=right><B>Region of:</B></TD><TD><INPUT TYPE=Text SIZE=30 NAME=Town Value=".$formValues['Town']."></TD>");

printf("<TR><TD align=right><B>Location:</B></TD><TD><INPUT TYPE=Text SIZE=30 NAME=Location Value=".$formValues['Location']."></TD>");

printf("</TABLE>");

printf("<INPUT TYPE=submit name=\"submit\" VALUE=\"%s\">", $submitLabel );

printf("</PRE></FORM>" );

}

[Phobia1]

Here is also my update.php
Could anyone tell me why it does not update the database?
<?

// update.php

require("./resources/globals.php") ;
require("./resources/common.php") ;

//echo $Ref; :-)OK

$updateStmt = "Update Garant SET Ref='$Ref',Portfolio='$Portfolio', Town='$Town', Price='$Price',Location='$Location', Size='$Size', Condition='$Condition', Bedrooms='$Bedrooms', smallpic='$smallpic' where Ref = '$Ref' ";

// Connect to the Database
if (!($link=mysql_pconnect($location,$userName,$password))) {
DisplayErrMsg(sprintf("error connecting to host %s, by user %s",
$location, $userName)) ;
exit() ;
}

// Select the Database
if (!mysql_select_db($dbname, $link)) {
DisplayErrMsg(sprintf("Error in selecting %s database", $dbname)) ;
DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;
exit() ;
}

// Execute the Statement
if (!mysql_query ($updateStmt)){

// Execute the Statement
//if (!mysql_query($updateStmt, $link)) {
DisplayErrMsg(sprintf("Error in executing %s stmt", $updateStmt)) ;
DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;
exit() ;
}

GenerateHTMLHeader("The entry was modified succesfully");

ReturnToDisplay( );

?>

[RonPK]

$updateStmt = "Update Garant SET Ref='$Ref' [...] where Ref = '$Ref' ";

That won't work. If Ref has been changed by the user the WHERE statement will not find the row (or the wrong one). If Ref has not been changed, why update the record.

My suggestion is to use something like $newRef (modified by the user in the form) and $oldRef - a hidden field that passes a reference to the table record that should be updated.

Also, it is really very important to escape variables when inserting them into a query.

[edited by: RonPK at 2:36 pm (utc) on Oct. 24, 2008]

[Phobia1]

Hi
Thanks for the reply. In fact it does work. though I will make what you suggest as its a better idea:-) In some cases the REf value may indeed be changed.
However, I still cannot get a full line of data into the form for editting which is confusing me. In another area, where the data is simply displayed, all is fine. When I inert into a form the data is truncates to just the first word of each item. Why is this? Thanks in advance for any replies.
Best
FJW