Gossamer Forum
> >
Quote Reply
SQL syntax
Hi

We are trying to use the following global to generate users list:

sub {
# Displays all Users.
my $tags=shift;
my $search_db= $DB->table('links_Users');
my $nh = $tags->{nh};
my $mh = $tags->{maxhits};
my $offset = ($nh-1) * $mh;
$search_db->select_options ('ORDER BY Username ASC',"LIMIT $offset, $mh");
my $sth = $search_db->select;
my $output;
while (my $user = $sth->fetchrow_hashref) {
$output .= qq~<li><a href="$CFG->{db_cgi_url}/page.cgi?page=user&user=$user->{Username}">$user->{Username}</a></li> ~;
}
return $output;
}


We are getting the following error:

Can't call method "fetchrow_hashref" on an undefined value at (eval 19) line 13.

and debug shows:


GT::SQL::error = Failed to execute query: 'SELECT * FROM links_Users ORDER BY Username ASC LIMIT 2.26030162959037e+16, SCALAR(0x8f60db4)': You have an error in your SQL syntax near 'SCALAR(0x8f60db4)' at line 1
@INC =
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
Quote Reply
Re: [katabd] SQL syntax In reply to
there appears to be something wrong with $nh, $offset, and $mh. try having your global return the values for those three variables...
Code:
return "next hit: $nh<br>offset: $offset<br>max hits: $mx";

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [katabd] SQL syntax In reply to
my $search_db= $DB->table('links_Users');

...should be;

my $search_db= $DB->table('Users');

GT::SQL automatically adds this prefix in for you Smile

Also, you need to add in some error tracking, or you'll never find out whats going on for sure;

my $sth = $search_db->select;

...should really be;

my $sth = $search_db->select || return GT::SQL::error;

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: [fuzzy logic] SQL syntax In reply to
Hi
Ther error now reads:

Unable to compile 'users_list': Global symbol "$mx" requires explicit package name at (eval 20) line 15.
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
Quote Reply
Re: [Andy] SQL syntax In reply to
Thanks

The prefix liks_ is a typo..

The error tracking vode returns an error:

Unable to compile 'users_list': Bareword "GT::SQL::error" not allowed while "strict subs" in use at (eval 20) line 10.
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
Quote Reply
Re: [katabd] SQL syntax In reply to
Mmm.. what does the global look like now?

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] SQL syntax In reply to
sub {
# Displays all Users.
my $tags=shift;
my $search_db= $DB->table('Users');
my $nh = $tags->{nh};
my $mh = $tags->{maxhits};
my $offset = ($nh-1) * $mh;
$search_db->select_options ('ORDER BY Username ASC',"LIMIT $offset, $mh");
my $sth = $search_db->select || return GT::SQL::error;
my $output;
while (my $user = $sth->fetchrow_hashref) {
$output .= qq~<li><a href="$CFG->{db_cgi_url}/page.cgi?page=user&user=$user->{Username}">$user->{Username}</a></li> ~;
}
return $output;
}
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
Quote Reply
Re: [katabd] SQL syntax In reply to
You need to make sure you put $ before GT::SQL::error, ie. $GT::SQL:error
Quote Reply
Re: [katabd] SQL syntax In reply to
Mmm.. what if you change;

my $sth = $search_db->select || return GT::SQL::error;

...to;

my $sth = $search_db->select() || return $GT::SQL::error;

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!

Last edited by:

Andy: Mar 29, 2005, 8:46 AM
Quote Reply
Re: [BSTR<T>] SQL syntax In reply to
Thanks

the error has changed to:

Failed to execute query: 'SELECT * FROM links_Users ORDER BY Username ASC LIMIT 2.63200873388262e+16, SCALAR(0x9ab818c)': You have an error in your SQL syntax near 'SCALAR(0x9ab818c)' at line 1
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
Quote Reply
Re: [katabd] SQL syntax In reply to
sorry for the typo in the code above. change $mx to $mh, and see what is outputed for those tags. I don't know why Andy insists the problem is your SQL syntax, when you clearly have incorrect values appearing for those tags. There is NOTHING wrong with your code, I have it working exactly as you have posted it here.

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [fuzzy logic] SQL syntax In reply to
Thanks a lot

The odd thing is this same global always worked before..

I am still getting the same error:

A fatal error has occured:
Can't call method "fetchrow_hashref" on an undefined value at (eval 20) line 11.

Please enable debugging in setup for more details.Stack Trace ====================================== Links (26577): Links::environment called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/Links.pm line 521 with no arguments. Links (26577): Links::fatal called at (eval 20) line 11 with arguments (Can't call method "fetchrow_hashref" on an undefined value at (eval 20) line 11. ). Links (26577): Links::__ANON__ called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/GT/Template.pm line 945 with arguments (HASH(0x86c5cb4)). Links (26577): GT::Template::_get_var called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/templates/default/compiled/users_list.html.compiled line 727 with arguments (GT::Template=HASH(0x86c5b4c), users_list, 0, 1). Links (26577): GT::Template::parsed_template called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/GT/Template.pm line 648 with arguments (GT::Template=HASH(0x86c5b4c)). Links (26577): GT::Template::_parse called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/GT/Template.pm line 134 with arguments (GT::Template=HASH(0x86c5b4c), users_list.html, HASH(0x86a2d7c)). Links (26577): GT::Template::parse called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/Links.pm line 387 with arguments (GT::Template, users_list.html, ARRAY(0x86beeb0), HASH(0x86a2d7c)). Links (26577): Links::user_page called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/Links/SiteHTML.pm line 250 with arguments (users_list.html, HASH(0x85d84b8), HASH(0x86a2d7c)). Links (26577): Links::SiteHTML::__ANON__ called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/GT/Plugins.pm line 142 with arguments (HASH(0x85d84b8), [undef]). Links (26577): GT::Plugins::dispatch called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/Links/SiteHTML.pm line 28 with arguments (GT::Plugins, /home/virtual/site1/fst/var/www/cgi-bin/links/admin/Plugins, site_html_users_list, CODE(0x86bae0c), HASH(0x85d84b8), [undef]). Links (26577): Links::SiteHTML::display called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/Links/User/Page.pm line 69 with arguments (users_list, HASH(0x85d84b8)). Links (26577): Links::User::Page::generate_custom_page called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/Links/User/Page.pm line 31 with arguments (users_list). Links (26577): Links::User::Page::handle called at /home/virtual/site1/fst/var/www/cgi-bin/links/admin/GT/Plugins.pm line 136 with no arguments. Links (26577): GT::Plugins::dispatch called at page.cgi line 25 with arguments (GT::Plugins, /home/virtual/site1/fst/var/www/cgi-bin/links/admin/Plugins, handle_page, CODE(0x83b6848)).
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
Quote Reply
Re: [katabd] SQL syntax In reply to
Are you sure, that
my $mh = $tags->{maxhits};
is right?
I don't know 'maxhits' tag.
It should be 'mh', I think.

So:
my $mh = $tags->{mh};

Just an idea at first sight.

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [webmaster33] SQL syntax In reply to
Yes I am..

I tried your suggestion anyways with no luck.
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
Quote Reply
Re: [katabd] SQL syntax In reply to
Passing in tags with 'shift' has always been a bit buggy. from the 3.0 docs:

---
Quote:

  • GT::Template provides a new, much improved way of accessing tags from plugins, globals, etc. You may now call GT::Template->vars to access a GT::Template::Vars object that gives you a transparent way of accessing and retrieving template tag values.
    • my $vars = GT::Template->vars; gives you a new object.
    • $vars->{abc} accesses the 'abc' tag. Unlike the hash reference returned by GT::Template->tags, this will alway be a string, and any HTML-escaping that would apply (or not apply) inside the template will apply to the value retrieved. No more worrying about a value retrieved being a scalar reference, or being escape or not escaped.
    • $vars->{abc} = "value"; sets a value. HTML-escaping will apply if the tag is accessed again - either inside a template or by retrieving the value of $vars->{abc}. If the value is already HTML-escaped, or should not be escaped, set the variable to a reference: $vars->{abc} = \"value";
    • $vars->{"abc.def"} accesses the same value it would in a template - that is, it accesses the "def" key of the "abc" hash reference. Likewise for $vars->{"def.length"}, which would return the length of the "def" array reference/loop.
    • GT::Template->vars is now recommended instead of GT::Template->tags in all cases. GT::Template->tags still works as it did before.
    • See the GT::Template::Vars documentation included with Gossamer Links.


  • Try using this instead of $vars = shift, and see if it makes a difference.

    I've found sometimes the values you expect, are not what is really being passed in.


    PUGDOG� Enterprises, Inc.

    The best way to contact me is to NOT use Email.
    Please leave a PM here.
    Quote Reply
    Re: [katabd] SQL syntax In reply to
    I'm sure, that the error happens in relation with these lines:
    Code:
    my $nh = $tags->{nh};
    my $mh = $tags->{maxhits};
    my $offset = ($nh-1) * $mh;

    The input of these tags is failing.
    Try to print out $nh and $mh to see their value.

    Also you could try to replace the
    Code:
    my $search_db= $DB->table('links_Users');
    to use without prefix:
    Code:
    my $search_db= $DB->table('Users');

    Best regards,
    Webmaster33


    Paid Support
    from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
    Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
    Quote Reply
    Re: [webmaster33] SQL syntax In reply to
    As I said before, there is nothing wrong with the code. I copied it verbatim and tried it on my site without any problems. Andy has already pointed out that the table prefix doesn't need to be added. And *shocker* I've been trying to get him to show us what those variables contain since my first post in this thread.

    Philip
    ------------------
    Limecat is not pleased.
    Quote Reply
    Re: [webmaster33] SQL syntax In reply to
    Thanks as well..

    ITHose values are the problem..

    They will not print, trying to print them will give the same error.
    What is odd is nothing have changed except upgrading to the newest version..

    And as the GT documentation states the old variables should still work.
    Regards
    KaTaBd

    Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
    Quote Reply
    Re: [fuzzy logic] SQL syntax In reply to
    Hi Philip!

    What version at you Links SQL?
    At me it is established 2.2.1 and any of the resulted variants on a site does not work.

    "
    Failed to execute query: 'SELECT * FROM lsql_Users ORDER BY Username ASC LIMIT -0, ': You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

    BR,
    Bigpat




    _________________________________________________________________________________________________________________
    Web directory and search engine | Dirlist.net Directory - Add your link today.
    Quote Reply
    Re: [Bigpat] SQL syntax In reply to
    2.2.1

    You need to make sure you access the page with that global with nh and maxhits parameters, or you'll get errors like that.

    if you change the code to this:
    Code:
    sub {
    # Displays all Users.
    my $tags=shift;
    my $search_db= $DB->table('Users');
    my $nh = $tags->{nh} || 1;
    my $mh = $tags->{maxhits} || 5;

    my $offset = ($nh-1) * $mh;
    $search_db->select_options ('ORDER BY Username ASC',"LIMIT $offset, $mh");
    my $sth = $search_db->select || return $GT::SQL::error;
    my $output;
    while (my $user = $sth->fetchrow_hashref) {
    $output .= qq~<li><a href="$CFG->{db_cgi_url}/page.cgi?page=user&user=$user->{Username}">$user->{Username}</a></li> ~;
    }
    return $output;
    }

    then you won't have that problem. ideally though, you want error checking on $nh and $mh, so that you have valid input (ie, you don't want a negative offset, or one that would be out of range with the number of actual records)

    Philip
    ------------------
    Limecat is not pleased.
    Quote Reply
    Re: [fuzzy logic] SQL syntax In reply to
    THX Philip!

    This code really works!

    BR,
    Bigpat


    _________________________________________________________________________________________________________________
    Web directory and search engine | Dirlist.net Directory - Add your link today.
    Quote Reply
    Re: [fuzzy logic] SQL syntax In reply to
    Thanks for all the help..

    I came to the conclusion that it must be our site..

    Your newest code returns the same error:



    Failed to execute query: 'SELECT * FROM links_Users ORDER BY Username ASC LIMIT 2.89763924861814e+16, SCALAR(0xa256bf8)': You have an error in your SQL syntax near 'SCALAR(0xa256bf8)' at line 1


    Does anyone know of another suggestion/global/plug in to display a list of all registered users dynamically?

    Regards
    KaTaBd

    Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
    Quote Reply
    Re: [katabd] SQL syntax In reply to
    This variant of a code works for me correctly, try:

    sub {
    # Displays all Users.
    my $search_db= $DB->table('Users');
    $search_db->select_options ('ORDER BY Username DESC');
    my $sth = $search_db->select;
    my $output;
    while (my $user = $sth->fetchrow_hashref) {
    $output .= qq~<a href="$CFG->{db_cgi_url}/page.cgi?page=user&user=$user->{Username}">$user->{Username}</a><br /> ~;
    }
    return $output;
    }

    BR,
    Bigpat

    _________________________________________________________________________________________________________________
    Web directory and search engine | Dirlist.net Directory - Add your link today.
    Quote Reply
    Re: [katabd] SQL syntax In reply to
    Hi,

    I think that this is because you are using mod_perl - there was a change in the libraries that causes this error - I'll try to find the relevant post.

    Edit: Here it is:
    http://www.gossamer-threads.com/...i?post=266471#266471

    Last edited by:

    afinlr: Apr 4, 2005, 8:47 AM
    Quote Reply
    Re: [afinlr] SQL syntax In reply to
    Thanks Laura

    That is what I have with the changes:
    sub {
    # Displays all Users.
    my $tags=shift;
    my $search_db= $DB->table('Users');
    my $nh = (ref $tags->{nh} ? ${$tags->{nh}} : $tags->{nh});
    my $mh = (ref $tags->{mh} ? ${$tags->{mh}} : $tags->{mh});
    my $offset = ($nh-1) * $mh;
    $search_db->select_options ('ORDER BY Username ASC',"LIMIT $offset, $mh");
    my $sth = $search_db->select;
    my $output;
    while (my $user = $sth->fetchrow_hashref) {
    $output .= qq~<li><a href="$CFG->{db_cgi_url}/page.cgi?page=user&user=$user->{Username}">$user->{Username}</a></li> ~;
    }
    return $output;
    }
    Still getting a syntax error: GT::SQL::error = Failed to execute query: 'SELECT * FROM links_Users ORDER BY Username ASC LIMIT 0, ': You have an error in your SQL syntax near '' at line 1
    @INC =
    Regards
    KaTaBd

    Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory
    > >