Welcome Guest, Not a member yet? Register   Sign In
Simple SQL question from a beginner...
#1

[eluser]designcloud[/eluser]
Hello

Am hoping someone can help with this!

I have 3 tables

Profile (p_id, p_name, p_details)
Category (c_id, c_name)
Category Line (cl_id, c_id, p_id)

A profile can be associated with more than 1 category
A category can be associated with more and 1 profile

I want to show 4 random profiles on my sites homepage.

The div contains the profile name, as well as listing out all of the categories they are associated with.

<img>
profile name
<category name 1>, <category name 2>, <category name 3> etc etc


Any idea on how to get this to work?

Code:
SELECT *
        FROM `profile`
        INNER JOIN `category_line` ON  `profile`.`profile_id` = `category_line`.`profile_id`
        INNER JOIN `category` ON  `category`.`category_id` = `category_line`.`category_id`
        GROUP BY `profile`.`profile_id`

This is what I have so far, however its only returning the name of the profile, without the category names they are associated with. Any ideas on what SQL i need to use, and how to return the results in an array in the model.
#2

[eluser]JanDoToDo[/eluser]
Hey so well for oneyou just said your tabls were p_id, C_id etc... but in your SQL you are using "profile_id" instead of "p_id"

Assuming that isnt the problem, this worksSadis this what you want??)

SELECT p.*, c.*
FROM `profile` p
INNER JOIN `category_line` cl ON p.`p_id` = cl.`p_id`
INNER JOIN `category` c ON c.`c_id` = cl.`c_id`
ORDER BY p.`p_id`;
#3

[eluser]designcloud[/eluser]
I used this SQL...

Code:
SELECT p . * , c . *
FROM `profile` p
INNER JOIN `category_line` cl ON p.`profile_id` = cl.`profile_id`
INNER JOIN `category` c ON c.`category_id` = cl.`category_id`
ORDER BY p.`profile_id`
LIMIT 0 , 30

That worked. But now how do I now display in this format with the controller and view?

----------------------------
profile name
category name 1, category name 2, category name 3
----------------------------
profile name 2
category name 1, category name 2, category name 3
----------------------------
profile name 3
category name 3, category name 4, category name 7
----------------------------

etc.. any ideas? Im a beginner.
#4

[eluser]Aken[/eluser]
I would use two separate queries. As that stands, you're going to have a lot of redundant profile data.

In my model, I would create three functions:

1) A function to pull the individual profiles.
2) A function to pull all category info associated with a given profile ID.
3) A function that uses the two previous functions and creates an array or object that is organized and ready for easy displaying in your view. It would call #1, loop it, call #2 for each profile, and generate the data.




Theme © iAndrew 2016 - Forum software by © MyBB