+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Simple SQL question from a beginner... (/showthread.php?tid=26395)
Simple SQL question from a beginner... - El Forum - 01-12-2010
[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.
Simple SQL question from a beginner... - El Forum - 01-12-2010
[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 works
is 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`;
Simple SQL question from a beginner... - El Forum - 01-16-2010
[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 , 30That 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.
Simple SQL question from a beginner... - El Forum - 01-16-2010
[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.