Gossamer Forum
Quote Reply
Annoying SQL Statement!
Please...can anyone see why this won't work?

Code:
my $sth = $table->select( { SetID => \$ID, Password => "$Password" } );

For some reason, the query always comes out like;

SELECT * FROM links2CatAdverts WHERE SetID = 3 AND Password = magnum

Notice the lack of quotes around the Password = value part. This seems to be causing an error. See below;

GT::SQL::error = Failed to execute query: 'SELECT * FROM links2CatAdverts WHERE SetID = 3 AND Password = magnum ' Reason: Unknown column 'magnum' in 'where clause'

I've tried several versions of the code, such as;

Code:
my $sth = $table->select( { SetID => \$ID, Password => \"$Password" } );

my $sth = $table->select( { SetID => \$ID, Password => \$Password } );

my $sth = $table->select( { SetID => \$ID, Password => \'$Password' } );

...and thats about it Unsure

Anyone got any ideas? This one really has me stumped.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
Why reference the id?...the following is fine:

Code:
my $sth = $table->select( { SetID => $ID, Password => $Password } );
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
Andy,
Code:
my $sth = $table->select( { SetID => $ID, Password => $Password } );
This is a case of RTFM, actually. GT::SQL::Table, select.

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] Annoying SQL Statement! In reply to
Thanks Paul and yogi for the replies. Yogi, that sarcasm wasn't really needed, as I did read the manual..thats why I'm so confused it wont work! I did exactly the same as you just said, but that still gave the following error;

GT::SQL::error = Failed to execute query: 'SELECT * FROM links2CatAdverts WHERE SetID = 3 AND Password = magnum ' Reason: Unknown column 'magnum' in 'where clause'

As you can see, still no joy! Frown

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
What does

print Dumper($Password)

say?

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] Annoying SQL Statement! In reply to
print $Password;

prints out;

magnum

...which is the correct value for it when it goes in (I'm sending '3' as the ID, and 'magnum' as the password).

I'm just stumped as to why it won't work. Numeric passwords work fine, but just not alpha ones Unimpressed

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
I *think* I know why this seems to happening although not 100%.

The fact that Dumper($Password) prints out magnum tells me $Password is already a scalar reference as I don't think Dumper() will print out anything other than references.

Try using:

Password => $$Password
Quote Reply
Re: [Paul] Annoying SQL Statement! In reply to
Dumper($Password) doesn't print out "magnum"...I was using;

print $Dumper (don't see why Dumper() needed to be used...as its only a variable being passed over via POST).

I'll give your idea a go...but otherwise I'm still stuck Frown

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
Well if thats the case then my code won't work. However yogi's/my first code examples should.
Quote Reply
Re: [Paul] Annoying SQL Statement! In reply to
Man...still no job :( It just doesn't like putting the quotes into the query (around the passwords value)...WHY OH WHY! Pirate

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Paul] Annoying SQL Statement! In reply to
Finally I got around it (last but not least).

Code:
my $table = $DB->table('CatAdverts');
my $sth = $table->select( {SetID => \$ID } );
my $got;

# set $got to true if we managed to find an entry matcinhg their details...
while (my $hit = $sth->fetchrow_hashref) {
if ($Password eq $hit->{Password}) {
$got = 1;
}
}

Adding/editing the parts in red seemed to have sorted it now. I'm still lost as to why the code above didn't work Unsure

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
I still don't get why you are using \$ID instead of $ID
Quote Reply
Re: [Paul] Annoying SQL Statement! In reply to
Using $ID on its own (without the \) just printed this into the query;

SetID =

...i.e. no value was given. Not sure why...but it did it Tongue

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
Sounds like your copy is a bit messed up. All these weirdnesses shouldn't happen.
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
How are you setting the value of $ID and $Password?

Try this:
Code:
if (ref $Password) {
print '$Password is a ' . ref $Password . ' reference. How come?';
}
else {
print '$Password is a normal scalar.';
}

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] Annoying SQL Statement! In reply to
That code prints out;

Quote:
magnum is a normal scalar.

Unsure

Paul...you may well be right. Either way...its working now. Thanks for the help guys :)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
If my code indeed prints out

> magnum is a normal scalar.

then your perl version is seriously broken....Wink

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] Annoying SQL Statement! In reply to
Ah well, the plugin works...I'll let the server admin sort out Perl Wink

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Annoying SQL Statement! In reply to
It can't have printed out magnum as $Password is inside single quotes so isn't interpolated Wink

Last edited by:

Paul: Feb 11, 2003, 8:37 AM