Retrieve and Edit data ... help!

fcarroll

Hi,

Hope someone can help me with some code for editing data that I retrieve from a database. I am a newbie so please bear with me. Below is the code I used for retrieving data from a database, the data is sent from a form I have set up (basic stuff I guess) what I now want to do is be able to retrieve the data for viewing and editing, how can I do this?

<html>
<head>
<title>IED Group Quote List</title>

<body>
<?php
//Set the variables for the database access:
$Host="localhost";
$User="xxxxxx";
$Password="xxxxxxx";
$DBName="xxxxxxx";
$TableName="xxxxxxx";

$Link=mysql_connect($Host,$User,$Password);
$Query="SELECT*from $TableName";
$Result=mysql_db_query ($DBName, $Query, $Link);

//Create a table.
print ("<TABLE BORDER=1 WIDTH=\"90%\" CELLSPACING=2 CELLPADDING=2 bgcolor=#D0D0D0 ALIGN=CENTER>\n");
print ("<TR ALIGN=CENTER bgcolor=#000000 VALIGN=TOP>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP><FONT COLOR=WHITE><b>Date</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP><FONT COLOR=WHITE><b>Customer</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP><FONT COLOR=WHITE><b>PartNumber</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP><FONT COLOR=WHITE><b>Qty</TD>\n");
print ("</TR>\n");

//Fetch the results from the database.
while($Row=mysql_fetch_array($Result)){
print ("<TR ALIGN=CENTER VALIGN=TOP>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row[Date]</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row[Customer]</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row[PartNumber]</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row[Qty]</TD>\n");
print ("</TR>\n");
}
mysql_close($Link);
print("<TABLE>\n");
?>.
</BODY>
</HTML>

tmaiden

dont really have time atm to really look this over but i did notice...

print("<TABLE>\n");

should be
print("</TABLE>\n");

Roger_Ramjet

Two basic approaches to this: put the data into text or textarea form elements that you can edit and then save instead of table cells; embed a link in each table row or cell that opens a form which will accept input to overwrite the data in the db. Which you choose depends on the nature of the edits you want to perform.

Also, I personally find echo a much better way to structure my html output then print and \n. If you really want to write clear code at both ends then HEREDOC is the recomended form (but it does not work for me, just another of the config problems my ISP plagues me with, like no bcmaths functions, etc, etc)

fcarroll

Roger, thanks for the input, I like the "embed a link in each table row or cell that opens a form which will accept input to overwrite the data in the db." sugestion, is there a certain line of code I would use?

Roger_Ramjet

I have a primary key called 'track' and the following is how I do it:

		while ($row = mysql_fetch_array($debts)) {
			$link = '<a href="index.php?go_search=1&go_field=track&go_string=' . $row['track'] . '">';
  			echo '<tr><td>' . $link . $row['track'] . '</a></td>';
  			echo '<td>' . $link . $row['ref'] . '</a></td>';
  			echo '<td>' . $link . $row['name'] . '</a></td>';
  			echo '<td align="right">' . $link . $row['curr'] . $row['amt'] . '</a></td>';
  			echo '<td align="right">' . $link . $row['curr'] . $row['amt_due'] . '</a></td>';
  			echo '<td title="' . $row['status'] . '">' . $link . $row['code'] . '</a></td></tr>';
 		}
  	echo '</table>';
  	echo '</div>';

(Lifted straight from one of my pages so I know it works)

This creates the link in each table cell which, in my case, returns to the same page but this time displaying the selected record instead of a summary table. You would construct your link to go to a page with a form that displays the selected data in text boxes for edit and save.

fcarroll

Roger,

I have entered some of the code as below, it pulls up correct, click one of the links and goes to the form but nothing past that, do you think that the problem lies with the form?

php is >>>

$Link=mysql_connect($Host,$User,$Password);
$Query="SELECT*from $TableName";
$Result=mysql_db_query ($DBName, $Query, $Link);

while ($row = mysql_fetch_array($Result)) {
$link = '<a href="formreturn.php?go_search=1&go_field=track&go_string=' . $row['Date'] . '">';
echo '<tr><td>' . $link . $row['Date'] . '</a></td>';
echo '<td>' . $link . $row['Customer'] . '</a></td>';
echo '<td>' . $link . $row['PartNumber'] . '</a></td>';
echo '<td>' . $link . $row['Qty'] . '</a></td>';
echo '</td>';
}
echo '</table>';
echo '</div>';
print ("</TR>\n");

mysql_close($Link);
print("<TABLE>\n");
?>.
</BODY>
</HTML>

Roger_Ramjet

OK. The parameters passed in the get in my code are for my page to tell: what kind of search is selected 'go_search=1' and the field that is being searched on 'go_field=track' . This is because I use a mulitpurpose page. When no search criteria are entered, a summary table of all accounts is displayed and the user can click on any row to view the details for that one account. I also have a search bar where they can enter account refs or name and search on that. So my page needs to know what type of search this is and what field to search on.

From what you tell me, you don't need to differentiate search types so you can loose my go_search parameter.

The go_field in my code never varies from column to column in the table, it is there because I also POST from a search bar, so you can loose that.

What you do need is the primary key for each row, is that the $date parameter? Assuming it is then that is all you need in your link.

The formreturn.php then needs something like this to process and present the data


if (isset($_GET['go_string'])) {
$query = "SELECT * FROM table WHERE date = '" . $_GET['go_string'] . "'";
$result = mysql_query($query);
while ($row= mysql_fetch_array($result)) {
echo '<form name="edit" method="post" action="update.php">';
//code to display the data in text boxes for edit etc
}
}