Form to edit TABLE data

Luke Warm · February 28, 2011

Gang,

I'm trying to create a form that allows me to edit table data within a MySQL database. I've been able to display the data, no problem. I want to be able to edit the fields in the database tables but have had no luck. I'm using session variables to connect and gather the information I need.

Here's the code for collecting the table data:

<?php
mysql_select_db($database);
if(empty($database)) {
echo "<p>You must be connected to a database in order to view any table data.</p>";
}else{

// Show table data start
$sql = "SHOW TABLES FROM $database";
$result = mysql_query($sql);
$table = array();

while ($row = mysql_fetch_row($result)) 
{
$table[] = $row[0];
}
if (count($table) == 0)
{
echo "<p>The database '" . $database . "' contains no tables.</p>\n";
}
else
{
foreach($table AS $aTable)
{
echo "<p style='text-align:left;float:left;width:100%;margin-top:15px;'>Table: <font color='green'>$aTable</font>";

if (!mysql_connect($hostname, $user, $passwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$aTable}");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<div style='text-align:left;width:100%;'><table border='0'><tr><td></td>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td style='text-align:left;padding:3px;font-size:11px;color:green;'>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr><td style='text-align:center;padding:3px;font-size:10px;border:1px solid #888;background:#fff;'><a href='edit_data.php?=$row[0]' style='color:red;text-decoration:none;'>edit</a></td>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td style='text-align:left;padding:3px;font-size:10px;border:1px solid #888;background:#fff;clear:both;float:left;'>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
echo "</table></div>";
}

    }
} //end show table data


include("includes/footer.php");
?>

I'm not sure how to create a form using the session variables in order to be able to edit the correct information since I want to edit numerous databases.

Any help would be great! Thanks

Muddy_Funster · March 1, 2011

<a href='edit_data.php?=$row[0]'

you haven't put in a variable name to assign $row[0] to. you would need something like

<a href='edit_data.php?table=$row[0]'

then use

$tableName = $_GET['table']

In your edit_data.php page.

That help at all?

TomFromKWD · March 1, 2011

the way I personally use forms for table editing is like this

A simple HTML form that goes onto the web page in question and directs to a php script

I usually use "update.php" but you can call it what ever you like, just ensure you change where the form submits to

<form method="post" action="update.php">

<input type="text" name="fname" size="20" />

<input type="text" name="lname" size="20" />

<input type="text" name="email" size="100" />

<input type="submit" value="Submit Data" />

</form>

From there you then create the php file with a script along these lines that you edit to your needs/details:

<?php

$fname = $_POST['fname'];
$lname = $_POST['lname'];
$email = $_POST['email'];



mysql_connect ("you mysql server goes here, very often its localhost, but check with providor", "mysql username", "mysql pass") or die ('error: ' .mysql_error());

mysql_select_db ("mysql databae name here");


$query="INSERT INTO tablename (firstname, lastname, email) VALUES ('$fname', '$lname', '$email')";

mysql_query($query) or die ('Error updating database: ' .mysql_error());

echo "Update Applied";

?>

During the section of

$query="INSERT INTO tablename (firstname, lastname, email) VALUES ('$fname', '$lname', '$email')";

the (firstname, lastname, email) needs to be your table heads that need the data

the ('$fname', '$lname', '$email') tells it to reffer to the respective $_POST and place what ever was in 'fname' on the incomming form to firstname on your table and so on

Providing you have your IDs on auto_increment I doubt youll have a problem with that

I find thats the easiest way for me, but not for all

I hope this can be of help

TomFromKWD · March 1, 2011

Ive also just had a thought regarding what you put

if you are CHANGING data you might need to use

$query="UPDATE tablename SET fieldname

but to do that you will need an option on your form to specify the ID to be edited.

Luke Warm · March 1, 2011

Thanks for the replies guys. I understand how to insert the edited data. I just don't know how to assign the session variables like $database or $table, etc. to pull the information from the database to the form in order to edit it.

I'm using an array to read the table data. Just not sure how to pull individual fields into a form for editing.

Muddy_Funster · March 1, 2011

use

echo '<input type="text" name="fieldName" value="'.$var['fieldName'].'" />';

to populate the form fields on page load.

Luke Warm · March 1, 2011

I am getting some results on the "edit" page, however, only the last table's data is appearing in the text boxes.

Here's the code for my edit page:

<?php
session_start();
$hostname = $_SESSION['hostname'];
$user = $_SESSION['user'];
$passwd = $_SESSION['passwd'];
$database = $_SESSION['database'];
$table = $_SESSION['table'];

$conn = mysql_connect($hostname, $user, $passwd, $database);

include("includes/header.html");
?>
<div id="container">
<div id="header">
<?php include("includes/title.php"); ?>
</div>
<div id="breadcrumbs">
<?php
function check_port($port) {
    $conn = @fsockopen("127.0.0.1", $port, $errno, $errstr, 0.2);
    if ($conn) {
        fclose($conn);
        return true;
    }
}

function server_report() {
    $report = array();
    $svcs = array('3306'=>'MySQL');
    foreach ($svcs as $port=>$service) {
        $report[$service] = check_port($port);
    }
    return $report;
}

$report = server_report();
?>
<div id="server"><p>Server is <?php echo $report['MySQL'] ? "running" : "offline"; ?></p></div>
<?php 
if(!$_SESSION['hostname']){
echo "<font color='red'>Not connected to server!</font>";
} else {
echo "<font color='green'>Server: ".$_SESSION['hostname']."</font>";
} 
if(!$_SESSION['database']){
echo "";
}else{
echo "<br><font color='green'>Database: ".$_SESSION['database']."</font>";
}
?>
</div>
<div id="nav">
<p><a href="connect_db.php"><img src="images/server_conn_ico.png" align="left" />Server Connection</a></p>
<p><a href="databases.php"><img src="images/databases_ico.png" align="left" />View Database</a></p>
<p><a href="create_db.php"><img src="images/server_folder_ico.png" align="left" />Create Database</a></p>
<p><a href="drop_db.php"><img src="images/drop_ico.png" align="left" />Drop Database</a></p>
<p><a href="create_table.php"><img src="images/status_ico.png" align="left" />Add Table</a></p>
<p><a href="add_fields.php"><img src="images/server_ico.png" align="left" />Add Fields</a></p>
<p><a href="delete_table.php"><img src="images/status_delete_ico.png" align="left" />Delete Table</a></p>
<p><a href="view_tables.php"><img src="images/tables_ico.png" align="left" />View Tables</a></p>
<p class="active"><a href="view_data.php"><img src="images/user_admin_ico.png" align="left" />View Table Data</a></p>
<p><a href="dbkiss_front.php"><img src="images/dbkiss_ico.png" align="left" />Database Browser</a></p>
<p><a href="connect_info.php"><img src="images/health_ico.png" align="left" />Server Info</a></p>
<p><a href="error_log.php"><img src="images/logs_ico.png" align="left" />Server Log</a></p>
<p><a href="backup.php"><img src="images/backup_ico.png" align="left" />Backup</a></p>
<p><a href="bug_report.php"><img src="images/bugs-icon.png" align="left" />Bug Reports</a></p>
<p><a href="logged_out.php" onclick="logOut();"><img src="images/server_logout_ico.png" align="left" />Logout</a></p>
</div>
<div id="controls"><a href="javascript:self.close()"><img src="images/controls.png" /></a></div>
<div id="main">
<div id="content">
<?php
mysql_select_db($database);
if(empty($database)) {
echo "<p>You must be connected to a database in order to view any table data.</p>";
}else{

		$sql = "SHOW TABLES FROM $database";
		$result = mysql_query($sql);
		$table = array();
while ($row = mysql_fetch_row($result)) 
		{
			$table[] = $row[0];
		}
		if (count($table) == 0)
		{
			echo "<p>The database '" . $database . "' contains no tables.</p>\n";
		}
		else
		{
		foreach($table AS $aTable)
			{
				echo "<p style='text-align:left;float:left;width:100%;margin-top:15px;'>Table: <font color='green'>$aTable</font>";

if (!mysql_connect($hostname, $user, $passwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$aTable}");
if (!$result) {
    die("Query to show fields from table failed");
}
}
$fields_num = mysql_num_fields($result);

echo "<form action='updateinfo.php' method='post'><table border='0' style='text-align:left;width:100%;'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td style='text-align:left;padding:3px;font-size:11px;color:green;'>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td style='text-align:left;padding:3px;font-size:12px;border:1px solid #888;background:#fff;clear:both;float:left;'><input type='text' value='{$cell}' /></td>";

    echo "</tr>\n";

}
mysql_free_result($result);

echo "</table>";
//++$i;
?>
<input type="submit" value="  Submit changes  " />   <input type="button" value="  Cancel " onclick="window.location='view_data.php'" />

</form>
<?php
}
}

include("includes/footer.php");
?>

The session variable are at the top of the page. I'm using a mysql_fetch_row array to gather the data. Is there a better way to do this?

flolam · March 1, 2011

it only shows the last table's data because after this:

foreach($table AS $aTable)

the value of $aTable remains set to the last item in the $table array. So you would have to put the code that shows the table into an foreach-loop as well:

foreach($table AS $aTable) {
$result = mysql_query("SELECT * FROM {$aTable}");
//table output code
}

Luke Warm · March 1, 2011

Now I'm getting the first two textboxes (still in the last table) :shrug:

Sorry, I probably messed up somewhere. Here's what I have now:

<?php
session_start();
$hostname = $_SESSION['hostname'];
$user = $_SESSION['user'];
$passwd = $_SESSION['passwd'];
$database = $_SESSION['database'];
$table = $_SESSION['table'];

$conn = mysql_connect($hostname, $user, $passwd, $database);

include("includes/header.html");
?>
<div id="container">
<div id="header">
<?php include("includes/title.php"); ?>
</div>
<div id="breadcrumbs">
<?php
function check_port($port) {
    $conn = @fsockopen("127.0.0.1", $port, $errno, $errstr, 0.2);
    if ($conn) {
        fclose($conn);
        return true;
    }
}

function server_report() {
    $report = array();
    $svcs = array('3306'=>'MySQL');
    foreach ($svcs as $port=>$service) {
        $report[$service] = check_port($port);
    }
    return $report;
}

$report = server_report();
?>
<div id="server"><p>Server is <?php echo $report['MySQL'] ? "running" : "offline"; ?></p></div>
<?php 
if(!$_SESSION['hostname']){
echo "<font color='red'>Not connected to server!</font>";
} else {
echo "<font color='green'>Server: ".$_SESSION['hostname']."</font>";
} 
if(!$_SESSION['database']){
echo "";
}else{
echo "<br><font color='green'>Database: ".$_SESSION['database']."</font>";
}
?>
</div>
<div id="nav">
<p><a href="connect_db.php"><img src="images/server_conn_ico.png" align="left" />Server Connection</a></p>
<p><a href="databases.php"><img src="images/databases_ico.png" align="left" />View Database</a></p>
<p><a href="create_db.php"><img src="images/server_folder_ico.png" align="left" />Create Database</a></p>
<p><a href="drop_db.php"><img src="images/drop_ico.png" align="left" />Drop Database</a></p>
<p><a href="create_table.php"><img src="images/status_ico.png" align="left" />Add Table</a></p>
<p><a href="add_fields.php"><img src="images/server_ico.png" align="left" />Add Fields</a></p>
<p><a href="delete_table.php"><img src="images/status_delete_ico.png" align="left" />Delete Table</a></p>
<p><a href="view_tables.php"><img src="images/tables_ico.png" align="left" />View Tables</a></p>
<p class="active"><a href="view_data.php"><img src="images/user_admin_ico.png" align="left" />View Table Data</a></p>
<p><a href="dbkiss_front.php"><img src="images/dbkiss_ico.png" align="left" />Database Browser</a></p>
<p><a href="connect_info.php"><img src="images/health_ico.png" align="left" />Server Info</a></p>
<p><a href="error_log.php"><img src="images/logs_ico.png" align="left" />Server Log</a></p>
<p><a href="backup.php"><img src="images/backup_ico.png" align="left" />Backup</a></p>
<p><a href="bug_report.php"><img src="images/bugs-icon.png" align="left" />Bug Reports</a></p>
<p><a href="logged_out.php" onclick="logOut();"><img src="images/server_logout_ico.png" align="left" />Logout</a></p>
</div>
<div id="controls"><a href="javascript:self.close()"><img src="images/controls.png" /></a></div>
<div id="main">
<div id="content">
<?php
mysql_select_db($database);
if(empty($database)) {
echo "<p>You must be connected to a database in order to view any table data.</p>";
}else{

		$sql = "SHOW TABLES FROM $database";
		$result = mysql_query($sql);
		$table = array();
while ($row = mysql_fetch_row($result)) 
		{
			$table[] = $row[0];
		}
		if (count($table) == 0)
		{
			echo "<p>The database '" . $database . "' contains no tables.</p>\n";
		}
		else
		{
		foreach($table AS $aTable)
			{
				echo "<p style='text-align:left;float:left;width:100%;margin-top:15px;'>Table: <font color='green'>$aTable</font>";

// sending query
$result = mysql_query("SELECT * FROM {$aTable}");
if (!$result) {
    die("Query to show fields from table failed");
}
}
$fields_num = mysql_num_fields($result);

echo "<form action='updateinfo.php' method='post'><table border='0' style='text-align:left;width:100%;'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td style='text-align:left;padding:3px;font-size:11px;color:green;'>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
foreach($table AS $aTable) {
$result = mysql_query("SELECT * FROM {$aTable}");
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    //foreach($row as $cell)
        echo "<td style='text-align:left;padding:3px;font-size:12px;border:1px solid #888;background:#fff;clear:both;float:left;'><input type='text' name='fieldName' value='".$var['fieldName']."' /></td>";

    echo "</tr>\n";

}
mysql_free_result($result);

echo "</table>";
//++$i;
?>
<input type="submit" value="  Submit changes  " />   <input type="button" value="  Cancel " onclick="window.location='view_data.php'" />

</form>
<?php
}
}

include("includes/footer.php");
?>

Sign In

Form to edit TABLE data

Recommended Posts

Luke Warm

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

TomFromKWD

Link to comment

Share on other sites

TomFromKWD

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

flolam

Link to comment

Share on other sites

Luke Warm

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information