CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
Search Posts SEARCH
Who's Online WHO'S
Log in LOG

Home: Perl Programming Help: Intermediate:
Using LIKE erases data



Oct 9, 2015, 4:59 AM

Post #1 of 3 (2876 views)
Using LIKE erases data Can't Post

When using this routine it erases all data in the "demo" column. Not sure why....
Tried different quotes around everything but no good.
When I use the print part, it does echo the correct variables to the screen.
Trying to update the demo column data to the correct data from another table.
Any suggestions?
Maybe this is MySQL question?

$SQL =<<SQL; 
SELECT * FROM opencart.product_attributetest

$sth = $dbh->prepare($SQL);
if(!$sth) { die("Error: " . $dbh->errstr . "\n"); }
if(!$sth->execute) { die("Error4: " . $sth->errstr . "\n"); }

if($sth->rows != 0)
$total_rows = $sth->rows;

for ($counter = 0; $counter < $total_rows; $counter++)
my($ref) = $sth->fetchrow_hashref;
%FORM = %$ref;

$fragment = substr $FORM{'text'}, 0, 16;
$text = $FORM{'text'};

#print "$text, $fragment\n";
$SQL =<<SQL;
update opencart.info5 SET demo = "$text" WHERE demo LIKE "%$fragment%"
$dbh->do($SQL) || die("Error5: " . $dbh->errstr . "\n");

Veteran / Moderator

Oct 9, 2015, 8:47 AM

Post #2 of 3 (2827 views)
Re: [newera] Using LIKE erases data [In reply to] Can't Post

If $text and $fragment hold the correct values as you claim, then there's no way that the code you posted would erase the data in the "demo" column. You probably have some other code in the script that is deleting the data. Can you post the entire script?

It's clear from what you've posted that you're not using the strict pragma and probably don't even have warnings enabled. That is a really big mistake. Every script you write should load those pragmas and every var will need to be declared via the my keyword.

Your script should begin like this:


use strict;
use warnings FATAL => "all";

Using a heredoc for a short 1 line string is silly.

If you enable RaiseError in the DBI connect statement, the module will handle the error checking on the rest of the statements which means you won't need to manually check each/every statement.

Your select statement is grabbing all fields, but you're only using/needing 1 field. Don't do that. Instead, you should only select the specific field(s) that you need.

Your method of fetching/looping over the result set is way to verbose. Use a more streamlined while loop.

while (my $ref = $sth->fetchrow_hashref) {

Do not use the $dbh->do($SQL) statement inside a loop; it's very inefficient. Instead do a prepare statement, which uses placeholders, prior to the loop and do an execute statement in the loop passing $text and $fragment as the args for the statement.

(This post was edited by FishMonger on Oct 9, 2015, 8:54 AM)

Veteran / Moderator

Oct 10, 2015, 3:27 AM

Post #3 of 3 (2778 views)
Re: [FishMonger] Using LIKE erases data [In reply to] Can't Post

In addition to all the comments by FishMonger, I would add that you should use proper and consistent indentation. Even though the compiler does not require it, correct indentation is neither an option, nor a nice thing to do, it is a MUST in almost any piece of code, well, at least in any Perl code having more than one line. Indent your code consistently, you'll save immense amounts of time.

I might hire a developer making a stupid mistake in a coding test during a job interview (anyone can make errors), but I would never hire someone giving me such badly formatted and badly indented code, because it is a strong sign of sloppiness (and perhaps even disrespect for coworkers), and a sloppy developer is just a bad developer. Please, don't take that last sentence as a personal offense, but as a friendly advice both stemming from my personal experience and reflecting good practices universally accepted for nearly 50 years.


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives