PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
zurih
Forum Newbie
Posts: 8 Joined: Sat Nov 08, 2008 9:20 am
Post
by zurih » Sat Nov 08, 2008 9:24 am
Hey I'm trying to use the code below to update a mysql table, but it doesn't seem to work for some reason.
Would appreciate any assistance.
Here is the code:
update_all.php
Code: Select all
<?php
include_once "db_conn.php";
?>
<?php
$sql="SELECT * FROM $tbl_name ORDER BY cat_id, menu_id";
$result=mysql_query($sql);
// Count table rows
$count=mysql_num_rows($result);
?>
<?php
include_once "html_start.php";
?>
<h1>Update All Records</h1>
<table width="700" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="update_all_ac.php">
<tr>
<td>
<table width="700" border="0" cellspacing="1" cellpadding="3">
<tr>
<td><strong>Cat ID</strong></td>
<td><strong>Menu ID</strong></td>
<td><strong>Cat Title</strong></td>
<td><strong>File Name</strong></td>
<td><strong>Class Name</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><input name="id" type="hidden" id="id" value="<? $id=$rows['id']; ?>">
<input name="cat_id" type="text" id="cat_id" value="<? echo $rows['cat_id']; ?>"></td>
<td><input name="menu_id" type="text" id="menu_id" value="<? echo $rows['menu_id']; ?>"></td>
<td><input name="cat_title" type="text" id="cat_title" value="<? echo $rows['cat_title']; ?>" style="direction:rtl"></td>
<td><input name="file_name" type="text" id="file_name" value="<? echo $rows['file_name']; ?>"></td>
<td><input name="class_name" type="text" id="class_name" value="<? echo $rows['class_name']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
include_once "html_end.php";
?>
update_all_ac.php
Code: Select all
<?php
include_once "db_conn.php";
?>
<?php
$id = $_POST['id'];
$cat_id = $_POST['cat_id'];
$menu_id = $_POST['menu_id'];
$cat_title = $_POST['cat_title'];
$file_name = $_POST['file_name'];
$class_name = $_POST['class_name'];
$sql1 = "UPDATE $tbl_name SET cat_id='$cat_id', menu_id='$menu_id', cat_title='$cat_title', file_name='$file_name', class_name='$class_name' WHERE id='$id'";
$result1 = mysql_query($sql1);
?>
<?php
include_once "html_start.php";
?>
<?php
if($result1){
echo "Saved!<br /><a href=\"update_all.php\">Click here to go back</a>";
}
else {
echo "ERROR!";
}
mysql_close();
?>
<?php
include_once "html_end.php";
?>
Ziq
Forum Contributor
Posts: 194 Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh
Post
by Ziq » Sat Nov 08, 2008 9:33 am
Try this:
Code: Select all
// Replace this
$result1 = mysql_query($sql1);
// to
$result1 = mysql_query($sql1) or die(mysql_error());
P.S. Always check data inserted by user.
zurih
Forum Newbie
Posts: 8 Joined: Sat Nov 08, 2008 9:20 am
Post
by zurih » Sat Nov 08, 2008 9:44 am
Ziq wrote: Try this:
Code: Select all
// Replace this
$result1 = mysql_query($sql1);
// to
$result1 = mysql_query($sql1) or die(mysql_error());
P.S. Always check data inserted by user.
Tried it, and still data not updated...
Thanks
Ziq
Forum Contributor
Posts: 194 Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh
Post
by Ziq » Sat Nov 08, 2008 9:56 am
Ok, Try to check a SQL query;
Code: Select all
$sql1 = "UPDATE $tbl_name SET cat_id='$cat_id', menu_id='$menu_id', cat_title='$cat_title', file_name='$file_name', class_name='$class_name' WHERE id='$id'";
echo $sql1;
What you meant by checking data inserted by user?
Read about a
SQL Injection . Your project isn't safe.
zurih
Forum Newbie
Posts: 8 Joined: Sat Nov 08, 2008 9:20 am
Post
by zurih » Sat Nov 08, 2008 10:06 am
This is what I got:
UPDATE menu SET cat_id='cat_05', menu_id='menu_07', cat_title='test_title', file_name='test.html', class_name='' WHERE id=''
ID seems not to there...
Syntac
Forum Contributor
Posts: 327 Joined: Sun Sep 14, 2008 7:59 pm
Post
by Syntac » Sat Nov 08, 2008 10:07 am
Is there a variable called $id?
zurih
Forum Newbie
Posts: 8 Joined: Sat Nov 08, 2008 9:20 am
Post
by zurih » Sat Nov 08, 2008 10:10 am
Syntac wrote: Is there a variable called $id?
Yes.
Ziq
Forum Contributor
Posts: 194 Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh
Post
by Ziq » Sat Nov 08, 2008 10:15 am
This is your problem
Code: Select all
<td><input name="id" type="hidden" id="id" value="<? $id=$rows['id']; ?>">
Replace to
Code: Select all
<td><input name="id" type="hidden" id="id" value="<?php echo $rows['id']; ?>">
JohnBoy2
Forum Newbie
Posts: 19 Joined: Fri Oct 17, 2008 9:31 am
Post
by JohnBoy2 » Sat Nov 08, 2008 10:19 am
Bingo. My next suggestion was going to be:
Try to echo this $id variable out. Does it have a value? If so, is that value an actual id in the database table you are trying to update?
But it looks like Ziq nailed it.
John
zurih
Forum Newbie
Posts: 8 Joined: Sat Nov 08, 2008 9:20 am
Post
by zurih » Sat Nov 08, 2008 10:29 am
Ok I changed it.
Now the echo $sql1 returns the ID, but the problem seems to be other....
It seems to just query only the last column from the form.
The form itself has about 30 editable records from the database.
If I change something is the last records, the data is updated. But if I change something else, it isn't.
Why is that?
Ziq
Forum Contributor
Posts: 194 Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh
Post
by Ziq » Sat Nov 08, 2008 11:01 am
I think you not understand what you are doing. If you try to dump your $_POST variable you see that this one contain only a last form values. So you should solve this problem. One of the way is use other HTML form. For example you can replace
Code: Select all
<td><input name="id" type="hidden" id="id" value="<? $id=$rows['id']; ?>">
<input name="cat_id" type="text" id="cat_id" value="<? echo $rows['cat_id']; ?>"></td>
<td><input name="menu_id" type="text" id="menu_id" value="<? echo $rows['menu_id']; ?>"></td>
<td><input name="cat_title" type="text" id="cat_title" value="<? echo $rows['cat_title']; ?>" style="direction:rtl"></td>
<td><input name="file_name" type="text" id="file_name" value="<? echo $rows['file_name']; ?>"></td>
<td><input name="class_name" type="text" id="class_name" value="<? echo $rows['class_name']; ?>"></td>
to
Code: Select all
<td><input name="id[b][][/b]" type="hidden" id="id" value="<? $id=$rows['id']; ?>">
<input name="cat_id[b][][/b]" type="text" id="cat_id" value="<? echo $rows['cat_id']; ?>"></td>
<td><input name="menu_id[b][][/b]" type="text" id="menu_id" value="<? echo $rows['menu_id']; ?>"></td>
<td><input name="cat_title[b][][/b] type="text" id="cat_title" value="<? echo $rows['cat_title']; ?>" style="direction:rtl"></td>
<td><input name="file_name[b][][/b]" type="text" id="file_name" value="<? echo $rows['file_name']; ?>"></td>
<td><input name="class_name[b][][/b]" type="text" id="class_name" value="<? echo $rows['class_name']; ?>"></td>
Then print your $_POST variable and try to understand what you should do next.
zurih
Forum Newbie
Posts: 8 Joined: Sat Nov 08, 2008 9:20 am
Post
by zurih » Sat Nov 08, 2008 1:04 pm
It works!
Thank you very much.
But now as I come to think about it, I rather to update each row at a time.
How it's possible for example to add a button near each table row, that when you click it, it updated only that specific row the button is.
Much appreciated...
Ziq
Forum Contributor
Posts: 194 Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh
Post
by Ziq » Sat Nov 08, 2008 1:16 pm
How it's possible for example to add a button near each table row, that when you click it, it updated only that specific row the button is.
What is your problem? Create new <form> for each block of fields. And then send a data to update_all_ac.php.
zurih
Forum Newbie
Posts: 8 Joined: Sat Nov 08, 2008 9:20 am
Post
by zurih » Sat Nov 08, 2008 3:49 pm
Ziq wrote: How it's possible for example to add a button near each table row, that when you click it, it updated only that specific row the button is.
What is your problem? Create new <form> for each block of fields. And then send a data to update_all_ac.php.
You just gave me the idea
thanks it works great