PHP + MySQL Creating a form to change data in MySQL tables

Question

I want to build a form to change the data in MySQL table. Firstly, I list all the data in the adminindex.php page. Then, I create a button to open the selected data in a form. I've done assigning the form fields to the main (pk) MySQL table. My problem started there when I need to fetch the foreign table data as the table contains many foreign data. As you guys know, a class may have many students, I have created the fields for class data, now the problem is in students data. Do I have to create many fields to fetch the data from MySQL foreign tables? If yes, could you guys guid me the code steps ? Thank you very much. Really appreciate your help :D

These are my steps:

Firstly I echo the rows, then I codes the form actions. Then, in adminpost.php, I create variables, link the fields and use UPDATE MYSQL to update the data in tables. I've succeeded in updating the primary table data but I'm stuck in foreign key data. Thanks :D

Can you add your actual code to the question?

FoolishSeth
– FoolishSeth

2012-11-30 08:40:07 +00:00
Commented Nov 30, 2012 at 8:40 — FoolishSeth
– FoolishSeth, Commented Nov 30, 2012 at 8:40

Bahadır İsmail Aydın · Accepted Answer · 2012-11-30 09:43:29Z

3

Have 2 pages. Display data in a form in first one and have update in the second. Here is a code for doing it one by one, you can build on it for multiple rows at a time if you want to.

edit.php

<?php
mysql_connect('ip', 'username', 'password') or die(mysql_error());
mysql_select_db("db_name") or die(mysql_error());

$query = mysql_query("SELECT * FROM table1 where order by question_id limit 1") or             die(mysql_error());

if(mysql_num_rows($query)>=1){
while($row = mysql_fetch_array($query)) {
    $id = $row['id'];
    $value1= $row['value1'];
    $value2= $row['value2'];
}
?>
<form action="update.php" method="post">
<input type="hidden" name="ID" value="<?php echo $id;?>">

Value1: <input type="text" name="value1" value="<?php echo $value1;?>">
<br>
Value2: <input type="text" name="value2" value="<?php echo $value2?>">
<input type="Submit" value="Change">
</form>
<?php
}else{
    echo 'No entry found. <a href="javascript:history.back()">Go back</a>';
}
?>

update.php

 <?php
 mysql_connect('ip', 'username', 'password') or die(mysql_error());
 mysql_select_db("db_name") or die(mysql_error());

 $id = mysql_real_escape_string($_POST["ID"]);
 $value1 = mysql_real_escape_string($_POST["value1"]);
 $value2 = mysql_real_escape_string($_POST["value2"]);

 $query="UPDATE table1 SET value1 = '.$value1.', value2 = '.$value2.' WHERE id='$id'";


 mysql_query($query)or die(mysql_error());
 if(mysql_affected_rows()>=1){
echo "<p>($id) Record Updated<p>";
 }else{
echo "<p>($id) Not Updated<p>";
 }
 ?>
 <a href="edit.php">Next</a>

edited Nov 30, 2012 at 9:43

answered Nov 30, 2012 at 8:54

Bahadır İsmail Aydın

3621 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1822825 Over a year ago

Thank you very much :D. Will it updates the 'many data in foreign table?. Does 'value1' and 'value2' represents the many foreign data from the other tables? Thank you :D

user1822825 Over a year ago

In another word, will the multiple related records in other foreign tables will be updated using "UPDATE table1 SET value1 = '$value1', value2 = '$value2' WHERE id='$id'" ?

Bahadır İsmail Aydın Over a year ago

No you need to update all tables with sepeate queries. i.e. have another update and run both.

user1822825 Over a year ago

If I got many menus in a restaurant, how to echo the row? $qry=mysql_query("SELECT * FROM client WHERE resID=$resID"); $qry1=mysql_query("SELECT * FROM menu WHERE resID=$resID"); then, $row=mysql_fetch_array($qry); $row1=mysql_fetch_array($qry1); then, echo $row['videourl']; echo $row1['services1']; echo $row1['services2']; .Is it right?

user1822825 Over a year ago

I've tried to echo the (fk) table rows but still can't do it. Really need some guidance.

Dayn Goodbrand · Accepted Answer · 2012-11-30 08:52:18Z

0

Might help to put your actual code up, but as i understand you are just wanting to edit the data that is already in your table? If thats the case :

//Connect to SQL DB
$dbcnx = @mysql_connect("localhost", "root", "password");

//Select DB
mysql_select_db("database_name", $dbcnx);

//Query DB
$sql = update table_name set column_name = "$variable" where column = "your_criteria";

@mysql_query($sql)

That will connect you to your SQL DB and update the records for you, hope thats what you needed

edited Nov 30, 2012 at 8:52

answered Nov 30, 2012 at 8:46

Dayn Goodbrand

6117 silver badges16 bronze badges

6 Comments

user1822825 Over a year ago

The thing is that I got many related records in the foreign tables. In another word, many rows with same primary id in another table. Could 1 variable sets all the records in the foreign tables? Thank you very much :D

Dayn Goodbrand Over a year ago

you can use an inner join query to do that I think :

update table1.column_name, table2.column_name from table1 inner join table2 on table1.column_name = table2.column_name where table1.column_name = "your_criteria";

user1822825 Over a year ago

Do I need to do the first code like this : $qry=mysql_query("SELECT * FROM client WHERE resID=$resID"); $qry1=mysql_query("SELECT * FROM services WHERE resID=$resID");

Dayn Goodbrand Over a year ago

you are creating a variable of an sql query here, in my first example i create the query as a string, then just mysql_query the string, so remove the "$qry=". Also the inner join code i gave you would replace the SELECT etc, so for example :

@mysql_query("SELECT client.resID, services.resID FROM client INNER JOIN services ON client.resID = services.resID WHERE client.resID = $resID")

This is assuming client resID is your primary key, and services resID is your secondary key. May be syntax errors with the "" in the query

Dayn Goodbrand Over a year ago

So i read the comment below, the $qry = mysql_query is being used in the foreach loop, so thats the correct way for that :)

|

Collectives™ on Stack Overflow

PHP + MySQL Creating a form to change data in MySQL tables

2 Answers 2

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related