Thread

i need some kind of “query module for dummies” intro or something. after reading through the docs and the wiki (and asking on the how to forum), it’s just not clicking for me. what i’m trying to do is use custom fields inside the calendar tag for a shows calendar i’m building. it seems simple enough, but the queries i’m writing ain’t cuttin’ it. a jpg with field_id and field_name info is attached.

many thanks.

I suspect this really does belong in ‘How to’, but let’s confirm before I need it.  Are you having trouble with how to write a query?  Or how the query module works?  If it’s the query itself, it’s more a ‘How to’ type thing and I’ll shift it over.

And say that I would be hesitant to nest a query tag inside the calendar tags- because assuming you’re wanting to show additional info for each entry in the calendar?  You’ll be running that query for EACH entry that’s in the calendar.  10 entries= 10 extra queries.  It could prove to be a performance drain.

But- let me know what specifically you’re up to and I can better advise- and if you’ve got example code that you’re trying to get working, post that as well.

Thanks Robin. No need to shift the thread - I started over there.

To answer your question - Yes, I’m trying to show additional info for each entry inside the calendar tag, which seems to have been hinted at here by Sue. Maybe there’s another way to use custom fields in the calendar tag?

I’m also having trouble understanding the query module in general, and the documentation isn’t making it through my skull. This was my attempt at writing a query to bring the custom fields into the calendar tag, which did nothing:

{exp:query sql="SELECT shows_venue_name, shows_venue_url, shows_location, shows_tix, shows_also FROM exp_weblog_fields WHERE group_id='2'"}
<a href="http://{shows_venue_url}">{shows_venue_name}</a>
{shows_location}
{shows_tix}
{shows_also}
{/exp:query}

Dang- that’s a good thread.  I think I’m not going to merge, as it would just confuse the issue.  And I’ll leave it here as long as we’re focusing on how to use the query module- rather than the query itself.

You’ve got the basic format right, the problem is, to really make use of it, you need to be very familiar with EE’s database and know how to write a query.  Which I’m pretty good at- and there’s still a ton of stuff that’s way over my head. 

But basically?  In the above example, your problem is those fields don’t exist in EE’s database- so it’s just going to error.  What you’re telling it is to go look in exp_weblog_fields and bring back the content in columns named ‘shows_venue’ etc.  Those columns don’t exist.  You could bring back ‘field_name’ or ‘field_label’- basically, go to ‘Admin- Utilities- SQL Manager- Manage DB tables’ and go browse the exp_weblog_fields table.  The column headings?  Those are your fields- you can pull back the content in those fields- grabbing the short_name of every custom field in group_id 2, for example.

Which as far as I can tell, wouldn’t really make sense to try to include in the calendar output- but that’s another issue entirely.

So- you’ve got the basic syntax for the module down- the problem is with the query itself.  You’re trying to pull from fields that don’t exist.  And even if you pull that data, I’m fuzzy on how it would help displaying field content in the calendar.  For that, you’d want to look at the exp_weblog_data table- check the field names, figure out which ones you need to pull in, then write a query to pull those fields from that table- where entry_id= the entry_id available in the calendar entries.

Er- make sense?

Adam—

Your query here:

{exp:query sql="SELECT shows_venue_name, shows_venue_url, shows_location, shows_tix, shows_also FROM exp_weblog_fields WHERE group_id='2'"}

won’t work because shows_venue_name and the rest aren’t stored in the database that way.

shows_venue_name is really field_id_4.

So you’d do something like this:

{exp:query sql="SELECT field_id_4 as shows_venue_name, field_id_5 as shows_venue_url, field_id_6 as shows_location, field_id_7 as shows_tix, field_id_8 as shows_also FROM exp_weblog_fields WHERE group_id='2'"}

Awesome - thanks Robin and Sue. It makes a lot more sense now. So, if I want to be able to use the custom_field name when doing a query, I need to use “as” to connect it to the specific field_id? Could I also do

{exp:query sql="SELECT field_name FROM exp_weblog_fields WHERE group_id='2'"}

to just get them all?

Which as far as I can tell, wouldn’t really make sense to try to include in the calendar output - but that’s another issue entirely.

Robin: is this because it will be running too many queries as you mentioned in your first response? Is it just completely inadvisable to try to include custom fields in the calendar tag and should I come up with a different solution?

No- it’s not completely inadvisable.  I’d do it as long as had either 1. not a lot of entries in the calendar and/or b. a lot of horsepower.  Now- I have 30-40 queries being added?  I’d hack the module before I’d do it.  So it just depends.

To grab all of the fields in a table, you could use SELECT * FROM…. but I wouldn’t do it in this case.  However, what I meant by it not working?  Er- all your query from the exp_weblog_fields table is going to get you is the names of your fields.  Not the data IN those fields.  It’s possible that’s what you want- but I can’t see why.  If I had to bet, you really want the content of the custom fields- in which case, you want to query the exp_weblog_data table.

No - it’s not completely inadvisable. I’d do it as long as had either 1. not a lot of entries in the calendar and/or b. a lot of horsepower. Now - I have 30-40 queries being added? I’d hack the module before I’d do it. So it just depends.

Well, the absolute max would be 31 for any month, but realistically it would be between 0 and 20 for any month - they can’t play every day!

Er - all your query from the exp_weblog_fields table is going to get you is the names of your fields. Not the data IN those fields. It’s possible that’s what you want - but I can’t see why. If I had to bet, you really want the content of the custom fields - in which case, you want to query the exp_weblog_data table.

Yes, I absolutely want the content of the fields. So, I would run this instead?

{exp:query sql="SELECT field_id_4 as shows_venue_name, field_id_5 as shows_venue_url, field_id_6 as shows_location, field_id_7 as shows_tix, field_id_8 as shows_also FROM exp_weblog_data WHERE weblog_id='2'"}

Or would that mean that whenever I use {shows_venue_name}, it will spit out the content from all the entries?

Much better!  But there’s still one thing I don’t get- the WHERE weblog_id=“2” bit.  Are you trying to associate this with stuff in the calendar?  If so- you probably want to pull the entry id for each of your items- use that as WHERE entry_id=’{entry_id}’.  Otherwise- no point in putting it inside the calendar tags at all.

But- you’re getting closer!

brilliant - it works perfectly. thanks so much, Robin.

Good deal.  Queries are a very, very powerful tool once you learn a bit of mysql.  Just be careful nesting them- only do it when you understand what’s going on and decide it’s acceptable.

When i tried this, i got the extra information {omschrijving_1} from the latest post at ALL posts.
Did i forgot something? Hope you can help me people.

{entries}    
{exp:weblog:entries weblog="agenda" dynamic="off'" }

{exp:query sql="SELECT field_id_20 as omschrijving_1 FROM exp_weblog_data WHERE weblog_id='5'"}


<ul>

<li>
<span class="title">{title}</span>
<span class="desc">{omschrijving_1}</span>
</li>

</ul>

{/exp:query}
{/exp:weblog:entries}

</div>
{/entries}