Gossamer Forum
Quote Reply
Is this possible?
Hello

I need to know if this would be easy to implement, or if it's even possible and if so how.

I have a mysql database table named prices. Inside is a field named "name" which every entry is unique. the second field is named price.

I need to somehow pull the price field out of the database and insert it on the detailed pages in Links SQL.

I know nothing about writing mysql statements or php etc. But here is what I would like it to do in laymans wording

Select from table prices where name equals <%cardname1%> and
bring back field price from where name equals <%cardname1%>

I want to somehow make it so that on the detailed html pages of links sql it will place the price from the row where the name equals the same thing as what is written in my detailed.html files (basically <%cardname1%> etc.

I hope that all makes sence.

That might all be confusing, basically I want to pull a fields data out of my price database and have it inserted into the detailed pages that gets written by Links sql, there is a field named "name" in the price database and it has the same name that I can get links sql to print out on my detailed pages when I use the tag <%cardname#%> (where # is replaced with a real number from 1 to 60 as determined by when the person added their record in in my links sql database to begin with)

Last edited by:

Westin: Apr 19, 2009, 10:32 PM
Quote Reply
Re: [Westin] Is this possible? In reply to
Hi,

Is the table in the same DB as your GLinks one? If so, you could try:
Code:
sub {
my $val = $DB->table('Links')->do_query(qq|SELECT price FROM prices WHERE name = "$_[0]"|)->fetchrow;
return $val;
}

..call with:
Code:
<%if cardname1%>
<%get_value_prices($cardname1)%>
<%endif%>

Untested, but that should work.

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] Is this possible? In reply to
I get the following:
Error: Variable 'get_value_prices' is not a code reference

I created a new global named prices with the
Code:
sub {
my $val = $DB->table('mymagicdeck')->do_query(qq|SELECT price FROM prices WHERE name = "$_[0]"|)->fetchrow;
return $val;
}

and put in my detailed.html template:
Code:
<%if cardname1%>
<%get_value_prices($cardname1)%><%endif%>


And on the built detailed page I get: Error: Variable 'get_value_prices' is not a code reference

Did I set this up wrong?

And yes the table prices is in the main Links mysql database, but it's not a part of the links system, i.e. it's just another table in the same database that the Links tables reside..

Last edited by:

Westin: Apr 20, 2009, 10:50 PM
Quote Reply
Re: [Westin] Is this possible? In reply to
Hi,

You need to remove the space before "sub {"

So, what you have:

Code:
sub {

..should be:

Code:
sub {

Hope that helps.

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] Is this possible? In reply to
Global named prices:
Code:
sub{
my $val = $DB->table('prices')->do_query(qq|SELECT price FROM prices WHERE name = "$_[0]"|)->fetchrow;
return $val;
}

On the Detailed.html template:
Code:
<%if cardname1%><%get_value_prices($cardname1)%><%endif%><br><%endif%>

Produces: Error: Variable 'get_value_prices' is not a code reference

Unless I done something else wrong, still get that error message.


// In the first example that I wrote, the table name as mymagicdeck, but changed it to what it should be which is prices as shown in this post.
Quote Reply
Re: [Westin] Is this possible? In reply to
You still have it wrong :P

Code:
sub{

..should be:

Code:
sub {

No space at beginning, none at end - but a space between sub and {

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] Is this possible? In reply to
Oh, I thought you meant to remove the space between sub and {

So, I put that space back in and removed the space before the first sub and saved.

Now I get this when trying to build:
GT::SQL (27376): File '/home/myaccount/admin/defs/magicdeck_prices.def' does not exist or the permissions are set incorrectly at (eval 77) line 2.
Quote Reply
Re: [Westin] Is this possible? In reply to
Hi,

Ok, at least the main global is working now.

Are you using exactly the same global I gave you? Can't see why that error would be coming up (not without having a look at your install)

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: [Westin] Is this possible? In reply to
Ah, I see - you edited one of my lines Tongue

This line:

Code:
my $val = $DB->table('prices')->do_query(qq|SELECT price FROM prices WHERE name = "$_[0]"|)->fetchrow;

Needs to be:

Code:
my $val = $DB->table('Links')->do_query(qq|SELECT price FROM prices WHERE name = "$_[0]"|)->fetchrow;

..otherwise it will try and load a new GT::SQL object (which we don't wanna do, as you don't have the "prices" table setup with a .def, etc)

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] Is this possible? In reply to
Yeah I think I know why it's doing that but not sure how to correct it.

All the links tables have the prefix of mymagicdeck_table name

But the prices is just named prices without the mymagicdeck_ before it, Im willing to bet if I could figure out how to rename the table prices to mymagicdeck_prices then it might work.
Quote Reply
Re: [Westin] Is this possible? In reply to
Westin wrote:
Yeah I think I know why it's doing that but not sure how to correct it.

All the links tables have the prefix of mymagicdeck_table name

But the prices is just named prices without the mymagicdeck_ before it, Im willing to bet if I could figure out how to rename the table prices to mymagicdeck_prices then it might work.

Just do as I suggested above, and it should work fine ;)

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: [Westin] Is this possible? In reply to
I stand corrected, I don't need to rename the table at all, changing prices to Links did the trick and now it's bringing back the price field for each line.

Much appreciated of your help Andy.
Quote Reply
Re: [Westin] Is this possible? In reply to
NP ;)

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] Is this possible? In reply to
ughhh. Don't pass is CGI input right into SQL queries - an SQL injection vulnerability right there. Use placeholders instead:

Code:
sub {
my $val = $DB->table('Links')->do_query('SELECT price FROM prices WHERE name = ?', [@_])->fetchrow;
return $val;
}

Adrian
Quote Reply
Re: [brewt] Is this possible? In reply to
I have another question regarding this.

I want to make it so that if there are no results to return for %get_value_prices% then return No prices found, otherwise return the value of the prices it did find in the database.

Is this possible, if so how?

Thanks

Last edited by:

Westin: Apr 29, 2009, 12:59 AM
Quote Reply
Re: [Westin] Is this possible? In reply to
Just change:

Code:
return $val;

to:

Code:
$val ? return $val : return q|No price found|;

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] Is this possible? In reply to
Or just:
Code:
return $val ? $val : 'No price found';

Adrian
Quote Reply
Re: [brewt] Is this possible? In reply to
Thank you,
Im hoping you all can help me further with this subject.

I now have two separate price sheets in my database, They both have the same structure in each table.

One table is named as was listed above, 'prices' the new table is named 'pricesf'

What I need to do is to pull the data out of 'pricesF' table IF that particular links record has the word 'Foil' in one of the columns in it's record. If it's got the word 'Not Foil' then it should pull the price data out of the original 'prices' table like it's doing now.

OR if it's easier, maybe it could just bring back the data from both tables, and show the letter F before the price from the 'pricesF' table.

Is that doable?
Quote Reply
Re: [Westin] Is this possible? In reply to
Would help if we knew what field had the value "foil" in, otherwise its not that easy to suggest a solution :P

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] Is this possible? In reply to
Sorry about that, I figured you'd have questions....

The field(s) that hold the entry of either Foil or Not Foil are

card1foil through card55foil
i.e.
Code:
card1foil
card2foil
card3foil
card4foil
card5foil
card6foil
card7foil
card8foil
card9foil
card10foil
card11foil
card12foil
card13foil
card14foil
card15foil
card16foil
card17foil
card18foil
card19foil
card20foil
card21foil
card22foil
card23foil
card24foil
card25foil
card26foil
card27foil
card28foil
card29foil
card30foil
card31foil
card32foil
card33foil
card34foil
card35foil
card36foil
card37foil
card38foil
card39foil
card40foil
card41foil
card42foil
card43foil
card44foil
card45foil
card46foil
card47foil
card48foil
card49foil
card50foil
card51foil
card52foil
card53foil
card54foil
card55foil
Quote Reply
Re: [Westin] Is this possible? In reply to
Ok, if I'm understanding your self - the easiest way would probably be to do it like this:

Code:
sub {
my $val = $DB->table('Links')->do_query('SELECT price FROM prices WHERE name = ?', @_[0] )->fetchrow;
my $val2 = $DB->table('Links')->do_query('SELECT pricesF FROM prices WHERE name = ?', $_[1])->fetchrow;
if ($val2) {
return $val2 ? $val2 : 'No price found';
}

return $val ? $val : 'No price found';

}
..then call with:

Code:
<%if cardname1 or card1foil%><%get_value_prices($cardname1,$card1foil)%><br /><%endif%>

Untested, but should work.

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: May 5, 2009, 5:43 AM
Quote Reply
Re: [Andy] Is this possible? In reply to
Get an error message of:
Code:
A fatal error has occured:

Can't call method "fetchrow" on an undefined value at (eval 55) line 3.

Please enable debugging in setup for more details.

Stack Trace
======================================
Links (15116): Links::environment called at /home/account/mydomain.com/admin/Links.pm line 751 with no arguments.
Links (15116): Links::fatal called at (eval 55) line 3 with arguments
(Can't call method "fetchrow" on an undefined value at (eval 55) line 3.
).
Links (15116): Links::__ANON__ called at GT::Template::_call_func line 791 with arguments
(Incinerate, [undef]).
Links (15116): GT::Template::_call_func called at /home/account/mydomain.com/admin/templates/luna/compiled/detailed.html.compiled line 804 with arguments
(GT::Template=HASH(0x893f5f8), get_price_fetch, 1, 0, Incinerate, [undef]).
Links (15116): GT::Template::parsed_template called at /home/account/mydomain.com/admin/GT/Template.pm line 565 with arguments
(GT::Template=HASH(0x893f5f8)).
Links (15116): GT::Template::_parse called at /home/account/mydomain.com/admin/GT/Template.pm line 158 with arguments
(GT::Template=HASH(0x893f5f8), detailed.html, HASH(0x89246e8)).
Links (15116): GT::Template::parse called at /home/account/mydomain.com/admin/Links.pm line 453 with arguments
(GT::Template, detailed.html, ARRAY(0x892bf68), HASH(0x89246e8)).
Links (15116): Links::user_page called at /home/account/mydomain.com/admin/Links/SiteHTML.pm line 185 with arguments
(detailed.html, HASH(0x88d9d1c), HASH(0x89246e8)).
Links (15116): Links::SiteHTML::site_html_detailed called at /home/account/mydomain.com/admin/GT/Plugins.pm line 133 with arguments
(HASH(0x88d9d1c), [undef]).
Links (15116): GT::Plugins::dispatch called at /home/account/mydomain.com/admin/Links/SiteHTML.pm line 27 with arguments
(GT::Plugins=HASH(0x838fa84), site_html_detailed, *Links::SiteHTML::site_html_detailed, HASH(0x88d9d1c), [undef]).
Links (15116): Links::SiteHTML::display called at Links::Build::build_detailed line 548 with arguments
(detailed, HASH(0x88d9d1c)).
Links (15116): Links::Build::build_detailed called at /home/account/mydomain.com/admin/GT/Plugins.pm line 133 with arguments
(HASH(0x86712d4)).
Links (15116): GT::Plugins::dispatch called at /home/account/mydomain.com/admin/Links/Build.pm line 30 with arguments
(GT::Plugins=HASH(0x838fa84), build_detailed, CODE(0x8642220), HASH(0x86712d4)).
Links (15116): Links::Build::build called at /home/account/mydomain.com/admin/Links/User/Page.pm line 249 with arguments
(detailed, HASH(0x86712d4)).
Links (15116): Links::User::Page::generate_detailed_page called at /home/account/mydomain.com/admin/GT/Plugins.pm line 133 with no arguments.
Links (15116): GT::Plugins::dispatch called at /home/account/mydomain.com/admin/Links/User/Page.pm line 61 with arguments
(GT::Plugins=HASH(0x838fa84), generate_detailed, CODE(0x8384aa0)).
Links (15116): Links::User::Page::handle called at /home/account/mydomain.com/admin/GT/Plugins.pm line 133 with no arguments.
Links (15116): GT::Plugins::dispatch called at page.cgi line 26 with arguments
(GT::Plugins=HASH(0x838fa84), handle_page, CODE(0x81672f4)).


Created a template global named get_foil_prices with:
Code:

sub {
my $val = $DB->table('Links')->do_query('SELECT price FROM prices WHERE name = ?', @_[0] )->fetchrow;
my $val2 = $DB->table('Links')->do_query('SELECT pricef FROM prices WHERE name = ?', $_[1])->fetchrow;
if ($val2) {
return $val2 ? $val2 : 'No price found';
}

return $val ? $val : 'No price found';

}



Calling it with:
Code:
<%get_price_fetch($cardname1,$card1foil)%>
Quote Reply
Re: [Westin] Is this possible? In reply to
Sorry, please change the code to the above global (I've edited it).

Thought the table was called "pricesf", but its "pricesF".

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] Is this possible? In reply to
OMG!

I've been so stupid. And just have to apologize for wasting your time. The simplest solution would have been to just take your orginal global coding and do this:

Code:

sub {
my $val = $DB->table('Links')->do_query(qq|SELECT price FROM pricef WHERE name = "$_[0]"|)->fetchrow;
$val ? return $val : return q|0.00|;
}


And name it something like get_price_foil

Then when the prices are looked up, it would grab the prices out of the first table, then look up the prices in the foil table and get them too.

Then printout the results with
Code:

<%if cardname2%><font color=orange>Avg</font>:&nbsp;$<%get_value_prices($cardname2)%>/$<%get_price_foil($cardname2)%></td>

Which would simply look like [CARD NAME] $1.17/$3.37 Which is exactly what I wanted to do so that people know how much people on eBay are paying for a non foil and for a foil version of the card.

Don't know why I didn't think of that earlier. All it requires me to do is insert the extra <%get_price_foil(cardname#)%> after the existing get_value_prices tag.

As Homer simpson would say, DOH!

Thanks and sorry for the waste of time.

Last edited by:

Westin: May 5, 2009, 6:16 AM
Quote Reply
Re: [Westin] Is this possible? In reply to
haha no prob =)

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] Is this possible? In reply to
Quote:
if ($val2) {
return $val2 ? $val2 : 'No price found';
}

Part of that code is redundant. If you reach the line starting "return $val2..." then "if ($val2)" is true so its pointless testing it again with "return $val2 ?" .