DaniWeb Forum Index > Web Development > PHP

Populating form with existing MySQL data

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Feb 2008

Posts: 24

Reputation: dwlamb_001 is an unknown quantity at this point

Solved Threads: 0

dwlamb_001 dwlamb_001 is offline

Offline

Newbie Poster

Populating form with existing MySQL data

Feb 19th, 2008

I've searched through the threads and I can't find what I am looking for exactly. I apologise if I'm posting something already resolved for someone else.

I have a page for browsing the contents of a database. An option for each of those records is to update/edit. The user clicks an edit button and a new page is called using $_GET to specify the record id and query the database for all fields in that record. I want my form to be populated with those fields so I can edit the data. This is the code so far:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<?php
 
//from the url, get the id 
$id = $_GET["id"];
if (!$id) 
  {
  die("Variable id not defined.  Script terminating.");
  }
//connect to the database
$con = mysql_connect("localhost","root");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
//select Jokes database
$db=mysql_select_db("jokes", $con) or die(mysql_error());
//define the query
$sql="SELECT * FROM data WHERE id=".$id."";
 
echo "<head>";
echo "	<title>Editing record id=".$id."</title>";
echo "</head>
";
echo "<body>
";
 
$result = mysql_query($sql, $con) or die('Could not connect: ' . mysql_error());
If (!$result) {
   die('Result not returned:' . mysql_error());
}
 
//close the connection
mysql_close();
while ($list = mysql_fetch_array($result)){ 
echo "<form ENCTYPE=\"multipart/form-data\" action=\"".$_SERVER['PHP_SELF']."\" method=\"post\">
";
echo "<input type=\"text\" value=\"".$list['category']."\" maxlength=\"64\" name=\"category\" size=\"25\" /><br />
";
echo "<input type=\"text\" value=\"".$list['title']."\" maxlength=\"128\" name=\"category\" size=\"25\" /><br />
";
echo "<textarea cols=\"80\" rows=\"10\" wrap=\"ON\" name=\"body\">
$list['localstyles']</textarea><br />
";
echo "<textarea cols=\"80\" rows=\"15\" wrap=\"ON\" name=\"body\">
$list['body']</textarea><br />
";
echo "<button type=\"submit\">Update</button>
";
echo "</form>";
}
?>
</body>
</html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<?php

//from the url, get the id 
$id = $_GET["id"];
if (!$id) 
  {
  die("Variable id not defined.  Script terminating.");
  }
//connect to the database
$con = mysql_connect("localhost","root");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
//select Jokes database
$db=mysql_select_db("jokes", $con) or die(mysql_error());
//define the query
$sql="SELECT * FROM data WHERE id=".$id."";

echo "<head>";
echo "	<title>Editing record id=".$id."</title>";
echo "</head>
";
echo "<body>
";

$result = mysql_query($sql, $con) or die('Could not connect: ' . mysql_error());
If (!$result) {
   die('Result not returned:' . mysql_error());
}

//close the connection
mysql_close();
while ($list = mysql_fetch_array($result)){ 
echo "<form ENCTYPE=\"multipart/form-data\" action=\"".$_SERVER['PHP_SELF']."\" method=\"post\">
";
echo "<input type=\"text\" value=\"".$list['category']."\" maxlength=\"64\" name=\"category\" size=\"25\" /><br />
";
echo "<input type=\"text\" value=\"".$list['title']."\" maxlength=\"128\" name=\"category\" size=\"25\" /><br />
";
echo "<textarea cols=\"80\" rows=\"10\" wrap=\"ON\" name=\"body\">
$list['localstyles']</textarea><br />
";
echo "<textarea cols=\"80\" rows=\"15\" wrap=\"ON\" name=\"body\">
$list['body']</textarea><br />
";
echo "<button type=\"submit\">Update</button>
";
echo "</form>";
}
?>
</body>
</html>

The latter part in the while {} statement is the problem. I've done the one quoted above and variations that have provided the form but not the data associated with the variables. Inspecting the HTML results displays value="" in each of the forms tags. I know the array is being returned for testing I have done using print_r(mysql_fetch_array($result)) displays the data.

I realise there may be several ways to do this and would like help as to the most efficient that will set-up the form and populate it with the existing array of data. I know the following:

I'm only going to have one row to work with. By querying on the id, it's only going to return one record.
To iterate the data, I'll need to do it using an inline method like the code quoted above or assign it to variables using mysql_result before the form, then include each of those variables in the forrm.
For defining the form, I can do it the way it's quoted or use a method using
Help with Code Tags

php Syntax (Toggle Plain Text)
```
echo <<<FORMENT
<form action = "{$_SERVER['PHP_SELF']}" method = "post">
 
   quoted form text and textareas as I would in html
 
</form>
FORMENT;
```
echo <<<FORMENT <form action = "{$_SERVER['PHP_SELF']}" method = "post"> quoted form text and textareas as I would in html </form> FORMENT;
I picked up that method for creating a form for data entry on a tutorial but I don't know how to make it work to populate the fields with existing data. As well, I can't find documentation for that echo <<< FORMENT ... FORMENT; syntax. The site I obtained it from has since removed its tutorials and gone over to a forum only format.

I know that I will still have code to write for updating the record once the Update button is clicked and I know that part.

If you can't tell, I'm a newbie to all of this. I would really appreciate on this and thank you for reading this far.

Dan

•

Join Date: Nov 2007

Posts: 3,760

Reputation: nav33n is a jewel in the rough

Solved Threads: 332

nav33n

Offline

Senior Poster

Re: Populating form with existing MySQL data

Feb 19th, 2008

Umm.. Are you working on linux by any chance ? Because in linux, the column names are case-sensitive. If you use $list['name'] in your query and you have NAME as a column name, then it would return null ! That might be the problem. Print out $list['category']. There must be something wrong ! And check this link for the syntax of heredoc.

Ignorance is definitely not bliss!

*PM asking for help will be ignored*

•

Join Date: Feb 2008

Posts: 11

Reputation: coffeepot! is an unknown quantity at this point

Solved Threads: 2

coffeepot! coffeepot! is offline

Offline

Newbie Poster

Re: Populating form with existing MySQL data

Feb 19th, 2008

All looks okay to me but you could try the following mysql_fetch_assoc($result) instead of array.

Anything one man can make - another man will try to break.

•

Join Date: Feb 2008

Posts: 24

Reputation: dwlamb_001 is an unknown quantity at this point

Solved Threads: 0

dwlamb_001 dwlamb_001 is offline

Offline

Newbie Poster

Re: Populating form with existing MySQL data

Feb 19th, 2008

•

Originally Posted by nav33n

Umm.. Are you working on linux by any chance ? Because in linux, the column names are case-sensitive. If you use $list['name'] in your query and you have NAME as a column name, then it would return null ! That might be the problem. Print out $list['category']. There must be something wrong ! And check this link for the syntax of heredoc.

Thanks for the input. No, I am not on Linux. I'm aware of the need for case consistency and have adopted the practise of using field names in the case they are defined as in the database. That way if I ever work on a Linux based project, I don't have to "unlearn" bad habits.

After posting my code here, i noticed inconsistency in the use of quotes and double-quotes in the variable I wanted output. I went back to my original code and tried with different variations. The variables were still empty.

Last edited by dwlamb_001; Feb 19th, 2008 at 10:56 pm. Reason: Fixed a typo

•

Join Date: Feb 2008

Posts: 24

Reputation: dwlamb_001 is an unknown quantity at this point

Solved Threads: 0

dwlamb_001 dwlamb_001 is offline

Offline

Newbie Poster

Re: Populating form with existing MySQL data

Feb 19th, 2008

Hello again,

I worked on this some more and came up with the following to populate data in the form fields;

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<?php
...Save as originally posted....
 
$result = mysql_query($sql, $con) or die('Could not connect: ' . mysql_error());
If (!$result) {
   die('Result did not function:' . mysql_error());
}
//determine number of rows in the result
$num=mysql_numrows($result);
 
//close the connection
mysql_close();
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$category=mysql_result($result,$i,"category");
$title=mysql_result($result,$i,"title");
$localstyles=mysql_result($result,$i,"localstyles");
$body=mysql_result($result,$i,"body");
$i++;
}
echo "<form ENCTYPE=\"multipart/form-data\" action=\"".$_SERVER['PHP_SELF']."?id=".$id."\" method=\"post\">\n";
echo "<input type=\"text\" value=\"".stripslashes($category)."\" maxlength=\"64\" name=\"category\" size=\"25\" /><br />\n";
echo "<input type=\"text\" value=\"".stripslashes($title)."\" maxlength=\"128\" name=\"title\" size=\"64\" /><br />\n";
echo "<textarea cols=\"80\" rows=\"5\" wrap=\"ON\" name=\"localstyles\">".stripslashes($localstyles)."</textarea><br />\n";
echo "<textarea cols=\"80\" rows=\"15\" wrap=\"ON\" name=\"body\">".stripslashes($body)."</textarea><br />\n";
echo "<button type=\"submit\">Update</button>
";
echo "</form>";
 
?>
</body>
</html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<?php
...Save as originally posted....

$result = mysql_query($sql, $con) or die('Could not connect: ' . mysql_error());
If (!$result) {
   die('Result did not function:' . mysql_error());
}
//determine number of rows in the result
$num=mysql_numrows($result);

//close the connection
mysql_close();
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$category=mysql_result($result,$i,"category");
$title=mysql_result($result,$i,"title");
$localstyles=mysql_result($result,$i,"localstyles");
$body=mysql_result($result,$i,"body");
$i++;
}
echo "<form ENCTYPE=\"multipart/form-data\" action=\"".$_SERVER['PHP_SELF']."?id=".$id."\" method=\"post\">\n";
echo "<input type=\"text\" value=\"".stripslashes($category)."\" maxlength=\"64\" name=\"category\" size=\"25\" /><br />\n";
echo "<input type=\"text\" value=\"".stripslashes($title)."\" maxlength=\"128\" name=\"title\" size=\"64\" /><br />\n";
echo "<textarea cols=\"80\" rows=\"5\" wrap=\"ON\" name=\"localstyles\">".stripslashes($localstyles)."</textarea><br />\n";
echo "<textarea cols=\"80\" rows=\"15\" wrap=\"ON\" name=\"body\">".stripslashes($body)."</textarea><br />\n";
echo "<button type=\"submit\">Update</button>
";
echo "</form>";

?>
</body>
</html>

Why it is working under this method as opposed to the previous one, I don't know. I tried coffepot's suggestion of mysql_fetch_assoc($result) and the variables still did not display.

This code does include the stripslashes function but that's a recent add-on. I had the code working on this arrangement before the stripslashes were added.

I guess this one can be marked as solved but I'd still appreciate it if someone can compare the two processes and establish why the first one failed.

Have a Great Day!

Dan

•

Join Date: Nov 2007

Posts: 3,760

Reputation: nav33n is a jewel in the rough

Solved Threads: 332

nav33n

Offline

Senior Poster

Re: Populating form with existing MySQL data

Feb 19th, 2008

mysql_fetch_array does the work of both mysql_fetch_row and mysql_fetch_assoc. (mysql_fetch_array can take an extra parameter!). Anyway, stripslashes just takes off the added slashes. unless you are storing the values using addslashes, You don't have to use stripslashes. Print out what's in $category. If it has slashes, then probably, that slash 'escaped' the " character and maybe that was the reason you were getting null ?

Cheers,
Naveen

Ignorance is definitely not bliss!

*PM asking for help will be ignored*

•

Join Date: Feb 2008

Posts: 11

Reputation: coffeepot! is an unknown quantity at this point

Solved Threads: 2

coffeepot! coffeepot! is offline

Offline

Newbie Poster

Re: Populating form with existing MySQL data

Feb 20th, 2008

Well I have to do this exact same operation. The only difference I can see, and this code below is tested and works fine, is the way i construct the HTML...

while($row=mysql_fetch_assoc($result)){
echo("<form name='myform' action='action_form.php' method='post'><tr><input type='hidden' name='control' value='".$row['id']."'><td>".$row['id']."</td><td>".$row['menuitem']."</td><td>".$row['pagetitle']."</td>
<td><input type='submit' name='submit' value='Do This'></td></tr></form>");
}

The difference is I wrap the values in single quotes.

Anything one man can make - another man will try to break.

•

Join Date: Jun 2009

Posts: 68

Reputation: djjjozsi is an unknown quantity at this point

Solved Threads: 10

djjjozsi

Offline

Junior Poster in Training

Re: Populating form with existing MySQL data

Jun 21st, 2009

in teh second code you have a mysql_numrows function, which is bad. you mean mysql_num_rows

that method is quite the longest way you fetch the values from a database.

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
	$sql_edit="select * from `table`";
    $result_result=mysql_query($sql_edit) or die(mysql_error());
    if(mysql_num_rows($result_result)>0)
    {
        while($row=mysql_fetch_assoc($result_result))
        {
		print '<tr>';
		echo "<td>". ( empty($row["category"]) ? "&nbsp;" : (htmlspecialchars($row["category"])) )."</td>";
		echo "<td>". ( empty($row["title"]) ? "&nbsp;" : (htmlspecialchars($row["title"])) )."</td>";
		echo "<td>". ( empty($row["localstyles"]) ? "&nbsp;" : (htmlspecialchars($row["localstyles"])) )."</td>";
		echo "<td>". ( empty($row["body"]) ? "&nbsp;" : (htmlspecialchars($row["body"])) )."</td>";
		echo "<td><a href=\"?edit={$row["id"]}\">Edit</a></td>";
		print '</tr>';
        }
    }
    else
    echo "No result in result table ...";
	$sql_edit="select * from `table`";
    $result_result=mysql_query($sql_edit) or die(mysql_error());
    if(mysql_num_rows($result_result)>0)
    {
        while($row=mysql_fetch_assoc($result_result))
        {
		print '<tr>';
		echo "<td>". ( empty($row["category"]) ? "&nbsp;" : (htmlspecialchars($row["category"])) )."</td>";
		echo "<td>". ( empty($row["title"]) ? "&nbsp;" : (htmlspecialchars($row["title"])) )."</td>";
		echo "<td>". ( empty($row["localstyles"]) ? "&nbsp;" : (htmlspecialchars($row["localstyles"])) )."</td>";
		echo "<td>". ( empty($row["body"]) ? "&nbsp;" : (htmlspecialchars($row["body"])) )."</td>";
		echo "<td><a href=\"?edit={$row["id"]}\">Edit</a></td>";
		print '</tr>';
        }
    }
    else
    echo "No result in result table ...";