data updating problem, any1 know ?!

[Richi]

hi all
i have problem when i wanna update some data in the mysql, here is the code :

<? 

if ($action  == "renamesubcat") {

?>

<form method="POST" action="<?=$PHP_SELF;?>?action=resubcat">
<input type="hidden" name="recat" value="1">
<div align="center">
<center>
<table border="0" cellspacing="1" cellpadding="3" class="table_border">
<tr>
<td colspan="2" class="header_cell">
<p align="center">.:: Rename Sub Catagory <font color="#ff6600"><?=$sub_cat_n;?></font> ::.</p>
</td>
</tr>

<!-- ##################### -->

<tr>
<td align="right" class="cells">Name :</td>
<td class="cells"><input class="box" type="text" name="sub_cat_n1" size="30" value="<?=$sub_cat_n;?>"></td>
</tr>

<tr>
<td align="right" class="cells"> Sorting In Catagory :</td>
<td class="cells">
<?
/*
##################################
##### make Catagory List #########
##################################  */
$sql_cat="select * from catagory";
$result_cat=@mysql_query($sql_cat,$db);

echo "

  <select name=\"catagory1\">
  ";
          while($mycat=@mysql_fetch_array($result_cat))
    {
$catagory_id=$mycat["catagory_id"];
$catagory=$mycat["catagory"];


  echo"<option value=\"$catagory\">$catagory_id . $catagory</option>";
}
echo"</select>";?>


</td>
</tr



<tr>
<td colspan="2" class="header_cell">
<p align="center"><input type="submit" name="submit">
</td></tr>

</div>
</table>
<?}
 if($action == "resubcat" ){


if ($sub_cat_n1==""){
      echo "<center><font color=\"#ff0000\"> you must write the new sub catagory name!<br>";
      echo"<a href=\"javascript:history.back(-1);\">Back</a>";
  }else {


$sql = "update sub_cat set sub_cat_n='$sub_cat_n1', catagory='$catagory1' where sub_cat_n='$sub_cat_n'";
// here is the problem !
$result=mysql_query($sql,$db);


echo mysql_error();
   echo"<center>Rename sub catagory to<font color=\"#ff6600\"> $sub_cat_n1</font>, has been done$b</center>";
     }

}


    ?>

mysql :

Field Type Attributes Null Default Extra Action 
sub_cat_id  int(7)    No    auto_increment  Change Drop Primary Index Unique 
sub_cat_n  varchar(30)    Yes      Change Drop Primary Index Unique 
catagory  varchar(30)    Yes      Change Drop Primary Index Unique

after updating i got msg its ok, but no changes !!
any1 know whay ??


thx alot !

[lynlimz]

hmm..mysql_error(); reports nothing?
it should if it cna't update.

I had problems where database ain't updated and by printing out mysql_error...its resolved.

have you tried checking for the mysql_error(); when you print out your category menu?

also..just an optimised version..

echo '<select name="catagory1">';
$result = mysql_query(SELECT category_id, category FROM catagory,$db);
while($row=mysql_fetch_array($result)) {
    extract($row);
    echo'<option value="'.$catagory.'">'."$catagory_id . $catagory".'</option>';
}
echo '</select>'; 

[keko]

it's because you use a buggy version of mysql

upgrade your mysql

I don't remember the version numbers but the change is after the n'th dot.

Male sure you have "the" latest version installed

[Richi]

the problem is not with catagory, and mysql is good not buggy, coz the same code i use it to updata another table ( with another function name and table name) !

and i got now mysql_error msg !

when i remove the Where (where sub_cat_n='$sub_cat_n'" it`s work but will change the the fields !

i must do this sub catagory, else the script will ....

[lynlimz]

Originally posted by Richi
the problem is not with catagory, and mysql is good not buggy, coz the same code i use it to updata another table ( with another function name and table name) !

and i got now mysql_error msg !

when i remove the Where (where sub_cat_n='$sub_cat_n'" it`s work but will change the the fields !

i must do this sub catagory, else the script will ....

i don't get what you mean. could you explain it once more?
Your code looks alright though

[Richi]

when i write in this way, to update what i want don`t work

 $sql = "update sub_cat set sub_cat_n='$sub_cat_n1', catagory='$catagory1' where sub_cat_n='$sub_cat_n'";
        $result = mysql_query($sql,$db);

but if i write like this (withou where) :

 $sql = "update sub_cat set sub_cat_n='$sub_cat_n1', catagory='$catagory1' ";
        $result = mysql_query($sql,$db);

will work but will updata all the fields , not 1 what i need to update !
am using the last mysql ver.3.23 .

[lynlimz]

That is very weird indeed.

$sql = "UPDATE sub_cat SET sub_cat_n='$sub_cat_n1', catagory='$catagory1' WHERE sub_cat_n='$sub_cat_n'"; 

When it doens't work, does mysql_error() bring out anything?

Have you made sure $sub_cat_n retrieves the right variable?
Or does sub_cat_n exists in the table?

[Richi]

it`s exists, and its work coz am order by sub_cat_n !
here is the table !

Field Type Attributes Null Default Extra Action 
sub_cat_id  int(7)    No    auto_increment  Change Drop Primary Index Unique 
sub_cat_n  varchar(30)    No      Change Drop Primary Index Unique 
catagory  varchar(30)    No      Change Drop Primary Index Unique 


Keyname Unique Field Action 
PRIMARY Yes sub_cat_id Drop

and i got no mysql_error .

[lynlimz]

The only thing i can think of now is, the value of the variable a number?
Have you verified the actual content of $sub_cat_n ?

[Richi]

i didn`t undestand u, can you explain more for plz, this my first start with php and mysql !

[lynlimz]

I meant..
OPPS. sorry. I was looking at the wrong one.

BTW, why don't you reference a row in the table by looking at $sub_cat_id since each row has a unique number?

[Richi]

how can i make this, is this will help ?

[lynlimz]

Okay. Let me explain,

lets say with your current table structure. you have 1 primary field, which means that no other row in the entire table can have the same value for this field. Go it?

Okay. So this goes a long way when managing your table. Like updating your table. Your primary field is now the 'refernece' point for each row in your table. So if you want to update a specific row, your WHERE query would be like: WHERE id='5'

That will query the table for row 5. get what i mean?
If you don't use your primary field for referencing your row, you might encounter a problem like having the value of '5' for 3 rows in the table.

There are some circumstances when you will want to query a table where a certain account is registred on this date.

so the query would be like:

WHERE datetime='$datetime'
This would obviously return several rows..so thats where you use the while statement. shan't go into that area now.


So now do you get what I mean?
Before I furthur advise you on which mehtod you should use, what detials are you trying to gather form the table now?

[Richi]

varchars !
now am trying this ,
$sql = "update subcat set sub_cat='$sub_cat1',
catagory='$catagory1' where '%mavi%'";

no error`s no changes !!!

[Richi]

another one :

$sql = "update subcat set sub_cat='$sub_cat1', 
catagory='$catagory1' where sub_cat like '%mavi%'"; 

also nothing !

[lynlimz]

nono..syntax is wrong.

do something like WHERE fieldname='$variable'

if it still doens't work. try doing this.
do a test on one of the rows.

instead of specifying $sub_cat_n in your WHERE sub_cat_n='$sub_cat_n'
do it like

WHERE sub_cat_n='hello'

where hello would be the value of that row's sub_cat_n. IT MUST DEFINITELY UPDATE SOMETHING as you know 'hello' exists. get what i mean?from there, if the above test works, it means your #sub_cat_n isn't getting the right variables..hence mysql_query("UPDATE...") won't work.

[Richi]

when i put this :
WHERE sub_cat_n='hello'

the updata its ok, there change in the hello field !

and now ?

[lynlimz]

goosh! you mean there's a field thats has 'hello' as its value?
i meant to change that hello to something you know exists in the table.

[Richi]

sub_cat_id sub_cat catagory 
 1   cd   vv  Edit Delete 
 2   hello  vv  Edit Delete

yes !

[Richi]

but the sub_cat field is normaly :

Field Type Attributes Null Default Extra Action
sub_cat varchar(30) Yes

[lynlimz]

okay. so it updates correctly when its WHERE sub_cat_id='hello' right?
so what you would do now is do this
WHERE sub_cat_id='$variable'

make sure $variable has the value of 'hello'

it should update just fine too. does it?

[Richi]

the $variable must be the same field name , coz ill not all of the inseted data will be hello !

this will drive me crazy !

[lynlimz]

yep. then make it $sub_cat_n then!
but make sure its value parsed is 'hello'

btw..do you know what i'm directing you?
i'm basically ironning out various areas of your code to test for errors.

[lynlimz]

I'm almost certain

$sql = "update sub_cat set sub_cat_n='$sub_cat_n1', catagory='$catagory1' where sub_cat_n='$sub_cat_n'";

your $sub_cat_n is empty. thats why it doesn't update,m and doesn't produce an error

[Richi]

i see, but its runing ok if i give $variable wich i know, it`s gonna updta it !
but i give it $sub_cat_n, it`s do nothing !!!