PDA

View Full Version : Managing a MySQL DB



Anirban1987
11 Mar 2009, 09:04 AM
I have a php page which outputs the data in the MySQL DB table. Also it has a HTML radio button form in the cells of a particular field. That form should modify another field in the same row/id. But now when I submit the form it inserts the entry in a new row. Now my question is how can that HTML form identifies the sql row's id and force the processor file (i.e. the file which is updating the DB by processing the form) to modify the particular cell in the row in which the form resides. I am posting the page's and processor file code here. Plus a screenshot of how the table looks like.

image link : http://img15.imageshack.us/img15/6589/screenshot002.gif

Page code :

<?php
session_start(); // If
if (!isset($_SESSION['web_user'])) {
// User is not logged in, so send user away.
header("Location:../index.php");
die();
}
?>
<!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>Content Management Panel</title>
<style type="text/css">
<!--
p {
text-align: center;
}
body {
text-align: center;
}
-->
</style>
</head>

<body>
<h2><u>TechDarpan Content Management Panel</u></h2>
<p>
<?php
function SQLResultTable($Query)
{
$link = mysql_connect(localhost, ******, ******) or die('Could not connect: ' . mysql_error()); //build MySQL Link
mysql_select_db(anirban_tdcms) or die('Could not select database'); //select database
$Table = ""; //initialize table variable
$Table.= "<table border='1' bordercolor=#000000 style=\"color: #000000;\">"; //Open HTML Table
$Result = mysql_query($Query); //Execute the query
if(mysql_error())
{
$Table.= "<tr><td>MySQL ERROR: " . mysql_error() . "</td></tr>";
}
else
{
//Header Row with Field Names
$NumFields = mysql_num_fields($Result);
$Table.= "<tr style=\"\">";
for ($i=0; $i < $NumFields; $i++)
{
$Table.= "<th>" . mysql_field_name($Result, $i) . "</th>";
}
$Table.= "</tr>";
//Loop thru results
$RowCt = 0; //Row Counter
while($Row = mysql_fetch_assoc($Result))
{
//Loop thru each field
foreach($Row as $field => $value)
{
if($field!='Action Taken')
{
$Table.= "<td>$value</td>";
}
elseif($field=='Action Taken')
{
$Table.='<td>
<form method=post enctype=multipart/form-data action=Action/processor.php onSubmit="return validatePage1();">
<li class="mainForm" id="fieldBox_1" style="list-style-type:none"><span>
<label class=formFieldOption for="Action_option_1"><br>
<input class=mainForm type=radio name=Action id=Action_option_3 value="Accepted" />
Accepted<br>
</label>
<input class=mainForm type=radio name=Action id=Action_option_2 value="Rejected" />
<label class=formFieldOption for="Action_option_2">Rejected</label>
</span><span>
<label class=formFieldOption for="Action_option_3">
<br>
<input class=mainForm type=radio name=Action id=Action_option_1 value="Accepted with Modification" />
Accepted with Modification </label>
</span></li>
<li class="mainForm" style="list-style-type:none"></li>
<li class="mainForm" style="list-style-type:none">
<!-- end of this page -->

<!-- next page buttons -->
<input id="saveForm" class="mainForm" type="submit" value="Submit" />
</li>
</form>
</td>';
}
}
$Table.= "</tr>";
}
$Table.= "<tr style=\"background-color: #FFFFFF; color: #000000;\"><td colspan='$NumFields'>Query Returned " . mysql_num_rows($Result) . " records</td></tr>"; }
$Table.= "</table>";
return $Table;
}
//Call the function like this:
echo SQLResultTable("SELECT * FROM reply");
?>
</p>
<p>&nbsp;</p>
<?php
include("../form.php");
?>
</body>
</html>

processor page code :



<?php

$where_form_is="http://".$_SERVER['SERVER_NAME'].strrev(strstr(strrev($_SERVER['PHP_SELF']),"/"));

include("config.inc.php");
$link = mysql_connect($db_host,$db_user,$db_pass);
if(!$link) die ('Could not connect to database: '.mysql_error());
mysql_select_db($db_name,$link);
$query = "INSERT into `".$db_table."` (Action) VALUES ('" . $_POST['Action'] . "')";
mysql_query($query);
mysql_close($link);
?>

Alan
11 Mar 2009, 02:23 PM
Now my question is how can that HTML form identifies the sql row's id and force the processor file (i.e. the file which is updating the DB by processing the form) to modify the particular cell in the row in which the form resides.

The syntax of an SQL insert query is like this: (using your table from the screenshot)


Insert into [table_name] Values('','$feedback','$email','$time','','$act_taken');

---

Now the way you are processing your form is all wrong. The HTML form contains inputs. Each input has a name as an identifier. You are using the post method. So...

$_POST[] is a standard PHP array. It stores values from a form using the post method. All data stored in this array can be accessed using an associative index.

E.g.


<form method="post" action="$_SERVER['PHP_SELF']>
<input type="text" name="username" value="" size="30"/>
</form>

<?php
if(isset($_POST['username']) && $_POST['username'] != '')
{
$username = $_POST['username'];
// ...
}
?>

It can be referenced pretty easily. One of your mistakes seems to be that the Action name is on all your inputs. This would simply overwrite the previous one. Which leaves you with one input trying to be inserted into the database and you get your errors. :-)

So your solution is to assign unique names to your form inputs. Such as name="feedback" and name="email". Then you can work with each variable and process them as you see fit.

On a final note, the query from before (see below). The first value to be entered is the id, you can input it manually or you can leave it blank like I have and the database will fill it in with the next logical value (assuming auto increment has been setup).


... Values('','$feedback ...