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



newera
Novice

Oct 9, 2015, 4:59 AM


Views: 2103
Using LIKE erases data

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?



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

$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%"
SQL
$dbh->do($SQL) || die("Error5: " . $dbh->errstr . "\n");
}}



FishMonger
Veteran / Moderator

Oct 9, 2015, 8:47 AM


Views: 2054
Re: [newera] Using LIKE erases data

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:

Code
#!/usr/bin/perl 

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.

Code
$sth->execute; 
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)


Laurent_R
Veteran / Moderator

Oct 10, 2015, 3:27 AM


Views: 2005
Re: [FishMonger] Using LIKE erases data

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.