UPDATE doesn't work...

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

Post Reply
zurih
Forum Newbie
Posts: 8
Joined: Sat Nov 08, 2008 9:20 am

UPDATE doesn't work...

Post 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";
?>
 
User avatar
Ziq
Forum Contributor
Posts: 194
Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh

Re: UPDATE doesn't work...

Post 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.
zurih
Forum Newbie
Posts: 8
Joined: Sat Nov 08, 2008 9:20 am

Re: UPDATE doesn't work...

Post 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
User avatar
Ziq
Forum Contributor
Posts: 194
Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh

Re: UPDATE doesn't work...

Post 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.
zurih
Forum Newbie
Posts: 8
Joined: Sat Nov 08, 2008 9:20 am

Re: UPDATE doesn't work...

Post 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...
User avatar
Syntac
Forum Contributor
Posts: 327
Joined: Sun Sep 14, 2008 7:59 pm

Re: UPDATE doesn't work...

Post by Syntac »

Is there a variable called $id?
zurih
Forum Newbie
Posts: 8
Joined: Sat Nov 08, 2008 9:20 am

Re: UPDATE doesn't work...

Post by zurih »

Syntac wrote:Is there a variable called $id?
Yes.

Code: Select all

 
$id = $_POST['id'];
 
User avatar
Ziq
Forum Contributor
Posts: 194
Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh

Re: UPDATE doesn't work...

Post 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']; ?>">
JohnBoy2
Forum Newbie
Posts: 19
Joined: Fri Oct 17, 2008 9:31 am

Re: UPDATE doesn't work...

Post 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
zurih
Forum Newbie
Posts: 8
Joined: Sat Nov 08, 2008 9:20 am

Re: UPDATE doesn't work...

Post 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?
User avatar
Ziq
Forum Contributor
Posts: 194
Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh

Re: UPDATE doesn't work...

Post 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); ?>
 
zurih
Forum Newbie
Posts: 8
Joined: Sat Nov 08, 2008 9:20 am

Re: UPDATE doesn't work...

Post 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...
User avatar
Ziq
Forum Contributor
Posts: 194
Joined: Mon Aug 25, 2008 12:43 am
Location: Russia, Voronezh

Re: UPDATE doesn't work...

Post 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.
zurih
Forum Newbie
Posts: 8
Joined: Sat Nov 08, 2008 9:20 am

Re: UPDATE doesn't work...

Post 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 :)
Post Reply