Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

question can you explain this code

Quote Reply
question can you explain this code
Hello all,

I need to mod this part code in the page.cgi but i don't know exactly what this does only i think i have to modify it because i need a different build.

# Fetch the subcategory list.
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute ($category_r->{'ID'});
$links_r = $get_links->fetchall_arrayref || [];


The part CategoryID = ? have to be something like:
CategoryName = Links.[here the content of Category.CategoryName] and Links.[here the content of Category.CategoryName] = "Yes"

But i don't know what to put in [here the content of Category.CategoryName] maybe $Category{CategoryName} or something like that ? Man i have to learn MySQL quick...

What does the code $get_links->execute ($category_r->{'ID'}); do ?

And maybe you can explain the whole part of code to me?

... well basically i have 12 extra fields in the links table and the 12 records in the category table where the categoryname is the same as the extra fields names in the links table.

I have to modify it this way because i can't use alternative links....

Allready thanks.

Regards Startpoint.

Quote Reply
Re: question can you explain this code In reply to
Hi,

this perl code does not fetch the subcategories as you may think when reading the first line

# Fetch the subcategory list.

$get_links = $LINKDB->prepare("SELECT * FROM Links WHERE CategoryID = ? ORDER BY ...);
This line prepares a recordset with a unknown CategoryID (CategoryID =?).

$get_links->execute ($category_r->{'ID'});
Here you tell the recordset ($get_links) to use the CategoryID with the value of $category_r->{'ID'}

$links_r = $get_links->fetchall_arrayref || [];
now you store the records in an array ($links_r ) if you have records. If you don't it will be empty ( || []).

In $links_r you now have all the links with the CategoryID = $category_r->{'ID'}

As for the prepare statement have a look at

http://www.mysql.com/documentation/mysql/commented/manual.php?section=Reference

hope it helps,

regards, alexander

Quote Reply
Re: question can you explain this code In reply to
Thanks a million Alexander this was really what i needed !!!!

Now that i understand it in a way Smile, i did find my solution in page.cgi.

The code is now:
$get_links = $LINKDB->prepare("SELECT * FROM Links WHERE $category_r->{'Name'} = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute ("Yes");


Now i only have to change some code in nph-build.cgi ...

2 minutes later...

Well it's now also working in nph-build.cgi, only i had to move the prepare line down right above get_links line so it's in the foreach loop because else there is no $category_r defined yet.

I am thinking about the code i did move.

Think think... *krak*

I don't know if it will select all the links and then filter or filter it directly ?

Let's look at the code again....

$get_links = $LINKDB->prepare("SELECT * FROM Links WHERE $category_r->{'Name'} = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute ("Yes");


I read in the documentation (thanks for that url you gave me) that execute will start the prepare.... hmm....

Do i have a type of 'overload' ?

Like, the above code will select all links first then filter or does this select with the filter directly ?
This because the prepare statement was not in the foreach loop and no is in the foreach loop (cats)...

Hmm.. But the excute command starting the prepare, and that is in the foreach loop !

So maybe it's fine now, or not ???

Hope you get want i mean.

Allready thanks.

Regards Startpoint.
Quote Reply
Re: question can you explain this code In reply to
Hi,

I think you mix-up Table Names with its values.
If you say "SELECT * FROM Links WHERE $category_r->{'Name'} = ...."
In Nph-build.cgi $category_r->{'Name'} has the value eg. "Maincat/Subcat1/Subcat2"
With this code you should get an error like "unknown Table Maincat/Subcat1/Subcat2"

The WHERE clause uses the syntax:

WHERE Tablename = Value

happy debugging,
regards, alexander

Quote Reply
Re: question can you explain this code In reply to
Thanks for the reply Alexander,

But my code really works ok ! :).....

Eh... Well i give you some extra background information:

I have modified SQLLinks 'big' time... Because i needed alternative cat. links. But there where problems with using that and even GS: Alex and the rest didn't have a propper solution for this problem.

So i maked 12 extra fields (Yes/No) in the links table. This Links.fieldnames are also categorynames in the table Category... So i have 12 records in table Categories and NO sub-categories ! Finally i stop to use category-id in the Links table for the records because it was in no futher use for me....

So then i had to think how to build it all (new/cool/category/template and so on) with use of page.cgi and nph-build.cgi... ? Think think.....

Well i really was stuck on the category pages, but thanks to you it is working now.....

Only my problem is that i can't do this:

$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE $category_r->{'Name'} = Yes ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute;

Somehow that's not working.. But i think the result will be the same .... because execute handle the start ?...

But as i told before i am not sure so that's why i asked it... But the code just works fine for my ...

Regards Startpoint.

Quote Reply
Re: question can you explain this code In reply to
Hi,

Are you shure you have a hash of tablenames in $category_r->{'Name'} ??


and if you call $get_links->execute(); without a parameter do not forget the empty bracets.

regards, alexander



Quote Reply
Re: question can you explain this code In reply to
In Reply To:
Are you sure you have a hash of table names in $category_r->{'Name'} ??
--> eh.. newbie alert ... what is a hash ?

$OUT{category_name} = $category_r->{'Name'};
is before the prepare yeah...


In Reply To:
and if you call $get_links->execute(); without a parameter do not forget the empty bracets.
Thanks... well i tried it and the build started (no errors) only it didn't build good, it found no links.. So i replaced it with the code that seems to work .....


This works for me:
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE $category_r->{'Name'} = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute ("Yes");


This doesn't works for me:
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE $category_r->{'Name'} = Yes ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute ();



If you look at the this part $category_r->{'Name'} = ? of the good code...

Will the ? sign be replaced with Yes ?


And this code REALLY works for me !! Only to try make thinks clear..

Thanks.

Regards Startpoint.

Quote Reply
Re: question can you explain this code In reply to
Hi,

a hash is a data structure. In your case $category_r is a hash of the record of one category.

$category_r->{'ID'} = the category ID eg. 12
$category_r->{'Name'} = the name of the category (Maincat/subcat/subcat2)
$category_r->{'Description'} = Description of the category (Bla Bala Bla...Text ...BlaBla)
$category_r->{'Has_New_Links'} = "Yes"
and so on...

And the SQL Statement requires a TABLE NAME.

If you want all the with the CategoryID of 12
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute(12);

This will do the following query:
"SELECT * FROM Links WHERE CategoryID = '12' ORDER BY Add_Date LIMIT 1000"

Try the query in the SQL Monitor in admin.cgi

If I understand you right, you want to do the following

$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE Name = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute ("Yes");

which is quite the same as

$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE Name = 'Yes' ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute();

I hope it helps,
regards, alexander

Quote Reply
Re: question can you explain this code In reply to
Thanks Alex,

Now i fully understand...



In Reply To:
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE Name = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute ("Yes");

which is quite the same as

$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE Name = 'Yes' ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_links->execute();
This is logical 2 only somehow the last code with name = 'Yes' somehow didn't work. The first code with ? worked for me... Maybe i did "Yes" don't know but i do understand you 100% ...

Thanks for the explaination.....

Regards Startpoint.. (learning day by day! :))...