Page 1 of 1
Help - Updating the Mysql database
Posted: Tue Aug 31, 2004 7:54 am
by Kingo
Hello,
I'm new to PHP and would appreciate any help. I have a form in html. If I submit that form I should insert the values in the database and when I pull the form back I should have an option to update only some fileds in the form. I donot know how to approach this. Any help is really appreciated.
Thanks
Posted: Tue Aug 31, 2004 7:57 am
by timvw
If you have your fields(columns) - value pairs in an array
And you have an array that contains the allowed fields
it's pretty easy to loop through the $allowed_fields array, and gather for each of the elements the data.....
And can you build your query/screen output
Posted: Tue Aug 31, 2004 8:31 am
by Kingo
I'm not exactly able to understand your idea. Any ways I'm pasting my code here.
Code: Select all
<html>
<head>
<title>TestForm</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form name="TestForm" method="post" action="TestForm.php">
<p><font size="2" face="Arial, Helvetica, sans-serif"><strong>1. Name :
<input type="text" name="Name">
</strong></font></p>
<p><strong><font size="2" face="Arial, Helvetica, sans-serif">2. SSN:
<input type="text" name="Ssn">
</font></strong></p>
<p><strong><font size="2" face="Arial, Helvetica, sans-serif">3.No. of Students
in Ele School :
<input type="text" name="Ele">
</font></strong></p>
<p><strong><font size="2" face="Arial, Helvetica, sans-serif">4. No. of Students
in Midd School:
<input type="text" name="Mid">
</font></strong></p>
<p><strong><font size="2" face="Arial, Helvetica, sans-serif">5. No. of Students
in High School:
<input type="text" name="High">
</font></strong></p>
<p> <strong><font size="2" face="Arial, Helvetica, sans-serif">
<input type="submit" name="Submit" value="Submit">
</font></strong> </p>
</form>
</body>
</html>
------------------------------------------------------------------------------------------------------------------------------------------
TestForm.php file.
------------------------------------------------------------------------------------------------------------------------------------------
Code: Select all
<html>
<head>
<title>Tewst Form PHP</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?php
$Var1 = $_POST["Name"];
$Var2 = $_POST["Ssn"];
$Var3 = $_POST["Ele"];
$Var4 = $_POST["Mid"];
$Var5 = $_POST["High"];
require_once('Connections/mysql_conn.php');
$sqlquery = "INSERT INTO test values ('".$Var1."','".$Var2."','".$Var3."','".$Var4."','".$Var5."')";
if($queryresult = mysql_query($sqlquery))
{
//or die("<b>Could not execute mysql query !</B>".mysql_error());
echo "<p align="center"><font size="2" face="Arial, Helvetica, sans-serif" color="blue"><B>Successfully Inserted into the Table & The Tabel Contents are Shown</B></font></p>";
echo "<table align="center" border =1 width= 400 >";
echo "<tr><td><font size="2" face="Arial, Helvetica, sans-serif"><B>Name:</B></font> </td> <td><font size="2" face="Arial, Helvetica, sans-serif">". $Var1 ."</td></font> </tr>";
echo "<tr><td><font size="2" face="Arial, Helvetica, sans-serif"><B>Ssn:</B></font> </td> <td><font size="2" face="Arial, Helvetica, sans-serif">". $Var2 ."</td></font> </tr>";
echo "<tr><td><font size="2" face="Arial, Helvetica, sans-serif"><B>Ele:</B> </font></td> <td><font size="2" face="Arial, Helvetica, sans-serif">". $Var3 ."</td></font> </tr>";
echo "<tr><td><font size="2" face="Arial, Helvetica, sans-serif"><B>Mid:</B> </font></td> <td><font size="2" face="Arial, Helvetica, sans-serif">". $Var4 ."</td></font> </tr>";
echo "<tr><td><font size="2" face="Arial, Helvetica, sans-serif"><B>HIgh:</B> </font></td> <td><font size="2" face="Arial, Helvetica, sans-serif">". $Var5 ."</td></font> </tr>";
echo "</table>";
}
else
{
echo "<br><br><p align="center"><font color="red"><b>ERROR: Cannot Insert Into The Table</font>
<br>Please Try Again ( Duplicate values are not Allowed )</b><br><a href="TestForm.html"><b>Back</b></a></p>";
}
?>
</body>
</html>
In this program I'm just aabl;e to display the contents form the table. I want to achieve this functionality. I should be able to have an option to change the numbers in the last 3 fields and should not be allowed to change the first 2 fields.
I once again apprecitae your help.
Posted: Tue Aug 31, 2004 8:38 am
by ol4pr0
Code: Select all
$sqlquery = "INSERT INTO test (Name,Ssn,Ele,Mid,High) values ('".$Var1."','".$Var2."','".$Var3."','".$Var4."','".$Var5."')";
$sqlresult = mysql_query($sqlquery) or die (mysql_error());
if($sqlresult)
{
Posted: Tue Aug 31, 2004 8:43 am
by Kingo
In this form there are 5 fields. I want to have the first 2 as static fields and the remaining 3 as dynamic fileds. I mean if i put new values in the last 3 fileds and click the submit button, i should update the databse with these new values and display the results.
Posted: Tue Aug 31, 2004 8:49 am
by ol4pr0
Code: Select all
#UPDATE SET
$query = mysql_query('UPDATE SET fields_value,field_value2,field_value3, WHERE Name="'.$_POST['Name'].'"') or die (mysql_error()); // additional to use AND Ssn='"$_POST['Ssn'].'" OR ....
Posted: Tue Aug 31, 2004 8:53 am
by Kingo
Where exactly should i use this statement
Posted: Tue Aug 31, 2004 8:54 am
by ol4pr0
Where ever u want to update those values. REplace it with the insert for example.. than it wont insert but.. it will update
Posted: Tue Aug 31, 2004 9:02 am
by Kingo
Thanx very much for your help. Actually I got confused and did not ask you the question in a correct way. Let me explain my problme in detail.
I have 10 Schools with ID's 001, 002, 003...010. I'm having a username and password for each school. When I login with the user name and password for each school I should be taken to a form. In that form, the data is populated from a database in the form of text fields etc. For some of the fileds I should be able to insert new data and when i click the submit button i should be able to display the form with the new data populated.
I'm new to this and i really really thank you very much for helping me
Posted: Tue Aug 31, 2004 9:26 am
by timvw
In both cases, you need to build $allowed (this is an array that contains the columns that are allowed to be changed)
Code: Select all
$allowed = array('Mid', 'High');
$res = mysql_query("build your query");
$row = mysql_fetch_assoc($res);
// build the screen
echo '<form method="post"><table>';
foreach($row as $key => $val)
{
echo '<tr><td>' . $key . '</td><td>';
if (array_key_exists(array_flip($allowed)))
{
echo '<input type="text" name="' . $key . '" value="' . $val . '" />';
}
else
{
echo $val;
}
echo '</td></tr>';
}
echo '<tr><td> </td><td><input type="submit" name="submit" /></td></tr>';
echo '</table></form>';
And to handle posts
Code: Select all
$allowed = array('Mid', 'High');
$query = "UPDATE table SET ";
foreach($allowed as $column)
{
if (isset($_POST[$column]))
{
$query .= $column . "='" . mysql_escape_string($_POST[$column] . "' , ";
}
}
$query = rtrim(", " , $query); // remove last ", " from the query
// add some WHERE stuff to your query
mysql_query($query);