Member Avatar for drewtemp

What I'm trying to do is produce a query that will use custom fields. I have the following tables:
entries

id
title
slug
user_id
section_id

entries_values

id
entry_id
sections_fields_id
value

sections_fields

id
name
value
section_id

So what I want to do is run a select beginning with entries and from there I want to be able to output the custom fields using something similar to entry.body where entry.body will be a custom field. I'm not sure if I'm being clear or not, but I'm not sure how to explain it so here's an example:
entries

id = 1
title = Test
slug = test

sections_fields

id = 1
name = body

entries_values

entry_id = 1
sections_fields_id = 1
value = This should be outputted by using entry.body

I'd like to perform this using one query instead of running multiple queries. Anyone have any guidance?

  • 2 Contributors
  • 1 Reply
  • 122 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by darkagn
Member Avatar for darkagn

Using your example, the following query should work:

-- get the required columns using aliases where necessary
SELECT e.id AS entry_id, sf.id AS sections_fields_id, sf.name AS value
-- from the entries table
FROM entries e
-- join to the section_fields table
JOIN sections_fields sf
-- describe how to join tables
ON e.section_id = sf.section_id
-- limit the returned results according to criteria?
WHERE e.id = 1 AND sf.id = 1

HTH :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.