Gossamer Forum
Home : Products : Gossamer Links : Pre Sales :

Special SQL Query

Quote Reply
Special SQL Query
Hi all,

im looking for a special query to get all new links for a special category.
Without having alternate cats, it seems no problem, but with it i dont get till now:

I think it must be something like:

get all the LinkdID from CategoryAlternates WHERE CategoryID = X
(SELECT * FROM CategoryAlternates WHERE CategoryID = x)

putting in an array:

while ($link = $sth->fetchrow_hashref) { ???? }

Then something like:

SELECT * FROM Links WHERE CategoryID = x AND all from array order by $LINKS{build_sort_order_home} LIMIT 5!

Hope someone knows more about that mine.

Robert

Quote Reply
Re: Special SQL Query In reply to
Did you look at the query in the nph-build.cgi?

It does an implicit join, then makes a second query for the alt-categories.

Just use the first query.



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Special SQL Query In reply to
Yes, i have seen it, but im not shure if i have understand it;
for now i have a db with only 6 Categories and so i add 6 fields to the main db; this has the effort that a user could send so i which cats he wants to have his entree too.

For another db with about 500 cats, this would not be a solution and i will come back to this tomorrow or so.
I just should wait for the new links, but im to far in this project to stop it now; every db calls at least two others, and much more stuff ...

Thank you, Robert

Quote Reply
Re: Special SQL Query In reply to
What you are trying to do is an implicit JOIN on two databases. Taking the category record, and using a value in that record to pull the matching records out of the Links database.

The code Alex has in his query is very good, and I've altered it a lot to use in different areas.

You might want to study the mysql manual on the JOIN, or if you have the MySQL book, (REQUIRED for anyone working with MySQL) you can read the pages on that.

It's not intuitive, and it's not very clear, but if you take Alex's example (that works) then delete the parts you don't need, and add what you do, you will be able to generate the queries you need.


Code:
SELECT Links.*, Category.Name
FROM Links, Category
WHERE Links.isNew = 'Yes' AND Links.CategoryID = Category.ID
ORDER BY $LINKS{build_sort_order_new}
LIMIT 1000
What this is doing is selecting a record that contains the fields of the Links table + Category.Name in each record returned, and is selecting those based on the "where" criteria.

This is an implicit join.

The field that links the two is the Links.CategoryID = Category.ID

But, none of the other information from the Category table record is selected out except for the Name field.

(I think I got that right)

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Special SQL Query In reply to
Now if you could get the Alternate Links in there, _with sorting_, I'd be very impressed. ;)

I'm so glad that will go away in the next version, the AltCategories always bothered me to no end.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Special SQL Query In reply to
you, and everyone else who added an alt-link then wondered what happened <G>

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/