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

I'm just getting to grips with perl and MySQL. The following snip of code *does* work, but when I print out $comp, $device, $model - it only prints out the record up until the first 'space'. ie 'THIS IS THE RECORD' prints as 'THIS' I believe the error may be in this part of the prog - can anyone help identify it - or is it likely to be in the MySQL syntax. Sorry to be vague - it's all new to me! 
sub_data{ 
$sql = "SELECT comp, device, model, uniq FROM p_company, p_devices, p_
+model WHERE p_model.uniq = '$model' AND p_model.comp_id = p_company.c
+omp_id AND p_model.device_id=p_devices.device_id";

my $sth = $dbh->prepare($sql);
$rv=$sth->execute;

while ($pointer = $sth->fetchrow_hashref){
    $comp  = $pointer->{'comp'};
    $device  = $pointer->{'device'}; 
    $model  = $pointer->{'model'};
    }
} # End sub
}

[download]
Many thanks, d.

2001-09-06 Edit by Corion : Fixed <BR> in code

Replies are listed 'Best First'.
(OT) Re: mysql no records after space
by trantor (Chaplain) on Sep 06, 2001 at 17:03 UTC

    Like others said, the problem could be in the printing phase itself, I'm not going to repeat here.

    Since you're a beginner, I would like to comment your code and give you some hopefully useful hints.

    You'll find much more information checking out Learning CGI & DBI well and with a Super Search.

    First of all, your use of quotes inside a SQL string can be dangerous and unportable. For example, if $model contains a single quote, your query is doomed to fail miserably...

    There are at least two easy solutions, both discussed elsewhere like I said. You can use the quote method, or placeholders. The former is a DBI method that you call passing a string, obtaining its quoted (thus safe to use in SQL) version in return. It's brilliant because it takes care of all the specific quoting methods of different databases, that's what DBI is about after all. The latter allows yuo to put ? in a SQL string where a needed value is unknown, and you can specify it later, for example at execute time. Quoting is added automagically when needed.

    Then I see you're using global variables for returning your values. That's really bad practice, it doesn't matter if it's just an example. It's dangerous, wrong and can be avoided very easily.

    For example, the last statement of your function could be

    return $hash{key1}, $hash{key2}, $hash{key3};

    and the calling code could simply use the needed local variables to store the returned values:

    ($field1, $field2, $field3) = function(arguments);

    or, equally:

    @array_of_fields = function(arguments);

    Finally, never forget to use strict; and the -w switch of the Perl interpreter. Not only it helps you designing your code more clearly, it also helps you avoiding common stupid mistakes like spelling errors or using undefined variables. Simply acquire the habit. Many people (me included) do it even for one-liners, throw away scripts.

    I hope this helps, even though it does not answer directly to yuor question.

    -- TMTOWTDI

[reply]
[d/l]
[select]
Re: mysql no records after space
by tune (Curate) on Sep 06, 2001 at 16:31 UTC
    Some extra (invisible) characters can make the remaining protion disappear. Consider the nullstring, and control chars.

    --
    tune

[reply]
Re: mysql no records after space
by ralphie (Friar) on Sep 06, 2001 at 16:40 UTC
    are you sure the entire string is in the relevant column? if you're not sure, i'd suggest using the mysql client to do a select to make sure. and if the full string isn't there, i'd check the quoting of the string prior to insertion.
[reply]
Re: mysql no records after space
by MZSanford (Curate) on Sep 06, 2001 at 15:38 UTC
    hmm, since it is only printing up until the first space, it may be a problem with the print portion. Can that be posted as well ? also, please use <code> tags around code sctions like that, makes it much easier. :-)
    can't sleep clowns will eat me
    -- MZSanford
[reply]
Re: mysql no records after space
by drivle (Initiate) on Sep 06, 2001 at 17:50 UTC
    Hello again - thanks for the input so far. Yes I realise my code is not good - loooong way to go....... Here is the print portion 
    sub print_output{
 print<<HTML;
 <HTML><HEAD><TITLE>Modify 1 Script</TITLE></HEAD>
  <BODY BGCOLOR="#F1EDD3">
   <FORM ACTION="modify2.cgi?$saved" METHOD="Post">
   <CENTER><FONT SIZE=6 FACE=ARIAL>Modify Record</FONT></CENTER>
   <HR WIDTH=80%>
    <P>
   <CENTER><TABLE BORDER=1 CELLSPACING=0>

    <TR>
     <TD BGCOLOR="#D7D3B9" COLSPAN=2><FONT FACE=ARIAL SIZE="4">
      <CENTER>ebme modify form</CENTER>
     </FONT></TD>
    </TR>
    <TR>
     <TD BGCOLOR="#D7D3B9">
      <FONT FACE=ARIAL SIZE="2"><B>Comp Name:</B></FONT>
     </TD>
     <TD><INPUT TYPE=TEXT SIZE=32 NAME="comp" VALUE=$comp></TD>
    </TR>
    <TR>
     <TD BGCOLOR="#D7D3B9">
      <FONT FACE=ARIAL SIZE="2"><B>Device type:</B></FONT>
     </TD>
     <TD><INPUT TYPE=TEXT SIZE=32 NAME="device" VALUE=$device></TD>
    </TR>
    <TR>
     <TD BGCOLOR="#D7D3B9">
      <FONT FACE=ARIAL SIZE="2"><B>Model Name:</B></FONT>
     </TD>
     <TD><INPUT TYPE=TEXT SIZE=32 NAME="model" VALUE="$model"></TD>
    </TR>
   
    <TR>
     <TD BGCOLOR="#D7D3B9" COLSPAN=2><CENTER><INPUT TYPE=SUBMIT VALUE=
+"Modify Record"></CENTER></TD>
    </TR>
   </TABLE></CENTER>
   <P>
   <HR WIDTH=80%>
   <P>

   <CENTER>
   <FONT SIZE=4 FACE=ARIAL>
    <A HREF="../../search.htm">Main Page</A>
   </FONT>
   </CENTER>
   </FORM>
   </BODY></HTML>
HTML
} # End

    [download]
[reply]
[d/l]

      Quoth your code:

           <TD><INPUT TYPE=TEXT SIZE=32 NAME="comp" VALUE=$comp></TD>

      The reason it is only displaying up to the first space is that you are not putting quotes around $comp (et al.) when you specify it as the VALUE. Thus, everything after the first space gets interpreted by the web browser as further properties of the <INPUT> tag (ie, it becomes: <TD><INPUT TYPE=TEXT SIZE=32 NAME="comp" VALUE=THIS IS THE RECORD></TD>, with IS, THE, and RECORD all being value-less properties to the tag, which are silently ignored). Put quotes around them and it should work.

      bbfu
      Seasons don't fear The Reaper.
      Nor do the wind, the sun, and the rain.
      We can be like they are.

[reply]
[d/l]
[select]
        Thank you, Thank you, Thank you - oh great enlightened BBFU I stared at that for so long..... And - STUPIDLY - I did have the quotes around $model !!! Sometimes you really can't see the wood for the trees... Thanks again - your a mate! d. Thanks to all for the input and TIMTOWTDI for the comments - as an amateur - I have started off with too many bad habits. I'm going to put them in check before they get out of hand!
[reply]

Back to Seekers of Perl Wisdom