Page 1 of 1

UPDATE doesn't work...

Posted: Sat Nov 08, 2008 9:24 am
by zurih
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";
?>
 

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 9:33 am
by Ziq
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.

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 9:44 am
by zurih
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

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 9:56 am
by Ziq
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.

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 10:06 am
by zurih
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...

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 10:07 am
by Syntac
Is there a variable called $id?

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 10:10 am
by zurih
Syntac wrote:Is there a variable called $id?
Yes.

Code: Select all

 
$id = $_POST['id'];
 

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 10:15 am
by Ziq
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']; ?>">

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 10:19 am
by JohnBoy2
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

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 10:29 am
by zurih
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?

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 11:01 am
by Ziq
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.

Code: Select all

 
<?php print_r($_POST); ?>
 

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 1:04 pm
by zurih
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...

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 1:16 pm
by Ziq
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.

Re: UPDATE doesn't work...

Posted: Sat Nov 08, 2008 3:49 pm
by zurih
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 :)