MYSQL Query Help

#1 / Sep 09, 2011 5:42pm

Dyad

29 posts

I’m doing something wrong but I just can’t figure out what it is.

I have a query that i’m running, and it works perfect, but when i go to add in the ability to grab the url_title from exp_channel_titles the results it returns are all wonky.

here is a sample of the working query:

SELECT DISTINCT
(exp_channel_data.channel_id),
exp_channel_data.field_id_1 AS first_name,
exp_channel_data.field_id_2 AS last_name,
exp_channel_data.field_id_3 AS school,
exp_channel_data.field_id_4 AS academic_year,
exp_channel_data.field_id_5 AS subject_of_study
FROM exp_channel_data
LEFT JOIN exp_channel_titles
ON (exp_channel_data.channel_id = exp_channel_titles.channel_id)
WHERE exp_channel_titles.channel_id='2'
ORDER BY exp_channel_data.field_id_2
ASC
LIMIT 30
OFFSET 0

But it doesn’t work if I try to grab the URL title.

SELECT DISTINCT
(exp_channel_data.channel_id),
exp_channel_titles.url_title,
exp_channel_data.field_id_1 AS first_name,
exp_channel_data.field_id_2 AS last_name,
exp_channel_data.field_id_3 AS school,
exp_channel_data.field_id_4 AS academic_year,
exp_channel_data.field_id_5 AS subject_of_study
FROM exp_channel_data
LEFT JOIN exp_channel_titles
ON (exp_channel_data.channel_id = exp_channel_titles.channel_id)
WHERE exp_channel_titles.channel_id='2'
ORDER BY exp_channel_data.field_id_2
ASC
LIMIT 30
OFFSET 0

I know it’s probably something stupid. Any help would be appreciated.

#2 / Sep 09, 2011 7:31pm

Mark Bowen

12637 posts

Hi Dyad,

I just tried this in a template :

{exp:query sql="

SELECT DISTINCT
    exp_channel_data.channel_id,
    exp_channel_titles.url_title,
    exp_channel_data.field_id_1 AS first_name,
    exp_channel_data.field_id_2 AS last_name,
    exp_channel_data.field_id_3 AS school,
    exp_channel_data.field_id_4 AS academic_year,
    exp_channel_data.field_id_5 AS subject_of_study
FROM
    exp_channel_data
LEFT JOIN
    exp_channel_titles
ON
    exp_channel_data.channel_id = exp_channel_titles.channel_id
WHERE
    exp_channel_titles.channel_id='2'
ORDER BY
    exp_channel_data.field_id_2
ASC
LIMIT
    30
OFFSET
    0 
"}

{first_name}


{/exp:query}

That seems to work fine for me. Can I therefore ask you what you meant by wonky?
I may need to move this down to the Community Help forums at some point but I just want to make sure you’re not having any actual technical difficulties first.

Thanks,

Mark

#3 / Sep 13, 2011 6:32pm

Dyad
29 posts

Hi Mark,

My bad. This should be posted in community help.

Basically the problem is when i try to get the url_title it returns results like in the attached image. Otherwise i works.

You see it returning a distinct url_title but all the other values are the same. It’s not finding the distinct url_title for each entry in exp_channel_data.

#4 / Sep 13, 2011 7:06pm

Mark Bowen

12637 posts

Hi Dyad,

Oops just noticed one line which should perhaps be a little different here.

Could you try this instead for me :

{exp:query sql="

SELECT DISTINCT
    exp_channel_data.channel_id,
    exp_channel_titles.url_title,
    exp_channel_data.field_id_1 AS first_name,
    exp_channel_data.field_id_2 AS last_name,
    exp_channel_data.field_id_3 AS school,
    exp_channel_data.field_id_4 AS academic_year,
    exp_channel_data.field_id_5 AS subject_of_study
FROM
    exp_channel_data
LEFT JOIN
    exp_channel_titles
ON
    exp_channel_data.entry_id = exp_channel_titles.entry_id
WHERE
    exp_channel_titles.channel_id='2'
ORDER BY
    exp_channel_data.field_id_2
ASC
LIMIT
    30
OFFSET
    0
"}

{first_name}


{/exp:query}

The change above being this line :

ON
    exp_channel_data.entry_id = exp_channel_titles.entry_id

Does that work for you?

Mark

#5 / Sep 14, 2011 10:58am

Dyad
29 posts

That does the trick. I knew it was a dumb oversight on my part. Thanks so much.
#6 / Sep 14, 2011 11:07am

Mark Bowen
12637 posts

Hi Dyad,

Excellent. Glad that got you sorted there.

If anything else crops up then please don’t hesitate to post again as needed.

Cheers,

Mark

Thread

Dyad

Mark Bowen

Dyad

Mark Bowen

Dyad

Mark Bowen

Username

Password

Thread

MYSQL Query Help

Dyad

Mark Bowen

Dyad

Mark Bowen

Dyad

Mark Bowen

ExpressionEngine News!

Username

Password

Email Address

Display Name

Password