Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

mysql update table how to

by VC (Novice)
on Mar 17, 2019 at 01:50 UTC ( #1231347=perlquestion: print w/replies, xml ) Need Help??

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

I want to update all Disabled in mysql table, but I am only being Abel to update just one? HELP


$username = DB::trim (param ("Username")); $password = DB::trim (param ("Password")); $dbh = DB::connect(); $sth = $dbh->prepare ("SELECT * FROM EmployerJobs WHERE Username = + ? AND Password = ?"); $sth->execute($username, $password); while (my $row = $sth->fetchrow_hashref ()) { $ID = $row->{ID}; if ($userid eq $row->{USERID}) { $DISABLE = $row->{Disabled}; if (param("Disabled") eq "Yes") { $DISABLE = "Yes"; param (-name => "Disabled", -value => $DISABLE); param (-name => "ID", -value => $ID); $tbl_info = DB::TableInfo->get ($dbh, "EmployerJobs"); $tbl_info->update_record ($dbh, "ID"); }elsif (param("Disabled") eq "No") { $DISABLE = "No"; param (-name => "Disabled", -value => $DISABLE); param (-name => "ID", -value => $ID); $tbl_info = DB::TableInfo->get ($dbh, "EmployerJobs"); $tbl_info->update_record ($dbh, "ID"); }else { param (-name => "Disabled", -value => "No"); param (-name => "ID", -value => $ID); $tbl_info = DB::TableInfo->get ($dbh, "EmployerJobs"); $tbl_info->update_record ($dbh, "ID"); } } } $sth->finish (); $dbh->disconnect ();

Replies are listed 'Best First'.
Re: mysql update table how to
by hippo (Bishop) on Mar 17, 2019 at 12:30 UTC
    if ($userid eq $row->{USERID}) {

    Clearly, this restricts which rows will be processed. If you don't want such a restriction then don't use it.

      Thanks

Re: mysql update table how to
by poj (Abbot) on Mar 17, 2019 at 13:57 UTC

    Only select the records you want to update

    #!/usr/bin/perl use strict; use warnings; use DBI; my $userid = DB::trim( param('Userid') ); # ?? my $username = DB::trim( param('Username') ); my $password = DB::trim( param('Password') ); my $DISABLED = ucfirst ( DB::trim( param('Disabled') ) || '' ); $DISABLED = 'No' unless $DISABLED eq 'Yes'; my $sql = 'SELECT ID,DISABLED FROM EmployerJobs WHERE Username = ? AND UserPassword = ? AND Userid = ? AND Disabled <> ?'; my $dbh = DB::connect(); my $tbl_info = DB::TableInfo->get ($dbh, "EmployerJobs"); my $sth = $dbh->prepare($sql); $sth->execute($username, $password, $userid, $DISABLED); param (-name => "Disabled", -value => $DISABLED); while ( my $row = $sth->fetchrow_hashref() ){ param (-name => "ID", -value => $row->{ID}); $tbl_info->update_record ($dbh, "ID"); printf "Updated ID '%s' from '%s' to '%s'\n",$row->{ID},$row->{DISAB +LED},$DISABLED; };
    poj
Re: mysql update table how to
by VC (Novice) on Mar 19, 2019 at 00:54 UTC

    this the error i am getting: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<> 'Yes'' at line 6 at admin.pl line 2316.

    this is the code i am using

    use strict; use warnings; use DBI; my $userid = DB::trim( param('Userid') ); # ?? my $username = DB::trim( param('Username') ); my $password = DB::trim( param('Password') ); my $DISABLED = ucfirst ( DB::trim( param('Disabled') ) || '' ); $DISABLED = 'No' unless $DISABLED eq 'Yes'; my $sql = 'SELECT ID,Disabled FROM EmployerJobs WHERE Username = ? AND Password = ? AND USERID = ? AND Disabled = <> ?'; my $dbh = DB::connect(); my $tbl_info = DB::TableInfo->get ($dbh, "EmployerJobs"); my $sth = $dbh->prepare($sql); $sth->execute($username, $password, $userid, $DISABLED); while ( my $row = $sth->fetchrow_hashref() ){ param (-name => "Disabled", -value => $DISABLED); param (-name => "ID", -value => $row->{ID}); $tbl_info->update_record ($dbh, "ID"); #print "Content-Type: text/html\n\n"; #printf "Updated ID '%s' from '%s' to '%s'\n",$row->{ID},$row->{DISA +BLED},$DISABLED; };

    Any help would be greatly appreciated, Thanks.

      near '<> '

      what is near <>? Right before it is an equals sign. So you are asing for "Disabled equals-to not-equals-a-value". so which is it, equals or not-equals that you want?

        That is the code poj gave me so I don't know?

        what you see in the code

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1231347]
Approved by stevieb
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (5)
As of 2022-05-24 06:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (82 votes). Check out past polls.

    Notices?