...

View Full Version : Unable to insert data with a variable



darkhider
12-13-2009, 08:55 AM
Hello guys. I am having trouble using an insert statement where I have to use variables that are in my script.


$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

oesxyl
12-13-2009, 12:27 PM
Hello guys. I am having trouble using an insert statement where I have to use variables that are in my script.


$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:

$insert = qq~insert into answers (UserID,answer) values($uid,$form{'$answer'})~;
or this:

$insert = qq~insert into answers (UserID,answer) values($uid,$form{"$answer"})~;

best regards

FishMonger
12-13-2009, 02:58 PM
The quotes around $answer are not needed.

The proper quoting of the insert statement would be:

$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.609/DBI.pm#Placeholders_and_Bind_Values


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

darkhider
12-13-2009, 05:30 PM
The quotes around $answer are not needed.

The proper quoting of the insert statement would be:

$insert = qq~insert into answers (UserID,answer) values($uid, '$form{$answer}')~;


I have tried using this as well, but even that didn't work.



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."

FishMonger
12-13-2009, 05:34 PM
That tells me that your $uid var doesn't hold the value you think.

darkhider
12-13-2009, 05:39 PM
That tells me that your $uid var doesn't hold the value you think.

I am getting uid from the cookie:


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?

FishMonger
12-13-2009, 05:47 PM
I am getting uid from the cookie:


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.

darkhider
12-13-2009, 05:53 PM
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).

bazz
12-13-2009, 06:39 PM
what about retrieving your cookie value ($uid) from the cookie sub routine?



my $uid = sub_cookie();


and then the sub cookie could be like this



sub cookie{

set your cookie uid here and finsih the sub with

return($uid);

}


hth

bazz

darkhider
12-13-2009, 06:59 PM
what about retrieving your cookie value ($uid) from the cookie sub routine?



my $uid = sub_cookie();


and then the sub cookie could be like this



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:



$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++;
}
}

FishMonger
12-13-2009, 08:57 PM
Why would you have thought that would work?

Add the strict pragma and fix the problems that it points out.

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.

darkhider
12-13-2009, 10:19 PM
Why would you have thought that would work?

Add the strict pragma and fix the problems that it points out.

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:


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();
}

FishMonger
12-14-2009, 04:39 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum