problem getting query to work with lg member_list
Development and Programming
I’m trying to use assign members to an entry in a weblog called Reviews. I’d like to allow this using a SAEF. I think the LG memberlist extension should help with this but I’ve run into an issue with it’s usage.
I can’t get the LG Memberlist to populate a drop-down box located in my SAEF. I’d like users of the SAEF to be able to select another member of the site from a drop-down box. Other custom fields are working without a problem.
My other issue is that the query on Leevi’s website is throwing an sql error (I’m using 5) for me. I know it’s 98% my error. but I really need the info. The custom-field is being populated properly, I checked for that. but when I try to retrieve the details for that member using Leevi’s supplied query I get this error
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 ‘} AND exp_members.member_id = exp_member_data.member_id LIMIT 1’ at line 1
Here’s the query I copied from Leevi’s website
{exp:query sql="SELECT exp_members.member_id as q_member_id,
exp_members.screen_name as q_member_screen_name,
exp_members.location as q_member_location,
exp_members.avatar_filename as q_member_avatar_url,
exp_members.avatar_width as q_member_avatar_width,
exp_members.avatar_height as q_member_avatar_height,
exp_member_data.m_field_id_11 as q_member_custom_field
FROM exp_members, exp_member_data
WHERE exp_members.member_id = {your_custom_field_that_stores_the_member_id}
AND exp_members.member_id = exp_member_data.member_id
LIMIT 1"}
{q_member_screen_name}
{/exp:query}Of course I changed “your_custom_field_that_stores_the_member_id” to the name of my custom field, but no luck.
Thanks in advance for any help.
It looks like your custom field which stores the member id isn’t being replaced correctly.
What happens if you add
SELECT exp_members.member_id as q_member_id,
exp_members.screen_name as q_member_screen_name,
exp_members.location as q_member_location,
exp_members.avatar_filename as q_member_avatar_url,
exp_members.avatar_width as q_member_avatar_width,
exp_members.avatar_height as q_member_avatar_height,
exp_member_data.m_field_id_11 as q_member_custom_field
FROM exp_members, exp_member_data
WHERE exp_members.member_id = {your_custom_field_that_stores_the_member_id}
AND exp_members.member_id = exp_member_data.member_id
LIMIT 1to your template without the query tag around it?
Thanks for the reply,
I tried without the query tag and nothing happens. the query just renders like a paragraph of text.
Like this:
SELECT exp_members.member_id as q_member_id, exp_members.screen_name as q_member_screen_name, exp_members.location as q_member_location, exp_members.avatar_filename as q_member_avatar_url, exp_members.avatar_width as q_member_avatar_width, exp_members.avatar_height as q_member_avatar_height, exp_member_data.m_field_id_11 as q_member_custom_field FROM exp_members, exp_member_data WHERE exp_members.member_id = 15 AND exp_members.member_id = exp_member_data.member_id LIMIT 1
Hmm weird… It seems that the custom_member_id field is being replaced correctly with the actual member id.
Your first exmplate showed the there was a “}” in your sql which is the reason for the error. I’m not sure why this is happening as your second test demonstrates that it is working. What happens if you replace your vailable with a hard coded id?
It works with the hardcoded id value! Hope that helps. BTW again, Thanks for the help. I really appreciate this.
So I’ve tried a bunch of stuff and no go. If I use the {member_id} or some global like that the query works but of course that’s not what I’m after. Why would my custom-field shortname not work?
Update:
I got the query to work by placing single quotes around the custom field that holds the member id.
Like this:
WHERE exp_members.member_id = '{your_custom_field_that_stores_the_member_id}'Thanks for your assistance Leevi. 😊
How do you write the query when you have selected multiple authors and you want to use “IN” in your SQL query? I need (1,2,3) to be (‘1’,’2’,’3’).
I embedded the author query into another template with PHP set to “input”, then was able to do this:
<?php
$fixed = str_replace(",", "','", "{embed:extra_authors}");
?>And then the query looks like this:
{exp:query sql="SELECT exp_members.member_id AS q_member_id,
exp_members.screen_name AS q_member_screen_name,
exp_members.bio AS q_member_bio
FROM exp_members, exp_member_data
WHERE exp_members.member_id IN('<?php echo $fixed; ?>')
AND exp_members.member_id = exp_member_data.member_id"}
...stuff here...
{/exp:query}