andrew has asked for the wisdom of the Perl Monks concerning the following question: 

  if(param('page') eq "yes") {
     $dbh->do("UPDATE category SET name='$cat' AND view_cats_prod='yes
+' AND description='$des' WHERE id='$id'") or die $dbh->errstr;
  } else {
     $dbh->do("UPDATE category SET name='$cat' AND view_cats_prod='no'
+ AND description='$des' WHERE id='$id'") or die $dbh->errstr;
  }

[download]
I do this and it sets the name to "0" and it wont change the rest??

Replies are listed 'Best First'.
Re: Somethings not rightwith my MySQL do()
by gav^ (Curate) on Jul 20, 2002 at 01:45 UTC
    You've got the UPDATE syntax wrong, it should be like:

    UPDATE category SET name = ?, view_cats_prod = 'yes' WHERE id = ?

    You should really test your SQL statements before you put them into your perl code.

    Documentation can be found (for mysql) here.

[reply]
      Thank you I though you could use AND in SET I gues its commas thanks
[reply]
Re: Somethings not rightwith my MySQL do()
by tstock (Curate) on Jul 20, 2002 at 01:17 UTC
    Something else is probably wrong, this code seems "runable" to me. Check what is $cat before running the query. You should also consider using placeholders: 
    my $sql = 'UPDATE category SET name=? AND view_cats_prod=? AND descrip
+tion=? WHERE id=?'

my $y;
if (param('page') eq "yes") {
    $y = 'yes';
} else {
    $y = 'no';
}

$dbh->do($sql, undef, $cat, $y, $des, $id) ||
    die $dbh->errstr;

    [download]
    Tiago

    Update: Without more information all I can do is toss wild guesses. Did you commit if you need to? Is the field of the right type for the value you are inserting?
[reply]
[d/l]
      nope plaholder do same-thing
[reply]
        Add some basic debugging code to your script that prints out the value of $cat (and any other variable) before using it. I would suspect that this variable is not being properly set prior to being used in this query.

        And note that the placeholder suggestion is also one of safety: if you are using variables that are provided by the user directly in your SQL, keep in mind that users can put single-quotes and SQL code in this variable and execute arbitrary queries against your database through insecure code like this.

        use strict and taint-checking (perl -wT) when dealing with potentially unsafe data and Perl will (usually) get your attention when you try and do something that someone else could exploit.

[reply]
[d/l]
[select]
        A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom