Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Dec 2009
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Unable to insert data with a variable

    Hello guys. I am having trouble using an insert statement where I have to use variables that are in my script.

    Code:
    $insert = qq~insert into answers (UserID,answer) values($uid,$form{'"$answer"'})~;
    I know its the quotes that's creating the problem, but I don't know how to solve this.

    Help is appreciated.

    Thanks

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by darkhider View Post
    Hello guys. I am having trouble using an insert statement where I have to use variables that are in my script.

    Code:
    $insert = qq~insert into answers (UserID,answer) values($uid,$form{'"$answer"'})~;
    I know its the quotes that's creating the problem, but I don't know how to solve this.

    Help is appreciated.

    Thanks
    one of this:
    Code:
    $insert = qq~insert into answers (UserID,answer) values($uid,$form{'$answer'})~;
    or this:
    Code:
    $insert = qq~insert into answers (UserID,answer) values($uid,$form{"$answer"})~;
    best regards

  • #3
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    The quotes around $answer are not needed.

    The proper quoting of the insert statement would be:
    Code:
    $insert = qq~insert into answers (UserID,answer) values($uid, '$form{$answer}')~;
    However, it's better to use placeholders.
    http://search.cpan.org/~timb/DBI-1.6...nd_Bind_Values

    Code:
    my $insert = qq~insert into answers (UserID,answer) values(?, ?)~;
    my $sth = $dbh->prepare($insert);
    $sth->execute($uid, $form{$answer});

  • Users who have thanked FishMonger for this post:

    darkhider (12-13-2009)

  • #4
    New Coder
    Join Date
    Dec 2009
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by FishMonger View Post
    The quotes around $answer are not needed.

    The proper quoting of the insert statement would be:
    Code:
    $insert = qq~insert into answers (UserID,answer) values($uid, '$form{$answer}')~;
    I have tried using this as well, but even that didn't work.
    Code:
    my $insert = qq~insert into answers (UserID,answer) values(?, ?)~;
    my $sth = $dbh->prepare($insert);
    $sth->execute($uid, $form{$answer});
    I tried using placeholders but that did not work either. The error I am getting is that "DBD::mysql::st execute failed: Column 'UserID' cannot be null."

  • #5
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    That tells me that your $uid var doesn't hold the value you think.

  • #6
    New Coder
    Join Date
    Dec 2009
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by FishMonger View Post
    That tells me that your $uid var doesn't hold the value you think.
    I am getting uid from the cookie:

    Code:
    print "Set-Cookie: $uid=$row[0]\n";
    I have added a $ sign before uid, but that doesn't seem to help. Note that cookie part is in a subroutine. Would that matter?

  • #7
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Quote Originally Posted by darkhider View Post
    I am getting uid from the cookie:

    Code:
    print "Set-Cookie: $uid=$row[0]\n";
    I have added a $ sign before uid, but that doesn't seem to help. Note that cookie part is in a subroutine. Would that matter?
    That neither sets nor retrieves the cookie.

    The cookie needs to be set/sent in the http header. That line is sent as part of the html content, not the http header.

  • #8
    New Coder
    Join Date
    Dec 2009
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by FishMonger View Post
    That neither sets nor retrieves the cookie.

    The cookie needs to be set/sent in the http header. That line is sent as part of the html content, not the http header.
    Okay, I think I know what I am doing wrong. I have declared answer=1 in the insert subroutine but I have not declared uid. But I can't make up a number for the uid. I have to get that number from the cookie which I am not sure since that would be coming from another subroutine (cookie subroutine).

  • #9
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    what about retrieving your cookie value ($uid) from the cookie sub routine?

    Code:
    my $uid = sub_cookie();
    and then the sub cookie could be like this

    Code:
    sub cookie{
    
    set your cookie uid here and finsih the sub with
    
    return($uid);
    
    }
    hth

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #10
    New Coder
    Join Date
    Dec 2009
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bazz View Post
    what about retrieving your cookie value ($uid) from the cookie sub routine?

    Code:
    my $uid = sub_cookie();
    and then the sub cookie could be like this

    Code:
    sub cookie{
    
    set your cookie uid here and finsih the sub with
    
    return($uid);
    
    }
    hth

    bazz

    I have tried your method but it did not work.

    My subroutines looks like this:

    Code:
    $uid=sendlogincookie();
    
    sub sendlogincookie
            {
            print "Set-Cookie: uid=$row[0]\n";
            return($uid);
            }
    
    
    
    sub insertsurveydata
            {
            $select = qq~select id,question,answer1,answer2,answer3,answer4,image from questions~;
            $dbh=DBI->connect($connectionInfo,$user,$passwd);
            $sth=$dbh->prepare($select);
            $sth ->execute();
    
            $answer=1;
            $uid2="$uid";
            while (@row2=$sth->fetchrow_array())
                    {
                    $insert = qq~insert into answers (UserID,answer) values(?, ?)~;
                    $dbh2=DBI->connect($connectionInfo,$user,$passwd);
                    $sth2=$dbh2->prepare($insert);
                    $sth2 ->execute($uid2, $form{$answer});
                    $answer++;
                    }
            }
    Last edited by darkhider; 12-13-2009 at 07:02 PM.

  • #11
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Why would you have thought that would work?

    Add the strict pragma and fix the problems that it points out.
    Code:
    use strict;
    If your instructor doesn't want you to use the pragma, then simply remove its use statement before turning in the assignment. However, that would be more evidence that you have a very poor instructor that teaches very bad coding practices.
    Last edited by FishMonger; 12-13-2009 at 08:59 PM.

  • #12
    New Coder
    Join Date
    Dec 2009
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by FishMonger View Post
    Why would you have thought that would work?

    Add the strict pragma and fix the problems that it points out.
    Code:
    use strict;
    If your instructor doesn't want you to use the pragma, then simply remove its use statement before turning in the assignment. However, that would be more evidence that you have a very poor instructor that teaches very bad coding practices.
    Okay, I have made it work without taking the variable from the cookie subroutine. I just did the variables inside the insert subroutine.

    But I have yet another problem. This html page is showing on the same page as displaysurvey which is supposed to actually show on a different page, and select statement isn't working.

    Here is the subroutine:

    Code:
    surveyresults
            {
            print qq~<html>
                    <head>
                    <Title>Survey Aswers</Title>
                    </head>
                    <body>
                    <table border=1>
                    <tr>
                    <th>User ID</th><th>Answers</th>
                    <tr>~;
    
                    $select = qq~select UserID,answer from answers~;
                    $dbh=DBI->connect($connectionInfo,$user,$passwd);
                    $sth=$dbh->prepare($select);
                    $sth ->execute();
    
                    $answer=1;
                    while (@row3=$sth->fetchrow_array())
                            {
                            $select = qq~select UserID,answer from answers~;
                            $dbh3=DBI->connect($connectionInfo,$user,$passwd);
                            $sth3=$dbh->prepare($select);
                            $sth3 ->execute();
                            }
                    </form>
            #$dbh->disconnect();
            }

  • #13
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    If you have the content of 2 separate pages being joined together, then you have a flaw in the code logic which is executing a subroutine when it shouldn't.

    You didn't specify which select statement wasn't working. The surveyresults sub has 2 select statements, but you're only fetching one; that is probably why it's not working.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •