Clever Geek Handbook

Perl script or MySQL fix?

I am new to Perl and just got an assignment with perl scipt. The statement I'm currently focusing on is:

my $sth = $dbh->prepare('SELECT StringValue FROM CustomData WHERE (Record_ID = \'' . $ref->{'Record_ID'} . '\' && Field_ID = \'' . $metadata[11] . '\') LIMIT 1;');

The current instruction will pull each entry that matches the value Record_ID

. However, it needs to be changed to , only pull records where the Record_ID starts with the number 1,2,9 .

I think this looks more like a regex problem, is that correct? If this is the case, I should only modify

 Record_ID = \'' . $ref->{'Record_ID'}

part. It's right? Or should it be something that needs to be recorded in the instructions prepare

?

+3


source to share


4 answers


You should be using placeholders instead of trying to interpolate your variables and trying to make your own quote. You might even consider using placeholders for '1%'

etc., aside from considering them static in all your queries.



my $sth = $dbh->prepare( q#
     SELECT StringValue FROM CustomData 
     WHERE (Record_ID = ? && Field_ID = ?) 
     AND (Record_ID LIKE '1%' OR Record_ID LIKE '2%' OR Record_ID LIKE '9%')
     LIMIT 1
#);

$sth->execute($ref->{'Record_ID'}, $metadata[11]);
+2


source


Add AND

-part to the WHERE clause to filter out unwanted Record_IDs.



SELECT StringValue 
FROM CustomData \
WHERE (Record_ID = \'' . $ref->{'Record_ID'} . '\' && Field_ID = \'' . $metadata[11] . '\') 
AND (Record_ID  LIKE "1%" OR Record_ID  LIKE "2%" OR Record_ID  LIKE "9%")
LIMIT 1
+1


source


To avoid disguising this '

many times, you can useqq 

my $sth = $dbh->prepare( qq§SELECT StringValue FROM CustomData 
                            WHERE (Record_ID = '$ref->{Record_ID}' AND 
                                   Field_ID = '$metadata[11]') LIMIT 1§
                       ) ;
+1


source


You're right about changing the part Record_ID = \'' . $ref->{'Record_ID'} . '\'

replace it with Record_ID LIKE \'1%\' || Record_ID LIKE \'2%\' || Record_ID LIKE \'9%\'

Remove LIMIT 1

, this only matches one line starting at 1,2 or 9

This is how I think the solution might go

my $sth = $dbh->prepare('
    SELECT StringValue 
    FROM CustomData 
    WHERE ( Record_ID LIKE \'1%\' || Record_ID LIKE \'2%\' || Record_ID LIKE \'9%\' 
    && Field_ID = \''. $metadata . '\');');

$sth->execute;

while (my @arr=$sth->fetchrow_array())
   {
   print @arr;
   }
+1


source






More articles:


All Articles